DB2 嵌套动态游标存储过程_记录

pgup12

　　DB2(V9.7) 嵌套动态游标存储过程，已经学习使用DB2近一年了，这里作一个技术记录，DB2有些资料找起来不是那么方便。贴出简易创建脚本，暂时不提供表结构数据，校验已经通过的，仅仅是证明我学习使用过^_^ ^_^

CREATE OR REPLACE  PROCEDURE "SP_NEST_DYNAMIC_CURSOR"
BEGIN
DECLARE v_counter   INTEGER DEFAULT 0;
DECLARE v_depotnme  VARCHAR(50);
DECLARE v_description VARCHAR(50);
for cursor1 as curs CURSOR WITH HOLD FOR
SELECT id, depot_name, description,is_available FROM BASE_INFO_DEPOT
do
for cursor2 as select color from base_info_train_line where id=cursor1.id
do
set v_description = cursor2.color;
end for;
insert into BASE_INFO_DAY_TYPE(id, DAY_TYPE_NAME, description)
values(cursor1.id+30,cursor1.depot_name||'_a',v_description);
SET v_counter = v_counter +1;
IF MOD(v_counter,3)=0 THEN
execute immediate 'insert into t_test values('||v_counter||','''||v_description||''')';
COMMIT;
END IF;
end for;
COMMIT;
END;