|
Create Procedure lymis.nzcl(in nf char(4), in cur_date date, out
outret char(8))
Language Sql
Begin
declare SQLCODE int default
0;
declare outret varchar(8);
declare v_kmh char(7);
declare v_bz char(1);
declare v_jgh char(9);
declare v_sjgh char(9);
declare v_nf char(4);
declare ck decimal(16,2) default 0.0;
declare v_dqck decimal(16,2) default 0.0;
declare v_hqck decimal(16,2) default 0.0;
declare v_tyck decimal(16,2) default 0.0;
declare v_bzjck decimal(16,2) default 0.0;
declare a varchar(200);
declare log_ret int;
declare jgh_cur cursor with hold for
select jgh,sjgh from
dbintfac.bank2_jgbmb where kzrq=cur_date;
declare ckkmdzb_cur cursor with hold for
select bz,kmh from
lymis.ckkmdzb;
set outret='00000000';
set v_nf=nf;
commit;
delete from lymis.ckkhnb where nf = v_nf;
open jgh_cur;
if SQLCODE != 0 then
set outret = 'D0000001';
return -1;
end if;
jgh_loop:
loop
fetch jgh_cur into v_jgh,
v_sjgh;
if SQLCODE != 0 then
leave
jgh_loop;
end if;
set v_dqck = 0.0;
set v_hqck = 0.0;
set v_tyck = 0.0;
set v_bzjck = 0.0;
open ckkmdzb_cur;
if SQLCODE != 0 then
set outret =
'D0000002';
rollback;
return
-1;
end if;
ckkmdzb_loop:
loop
fetch
ckkmdzb_cur into v_bz,v_kmh;
if SQLCODE !=
0 then
leave
ckkmdzb_loop;
end if;
select
ncdye into ck from dbintfac.bank2_zzrj
where
jgh=v_jgh and kmh = v_kmh and kzrq=cur_date
and
bz = '00';
if SQLCODE !=
0 then
iterate
ckkmdzb_loop;
end if;
if v_bz = '1'
then
set
v_dqck = v_dqck + ck;
elseif v_bz =
'2' then
set
v_hqck = v_hqck + ck;
elseif v_bz =
'3' then
set
v_tyck = v_tyck + ck;
elseif v_bz =
'4' then
set
v_bzjck = v_bzjck + ck;
end if;
end loop ckkmdzb_loop;
close ckkmdzb_cur;
insert into lymis.ckkhnb values
(v_jgh, v_sjgh, v_dqck, v_hqck, v_tyck, v_bzjck, v_nf);
if SQLCODE != 0 then
set outret =
'D0000006';
close
jgh_cur;
rollback;
return
-1;
end if;
end loop jgh_loop;
close jgh_cur;
commit;
end;
使用游标时要特别注意如果没有加with hold
选项,在Commit和Rollback时,该游标将被关闭。Commit 和Rollback有很多东西要注意。特别小心
哥在这上面吃亏了，希望大家以后要注意了！
|
