Re: Database procedures
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. = select max(id) from table_1 where utc Date; 2. delete from table_2 where id ; 3. delete from table_1 where id ; 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
Re: Database procedures
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. = select max(id) from table_1 where utc Date; 2. delete from table_2 where id ; 3. delete from table_1 where id ; 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
Database procedures
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. = select max(id) from table_1 where utc Date; 2. delete from table_2 where id ; 3. delete from table_1 where id ; 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
