存储过程实例3：批量提交

create or replace procedure P_cfe_alm_ResSum_Orgs(pk_org    in char,
                                                  innercode in char) is
 
  v_sql  clob; ---自定义SQL语句
  v_sql2 clob;

  pks     VARCHAR2(32);
     cfe_row   VARCHAR2(500);
                         
                         
                        
  i_count  number;
 
begin
  i_count := 0;
  for cfe_row in (select   is_total,
         DATA_DATE,
      currency,
     pk_ir_gap_run,
     pk_prd_set,
    pk_org,
      pk_pmt_run,
     pk_coa_set,
     pk_rm_coa,
      asst_liab,
      pk_rm_prd,
      ACCT_PRD,
      
      bl_code,
      busi_type,
      COUNTERPARTY,
      CREDIT_RATING,
      CUST_CODE,
      EXCHANGE_RATE,
      ISSUER,
      ITEM_ID,
      PROD_ID,
      RP_GAP,
       sum(prin_pmt) as  prin_pmt,
     sum(int_pmt) as int_pmt,
     sum(cf) as cf
      from cfe_alm_res_sum a
     where pk_alm_res_sum in
           (select pk_alm_res_sum from cfe_alm_res_sum_pks)
           and branch_code in (select pk_org
                         from org_orgs
                         where nvl(dr,0)=0
                         and innercode like innercode||'%' 
                         )
           
           
      group by 
      is_total,
         DATA_DATE,
      currency,
     pk_ir_gap_run,
     pk_prd_set,
    pk_org,
      pk_pmt_run,
     pk_coa_set,
     pk_rm_coa,
      asst_liab,
      pk_rm_prd,
      ACCT_PRD,
      asst_liab,
      bl_code,
      busi_type,
      COUNTERPARTY,
      CREDIT_RATING,
      CUST_CODE,
      EXCHANGE_RATE,
      ISSUER,
      ITEM_ID,
      PROD_ID,
      RP_GAP) loop
    
    pks     := sys_guid();
    v_sql   := 'insert into cfe_alm_res_sum_orgs
     (
     PK_ALM_RES_SUM_ORGS,
     ACCT_PRD,
     asst_liab,
     bl_code,
     branch_code,
     busi_type,
     COUNTERPARTY,
     CREDIT_RATING,
     CURRENCY,
     CUST_CODE,
     DATA_DATE,
      dr,
     EXCHANGE_RATE,
      IS_TOTAL,
      ISSUER,
      ITEM_ID,
     PK_COA_SET,
      PK_IR_GAP_RUN,
     PK_ORG,
    PK_PMT_RUN,
     PK_PRD_SET,
     PK_RM_COA,
     PK_RM_PRD,
     PROD_ID,
     RP_GAP,
     ts,
     prin_pmt,
     int_pmt,
     cf
     )
     values (
     ''' || pks || ''',--PK_ALM_RES_SUM_ORGS
     ''' || cfe_row.ACCT_PRD || ''',
     ''' || cfe_row.asst_liab || ''',
     ''' || cfe_row.bl_code || ''',
     ''' || pk_org || ''',
     ''' || cfe_row.busi_type || ''',
     ''' || cfe_row.COUNTERPARTY || ''',
      ''' || cfe_row.CREDIT_RATING || ''',
      ''' || cfe_row.CURRENCY || ''',
      ''' || cfe_row.CUST_CODE || ''',
      ''' || cfe_row.DATA_DATE || ''',
      0,--dr
      ''' || cfe_row.EXCHANGE_RATE || ''',
      ''' || cfe_row.is_total || ''',
      ''' || cfe_row.ISSUER || ''',
      ''' || cfe_row.ITEM_ID || ''',
      ''' || cfe_row.PK_COA_SET || ''',
      ''' || cfe_row.PK_IR_GAP_RUN || ''',
     ''' || cfe_row.PK_ORG || ''',
     ''' || cfe_row.PK_PMT_RUN || ''',
     ''' || cfe_row.PK_PRD_SET || ''',
     ''' || cfe_row.PK_RM_COA || ''',
     ''' || cfe_row.PK_RM_PRD || ''',
     ''' || cfe_row.PROD_ID || ''',
     ''' || cfe_row.RP_GAP || ''',
     to_char(sysdate, ''yyyymmddhh24miss''),--ts
      ''' || cfe_row.prin_pmt || ''',
      ''' || cfe_row.int_pmt || ''',
      ''' || cfe_row.cf || '''
     )';
    --dbms_output.put_line(v_sql);
    /*if cfe_row.pk_rm_coa = '1001A310000000036Y4D' and cfe_row.pk_rm_prd='1001A310000000002M6V' then
       
      end if;*/
    execute immediate v_sql;
  
    v_sql2 := 'insert into cfe_alm_res_sum_orgs_pks(PK_ALM_RES_SUM_ORGS) values(''' || pks ||
              ''')';
    execute immediate v_sql2;
     
    
    if i_count mod 1000 = 0 then
      commit;
    end if;
  end loop;
  commit;
end P_cfe_alm_ResSum_Orgs;