Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use WHILE,REPEAT and LOOP loop statements in mysql stored procedures. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

MySQL provides loop statements that allow us to repeat a block of SQL code based on conditions, where there are three loop statements: WHILE,REPEAT and LOOP, which we'll take a look at next. The first is the WHILE statement to look at the syntax:

WHILE expression DO statementsEND WHILE

The WHILE loop checks the expression at the beginning of each iteration. If expressionevaluates is TRUE,MySQL, the statement between WHILE and END WHILE will be executed until expressionevaluates is FALSE. The WHILE loop is called a pre-test conditional loop because it always checks the expression of the statement before execution. Take a look at the flowchart:

When we're done, let's try to use the WHILE loop statement in the stored procedure. 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 CALL test_mysql_while_loop (); +-+ | str | +-+ | 1 row in setQuery OK, 3 row in setQuery OK, 0 rows affected

After that, let's take a look at the grammatical structure of the REPEAT loop statement:

REPEAT statements;UNTIL expressionEND REPEAT

The above sql is first executed by mysql, and the mysql evaluates the evaluation expression (expression). If the expression (expression) evaluates to FALSE, mysql will repeat the statement until the expression evaluates to TRUE. Because the REPEAT loop statement checks the expression (expression) after the statement is executed, the REPEAT loop statement is also called the post-test loop. Let's take a look at the flowchart:

When we're done, let's rewrite the test_mysql_while_loop 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; 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. Execute the above query statement and get the following results:

Mysql > CALL mysql_test_repeat_loop (); +-+ | str | +-+ | 1 row in setQuery OK, 3 rows affected, 4 rows affected, 5, +-+ 1 Personality

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

CREATE PROCEDURE test_mysql_loop () BEGIN DECLARE x INT; DECLARE str VARCHAR; 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 procedures only construct strings with even numeric strings, such as 2, 4, 6 and so on.

Place 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 x is an odd number, the ITERATE statement ignores everything below it and starts a new iteration.

If the value of x is even, the blocks in the ELSE statement will use even numbers to build the string.

Execute the above query statement and get the following results:

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

When we're done, let's take a look at two key words that control the cycle:

The LEAVE statement is used to exit the loop immediately without waiting for a check condition. LEAVE statements work just like break statements in other languages such as PHP,C/C++,java.

The ITERATE statement allows you to skip the rest of the entire code and start a new iteration. The ITERATE statement is similar to the continue statement in PHP,C/C++,Java and so on.

On how to use WHILE,REPEAT and LOOP loop statements in mysql stored procedures to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.