How is the MySQL LOOP statement used in stored procedures?

MySQL provides us with a LOOP statement, which can repeatedly execute blocks of code and adds flexibility in using loop labels. 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 the check

Iteration statement

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

Demonstrates the use of the LOOP statement with a stored procedure. The following is a stored procedure that constructs a string with even numbers, such as 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, when we call this procedure, we can see the following results -

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)

In the above query, if the value of A is greater than 10, the loop occurs due to the LEAVE statement And terminate. If the value of A is odd, the ITERATE statement ignores everything below it and starts a new iteration. If the value of A is an even number, the block in the ELSE statement will build the string using the even number.

The above is the detailed content of How is the MySQL LOOP statement used in stored procedures?. For more information, please follow other related articles on the PHP Chinese website!