MySQL 存储过程中的循环

来源：互联网发布：网络签约作家编辑：程序博客网时间：2024/05/19 23:26

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

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()begindeclare 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()begindeclare i int default 0;repeat    insert into t1(c1) values(i);    set i=i+1;    until i>=5end 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()begindeclare 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:beginlabel_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:beginlabel_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()begindeclare 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)

0 0