1、步骤:
创建游标
打开游标
获取记录
关闭游标
语法
Declare游标名称 cursorfor
Select 。。。。。from 。。。where--------不能有into关键字
注意:声明完后并不执行
Open游标名称
Fetch游标名称 into变量
Close游标名称
示例 delimiter //
create procedure p18(out para1 int)
begin
declare c cursor for
select s1 from t1;
open c;
fetch c into para1;
end //
2、与while结合
reate procedure ph19(out para1 int)
-> begin
-> declare v int default 0;
-> declare c cursor for
-> select id1 from tb3;
-> open c;
-> while v<3 do
-> fetch c into para1;
-> set v=v+1;
-> end while ;
-> close c;
-> end //
3、异常代码
SQLSTATE 代码
Erroe 1022
参见手册
内置异常描述
Declareexit handler for SQULSTATE 信息执行的动作
Declarecontinue handler for SQULSTATE 信息执行的动作
语法
mysql> create procedure ph20(out para1int)
-> begin
-> declare a boolean default ture;
-> declare c cursor for
-> select id1 from tb3;
-> declare exit handler fornot found
-> set a=false;
-> open c;
-> while a do
-> fetch c into para1;
-> end while ;
-> close c;
-> end //