1:声明游标;
2:打开游标;
3:从一个游标中查找信息;
4:关闭游标;
5:释放游标;
创建游标
游标用declare语句创建,declare命名游标,并定义相关的select语句,根据
需要带where和其他子句。
下面定义了名为order的游标,使用可以检索所有订单的select语句
create procedure p()
begin
declare order cursor
for
select order_num from orders;
open order;
close order;
end
从游标中检索单个行(第一行)
create procedure p()
begin
declare o int;
declare order cursor
for
select order_num from orders;
open order;
fetch order into o;
close order;
end
注意:其中fetch用来检索当前行的order_num列到一个名为0的局部声明的
变量中,对检索出来的数据不做任何处理。
循环检索数据,从第一行到最后一行
create procedure p()
begin
declare o int;
declare done boolean default 0;
declare order cursor
for
select order_num from orders;
declare continue handler for SQLSTATE '02000' set done=1
open order;
repeat
fetch order into o;
util done end repeat
close order;
end