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>