Database procedures

--=-0cHQmJFtFSVgujCj6ltW
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 7bit

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

--=-0cHQmJFtFSVgujCj6ltW--

Re: Database procedures

--=-mKz5PydnDis0s5Ta7Zr1
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 7bit

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
To: mysql mailing list
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

--=-mKz5PydnDis0s5Ta7Zr1--

Re: Database procedures

--001636c5b55ce1ab730497fbef3a
Content-Type: text/plain; charset=UTF-8

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: yueliangdao0608@gmail.com



2010/12/14 Machiel Richards

> 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
> To: mysql mailing list
> 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
>

--001636c5b55ce1ab730497fbef3a--