Tuesday, 3 April 2012

DB2 SQL PL, looping over cursors

The simplest way to loop over a cursor in SQL PL is for loops. Consider the following example:

for i as cursor1 cursor for SELECT ID,SQLTEXT FROM  test;
    do
      insert into session.Rslt values (ID, SQLTEXT , '0');
      commit; 
 end for;



You can achieve the same result by:
declaring an exit handler for not found:
   DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET exitcode = 1;

and in the procedure code, check for the existcode to exit, something like this:
REPEAT
     fetch cursor1 into v_Tid, v_Tsql;
     insert into session.Rslt values(v_Tsql, v_Tid, '0');
     commit;
     UNTIL exitcode = 1
 END REPEAT;

No comments:

Post a Comment