MySQL note statement

MySQL operations also have cyclic statement operations. On the internet, there are three standard loop methods: while loop, loop, and repeat loop. There is also a non-standard loop: Goto. We do not recommend that you use a GOTO statement because it may cause confusion.

The format of these loop statements is as follows:
While ...... Do ...... End while
Repeat ...... Until end repeat
Loop ...... End Loop
Goto.

 

Currently, I only tested the while loop:

 

1. While Loop

Delimiter $ // define the end character as $ drop procedure if exists wk; // delete an existing stored procedure create procedure wk () // create a new stored procedure begin declare I int; // variable Declaration
Set I = 1; while I <11 do // insert into user_profile (UID) values (I); set I = I + 1; end while; end $ // end Definition Statement

// Call

Delimiter; // first returns the terminator;

call wk();               

Delimter: the default MySQL Delimiter is. It tells the MySQL interpreter whether the command has been completed and whether MySQL can be executed.

Here, Delimiter is used to redefine the terminator so that statements in stored procedures are not output during definition.

The simple syntax for creating a MySQL stored procedure is:

Create procedure stored procedure name ([in | Out | inout] parameter) Begin MySQL statement end

Call the stored procedure:

Call stored procedure name () // name must be followed ()

 

Ii. repeat loop

Delimiter // drop procedure if exists looppc; Create procedure looppc () Begin declare I int;
Set I = 1;
Repeat insert into user_profile_company (UID) values (I + 1); set I = I + 1; until I> = 20
End repeat;

End //

---- Call
Call looppc ()

 

3. loop

Delimiter $ drop procedure if exists lopp; Create procedure lopp () Begin declare I int; set I = 1; lp1: loop // lp1 is the loop body name loop is the keyword insert into user_profile (UID) values (I); set I = I + 1; if I> 30 thenleave lp1; // exit the loop body end if; end loop; // end the loop end $

 

Note that the default Terminator ";" is used to end a MySQL statement. Otherwise, a syntax error occurs.