How Can MySQL LOOP statement be used in a stored procedure?

MySQL provides us a LOOP statement that executes a block of code repeatedly along with an added flexibility of using a loop label. We have the following two statements that allow us to control the loop −

LEAVE statement

It allows us to exit the loop immediately without waiting for checking the condition.

Iterate statement

It allows us to skip the entire code under it and start a new iteration.

To demonstrate the use of LOOP statement with stored procedures, the following is a stored procedure which constructs a string with even numbers like 2,4,6,8 etc. −

mysql> Delimiter //
mysql> CREATE PROCEDURE LOOP_loop()
    -> BEGIN
    -> DECLARE A INT;
    -> DECLARE XYZ VARCHAR(255);
    -> SET A = 1;
    -> SET XYZ = '';
    -> loop_label: LOOP
    -> IF A > 10 THEN
    -> LEAVE loop_label;
    -> END IF;
    -> SET A = A + 1;
    -> IF (A mod 2) THEN
    -> ITERATE loop_label;
    -> ELSE
    -> SET XYZ = CONCAT(XYZ,A,',');
    -> END IF;
    -> END LOOP;
    -> SELECT XYZ;
    -> END //
Query OK, 0 rows affected (0.07 sec)

Now, we can see the result below when we invoke this procedure −

mysql> DELIMITER ;
mysql> CALL LOOP_loop ();
+-------------+
| XYZ         |
+-------------+
| 2,4,6,8,10, |
+-------------+
1 row in set (0.04 sec)
Query OK, 0 rows affected (0.04 sec)

Here in the above query, if the value of A is greater than 10 then the loop terminated because of the LEAVE statement. If the value of A is an odd number then the ITERATE statement ignores everything below it and starts a new iteration. If the value of A is an even number then the block in the ELSE statement will build the string with even numbers.

Abhinanda Shri

e

Updated on: 22-Jun-2020

374 Views

Kickstart Your Career

Get certified by completing the course

Get Started