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

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;。