MySQL - Stored Procedure Language

Part:   1  2   3 

(Continued from previous part...)

Below is a sample code that uses a while loop to insert multiple rows into a table:

-- ProcedureLoop.sql
-- Copyright (c) 2004 by Dr. Herong Yang
--
DROP DATABASE IF EXISTS HyTest;
CREATE DATABASE HyTest;
USE HyTest;
--
DROP PROCEDURE IF EXISTS InitTable;
DELIMITER '/';
CREATE PROCEDURE InitTable(IN N INTEGER)
BEGIN
   DECLARE I INTEGER;
   SET I = 0;
   WHILE I < N DO
      INSERT INTO MyTable VALUES (I, RAND()*N);
      SET I = I + 1;
   END WHILE;
END/
DELIMITER ';'/
--
DROP TABLE IF EXISTS MyTable;
CREATE TABLE MyTable (ID INTEGER, Value INTEGER);
CALL InitTable(100);
--
SELECT 'My table:' AS '---';
SELECT * FROM MyTable WHERE ID < 10;

Output:

My table:
ID      Value
0       3
1       63
2       8
3       52
4       35
5       18
6       86
7       75
8       18
9       63

To break out of the current iteration and continue with the next iteration of a LOOP, REPEAT or WHILE statement, you can use the ITERATE statement:

label WHILE condition
   ......
   INTERATE label; 
   ......
END WHILE label

To break out of the current iteration and leave the LOOP, REPEAT or WHILE statement, you can use the LEAVE statement:

label WHILE condition
   ......
   LEAVE label; 
   ......
END WHILE label

A good exercise of using loop statements and break statements is to calculate prime numbers. Here is my example:

-- PrimeNumbers.sql
-- Copyright (c) 2004 by Dr. Herong Yang
--
DROP DATABASE IF EXISTS HyTest;
CREATE DATABASE HyTest;
USE HyTest;
--
DROP PROCEDURE IF EXISTS SetPrimeNunmbers;
DELIMITER '/';
CREATE PROCEDURE InitTable(IN N INTEGER)
BEGIN
   DECLARE I, P, D INTEGER;
   DECLARE PrimeFound INTEGER;
   SET I = 0;
   SET P = 2; 
   WHILE I < N DO
      SET PrimeFound = 0;
      NextPrime: LOOP 
         SET P = P + 1;          
         SET D = P DIV 2;
         WHILE D > 1 DO
            IF P MOD D = 0 THEN
               ITERATE NextPrime;
            END IF;
            SET D = D - 1;
         END WHILE;
         LEAVE NextPrime;
      END LOOP NextPrime;
      INSERT INTO PrimeNumber VALUES (I, P);
      SET I = I + 1;
   END WHILE;
END/
DELIMITER ';'/
--
DROP TABLE IF EXISTS MyTable;
CREATE TABLE PrimeNumber (ID INTEGER, Prime INTEGER);
CALL InitTable(20);
SELECT 'Table detail :' AS '---';
SELECT * FROM PrimeNumber WHERE ID < 20;

Output:

---
Table detail :
ID      Prime
0       3
1       5
2       7
3       11
4       13
5       17
6       19
7       23
8       29
9       31
10      37
11      41
12      43
13      47
14      53
15      59
16      61
17      67
18      71
19      73

(Continued on next part...)

Part:   1  2   3