oracle function执行dml语句的方法

琴轩5114

create or replace function F_DEALWITH_LSBD(YDYF varchar2) return varchar2 is
TYPE ref_type IS REF CURSOR;
P_YDYF varchar2(8);--年度月份
V_SQL_QUERY CLOB;--查询未返写的数据的sql
V_NSRDZDAH varchar2(30);
V_FKJG_BH varchar2(32);
V_SQL_UPDATE CLOB;--返写更新sql
cur ref_type;--动态游标
pragma autonomous_transaction;
begin
P_YDYF := YDYF;
V_SQL_QUERY := 'select mx.nsrdzdah,nvl(k.fkjg_bh,'''') from LDMT04_LSBDDZ_HZ h,fxyd_rwmx mx,fxyd_ydjgfk k
where h.dsnsrzhdah = mx.nsrdzdah
and mx.rwpc_bh is not null
and mx.rwtczt_dm = ''YXF''
and mx.hxcl_dm = ''YDCZ''
and mx.xxly_dm = ''00''
and exists(select 1 from fxfx_smmx m,FXGL_FXSB_MXKZSX kz
where m.fxmx_bh = mx.fxxx_bh and kz.mx_dm = m.mxdm and kz.tsfx_lx = ''02'')
and h.sfczcy is null
and mx.fxxx_bh = k.fxxx_bh(+)
and h.ydyf = '''||P_YDYF||'''';
open cur for V_SQL_QUERY;
FETCH cur into V_NSRDZDAH,V_FKJG_BH;
while cur%FOUND LOOP
if V_FKJG_BH='' THEN

V_SQL_UPDATE:='update LDMT04_LSBDDZ_HZ set sfczcy=''Y'',sfycl=''N''
where dsnsrzhdah='''||V_NSRDZDAH||''' and ydyf='''||P_YDYF||'''';
ELSE
V_SQL_UPDATE:='update LDMT04_LSBDDZ_HZ set (sfczcy,sfycl,BJCJS,BJCJSZNJ,
BJJYFFJ,BJJYFFJZNJ,BJDFJYFJ,BJDFJYFJZNJ,BJFK,CYYY,FKLRRY,FKRQ,BZ) =
(select ''Y'',
''Y'',
sum(decode(z.zsxm_dm,
''10109'',decode(z.zspm_dm,''101099900'',null,''101099901'',null,''101099961'',null,z.rksk),null)),
sum(decode(z.zsxm_dm,
''10109'',decode(z.zspm_dm,''101099900'',z.rksk,null),null)),
sum(decode(z.zsxm_dm,
''10381'',decode(z.zspm_dm,''103819900'',null,''103819901'',null,''103819961'',null,z.rksk),null)),
sum(decode(z.zsxm_dm,
''10381'',decode(z.zspm_dm,''103819900'',z.rksk,null),null)),
sum(decode(z.zsxm_dm,
''10386'',decode(z.zspm_dm,''103869900'',null,''103869901'',null,z.rksk),null)),           
sum(decode(z.zsxm_dm,''10386'',decode(z.zspm_dm,''103869900'',z.rksk,null),null)),
sum(decode(z.zsxm_dm||''!''||z.zspm_dm,
''10109!101099901'',z.rksk,''10109!101099961'',z.rksk,
''10381!103819901'',z.rksk,''10109!103819961'',z.rksk,
''10386!103869901'',z.rksk, null)),
'''',
max(k.YDCZRY_MC),
to_char(max(k.FKSJ),''yyyy-mm-dd''),
''''
from fxyd_ydjgfk_zs z, fxyd_ydjgfk k
where k.fkjg_bh = z.fkjg_bh(+)
and k.fkjg_bh = '''||V_FKJG_BH||'''
group by k.fkjg_bh)
where dsnsrzhdah='''||V_NSRDZDAH||''' and ydyf='''||P_YDYF||'''';
END IF;
--执行更新
EXECUTE IMMEDIATE V_SQL_UPDATE;
commit;
FETCH cur into V_NSRDZDAH,V_FKJG_BH;
END LOOP;
CLOSE cur;
return '';
end;
　　
　　

pragma autonomous_transaction;是关键加了这个提供了一个自治事物就可以在执行function时执行dml语句了
select f_dealwith_lsbd('?') from dual;