DB2 存储过程中有时候会在一个游标循环内部,还有一个游标循环。
第二个游标循环中,也会使用到第一个游标循环中的值作为输入参数,来获取第二个循环结果。
1 游标循环结构是这样的:
FOR txn as curs CURSOR WITH HOLD FOR
select fields_name from table where condition group by condition order by fields
DO
FOR conn as
select fields_name from table where txn.fields_name group by condition order by fields
DO
To Do the job in here
END FOR;
END FOR;
在 To Do the job 位置可以 组织返回结果。这样就可以嵌套循环了。
同样在To Do the job 位置还可以加入select into语句。
如下:
select count(chgpntcd), sum(kwhconsumed), sum(duration) into totalKWHours, totalKWHours, totalKWHours from ntwpt.rechtxnhis;
set Response = totalKWHours || totalKWHours || totalKWHours;
2 同样的也可以在第一个循环里面并列加入多个二级For循环:
FOR txn as curs CURSOR WITH HOLD FOR
select fields_name from table where condition group by condition order by fields
DO
//第一个并列二级循环
FOR conn as
select fields_name from table where txn.fields_name group by condition order by fields
DO
To Do the job in here
END FOR;
//第 二个并列二级循环
FOR conn as
select fields_name from table where txn.fields_name group by condition order by fields
DO
To Do the job in here
END FOR;
END FOR;