Man on the moon

My reminder place

Mysql store procedure example

with 2 comments

Frank Mash has some examples of store procedure. Check it out

mysql> DROP PROCEDURE IF EXISTS build_table;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 8976
Current database: odp

Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> DELIMITER '/';
mysql> CREATE PROCEDURE build_table()
-> BEGIN
-> DECLARE i INTEGER;
-> DECLARE v INTEGER;
-> SET i = 1;
-> SET v = 100;
-> WHILE i <= 125 DO
-> INSERT into mytable VALUES (i, v);
-> SET i = i + 1;
-> SET v = v + 2;
-> END WHILE;
-> END/
DELIMITER ';'/Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ';'/
mysql> DROP TABLE IF EXISTS mytable;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE mytable (id INTEGER, value INTEGER);
Query OK, 0 rows affected (0.04 sec)

mysql> CALL build_table();
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * from mytable LIMIT 0,1;
+------+-------+
| id | value |
+------+-------+
| 1 | 100 |
+------+-------+
1 row in set (0.00 sec)


[Frank Mash blog]

Written by dinh

February 16, 2006 at 1:32 pm

2 Responses

Subscribe to comments with RSS.

  1. how to implement stroe proc to delphi…. is it need special VCL?

    WIN

    July 12, 2006 at 5:34 am

  2. hi,

    i want more store procedure for practice.
    Accutually currently i am working in one of the company at mumbai. Here i need to work on mysql 5 and need to use of store procedure. So please send to more store procedure example .

    thanks
    sanjay

    sanjay raut

    September 28, 2007 at 12:21 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s