oracle function执行dml语句的方法

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;