MySQL Tutorial/Procedure Function/LOOP

Материал из SQL эксперт
Перейти к: навигация, поиск

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>