3 loops in the MySQL stored procedure

There are three standard loops in a MySQL stored procedure statement: While loop, loop loop, and repeat loop. There is also a non-standard cycle mode: GOTO, but this cycle is best not to use, it is easy to cause confusion of the program, here is a good concrete introduction.

The format of these loop statements is as follows:

• While ... Do ... END while
• REPEAT ... UNTIL END REPEAT
• LOOP ... END LOOP
• Goto

The following first uses the first loop to write an example.

Mysql> Create procedurepro10 () - begin     - DeclareIint;  - SetI=0;  -  whileI<5 Do -     Insert  intoT1 (filed)Values(i);  -     SetI=I+1;  - End  while;  - End;//

Query OK, 0 rows Affected (0.00 sec)
In this example, the INSERT and SET statements exit the loop between the while and the end while the variable i is greater than or equal to 5. Use set i=0; statement is to prevent a common error, if not initialized, I default variable value is NULL, and null and any value operation result is null.
Execute this stored procedure merged look at the results of the execution:
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 above is the result of execution, there are 5 rows of data inserted into the database, to prove that the stored procedure is written correctly ^_^.

Let's take a look at the second loop control directive REPEAT ... END REPEAT. Use the Repeat loop control statement to write the following stored procedure:

Mysql> Create procedurepro11 () - begin     - DeclareIint default 0;  -Repeat -     Insert  intoT1 (filed)Values(i);  -     SetI=I+1;  -Until I>=5     - Endrepeat;  - End;//

Query OK, 0 rows Affected (0.00 sec)
The function of this repeat loop is the same as the front while loop, except that it checks to see if the loop condition (until i>=5) is satisfied after execution, while the while is the pre-execution check (while i<5 do).
Note, however, that until i>=5 do not add semicolons, and if you add a semicolon, you are prompted with a syntax error.
Once written, call this stored procedure and view the results:
Mysql> Delete from t1//
Query OK, 5 rows Affected (0.00 sec)

Mysql> call PRO11 ()//
Query OK, 1 row Affected (0.00 sec) #虽然在这里显示只有一行数据受到影响, but with the data selected below, 5 rows of data are inserted.

Mysql> SELECT * from t1//
+ ——-+
| Filed |
+ ——-+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
+ ——-+
5 rows in Set (0.00 sec)
A row is the execution result, and the actual effect is to insert 5 rows of data, just as you would with a stored procedure written in while.

Let's take a look at the third loop control statement loop ... END LOOP. Write a stored procedure program as follows:

Mysql> Create procedurePro12 () - begin     - DeclareIint default 0;  -Loop_label:loop -     Insert  intoT1 (filed)Values(i);  -     SetI=I+1;  -     ifI>=5  Then     -leave Loop_label;  -     End if;  - EndLoop;  - End;//

Query OK, 0 rows Affected (0.00 sec)
As can be seen from the above example, it is more complicated to write the same loop control statements using loops than to write with the while and repeat: the if is added inside the loop ... The END if statement, in the IF statement added the LEAVE statement, the LEAVE statement means to leave the loop, LEAVE format is: LEAVE loop label.
After writing the stored procedure program, execute and look at the results of the operation:
Mysql> Delete from t1//
Query OK, 5 rows Affected (0.00 sec)

Mysql> Call pro12//
Query OK, 1 row Affected (0.00 sec) #虽然说只有一行数据受影响, but actually inserted 5 rows of data.

Mysql> SELECT * from t1//
+ ——-+
| Filed |
+ ——-+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
+ ——-+
5 rows in Set (0.00 sec)
The result is the same as the loop written using while, loop, which inserts 5 rows of values into the label.

Labels marking and end Labels ending label
In the use of loop, the use of the labels label, for labels can be used in while,loop,rrepeat and other loop control statements. And it's necessary to have a good understanding of lables!!
Mysql> CREATE PROCEDURE Pro13 ()
Label_1:begin
-Label_2:while 0=1 do leave label_2;end while;
-Label_3:repeat leave label_3;until 0=0 end repeat;
-Label_4:loop leave Label_4;end loop;
end;//
Query OK, 0 rows Affected (0.00 sec)
The example above shows that a statement label can be used before a begin, while, repeat, or loop statement, and the statement designator can only be used before a valid statement, so leave Label_3 means leaving a statement or conforming statement with a statement label named Label_3.
In fact, you can also use end labels to represent the label Terminator.
Mysql> CREATE PROCEDURE pro14 ()
Label_1:begin
-Label_2:while 0=1 do leave label_2;end while label_2;
-Label_3:repeat leave label_3;until 0=0 end repeat label_3;
-Label_4:loop leave label_4;end loop label_4;
-End label_1;//
Query OK, 0 rows Affected (0.00 sec)
The above is the use of the label Terminator, in fact, the end of the label is not very useful, and he must start with the definition of the name of the label, otherwise it will be an error. If you want to develop a good programming habit for others to read, you can use this label terminator.

Iterate Iteration
If you are in the iterate statement, which is the iteration statement, you must use the Leave statement. Iterate can only appear in the loop,repeat and while statements, which means "looping again", for example:
Mysql> CREATE PROCEDURE pro15 ()

Begin
-DECLARE i int default 0;
Loop_label:loop
If I=3 Then
Set i=i+1;
Iterate Loop_label;
-End If;
INSERT into T1 (filed) values (i);
Set i=i+1;
If I>=5 Then
Leave Loop_label;
-End If;
-End loop;
end;//
Query OK, 0 rows Affected (0.00 sec)
The iterate statement, like the leave statement, is also used inside the loop, which is somewhat similar to continue in the C + + language.
So how does this storage program work? First I value is 0, conditional judgment statement if I=3 then judge false, skip if segment, insert 0 into the database, and then i+1, the same back if I>=5 then judge also false, also skip, continue to loop, same insert 1 and 2; in i=3, conditional judgment statement if i=3 Then the execution i=i+1,i value is 4, then executes the iteration iterate Loop_label, that is, the statement executes to iterate Loop_label, then jumps directly to the if I=3 then judgment statement, the execution judgment, this time because I=4,if i=3 Then the judgment is false, skips the if segment, adds 4 to the table, I becomes 5, the condition determines if i>=5 then is true, executes leave Loop_label, jumps out of the loop loop, and then executes end;//, ending the entire stored procedure.
In summary, the database will insert a value: 0,1,2,4. Execute the stored procedure and view the results: |
Mysql> Delete from t1//
Query OK, 5 rows Affected (0.00 sec)

Mysql> Call pro15//
Query OK, 1 row Affected (0.00 sec)

Mysql> SELECT * from t1//
+ ——-+
| Filed |
+ ——-+
| 0 |
| 1 |
| 2 |
| 4 |
+ ——-+
4 rows in Set (0.00 sec)

As with the results of our analysis above, only numeric 0,1,2,4 are inserted.

3 loops in the MySQL stored procedure