MySQL 存储过程中的循环

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/gua___gua/article/details/49995285

以下是常用的三种循环方式

WHILE……DO……END WHILE

REPEAT……UNTIL END REPEAT

LOOP……END LOOP


1、 WHILE……DO……END WHILE 示例

DROP PROCEDURE IF EXISTS p_while;
delimiter //
 
create procedure p_while()
begin
declare i int;
set i=0;
while i<5 do
    insert into t1(c1) values(i);
    set i=i+1;
end while;
end;
//

delimiter ;

mysql> delete from t1;
Query OK, 0 rows affected (0.00 sec)

mysql> call p_while();
Query OK, 1 row affected (0.59 sec)

mysql> select * from t1;
+------+
| c1   |
+------+
|    0 |
|    1 |
|    2 |
|    3 |
|    4 |
+------+
5 rows in set (0.00 sec)

2、 REPEAT……UNTIL END REPEAT 示例
DROP PROCEDURE IF EXISTS p_repeat;
delimiter //

create procedure p_repeat()
begin
declare i int default 0;
repeat
    insert into t1(c1) values(i);
    set i=i+1;
    until i>=5
end repeat;
end;
//

delimiter ;

mysql> delete from t1;
Query OK, 0 rows affected (0.00 sec)

mysql> call p_repeat();
Query OK, 1 row affected (0.28 sec)

mysql> select * from t1;
+------+
| c1   |
+------+
|    0 |
|    1 |
|    2 |
|    3 |
|    4 |
+------+
5 rows in set (0.00 sec)

3、LOOP……END LOOP示例
DROP PROCEDURE IF EXISTS p_loop;
delimiter //

create procedure p_loop()
begin
declare i int default 0;
loop_label: loop
    insert into t1(c1) values(i);
    set i=i+1;
    if i>=5 then
        leave loop_label;
    end if;
end loop;
end;
//

delimiter ;

mysql> delete from t1;
Query OK, 0 rows affected (0.00 sec)

mysql> call p_loop();
Query OK, 1 row affected (0.28 sec)

mysql> select * from t1;
+------+
| c1   |
+------+
|    0 |
|    1 |
|    2 |
|    3 |
|    4 |
+------+
5 rows in set (0.00 sec)

4、lebels标号和end labels 结束符标号

说明:下面这个例子可以看出可以在BEGIN、WHILE、REPEAT或者LOOP语句前使用语句标号。leave label_4意思为离开label_4的语句。

DROP PROCEDURE IF EXISTS p_lebel_1;
delimiter //

create procedure p_lebel_1()
label_1:begin
label_2:while 0=1 do leave label_2;end while;
label_3:repeat leave label_3;until 0=0 end repeat;
label_4:loop leave label_4;end loop;
end;
//

delimiter ;

mysql> call p_lebel_1();
Query OK, 0 rows affected (0.00 sec)

也可以使用END labels来表示标号结束符。个结束标号并不是十分有用,而且他必须和开始定义的标号名字一样,否则就会报错。

DROP PROCEDURE IF EXISTS p_lebel_2;
delimiter //

create procedure p_lebel_2()
label_1:begin
label_2:while 0=1 do leave label_2;end while label_2;
label_3:repeat leave label_3;until 0=0 end repeat label_3;
label_4:loop leave label_4;end loop label_4;
end label_1;
//

delimiter ;

mysql> call p_lebel_2();
Query OK, 0 rows affected (0.00 sec)
5、 ITERATE 迭代
如果是在ITERATE语句,即迭代语句中的话,就必须使用LEAVE语句。 ITERATE 只能出现在LOOP,REPEAT和WHILE语句中,它的意思是“再次循环”,

DROP PROCEDURE IF EXISTS p_iterate;
delimiter //

create procedure p_iterate()
begin
declare i int default 0;
loop_label:loop
    if i=3 then
        set i=i+1;
        iterate loop_label;
    end if;
    insert into t1(c1) values(i);
    set i=i+1;
    if i>=5 then
       leave loop_label;
    end if;
  end loop;
end;
//

delimiter ;

mysql> delete from t1;
Query OK, 0 rows affected (0.00 sec)

mysql> call p_iterate();
Query OK, 1 row affected (0.36 sec)

mysql> select * from t1;
+------+
| c1   |
+------+
|    0 |
|    1 |
|    2 |
|    4 |
+------+
4 rows in set (0.00 sec)






展开阅读全文

没有更多推荐了,返回首页