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 −
It allows us to exit the loop immediately without waiting for checking the condition.
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.