Analysis of the usage of loop statements (WHILE, REPEAT and LOOP) in MySQL stored procedures

This article uses examples to illustrate the usage of loop statements (WHILE, REPEAT and LOOP) in MySQL stored procedures. Share with you for your reference, the details are as follows:

MySQL provides loop statements that allow us to repeatedly execute a SQL code block based on conditions. There are three loop statements: WHILE, REPEAT, and LOOP. Let's look at them one by one. First, let's look at the syntax of the WHILE statement:

WHILE expression DO
  statements
END WHILE

The WHILE loop checks the expression at the beginning of each iteration. If expressionevaluates is TRUE, MySQL executes the statements between WHILE and END WHILE until expressionevaluates is FALSE. A WHILE loop is called a pre-test condition loop because it always checks the expression of the statement before executing it. Let’s look at the flow chart:

After that, let's try to use the WHILE loop statement in the stored procedure. Let's take a look at an example:

DELIMITER $$
 DROP PROCEDURE IF EXISTS test_mysql_while_loop$$
 CREATE PROCEDURE test_mysql_while_loop()
 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 above test_mysql_while_loop stored procedure, its operation is to repeatedly build the str string until the value of the x variable is greater than 5, and then use the select statement to display the final string. We should note that if the value of the x variable is not initialized, then its default value is NULL. Therefore, the condition in the WHILE loop statement will always be TRUE, and we will have an indefinite loop, which is unpredictable. Without further ado, let's call the test_mysql_while_loopstored stored procedure:

CALL test_mysql_while_loop();

Execute the above query statement and get the following results:

mysql> CALL test_mysql_while_loop();
+------------+
| str |
+------------+
| 1,2,3,4,5, |
+------------+
1 row in set
Query OK, 0 rows affected

After finishing, let's look at the grammatical structure of the REPEAT loop statement:

REPEAT
 statements;
UNTIL expression
END REPEAT

The above SQL is first executed by MySQL. After that, MySQL will evaluate the expression. If the expression is FALSE, MySQL will repeatedly execute the statement until the expression is TRUE. Because the REPEAT loop statement checks expression after executing statements, the REPEAT loop statement is also called a test-after loop. Let's look at the flow chart:

After finishing, let's use the REPEAT loop statement to rewrite the test_mysql_while_loop stored procedure:

DELIMITER $$
 DROP PROCEDURE IF EXISTS mysql_test_repeat_loop$$
 CREATE PROCEDURE mysql_test_repeat_loop()
 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 ;

We should note that there is no semicolon (;) in the UNTIL expression. Execute the above query statement and get the following results:

mysql> CALL mysql_test_repeat_loop();
+------------+
| str |
+------------+
| 1,2,3,4,5, |
+------------+
1 row in set
Query OK, 0 rows affected

Finally, let's look at an example of using the LOOP statement:

CREATE PROCEDURE test_mysql_loop()
 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;

The specific functions of the above SQL are as follows:

• The above stored procedure constructs only strings with even numbers of characters, such as 2, 4, 6, etc.
• Place a loop label loop_label before the LOOP statement.
• If the value of x is greater than 10, the loop is terminated due to the LEAVE statement.
• If the value of x is an odd number, the ITERATE statement ignores everything below it and starts a new iteration.
• If the value of x is even, then the block in the ELSE statement builds the string with an even number.

Execute the above query statement and get the following results:

mysql> CALL test_mysql_loop();
+-------------+
| str |
+-------------+
| 2,4,6,8,10, |
+-------------+
1 row in set
Query OK, 0 rows affected

Now let's look at the two key words for the control loop:

• LEAVE statement is used to exit the loop immediately without waiting for the condition to be checked. The working principle of the LEAVE statement is similar to the break statement in other languages ​​such as PHP, C/C++, Java, etc.
• The ITERATE statement allows you to skip the entire rest of the code and start a new iteration. The ITERATE statement is similar to the continue statement in PHP, C/C++, Java, etc.

Okay, that’s all for this record.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL stored procedure skills", "MySQL common function summary", "MySQL log operation skills", "MySQL transaction operation skills summary" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.