MySQL process control while, repeat, loop loop

This article brings you relevant knowledge about mysql. It mainly introduces the process control of MySQL stored procedures while, repeat, and loop loops. The code in the loop will run a specific number of times, or It runs until a specific condition is met and ends the loop. Let’s take a look at it. I hope it will be helpful to everyone.

Recommended learning: mysql video tutorial

Preface

• A loop is a section in a program that only Code that appears once, but may run multiple times in succession.
• The code in the loop will run a specific number of times, or the loop will end when a specific condition is met.

Loop classification:

• ##while
##repeat
loop

Loop control:

leave

Similar to break, jump out, end the current loop

iterate

Similar to continue, continue, end this loop, continue to the next one while loop

【标签:】while 循环条件 do
循环体;
end while【 标签】;

-- 创建测试表
create table user (
uid int primary_key,
username varchar ( 50 ),
password varchar ( 50 )
);

-- -------存储过程-while
delimiter $$
create procedure proc16_while1(in insertcount int)
begin
declare i int default 1;
label:while i<=insertcount do
insert into user(uid,username,`password`) values(i,concat(&#39;user-&#39;,i),&#39;123456&#39;);
set i=i+1;
end while label;
end $$
delimiter ;
call proc16_while(10);

The stored procedure syntax is fixed:

delimiter $$ create peocedure loop name (parameter) begin code end $$ delimiter;Note that when writing the loop body, you must have the first variable to define the loop, using declare i int default default value

Then dlabel:

while judgment condition do Loop body end while label； end && must have

-- -------存储过程-while + leave
truncate table user;
delimiter $$
create procedure proc16_while2(in insertcount int)
begin
declare i int default 1;
label:while i<=insertcount do
insert into user(uid,username,`password`) values(i,concat(&#39;user-&#39;,i),&#39;123456&#39;);
if i=5 then leave label;
end if;
set i=i+1;
end while label;
end $$
delimiter ;
call proc16_while2(10);

If you need to jump out of the loop internally, use if judgment, but end if is required at the end

The leave here is to jump out of the loop, relative to break

-- -------存储过程-while+iterate
truncate table user;
delimiter $$
create procedure proc16_while3(in insertcount int)
begin
declare i int default 1;
label:while i<=insertcount do
set i=i+1;
if i=5 then iterate label;
end if;
insert into user(uid,username,`password`) values(i,concat(&#39;user-&#39;,i),&#39;123456&#39;);
end while label;
end $$
delimiter ;
call proc16_while3(10);

The iterate here is relative to continue. The following code will not be executed when encountered.

repeat loop

[标签:]repeat
循环体;
until 条件表达式
end repeat [标签];

-- -------存储过程-循环控制-repeat
use mysql7_procedure;
truncate table user;
delimiter $$
create procedure proc18_repeat(in insertCount int)
begin
declare i int default 1;
label:repeat
insert into user(uid, username, password) values(i,concat('user-',i),'123456');
set i = i + 1;
until i > insertCount
end repeat label;
select '循环结束';
end $$
delimiter ;
call proc18_repeat(100);

This is relative to , the loop will be executed once no matter what, and then it will be judged internally. If it is satisfied, it will jump out directly

looploop

[标签:] loop
循环体;
if 条件表达式 then
leave [标签];
end if;
end loop;

-- -------存储过程-循环控制-loop
truncate table user;

delimiter $$
create procedure proc19_loop(in insertCount int)
begin
declare i int default 1;
label:loop
insert into user(uid, username, password) values(i,concat('user-',i),'123456');
set i = i + 1;
if i > 5
then
leave label;
end if;
end loop label;
select '循环结束';
end $$
delimiter ;
call proc19_loop(10);

The difference between this and repeat is that after execution, use leave No matter which method is used to break out of the loop, we can achieve the effect we need, but in business application scenarios, while is still relatively common.

Recommended learning:

mysql video tutorial

The above is the detailed content of MySQL process control while, repeat, loop loop. For more information, please follow other related articles on the PHP Chinese website!