-- cursor 游标

/*

declare 声明; declare 游标名 cursor for select_statement;

open 找开; open 游标名

fetch 取值; fetch 游标名 into var1,var2,var3[,...]

close 关闭; 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 p13()

begin

declare cnt int default 0;

declare i int default 0 ;

declare row_gid int;

declare row_num int;

declare row_name varchar(20);

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;

--利用标误码来结束循环

--在mysql cursor中,可以把declare continue handler来操作1个越界标识

/*

declare continue handler for not found 语句;

*/

create procedure p14()

begin

declare row_gid int;

declare row_num int;

declare row_name varchar(20);

declare flag int default 1; --标识

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

declare continue handler for NOT FOUND set flag := 0;

open getgoods;

repeat

if flag!=0 then

fetch getgoods into row_gid,row_num,row_name;

select row_num,row_name;

end if;

until flag=0 end repeat;

close getgoods;

end;