声明游标
declare CURSOR_NAME curosr for SELECT_STATEMENT; 打开游标
open CURSOR_NAME; 关闭游标
close CURSOR_NAME; 从游标中取数据
fetch CURSOR_NAME into var1, var2, ...; 示例1：
delimiter // drop procedure if exists p// create procedure p() begin declare id int; declare name varchar(20); declare pass varchar(20); #声明游标 declare cur_user cursor for select * from user; #打开游标 open cur_user; #取数据 fetch cur_user into id,name,pass; select id,name,pass; fetch cur_user into id,name,pass; select id,name,pass; fetch cur_user into id,name,pass; select id,name,pass; #关闭游标 close cur_user; end// delimiter ;
示例2：
delimiter // drop procedure if exists p2// create procedure p2() begin declare id int; declare name varchar(20); declare pass varchar(20); # 记录总数 declare num int; declare i int default 0; #声明游标 declare cur_user cursor for select * from user; # 查询记录总数 select count(*) into num from user; set i := 1; #打开游标 open cur_user; #取数据 repeat fetch cur_user into id,name,pass; select id,name,pass; set i := i + 1; until i>num end repeat; #关闭游标 close cur_user; end// delimiter ; 示例3：
delimiter // drop procedure if exists p3// create procedure p3() begin declare id int; declare name varchar(20); declare pass varchar(20); # has=1 表示还有数据 declare has int default 1; #声明游标 declare cur_user cursor for select * from user; # continue handler 触发后后面的语句急促执行 # exit handler 触发后后面的语句不再执行 # undo handler 触发后前面的语句撤销, 但是 MySQL 不支持 #声明 continue handler declare continue handler for NOT FOUND set has := 0; #打开游标 open cur_user; #取数据 repeat fetch cur_user into id,name,pass; select id,name,pass; until has=0 end repeat; #关闭游标 close cur_user; end// delimiter ; 示例4：
delimiter // drop procedure if exists p4// create procedure p4() begin declare id int; declare name varchar(20); declare pass varchar(20); # has=1 表示还有数据 declare has int default 1; #声明游标 declare cur_user cursor for select * from user; #声明 continue handler declare continue handler for NOT FOUND set has := 0; #打开游标 open cur_user; fetch cur_user into id,name,pass; while has=1 do select id,name,pass; fetch cur_user into id,name,pass; end while; #关闭游标 close cur_user; end// delimiter ;
