先把语句结束符设置成//
mysql> DELIMITER //
- 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)
- 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)
- 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)
实时内容请关注微信公众号,公众号与博客同时更新:程序员星星