本文演示MySql 存储过程(procedure)中的continue和exit的区别。
1.建表
create table goods(
id int,
name varchar(20),
num smallint
);
2.插入数据
insert into goods values(1,'cat',10),(2,'dog',5);
3.定义存储过程
delimiter $$
create procedure p10()
begin
declare s_id int;
declare s_name varchar(20);
declare s_num int;
declare flag int default 1;
declare getGoods cursor for select id,name,num from goods; -- 声明游标
declare continue handler for NOT FOUND set flag := 0;
open getGoods; -- 打开游标
repeat
fetch getGoods into s_id,s_name,s_num;
select s_id,s_name,s_num;
until flag =0 end repeat;
close getGoods; -- 关闭游标
end $$
delimiter ;
4 执行存储过程
mysql> call p10();
+------+--------+-------+
| s_id | s_name | s_num |
+------+--------+-------+
| 1 | cat | 10 |
+------+--------+-------+
1 row in set (0.00 sec)
+------+--------+-------+
| s_id | s_name | s_num |
+------+--------+-------+
| 2 | dog | 5 |
+------+--------+-------+
1 row in set (0.00 sec)
+------+--------+-------+
| s_id | s_name | s_num |
+------+--------+-------+
| 2 | dog | 5 |
+------+--------+-------+
1 row in set (0.00 sec)
5.问题分析
数据库中只有2条数据,但是存储过程中却输出了3条记录,为什么?
原因是定义存储过程时,使用了修饰符continue。
declare continue handler for NOT FOUND set flag := 0;
第1条和第2条记录读取完毕后,再次在repeat循环中执行fetch语句时,会触发“NOT FOUND”事件,flag被置为0。
fetch getGoods into s_id,s_name,s_num;
但是由于声明了修饰符continue,此时程序并没有跳出repeat循环,而是继续执行下面的select语句
select s_id,s_name,s_num;
因此,第2条记录被输出了两次,共输出了3条记录。
6.解决方案
修饰符使用exit替换continue。exit会终止当前循环的执行,类似于c++中break的用法。
更新存储过程的定义。
declare exit handler for NOT FOUND set flag := 0;
执行存储过程
mysql> call p10();
+------+--------+-------+
| s_id | s_name | s_num |
+------+--------+-------+
| 1 | cat | 10 |
+------+--------+-------+
1 row in set (0.00 sec)
+------+--------+-------+
| s_id | s_name | s_num |
+------+--------+-------+
| 2 | dog | 5 |
+------+--------+-------+
1 row in set (0.00 sec)
此时,输出的结果是正确的。