SQL/MySQL/Procedure Function/LOOP

LOOP Statement with LEAVE

 

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>

LOOP with ITERATE

 
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>

LOOP with LEAVE

 
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>

Nesting if statement with LOOP statement

 

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)

Simple LOOP

 
mysql>
mysql> delimiter $$
mysql> CREATE PROCEDURE myProc()
    -> DETERMINISTIC
    -> BEGIN
    ->   DECLARE counter INT DEFAULT 0;
    ->
    ->   simple_loop: LOOP
    ->     SET counter=counter+1;
    ->     select counter;
    ->     IF counter=10 THEN
    ->        LEAVE simple_loop;
    ->     END IF;
    ->   END LOOP simple_loop;
    ->   SELECT "I can count to 10";
    -> END$$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
mysql>
mysql> call myProc();
+---------+
| counter |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)
+---------+
| counter |
+---------+
|       2 |
+---------+
1 row in set (0.02 sec)
+---------+
| counter |
+---------+
|       3 |
+---------+
1 row in set (0.02 sec)
+---------+
| counter |
+---------+
|       4 |
+---------+
1 row in set (0.02 sec)
+---------+
| counter |
+---------+
|       5 |
+---------+
1 row in set (0.02 sec)
+---------+
| counter |
+---------+
|       6 |
+---------+
1 row in set (0.02 sec)
+---------+
| counter |
+---------+
|       7 |
+---------+
1 row in set (0.02 sec)
+---------+
| counter |
+---------+
|       8 |
+---------+
1 row in set (0.02 sec)
+---------+
| counter |
+---------+
|       9 |
+---------+
1 row in set (0.33 sec)
+---------+
| counter |
+---------+
|      10 |
+---------+
1 row in set (0.33 sec)
+-------------------+
| I can count to 10 |
+-------------------+
| I can count to 10 |
+-------------------+
1 row in set (0.33 sec)
Query OK, 0 rows affected (0.33 sec)
mysql>
mysql> DROP PROCEDURE myProc;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>