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隱式循環(huán)
   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,'隱式循環(huán)');
   end if;
   update session.tmp_hy set mc='0'||''||cur1.mc||'' where dm=cur1.dm;
   end for;
 
    p2: begin
  --簡單循環(huán)
   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,'簡單循環(huán)');
   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循環(huán)');
   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循環(huán)');
   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;