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;
	repeat
		fetch getgoods into row_gid,row_name,row_num;
		select row_num,row_name;		
	until you=0 end repeat;   -- 标志是否为0

	close 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;
	repeat
		fetch getgoods into row_gid,row_name,row_num;
		select row_num,row_name;		
	until you=0 end repeat;   -- 标志是否为0

	close 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一下

	repeat
		select 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)
---------------------------------------