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>