Loop in Stored Procedures

MySQL provides loop statements that allow you to execute a block of SQL code repeatedly based on a condition. There are three loop statements in MySQL: WHILE, REPEAT and LOOP.
We will examine each statement in more detail in the following section.

WHILE loop

The syntax of the WHILE statement is as follows:

WHILE expression DO Statements END WHILE

1 2 3

WHILE expression DO    Statements END WHILE

The WHILE loop checks the expression at the beginning of each iteration. If  the expression evaluates to TRUE,  MySQL will executes statements between WHILE and END WHILE until the expression evaluates to FALSE. The WHILE loop is called pretest loop because it checks the expression before the statements execute.
Here is an example of using the WHILE loop statement in stored procedure:

DELIMITER $$ DROP PROCEDURE IF EXISTS WhileLoopProc$$ CREATE PROCEDURE WhileLoopProc() BEGIN DECLARE x INT; DECLARE str VARCHAR(255); SET x = 1; SET str = ''; WHILE x <= 5 DO SET str = CONCAT(str,x,','); SET x = x + 1; END WHILE; SELECT str; END$$ DELIMITER ;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

DELIMITER $$ DROP PROCEDURE IF EXISTS WhileLoopProc$$ CREATE PROCEDURE WhileLoopProc()        BEGIN                DECLARE x  INT;                DECLARE str  VARCHAR(255);                SET x = 1;                SET str =  '';                WHILE x  <= 5 DO                            SET  str = CONCAT(str,x,',');                            SET  x = x + 1;                END WHILE;                SELECT str;        END$$    DELIMITER ;

In the stored procedure above:

• First, we build str string repeatedly until the value of the x variable is greater than 5.
• Then, we display the final string using the SELECT statement.

Notice that if we don’t initialize x variable, its default value is NULL. Therefore the condition in the WHILE loop statement is always TRUE and you will have a indefinite loop, which is not expected.

REPEAT loop

The syntax of the REPEAT loop statement is as follows:

REPEAT Statements; UNTIL expression END REPEAT

1 2 3 4

REPEAT Statements; UNTIL expression END REPEAT

First MySQL executes the statements, and then it evaluates the expression. If the expression evaluates to TRUE, MySQL executes the statements repeatedly until the expression evaluates to FALSE.
Because the REPEAT loop statement checks the expression after the execution of statements therefore the REPEAT loop statement is also known as post-test loop.
We can rewrite the stored procedure that uses WHILE loop statement above using the REPEAT loop statement:

DELIMITER $$ DROP PROCEDURE IF EXISTS RepeatLoopProc$$ CREATE PROCEDURE RepeatLoopProc() BEGIN DECLARE x INT; DECLARE str VARCHAR(255); SET x = 1; SET str = ''; REPEAT SET str = CONCAT(str,x,','); SET x = x + 1; UNTIL x > 5 END REPEAT; SELECT str; END$$ DELIMITER ;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

DELIMITER $$ DROP PROCEDURE IF EXISTS RepeatLoopProc$$ CREATE PROCEDURE RepeatLoopProc()        BEGIN                DECLARE x  INT;                DECLARE str  VARCHAR(255);                SET x = 1;                SET str =  '';                REPEAT                            SET  str = CONCAT(str,x,',');                            SET  x = x + 1;                UNTIL x  > 5                END REPEAT;                SELECT str;        END$$ DELIMITER ;

It is noticed that there is no delimiter semicolon (;) in the UNTIL expression.

LOOP, LEAVE and ITERATE Statements

The LEAVE statement allows you to exit the loop immediately without waiting for checking the condition. The LEAVE statement works like the  break statement in other languages such as PHP, C/C++, Java, etc.
The ITERATE statement allows you to skip the entire code under it and start a new iteration. The ITERATE statement is similar to the continue statement in PHP, C/C++, Java, etc.
MySQL also gives you a LOOP statement that allows you to execute a block of code repeatedly with an additional flexibility of using a loop label.
The following is an example of using the LOOP loop statement.

DELIMITER $$ DROP PROCEDURE IF EXISTS LOOPLoopProc$$ CREATE PROCEDURE LOOPLoopProc() BEGIN DECLARE x INT; DECLARE str VARCHAR(255); SET x = 1; SET str = ''; loop_label: LOOP IF x > 10 THEN LEAVE loop_label; END IF; SET x = x + 1; IF (x mod 2) THEN ITERATE loop_label; ELSE SET str = CONCAT(str,x,','); END IF; END LOOP; SELECT str; END$$ DELIMITER ;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

DELIMITER $$ DROP PROCEDURE IF EXISTS LOOPLoopProc$$ CREATE PROCEDURE LOOPLoopProc()        BEGIN                DECLARE x  INT;                DECLARE str  VARCHAR(255);                SET x = 1;                SET str =  '';                loop_label:  LOOP                            IF  x > 10 THEN                                LEAVE  loop_label;                            END  IF;                            SET  x = x + 1;                            IF  (x mod 2) THEN                                ITERATE  loop_label;                            ELSE                                SET  str = CONCAT(str,x,',');                            END  IF;                  END LOOP;                    SELECT str;        END$$ DELIMITER ;

• The stored procedure only constructs string with even numbers e.g., 2, 4, 6, etc.
• We put a loop_label  loop label before the LOOP statement.
• If the value of  x is greater than 10, the loop is terminated because of the LEAVE statement.
• If the value of the x is an odd number, the   ITERATE statement ignores everything below it and starts a new iteration.
• If the value of the x is an even number, the block in the ELSE statement will build the string with even numbers.