MySQL的存储过程——流程控制-while、repeat、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('user-',i),'123456');
        set i=i+1;
    end while label;
end $$
delimiter ;
call proc16_while(10);
存储过程语法是固定的：delimiter
createpeocedure循环名（参数）begin代码end
createpeocedure循环名（参数）begin代码end
delimiter;

-- -------存储过程-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('user-',i),'123456');
        if i=5 then leave label;
        end if;
        set i=i+1;
    end while label;
end $$
delimiter ;
call proc16_while2(10);

-- -------存储过程-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('user-',i),'123456');
    end while label;
end $$
delimiter ;
call proc16_while3(10);

[标签:]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;

[标签:] 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);