db2存储过程 可以使用游标循环嵌套吗_DB2存储过程中的游标和动态SQL

drop procedure report.jqx_bd_test;

CREATE procedure report.jqx_bd_test()

language sql

begin atomic

DECLARE  i  int default 1;

declare N int default 0;

declare  acct_code varchar(10);

declare  acct_sum  varchar(1000);

declare  acct_sum_mid  varchar(1000);

declare  sSql varchar(1000) ;

declare  sSql2 varchar(1000) ;

begin

DECLARE cur1 CURSOR FOR

select char(left(f1,8)),--  as acct_code ,

char(trim(REPLACE(substr(f1,10,length(f1)),'+','#' )))-- as acct_sum,

-- char(f2)-- as acct_name

from report.t_jqx_jygx_ram  p1

where p1.f2  like '承保辆数－交强险－其中：短期险－非%' and f1 like '81040068%';

OPEN  cur1;

FETCH cur1 INTO acct_code,acct_sum;--,acct_name;

while acct_code is  not  null do

set N=length(trim(acct_sum))/9;

set sSql=substr(acct_sum,1,8);

while N !=0 do

set i=i+9;

set N=N-1;

set acct_sum_mid=substr(acct_sum,10,8);

set sSql=sSql||''','''||acct_sum_mid;

end while;

set  sSql =chr(39)||sSql||chr(39);

set sSql2 = 'insert into  report.t_jqxdn_update_gj  select p1.survey_date, p1.organ_code,''code'',''name'',sum(acct_value),''222'',''1''  from report.t_jqxdn  p1 where acct_code in ('||sSql||')group by p1.survey_date,p1.organ_code';

prepare s1 from sSql2;

execute s1;

END WHILE;

end;

end;

commit;

demo：

CREATE PROCEDURE test4()  \

LANGUAGE SQL  \

BEGIN   \

DECLARE result int;  \

declare demo char(10); \

declare v_rows integer;  \

declare v_numrecords integer default 1;  \

DECLARE c1 CURSOR FOR  \

select x from t;  \

set v_rows=0;  \

select count(*) into v_numrecords from t; \

OPEN c1;  \

FETCH c1 INTO result;     \

while v_rows<=v_numrecords-1 do \

set v_rows=v_rows+1; \

insert into t1 values(result);   \

end while;  \

CLOSE c1;   \

commit;  \

set demo=demo(); \

insert into demo values(demo);  \

END

DB20000I  SQL命令成功完成