MySQL Tutorial/Procedure Function/While

IF statement in a While loop

   <source lang="sql">

mysql> mysql> delimiter $$ mysql> mysql> CREATE PROCEDURE myProc()

   -> BEGIN
   ->
   ->     DECLARE i int;
   ->     SET i=1;
   ->     loop1: WHILE i<=10 DO
   ->          IF MOD(i,2)<>0 THEN /*Even number - try again*/
   ->             SELECT CONCAT(i," is an odd number");
   ->          END IF;
   ->          SET i=i+1;
   ->     END WHILE loop1;
   -> END$$

Query OK, 0 rows affected (0.00 sec) mysql> mysql> delimiter ; mysql> call myProc(); +-------------------------------+ | CONCAT(i," is an odd number") | +-------------------------------+ | 1 is an odd number | +-------------------------------+ 1 row in set (0.02 sec) +-------------------------------+ | CONCAT(i," is an odd number") | +-------------------------------+ | 3 is an odd number | +-------------------------------+ 1 row in set (0.02 sec) +-------------------------------+ | CONCAT(i," is an odd number") | +-------------------------------+ | 5 is an odd number | +-------------------------------+ 1 row in set (0.02 sec) +-------------------------------+ | CONCAT(i," is an odd number") | +-------------------------------+ | 7 is an odd number | +-------------------------------+ 1 row in set (0.02 sec) +-------------------------------+ | CONCAT(i," is an odd number") | +-------------------------------+ | 9 is an odd number | +-------------------------------+ 1 row in set (0.02 sec) Query OK, 0 rows affected (0.38 sec) mysql> drop procedure myProc; Query OK, 0 rows affected (0.00 sec) mysql></source>

While loop

   <source lang="sql">

mysql> mysql> mysql> delimiter // mysql> create procedure test_while (IN in_count INT)

   -> BEGIN
   ->     declare count INT default 0;
   ->
   ->     while count < 10 do
   ->         set count = count + 1;
   ->     end while;
   ->
   ->     select count;
   -> END
   -> //

Query OK, 0 rows affected (0.00 sec) mysql> mysql> delimiter ; mysql> mysql> call test_while(10); +-------+ | count | +-------+ | 10 | +-------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> mysql> drop procedure test_while; Query OK, 0 rows affected (0.00 sec)</source>

WHILE Statement

The WHILE statement is another mechanism to loop over a set of statements until a condition is true.

   <source lang="sql">

[begin_label:] WHILE search_condition DO

   statement_list

END WHILE [end_label]</source>