DB2 存储过程中怪异的问题求指点。
create or replace procedure vaadac
(
)
language sql
result set 1
p1:begin
declare cid int;
declare csalary int;
declare cbonus int;
p2:begin
declare c1 cursor with return to caller for
select ID,SALARY,BONUS from administrator.employee1;
open c1;
fetch c1 into cid,csalary,cbonus;
while (cid<10) do
set cid=cid;
set csalary=csalary+150;
set cbonus=cbonus+1362;
fetch c1 into cid,csalary,cbonus;
end while;
end p2;
end p1
call vaadac()
我就是想着，在while 这个循环里边修改 改变查询结果集中的数据。但是这个 循环 不是很懂，那位帮忙指点下。求改后的
不胜感激涕零
这个是目前的结果集
ID SALARY BONUS
----------- ----------- -----------
11 100.00 189000.00
12 100.00 189000.00
13 100.00 189000.00
14 100.00 189000.00
15 100.00 189000.00
16 100.00 189000.00
17 100.00 189000.00
18 100.00 189000.00
19 100.00 189000.00
9 条记录已选择。
但是我是想要的是
ID SALARY BONUS
----------- ----------- -----------
1 100.00 189000.00
2 100.00 189000.00
3 100.00 189000.00
4 100.00 189000.00
5 100.00 189000.00
6 100.00 189000.00
7 100.00 189000.00
8 100.00 189000.00
9 100.00 189000.00
还有要有
set cid=cid;
set csalary=csalary+150;
set cbonus=cbonus+1362;
修改 结果集
[解决办法]
改变查询结果集，可以这样来实现，在查询的时候根据id值修改读取到的数据:
...
declare c1 cursor with return to caller for
select ID,SALARY,BONUS from administrator.employee1;
open c1;
fetch c1 into cid,csalary,cbonus;
while (cid<10) do
set cid=cid;
set csalary=csalary+150;
set cbonus=cbonus+1362;
...
--修改
...
declare c1 cursor with return to caller for
select ID,
case when id < 10 then SALARY + 150 else salary as sal,
case when id < 10 then BONUS + 1362 else bonus as bon
from administrator.employee1
...
