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

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;