mysql存储过程循环 while/repeat/loop

朱雀 2022-05-03 08:26 266阅读 0赞

先把语句结束符设置成`//`

mysql> DELIMITER //

1.  while 条件 do … end while

mysql> create procedure proce_while()
        -> begin
        -> declare count int;
        -> set count = 0;
        -> while count < 5 do
        -> insert into onecolumn values(count);
        -> set count = count + 1;
        -> end while;
        -> end//

然后执行

mysql> call proce_while()// 
    Query OK, 1 row affected (0.01 sec)
    
    mysql> select * from onecolumn//
    +----+
    | id |
    +----+
    |  0 |
    |  1 |
    |  2 |
    |  3 |
    |  4 |
    +----+
    5 rows in set (0.00 sec)

1.  repeat xxx until 条件 end repeat  
    创建存储过程

mysql> create procedure proce_repeat()
        -> begin
        -> declare
        -> count int;
        -> set count = 10;
        -> repeat
        -> insert into onecolumn values(count);
        -> set count = count + 1;
        -> until count >=15 end repeat;
        -> end
        -> //
    Query OK, 0 rows affected (0.00 sec)

执行存储过程

mysql> call proce_repeat()//
    ERROR 1062 (23000): Duplicate entry '10' for key 'PRIMARY'
    mysql> call proce_repeat()//
    Query OK, 1 row affected (0.03 sec)
    
    mysql> select * from onecolumn where id >=10//
    +----+
    | id |
    +----+
    | 10 |
    | 11 |
    | 12 |
    | 13 |
    | 14 |
    +----+
    5 rows in set (0.00 sec)

1.  loop\_label:loop … leave loop\_lable … end loop  
    创建存储过程

mysql> create procedure proce_loop()
        -> begin
        -> declare count int;
        -> set count = 20;
        -> loop_lable:loop
        -> insert into onecolumn values(count);
        -> set count = count + 1;
        -> if(count >=25) then 
        -> leave loop_lable;
        -> end if;
        -> end loop;
        -> end//

执行存储过程：

call proce_loop()//
    Query OK, 1 row affected (0.03 sec)
    
    mysql> select * from onecolumn where id >=20//
    +----+
    | id |
    +----+
    | 20 |
    | 21 |
    | 22 |
    | 23 |
    | 24 |
    +----+
    5 rows in set (0.00 sec)

**实时内容请关注微信公众号，公众号与博客同时更新：程序员星星**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTIzMjY0NjI_size_16_color_FFFFFF_t_70]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTIzMjY0NjI_size_16_color_FFFFFF_t_70]: /images/20220503/94136b0cec9348d683987ce4d2daadfc.png