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 <[email protected]>
To: mysql mailing list <[email protected]>
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
