DB2存储过程4类循环简单实例

SET SCHEMA = 'DB2ADMIN';

SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","DB2ADMIN";

CREATE PROCEDURE "DB2ADMIN"."TEST_CIRCULATION" ( )
  DYNAMIC RESULT SETS 1
  LANGUAGE SQL
  NOT DETERMINISTIC
  EXTERNAL ACTION
  MODIFIES SQL DATA
  OLD SAVEPOINT LEVEL
p1: begin
declare aa varchar(10);
 declare bb varchar(10);
 declare a integer DEFAULT 0;

-- 定义一个全局临时表tmp_hy
declare global temporary table session.tmp_hy
    (
       dm varchar(10),
       mc varchar(10)       
     )
     with replace -- 如果存在此临时表，则替换
     not logged;  -- 不在日志里纪录
    -- 给临时表插入三条数据
    insert into session.tmp_hy values('1','01');
     insert into session.tmp_hy values('2','02');
    insert into session.tmp_hy values('3','03');  
  
   --for隐式循环
   for cur1 as select dm,mc from session.tmp_hy
   do
   if cur1.dm='1' or cur1.dm='2' or cur1.dm='3' then
   insert into session.tmp_hy values(cur1.mc,'隐式循环');
   end if;
   update session.tmp_hy set mc='0'||''||cur1.mc||'' where dm=cur1.dm;
   end for;
 
    p2: begin
  --简单循环
   declare cursor2 cursor for
  select dm,mc from session.tmp_hy;
  OPEN cursor2;
  FETCH_LOOP: LOOP
   FETCH cursor2 INTO aa,bb;
     IF a >= 3 THEN    -- loop until last row of the cursor
       LEAVE FETCH_LOOP;
      END IF;
    if aa='1' or aa='2' or aa='3' then
   insert into session.tmp_hy values(bb,'简单循环');
   end if;
   set a=a+1;
   END LOOP FETCH_LOOP;
   close cursor2;
   end p2;
   set a=0;
  
    p3: begin
  --进入前检查条件
   declare cursor2 cursor for
  select dm,mc from session.tmp_hy;
  OPEN cursor2;
   FETCH cursor2 INTO aa, bb;
   while a<3
   do
    if aa='1' or aa='2' or aa='3' then
   insert into session.tmp_hy values(bb,'while循环');
   end if;
   set a=a+1;
   FETCH cursor2 INTO aa, bb;
  end while;
   close cursor2;
   end p3;
   set a=0;
  
    p4: begin
  --退出前检查条件
   declare cursor2 cursor for
  select dm,mc from session.tmp_hy;
  OPEN cursor2;
   REPEAT
   FETCH cursor2 INTO aa, bb;
    if aa='1' or aa='2' or aa='3' then
   insert into session.tmp_hy values(bb,'REPEAT循环');
   end if;
   set a=a+1;
   UNTIL a>=3
  end REPEAT;
   close cursor2;
   end p4;
 
  p5: begin  
   --声明游标
 declare cursor1 cursor with return for
  select * from session.tmp_hy; 
  --游标对客户机应用程序保持打开
 open cursor1;
 
 end p5;
end p1;