MySQL stored procedures

Lately I have been playing with MySQL stored procedures and came across a comment posted by Scot G that uses procedures to populate a table.

I thought it was a great example for beginners to see the power of MySQL procedures.

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)