mysql高级 存储过程[2] handler 的continue 和 exit 还有[undo]

上一篇中， 的多取出， 逻辑错误：

create procedure p15()begin declare row_gid int;declare row_num int;declare row_name varchar(20);  declare you int default 1;  -- 标志declare getgoods cursor for select gid,name,number from goods; declare continue handler for NOT FOUND set you := 0;  -- 如果为空设置为0  open getgoods;repeatfetch getgoods into row_gid,row_name,row_num;select row_num,row_name;until you=0 end repeat;   -- 标志是否为0close getgoods;end$/* 逻辑错误多取出一行*/create procedure p16()begin declare row_gid int;declare row_num int;declare row_name varchar(20);  declare you int default 1;  -- 标志declare getgoods cursor for select gid,name,number from goods; declare exit handler for NOT FOUND set you := 0;  -- 如果为空设置为0  open getgoods;repeatfetch getgoods into row_gid,row_name,row_num;select row_num,row_name;until you=0 end repeat;   -- 标志是否为0close getgoods;end$/*  fetch getgoods xxx。。。的语句后出发如果他continue只是在 fetch这句不再执行， 但是select这句还是在执行，所以不能让他单单跳过fetch这句得用exit这句让他全部跳过*/----------------------------------mysql> call p16()$+---------+----------+| row_num | row_name |+---------+----------+|      20 | cat      |+---------+----------+1 row in set (0.05 sec)+---------+----------+| row_num | row_name |+---------+----------+|      20 | dog      |+---------+----------+1 row in set (0.07 sec)+---------+----------+| row_num | row_name |+---------+----------+|       0 | pig      |+---------+----------+1 row in set (0.08 sec)----------------------------------

如果不用上面的方法， 改用逻辑处理也可以

除了continue，exit外， 还有一种undo handler，

-- continue 触发后是后面语句继续执行-- exit 触发后是后面语句不执行-- undo 是触发后，前面的语句撤销(但是mysql还不支持)-- 若不用exit的话怎么办？create procedure p17()begin declare row_gid int;declare row_num int;declare row_name varchar(30);declare you int default 1;declare getgoods cursor for select gid,name,number from goods;declare continue handler for not found set you :=0;open getgoods;fetch getgoods into row_gid, row_name, row_num; -- 首先fetch一下repeatselect row_name, row_num;fetch getgoods into row_gid, row_name, row_num;until you=0 end repeat;close getgoods;end$-- repeat 就像do while一样----------------------------------------mysql> call p17$+----------+---------+| row_name | row_num |+----------+---------+| cat      |      20 |+----------+---------+1 row in set (0.00 sec)+----------+---------+ | row_name | row_num |+----------+---------+| dog      |      20 |+----------+---------+1 row in set (0.03 sec)+----------+---------+| row_name | row_num |+----------+---------+| pig      |       0 |+----------+---------+1 row in set (0.04 sec)---------------------------------------