A2-06-09.MySQL Loop in Stored Procedures
转载自：http://www.mysqltutorial.org/stored-procedures-loop.aspx
MySQL Loop in Stored Procedures
Summary: in this tutorial, you will learn how to use various MySQL loop statements including WHILE, REPEAT and LOOPto run a block of code repeatedly based on a condition.
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 loop statement in more detail in the following sections.
WHILE loop
The syntax of the WHILEstatement is as follows:
|
1
2
3
|
WHILE expression DO
statements
END WHILE
|
The WHILE loop checks the expressionat the beginning of each iteration. If the expressionevaluates to TRUE, MySQL will execute statementsbetween WHILEand END WHILE until the expressionevaluates to FALSE. The WHILE loop is called pretest loop because it checks the expression before the statements execute.
The following flowchart illustrates the WHILEloop statement:
Here is an example of using the WHILE loop statement in a stored procedure:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
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 test_mysql_while_loopstored procedure above:
- First, we build
strstring repeatedly until the value of thexvariable is greater than5. - Then, we display the final string using the
SELECTstatement.
Notice that if we don’t initialize the xvariable, its default value is NULL. Therefore, the condition in the WHILEloop statement is always TRUE and you will have an indefinite loop, which is not expected.
Let’s test the test_mysql_while_loopstored procedure:
|
1
|
CALL test_mysql_while_loop();
|
REPEAT loop
The syntax of the REPEAT loop statement is as follows:
|
1
2
3
4
|
REPEAT
statements;
UNTIL expression
END REPEAT
|
First, MySQL executes the statements, and then it evaluates the expression. If the expressionevaluates to FALSE, MySQL executes the statements repeatedly until the expression evaluates to TRUE.
Because the REPEAT loop statement checks the expression after the execution of statements, the REPEATloop statement is also known as the post-test loop.
The following flowchart illustrates the REPEATloop statement:
We can rewrite the test_mysql_while_loopstored procedure that uses WHILE loop statement above using the REPEAT loop statement:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
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 ;
|
It is noted that there is no semicolon (;) in the UNTIL expression.
|
1
|
CALL mysql_test_repeat_loop();
|
LOOP, LEAVE and ITERATE statements
There are two statements that allow you to control the loop:
- The
LEAVEstatement allows you to exit the loop immediately without waiting for checking the condition. TheLEAVEstatement works like thebreakstatement in other languages such as PHP, C/C++, and Java. - The
ITERATEstatement allows you to skip the entire code under it and start a new iteration. TheITERATEstatement is similar to thecontinuestatement in PHP, C/C++, and Java.
MySQL also gives you a LOOPstatement that executes 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:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
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;
|
In this example,
- The stored procedure only constructs a string with even numbers e.g., 2, 4, and 6.
- We put a
loop_labelloop label before theLOOPstatement. - If the value of
xis greater than10, the loop is terminated because of theLEAVEstatement. - If the value of the
xis an odd number, theITERATEstatement ignores everything below it and starts a new iteration. - If the value of the
xis an even number, the block in theELSEstatement will build the string with even numbers.
In this tutorial, you have learned various MySQL loop statements that execute a block of code repeatedly based on a condition.
浙公网安备 33010602011771号