mysql 游标实例

游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次一行或者多行前进或向后浏览数据的能力。可以把游标当作一个指针，它可以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理。

--declare

--open

--fetch

--close

create procedure p12()
begin
	declare row_gid int;
	declare row_num int;
	declare row_name varchar(20);

	declare getgoods cursor for select gid,num,name from goods;
	open getgoods;
	fetch getgoods into row_gid,row_num,row_name;
	select row_num,row_name;
	close getgoods;
end$


create procedure p14()
begin
	declare row_gid int;
	declare row_num int;
	declare row_name varchar(20);

	declare cnt int default 0;
	declare i int default 0;

	declare getgoods cursor for select gid,num,name from goods;

	select count(*) into cnt from goods;

	open getgoods;

	repeat
		set i:=i+1;
		fetch getgoods into row_gid,row_num,row_name;
		select row_num,row_name;
	until i>=cnt end repeat;	
	close getgoods;
end$

########### bug 版本 ##########
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,num,name from goods;
	declare continue handler for NOT FOUND set you:=0;

	open getgoods;
	repeat
		fetch getgoods into row_gid,row_num,row_name;
		select row_num,row_name;
	until you=0 end repeat;
	close getgoods;
end$

call p15();

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,num,name from goods;
	declare exit handler for NOT FOUND set you:=0;

	open getgoods;
	repeat
		fetch getgoods into row_gid,row_num,row_name;
		select row_num,row_name;
	until you=0 end repeat;

	close getgoods;
end$