What are the loop statements in mysql stored procedures?

There are three types of loop statements in mysql stored procedures: 1. WHILE loop statement, syntax "WHILE conditional expression DO loop statement END WHILE"; 2. REPEAT loop statement, syntax "REPEAT loop statement UNTIL conditional expression END REPEAT"; 3. LOOP loop statement, syntax "[begin_label:] LOOP condition and loop statement list END LOOP [end_label]".

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

MySQL provides loop statements that allow you to repeatedly execute a section of SQL code based on conditions. There are three types of loop statements in MySQL: WHILE, REPEAT and LOOP.

WHILE loop

The syntax of the WHILE statement is as follows:

WHILE expression DO
   statements
END WHILE

WHILE loop check expression in The beginning of each iteration. If expression evaluates to TRUE, MySQL will execute WHILE between evaluations of statements, END WHILE until expressionEvaluated until FALSE. The WHILE loop is called a pretest loop because it checks the expression before statements is executed.

The following flow chart illustrates the WHILE loop statement:

The following is an example of using the WHILE loop statement in a stored procedure:

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, &#39;,&#39; );
		SET x = x + 1;
	END WHILE;
	SELECT str;
END $$
DELIMITER ;

In the stored procedure above test_mysql_while_loop:

• First, we construct the string str repeatedly until the value of the x variable is greater than 5.

• Then, we use the SELECT statement to display the final string.

Please note that if we do not initialize the x variable, its default value is NULL. Therefore, the condition in the WHILE loop statement will always be TRUE and you will have an infinite loop, which is not desired.

Let’s test the test_mysql_while_loop stored procedure:

CALL test_mysql_while_loop();

Output result:

##REPEAT loop

The syntax of the REPEAT loop statement is as follows:

REPEAT
 statements
UNTIL expression
END REPEAT

First, MySQL executes the

statements, and then evaluates the expression. If expression evaluates to FALSE, MySQL statements is executed repeatedly until expression evaluates to TRUE.

Because the REPEAT loop statement

expression checks statements after execution, the REPEAT loop statement is also called a post-test loop.

The following flow chart illustrates the REPEAT loop statement:

We can test_mysql_while_loop use the WHILE loop statement to rewrite the above stored procedure using the REPEAT loop statement:

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 ;

Note that there is no semicolon (;) in the UNTIL expression.

CALL mysql_test_repeat_loop();

Output results:

LOOP, LEAVE and ITERATE statements

Yes Two statements allow you to control the loop:

• The LEAVE statement allows you to exit the loop immediately without waiting to check the condition. The LEAVE statement works similarly to the break statement in other languages ​​such as PHP, C/C and Java.

• The ITERATE statement allows you to skip the entire code below it and start a new iteration. The ITERATE statement is similar to the continue statement in PHP, C/C and Java.

MySQL also gives you the LOOP statement to repeatedly execute a block of code, with the added flexibility of using loop labels.

The following is an example of using the LOOP loop statement:

DELIMITER $$
DROP PROCEDURE IF EXISTS test_mysql_loop $$ 
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 $$
DELIMITER ;

Test it:

call test_mysql_loop();

In this example,

• The stored procedure only constructs strings with even numbers, for example, 2, 4 and 6.

• We place a loop_label loop_label before the LOOP statement.

• If the value x is greater than 10, the loop is terminated due to the LEAVE statement.

• If the value of x is odd, the ITERATE statement ignores everything below it and starts a new iteration.

• If the value of x is an even number, the block in the ELSE statement will build a string with an even number.

[Related recommendations:

mysql video tutorial]

The above is the detailed content of What are the loop statements in mysql stored procedures?. For more information, please follow other related articles on the PHP Chinese website!