MySQL loop statement instance tutorial mysqlwhile loop test

MySQL loop statement instance tutorial mysqlwhile loop test operations in the mysql database also have loop statement operations, the standard cycle mode: 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:

Delimiter $ // defines the Terminator 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 // loop body

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. (Script school www.jbxue.com)

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;
---- 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 then
Leave lp1; // exit the loop body
End if;
End LOOP; // end LOOP
End $