DB2游标循环

CREATE OR REPLACE PROCEDURE "test"

  (

   IN "@req" VARCHAR (100),

   OUT "@rsp" VARCHAR (100)

  )

   LANGUAGE SQL

   NOT DETERMINISTIC

   CALLED ON NULL INPUT

   EXTERNAL ACTION

   OLD SAVEPOINT LEVEL

   MODIFIES SQL DATA

   INHERIT SPECIAL REGISTERS

 

   P1: BEGIN

         DECLARE sqlcode int default -1;

         declare pcount int default 0;

         declare id int default 0;

         declare sql_collect VARCHAR (2000); --

         declare v_notfound int default 0; --

         declare cus CURSOR WITH RETURN TO CALLER FOR s1; --

 

     set sql_collect = 'select id from table' ; --

     prepare s1 from sql_collect; --

     open cus; --

 

--循环部分

     set v_notfound = 0; --

     while v_notfound = 0 DO

         set id = null ;

         fetch from cus into id; --

         if (sqlcode=0) then        --sqlcode属于一个内置变量，查询结果成功就会自动赋值0，这里我们可以直接这样用，不必关心怎样得0的

             --处理逻辑

         else

             set v_notfound = 1; --

         end if; --

     end while; --

--循环结束

 

     close c_operator; --

     commit ; --

end P1