该文章是我初学Mysql时总结的。仅供参考。
游标 cursor
1.cursor 游标 (不同于select的一次性全部取出)
比如一条where语句,对应了N条结果,而对应N条结果集组成的资源,取出资源的接口/句柄,就是游标
我们沿着游标,可以一次只取一行
2.基本知识
declare 声明游标 declare 游标名 cursor for select_statement
open 打开游标资源 open 游标名
fetch 取值 fetch 游标名 into var1, var2[]...
close 关闭 close 游标名
3.使用游标每次只能取出一行数据
create procedure p13()
begin
//声明临时变量类储存gid, num, name 的值,否则取出的时候没有地方存放
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_gid, row_num, row_name;
fetch getgoods into row_gid, row_num, row_name;
select row_gid, row_num, row_name;
fetch getgoods into row_gid, row_num, row_name;
select row_gid, row_num, row_name;
close getgoods;
end$
--会依次取出3组不同的数据,我们可以在每一次中实现非常复杂的运算,这是 select 所不能比的
4.游标越界标志
--游标取值越界标志
--在mysql cursor中,可以declare continue handler 来操作一个越界标示
--语法是: declare continue handler for NOT FOUND statement;
delimiter $
create procedure p17()
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;
//declare exit handeler for NOT FOUND set you := 0; //立即退出
open getgoods;
repeat
fetch getgoods into row_gid, row_num, row_name;
//一旦not found 触发了,就不可以执行select
//另外,我们可以声明处理的handler不是cntinue,而是exit
//使用 declare exit handler for NOT FOUND statement
if you then
select row_gid, row_num, row_name;
end if;
until you=0 end repeat;
close getgoods;
end$
--除了 continue, exit , 还有一种undo handler
--continue 触发以后,后面的语句继续执行
--exit 触发以后,后面的语句不再进行
--undo 触发后,前面的语句撤销(但是,目前mysql不支持)
--正确的游标逻辑
create procedure p18()
begin
declare row_gid int;
declare row_num int;
declare row_name varchar(20);
declare you int 1;
declare getgoods cursor for select gid, num, name from goods;
declare continue handeler for NOT FOUND set you := 0;
open getgoods;
fetch getgoods into row_gid, row_num, row_name;
//先取一行,然后循环显示,稍微改变一下逻辑
repeat
select row_gid, row_num, row_name;
fetch getgoods into row_gid, row_num, row_name;
until you=0 end repeat;
close getgoods;
end$