Three cycles in the MySQL Stored Procedure

The three loops in the MySQL stored procedure have three standard loops: 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 WHILE and end while, 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 function is the same as the while loop function. The difference is that it checks whether the loop condition is 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. Let's take a look at the third LOOP control statement LOOP ...... End loop. Write a stored procedure program as follows: mysql> create procedure pro12 ()-> begin-> declare I int default 0;-> loop_label: loop-> 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) as shown in the preceding example, using LOOP to write the same LOOP control statement is more complicated than using while and repeat: adding IF ...... The end if statement adds the LEAVE statement to the IF statement. The LEAVE statement means to exit the loop. The LEAVE format is the LEAVE loop label. After writing the Stored Procedure program, run it and check the running result: mysql> delete from t1 // Query OK, 5 rows affected (0.00 sec) mysql> call pro12 // Query OK, 1 row affected (0.00 sec) # Although only one row of data is affected, 5 rows of data are actually inserted. Mysql> select * from t1 // + --- + | filed | + --- + | 0 | 1 | 2 | 3 | 4 | + --- + 5 rows in set (0.00 sec) the execution result is the same as the LOOP written using the WHILE and LOOP statements. It inserts 5 rows of values into the mark. Labels and END labels when loop is used, labels Labels can be used in loop control statements such as while, loop, and rrepeat. It is also necessary to have a good understanding of lables !! Mysql> create procedure pro13 ()-> label_1: begin-> label2: while 0 = 1 do leave label2; end while;-> label3: repeat leave label3; until 0 = 0 end repeat;-> label4: loop leave label4; end loop;-> end; // Query OK, 0 rows affected (0.00 sec) the preceding example shows that the statement label can be used before BEGIN, WHILE, REPEAT, or LOOP statements. The statement label can only be used before valid statements, therefore, LEAVE label3 indicates a statement with the statement label label3 or a statement that complies with the label. In fact, you can also use END labels to indicate the END of the label. Mysql> create procedure pro14 ()-> label_1: begin-> label2: while 0 = 1 do leave label2; end while label_2;-> label3: repeat leave label3; until 0 = 0 end repeat label_3;-> label4: loop leave label4; end loop label_4;-> end label_1; // Query OK, 0 rows affected (0.00 sec) the ending mark is used above. In fact, this ending mark is not very useful and must be the same as the name of the starting label. Otherwise, an error is reported. If you want to develop a good programming habit for others to read, you can use this label Terminator. If the ITERATE iteration is in the ITERATE statement, that is, the iteration statement, you must use the LEAVE statement. ITERATE can only appear in LOOP, REPEAT, and WHILE statements. It means "LOOP 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) iterate statement is the same as the leave statement and used inside the loop, it is a bit similar to C/C ++.. How does this storage program run? First, the value of I is 0, and the condition judgment statement if I = 3 then is false. Skip the if Language Segment, insert 0 to the database, and then I + 1, similarly, if I> = 5 then is also false and skipped. Continue the loop and insert 1 and 2; when I = 3, the condition judgment statement if I = 3 then is true. Execute I = I + 1 and the I value is 4, and then execute iteration iterate looplabel ;, that is, the statement is executed to the iterate looplabel; and then directly jumps to the if I = 3 then judgment statement to execute the judgment. At this time, the if I = 4 and if I = 3 then judgment is false, skip the IF clause, add 4 to the table, change I to 5, and determine if I> = 5 then as the condition. Run leave loop_label, jump out of the loop, and then run end ;//, end the entire stored procedure. To sum up, the database inserts values 0, 1, 2, and 4. Run the stored procedure and view the result: | 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 shown in the preceding analysis, only values 0, 1, 2, and 4 are inserted.