Hi Richards .
Here are some of my codes. You can do some changes whatever you want.
http://blog.chinaunix.net/u/29134/showart_1002486.html
David Yeung, In China, Beijing.
My First Blog:http://yueliangdao0608.cublog.cn
My Second Blog:http://yueliangdao0608.blog.51cto.com
My Msn: yueliangdao0...@gmail.com
2010/12/14 Machiel Richards <machi...@rdc.co.za>
> HI All
>
> Just to give you some idea of what I have tried thus far:
>
>
>
> mysql> delimiter //
> mysql> create procedure select_delete_id (in dt date, out id bigint)
> begin select max(id) into id from archive_collections where utc < dt;
> end//
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> delimiter ;
> mysql> call select_delete_id("2010-12-13 00:00",@delete_id);
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> select @delete_id;
> +------------+
> | @delete_id |
> +------------+
> | NULL |
> +------------+
> 1 row in set (0.00 sec)
>
>
>
> The delete_id should however return the value 3823054 and not null.
>
> Regards
> Machiel
>
>
>
>
>
> -----Original Message-----
> From: Machiel Richards <machi...@rdc.co.za>
> To: mysql mailing list <mysql@lists.mysql.com>
> Subject: Database procedures
> Date: Tue, 14 Dec 2010 08:37:10 +0200
>
> HI All
>
> I am hoping that someone with more experience than me can assist
> here.
>
> I am trying to find out how to write a database procedure within
> MySQL, however I have never worked with procedures in my life and the
> resources I found on the net thus far seems greek to me...
>
>
> What we are trying to achieve is the following:
>
> There are currently 2 specific tables which contains
> archived data which are being deleted manually each day.
>
> We would like to put the delete of the data within
> a procedure and then have an automated process to execute the procedure.
>
>
> What we are using currently is the following:
>
> 1. xxxx = select max(id) from table_1
> where utc < "Date";
> 2. delete from table_2 where id < xxxx;
> 3. delete from table_1 where id < xxxx;
>
> basically we currently delete everything
> older than the start of yesterday but this might be changin at some
> point to be done hourly as the system grows.
>
>
> Does anybody have a good resource which explains
> exactly how I would be able to create this procedure or can otherwise
> assist or guide me?
>
> I would really appreciate the help as I would
> love to learn how to write procedures.
>
> Regards
> Machiel
>
