Home > Database > Mysql Tutorial > How is the MySQL LOOP statement used in stored procedures?

How is the MySQL LOOP statement used in stored procedures?

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
Release: 2023-08-26 19:13:10
forward
1362 people have browsed it

MySQL LOOP语句如何在存储过程中使用?

MySQL provides us with a LOOP statement, which can repeatedly execute blocks of code and adds flexibility in using loop labels. We have the following two statements that allow us to control the loop -

LEAVE statement

It allows us to exit the loop immediately without waiting for the check

Iteration statement

It allows us to skip the entire code underneath it and start a new iteration.

Demonstrates the use of the LOOP statement with a stored procedure. The following is a stored procedure that constructs a string with even numbers, such as 2, 4, 6, 8, etc. -

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

mysql> Delimiter //

mysql> CREATE PROCEDURE LOOP_loop()

    -> BEGIN

    -> DECLARE A INT;

    -> DECLARE XYZ VARCHAR(255);

    -> SET A = 1;

    -> SET XYZ = '';

    -> loop_label: LOOP

    -> IF A > 10 THEN

    -> LEAVE loop_label;

    -> END IF;

    -> SET A = A + 1;

    -> IF (A mod 2) THEN

    -> ITERATE loop_label;

    -> ELSE

    -> SET XYZ = CONCAT(XYZ,A,',');

    -> END IF;

    -> END LOOP;

    -> SELECT XYZ;

    -> END //

Query OK, 0 rows affected (0.07 sec)

Copy after login

Now, when we call this procedure, we can see the following results -

1

2

3

4

5

6

7

8

9

mysql> DELIMITER ;

mysql> CALL LOOP_loop ();

+-------------+

| XYZ         |

+-------------+

| 2,4,6,8,10, |

+-------------+

1 row in set (0.04 sec)

Query OK, 0 rows affected (0.04 sec)

Copy after login

In the above query, if the value of A is greater than 10, the loop occurs due to the LEAVE statement And terminate. If the value of A is odd, the ITERATE statement ignores everything below it and starts a new iteration. If the value of A is an even number, the block in the ELSE statement will build the string using the even number.

The above is the detailed content of How is the MySQL LOOP statement used in stored procedures?. For more information, please follow other related articles on the PHP Chinese website!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template