循环和游标例子

31．Db2的循环控制语句loop用法?
答：loop例子：
open c1;
set at_end = 0;
set numrec = 0;
fetch_loop: 1
loop
fetch c1 into proc_cusnbr, proc_cuscrd;
if sqlcode = 0 then
set proc_cuscrd = proc_cuscrd * 1.2;
update ordapplib.customer
set cuscrd = proc_cuscrd
where current of c1;
set numrec = numrec + 1;
else
leave fetch_loop; 2
end if;
end loop fetch_loop; 3
close c1;

32．Db2的循环控制语句while用法?
答：while 例子：
open c1;
set at_end = 0;
set numrec = 0;
while at_end = 0 do
fetch c1 into proc_cusnbr, proc_cuscrd;
if sqlcode = 0 then
set proc_cuscrd = proc_cuscrd * 1.2;
update ordapplib.customer
set cuscrd = proc_cuscrd
where current of c1;
set numrec = numrec + 1;
else
set at_end = 1;
end if;
end while;
close c1;
myfriend2010 (2007-8-30 12:54:02)
33．Db2的循环控制语句repeat用法？
答：repeat例子
set numrec = 0;
fetch_loop:
repeat
fetch c1 into proc_cusnbr, proc_cuscrd;
if sqlcode = 0 then
set proc_cuscrd = proc_cuscrd * 1.2;
update ordapplib.customer
set cuscrd = proc_cuscrd
where current of c1;
set numrec = numrec + 1;
end if;
until sqlcode <> 0
end repeat fetch_loop;

34．Db2的循环控制语句for用法？
答：for 例子
for each_record as
cursor1 cursor for
select cusnbr, cuscrd from ordapplib.customer
do
update ordapplib.customer
set cuscrd = cuscrd * 1.1
where current of cursor1;
end for;
myfriend2010 (2007-8-30 12:54:41)
34．循环控制Break,Continue在Db2用法？
答：在db2中 Break 转换为leave lab, Continue 转换为ITERATE lab
等同于GOTO语句
举例如下：
============== leave 例子
open c1;
set at_end = 0;
set numrec = 0;
fetch_loop: 1
loop
fetch c1 into proc_cusnbr, proc_cuscrd;
if sqlcode = 0 then
set proc_cuscrd = proc_cuscrd * 1.2;
update ordapplib.customer
set cuscrd = proc_cuscrd
where current of c1;
set numrec = numrec + 1;
else
leave fetch_loop; 2
end if;
end loop fetch_loop;
close c1;
============ iterate 例子
begin
open c1;
ins_loop: 1
loop
fetch c1 into v_dept, v_deptname, v_admdept;
if at_end = 1 then
leave ins_loop;
elseif v_dept = 'd11' then
iterate ins_loop; 2
end if;
insert into sampledb02.deptnew (deptno, deptname, admrdept)
values (v_dept, v_deptname, v_admdept);
end loop;
close c1;
end;
35．在Db2存储过程中使用滚动游标（scrollable cursor）？
答：例子
create procedure mymax
( in fld_name char(30),
in file_name char(128),
inout max_value integer)
language sql
begin atomic
declare sql_stmt char(256);
declare not_found
condition for '02000';
declare c1 dynamic scroll cursor for s1; -- 声明动态滚动游标
declare continue handler for not_found
set max_value = null;
set sql_stmt = 'select ' || fld_name || ' from ' || file_name ||
' order by 1'; --组合sql语句
prepare s1 from sql_stmt;
open c1;
fetch last from c1 into max_value; --转到最后行
close c1;
end
滚动游标的使用例子：（rpg）
exec sql begin declare section;
char fld_name[ 30 ];
char file_name[ 128 ];
integer max_value;
short ind3;
exec sql end declare section;
then the indicator variable is used in the call statement:
exec sql
call mymax( :fld_name, :file_name, :max_value :ind3);
myfriend2010 (2007-8-30 12:55:05)
36．db2中存储过程中使用动态游标（dynamic cursor）？
答：使用PREPARE ， EXECUTE ，EXECUTE IMMEDIATE语句
例子：
create procedure dynsqlsample()
language sql
begin
declare stmt varchar(256);
set stmt = 'update employee set salary = salary * 1.1 where empno = ?'; 1
prepare s1 from stmt;
ins_loop:
for each_department as
c1 cursor for
select mgrno from department where mgrno is not null
do
execute s1 using mgrno;
end for;
end;
EXECUTE IMMEDIATE statement 例子：
prepare s1 from ‘update employee set salary = salary * 1.1 where
empno in (select distinct mgrno from department where mgrno is not null);
execute s1;
等同于
execute immediate ??update employee set salary = salary * 1.1 where
empno in (select distinct mgrno from department where mgrno is not null);

最基本动态游标语句
...
declare stmt varchar(256);

declare cursor1 cursor for preparedstatement;
...
set stmt = 'select column1, column2, column3 from tbl1';
prepare preparedstatement from s1;