MySQL Stored Procedure cyclic control commands

There are three standard LOOP methods in MySQL stored procedure statements: while loop, LOOP, and repeat loop. There is also a non-standard loop method: GOTO, but it is best to use this loop method, it is easy to cause program confusion, here is a good specific introduction.

The format of these loop statements is as follows:
WHILE ...... DO ...... END WHILE
REPEAT ...... UNTIL END REPEAT
LOOP ...... END LOOP
GOTO.

The following is an example of using the first loop.
Mysql> create procedure pro10 ()
-> Begin
-> Declare I int;
-> Set I = 0;
-> While I <5 do
-> Insert into t1 (filed) values (I );
-> Set I = I + 1;
-> End while;
-> End ;//
Query OK, 0 rows affected (0.00 sec)
In this example, the INSERT and SET statements are between the WHILE and end while statements, and exit the loop when the variable I is greater than or equal to 5. The set I = 0 statement is used to prevent a common error. If Initialization is not performed, the default I variable value is NULL, and the result of NULL and any value operation is NULL.
Run the stored procedure and check the execution result:
Mysql> delete from t1 //
Query OK, 0 rows affected (0.00 sec)
Mysql> call pro10 ()//
Query OK, 1 row affected (0.00 sec)
Mysql> select * from t1 //
+ --- +
| Filed |
+ --- +
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
+ --- +
5 rows in set (0.00 sec)
The preceding figure shows the execution result. Five rows of data are inserted into the database, proving that the stored procedure is correctly written.

Let's take a look at the second loop control command REPEAT ...... End repeat. Use the REPEAT loop control statement to write the following stored procedure:
Mysql> create procedure pro11 ()
-> Begin
-> Declare I int default 0;
-> Repeat
-> Insert into t1 (filed) values (I );
-> Set I = I + 1;
-> Until I> = 5
-> End repeat;
-> End ;//
Query OK, 0 rows affected (0.00 sec)
The REPEAT loop has the same function as the WHILE loop. The difference is that it checks whether the loop conditions are met after execution (until I> = 5 ), WHILE while is the pre-check (WHILE I <5 do ).
However, note that until I> = 5 is not followed by a plus sign. If the plus sign is used, a syntax error is prompted.
After writing, call the stored procedure and view the result:
Mysql> delete from t1 //
Query OK, 5 rows affected (0.00 sec)

Mysql> call pro11 ()//
Query OK, 1 row affected (0.00 sec) # Although only one row of data is affected, five rows of data are inserted if data is selected below.

Mysql> select * from t1 //
+ --- +
| Filed |
+ --- +
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
+ --- +
5 rows in set (0.00 sec)
A row is the execution result. The actual function is to insert 5 rows of data in the same way as the stored procedure written in while.

1 2 3 Next Page