MySQL Tutorial/Procedure Function/LOOP
Содержание
LOOP Statement with LEAVE
The LOOP statement creates a loop that will run until the LEAVE statement is invoked.
Optional to the LOOP is a label.
A label is a name and a colon prefixed to the LOOP statement.
<source lang="sql">
mysql> mysql> DELIMITER // mysql> CREATE FUNCTION myFunction(quantity INT(10)) RETURNS INT(10)
-> BEGIN -> -> increment: LOOP -> -> IF quantity MOD 10 < 1 THEN -> LEAVE increment; -> END IF; -> -> SET quantity = quantity - 1; -> -> END LOOP increment; -> -> RETURN quantity; -> -> END -> //
Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; mysql> mysql> select myFunction(10); +----------------+ | myFunction(10) | +----------------+ | 10 | +----------------+ 1 row in set (0.00 sec) mysql> mysql> mysql> drop function myFunction; Query OK, 0 rows affected (0.00 sec) mysql></source>
LOOP with ITERATE
<source lang="sql">
mysql> mysql> delimiter $$ mysql> mysql> CREATE PROCEDURE myProc()
-> BEGIN -> -> DECLARE i int; -> SET i=0; -> loop1: LOOP -> SET i=i+1; -> IF i>=10 THEN /*Last number - exit loop*/ -> LEAVE loop1; -> ELSEIF MOD(i,2)=0 THEN /*Even number - try again*/ -> ITERATE loop1; -> END IF; -> -> SELECT CONCAT(i," is an odd number"); -> -> END LOOP loop1; -> -> -> END$$
Query OK, 0 rows affected (0.02 sec) mysql> mysql> delimiter ; mysql> call myProc(); +-------------------------------+ | CONCAT(i," is an odd number") | +-------------------------------+ | 1 is an odd number | +-------------------------------+ 1 row in set (0.00 sec) +-------------------------------+ | CONCAT(i," is an odd number") | +-------------------------------+ | 3 is an odd number | +-------------------------------+ 1 row in set (0.00 sec) +-------------------------------+ | CONCAT(i," is an odd number") | +-------------------------------+ | 5 is an odd number | +-------------------------------+ 1 row in set (0.00 sec) +-------------------------------+ | CONCAT(i," is an odd number") | +-------------------------------+ | 7 is an odd number | +-------------------------------+ 1 row in set (0.01 sec) +-------------------------------+ | CONCAT(i," is an odd number") | +-------------------------------+ | 9 is an odd number | +-------------------------------+ 1 row in set (0.01 sec) Query OK, 0 rows affected (0.01 sec) mysql> drop procedure myProc; Query OK, 0 rows affected (0.00 sec) mysql></source>
LOOP with LEAVE
<source lang="sql">
mysql> mysql> delimiter $$ mysql> mysql> CREATE PROCEDURE myProc()
-> BEGIN -> DECLARE i int; -> SET i=1; -> myloop: LOOP -> SET i=i+1; -> IF i=10 THEN -> LEAVE myloop; -> END IF; -> END LOOP myloop; -> SELECT "I can count to 10"; -> -> END$$
Query OK, 0 rows affected (0.00 sec) mysql> mysql> delimiter ; mysql> mysql> call myProc(); +-------------------+ | I can count to 10 | +-------------------+ | I can count to 10 | +-------------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> mysql> drop procedure myProc; Query OK, 0 rows affected (0.00 sec) mysql></source>
Nesting if statement with LOOP statement
<source lang="sql">
mysql> mysql> delimiter // mysql> mysql> create procedure increment (IN in_count INT)
-> BEGIN -> declare count INT default 0; -> -> increment: loop -> set count = count + 1; -> if count < 20 then -> iterate increment; -> end if; -> if count > in_count then -> leave increment; -> end if; -> end loop increment; -> select count; -> END -> //
Query OK, 0 rows affected (0.00 sec) mysql> mysql> delimiter ; mysql> call increment(3); +-------+ | count | +-------+ | 20 | +-------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> mysql> drop procedure increment; Query OK, 0 rows affected (0.00 sec)</source>
Simple LOOP
<source lang="sql">
[begin_label:] LOOP
statement_list
END LOOP [end_label]</source>