您的位置：程序门 -> db2 -> 数据库开发

db2循环游标，如何使循环能够继续

[收藏此页] [打印本页]选择字色：背景色：字体：[大][中][小]

db2循环游标，如何使循环能够继续[已结贴,结贴人:cf7852]

发表于：2007-12-14 14:08:03 楼主

create procedure bump_salary_iftest （in deptnumber smallint)
language sql
begin
declare sqlstate char（5);
declare v_salary double;
declare v_years smallint;
declare v_id smallint;
declare at_end int default 0;
declare not_found condition for sqlstate '02000';

declare c1 cursor for
select id, cast（salary as double), years
from staff;
declare continue handler for not_found
set at_end = 1;

open c1;
fetch c1 into v_id, v_salary, v_years;
while at_end = 0 do

update staff
set salary = 2150 * v_years
where id = -1;

fetch c1 into v_id, v_salary, v_years;
end while;
close c1;
end

请问，我想让这个循环执行完，但是由于set salary = 2150 * v_years
where id = -1 更新的数据为0条，at_end变量就被置为1，从而导程序跳出循环，我如何做，能够及时更新数据为0行
，而循环还能继续下去呢，请高手指点

发表于：2007-12-14 15:38:321楼得分:20

可以先计算循环次数，根据这个数值进行循环
结果：
d:\> db2 call bump_salary_iftest（10,?)

输出参数的值
--------------------------
参数名： ireturn
参数值： 35

返回状态 = 0

create procedure bump_salary_iftest（in deptnumber smallint,out ireturn int)
language sql
begin
declare sqlstate char（5);
declare v_salary double;
declare v_years smallint;
declare v_id smallint;
declare at_end int default 0;
declare not_found condition for sqlstate '02000';

declare v_temp int;

declare c1 cursor for
select id, cast（salary as double), years
from staff;
declare continue handler for not_found
set at_end = 1;

-- get the loop number
select count（*) into v_temp from staff;
set ireturn =0;
open c1;
fetch c1 into v_id, v_salary, v_years;
while v_temp> 0 do
-- out the ireturn to check the result
set ireturn =ireturn+1;
update staff
set salary = 2150 * v_years
where id = -1;
fetch c1 into v_id, v_salary, v_years;
set v_temp = v_temp-1;
end while;
close c1;
end@

其他资讯