MySQL--存储过程的循环语句代码笔记--while、repeat和loop

mysql> delimiter $$
mysql> create table testB
    -> (
    -> id int(6) not null
    -> ) $$
Query OK, 0 rows affected (0.86 sec)

mysql> create procedure pro_test5()
    -> begin
    -> declare i int;
    -> set i = 1;
    -> while i <= 5 do                            //使用while
    -> insert into testB values(i);
    -> set i = i + 1;
    -> end while;
    -> end $$
Query OK, 0 rows affected (0.03 sec)

mysql> call pro_test5() $$
Query OK, 1 row affected (0.16 sec)

mysql> select * from testB $$
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.02 sec)

mysql> delete from testB $$                        //删除表数据
Query OK, 5 rows affected (0.31 sec)

mysql> select * from testB $$
Empty set (0.00 sec)

mysql> create procedure pro_test6()
    -> begin
    -> declare i int default 0;
    -> repeat                                        //使用repeat
    -> insert into testB values(i);
    -> set i = i + 1;
    -> until i >= 5
    -> end repeat;
    -> end $$
Query OK, 0 rows affected (0.02 sec)

mysql> call pro_test6() $$
Query OK, 1 row affected (0.37 sec)

mysql> select * from testB $$
+----+
| id |
+----+
|  0 |
|  1 |
|  2 |
|  3 |
|  4 |
+----+
5 rows in set (0.00 sec)

mysql> truncate testB $$                         //清空表数据
Query OK, 0 rows affected (0.02 sec)

mysql> select * from testB $$
Empty set (0.00 sec)

mysql> create procedure pro_test7()
    -> begin
    -> declare i int default 0;
    -> loop_test: loop                            //使用loop
    -> insert into testB values(i);
    -> set i = i + 2;
    -> if i >= 5 then
    -> leave loop_test;
    -> end if;
    -> end loop;
    -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> call pro_test7() $$
Query OK, 1 row affected (0.02 sec)

mysql> select * from testB $$
+----+
| id |
+----+
|  0 |
|  2 |
|  4 |
+----+
3 rows in set (0.00 sec)

mysql>