MySQL stored procedure statements have three standard looping Methods: 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.
Today, let's take a look at LOOP
Mysql>
Mysql> delimiter $
Mysql>
Mysql> create procedure myProc ()
-> BEGIN
->
-> DECLARE I int;
-> SET I = 0;
-> Loop1: LOOP
-> SET I = I + 1;
-> IF I> = 10 THEN/* Last number-exit loop */
-> LEAVE loop1;
-> Elseif mod (I, 2) = 0 THEN/* Even number-try again */
-> ITERATE loop1;
-> End if;
->
-> Select concat (I, "is an odd number ");
->
-> End loop loop1;
->
->
-> END $
Query OK, 0 rows affected (0.02 sec)
Mysql>
Mysql> delimiter;
Mysql> call myProc ();
+ ------------------------------- +
| CONCAT (I, "is an odd number") |
+ ------------------------------- +
| 1 is an odd number |
+ ------------------------------- +
1 row in set (0.00 sec)
+ ------------------------------- +
| CONCAT (I, "is an odd number") |
+ ------------------------------- +
| 3 is an odd number |
+ ------------------------------- +
1 row in set (0.00 sec)
+ ------------------------------- +
| CONCAT (I, "is an odd number") |
+ ------------------------------- +
| 5 is an odd number |
+ ------------------------------- +
1 row in set (0.00 sec)
+ ------------------------------- +
| CONCAT (I, "is an odd number") |
+ ------------------------------- +
| 7 is an odd number |
+ ------------------------------- +
1 row in set (0.01 sec)
+ ------------------------------- +
| CONCAT (I, "is an odd number") |
+ ------------------------------- +
| 9 is an odd number |
+ ------------------------------- +
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
Mysql> drop procedure myProc;
Query OK, 0 rows affected (0.00 sec)
Mysql>