存储过程实例2:通过把group by 放入循环，对生成的主键可以进行其他处理

create or replace procedure P_cfe_alm_ResSum_CoaLevel(i_pk_coa_set in char,
                                                      i_pk_prd_set in char,
                                                      i_coa_level  in number) is
  v_sql     clob; ---自定义SQL语句
  v_sql2    clob;
  coa_level fc_map_coa.coa_level%TYPE;
  coa_row   fc_map_coa%ROWTYPE;
  coal_row  cfe_alm_res_sum_coal%ROWTYPE;
  pks       VARCHAR2(32);
begin

  for coa_row in (select *
                    from fc_map_coa
                   where nvl(dr, 0) = 0
                     and coa_level = i_coa_level
                     and pk_coasch = i_pk_coa_set) loop
    for coal_row in (select
                     --PK_ALM_RES_SUM_ORGS
                      data_date,
                      pk_ir_gap_run,
                      pk_prd_set,
                      pk_org,
                      pk_pmt_run,
                      pk_coa_set,
                      branch_code,
                      currency,
                      asst_liab,
                      prin_pmt,
                      int_pmt,
                      cf,
                      coa_row.pk_coa as pk_rm_coa,
                      pk_rm_prd,
                      (select acct_prd
                         from fc_map_prd
                        where nvl(dr, 0) = 0
                          and pk_prd_sch = i_pk_prd_set
                          and pk_prd = pk_rm_prd) as acct_prd,
                      0 as is_total, --is_total
                      i_coa_level as coa_level, --coa_level
                      0 as dr, --dr
                      to_char(sysdate, 'yyyymmddhh24miss') as ts --ts
                       from (select data_date,
                                    pk_ir_gap_run,
                                    pk_prd_set,
                                    pk_org,
                                    pk_pmt_run,
                                    pk_coa_set,
                                    pk_rm_prd,
                                    branch_code,
                                    currency,
                                    asst_liab,
                                    sum(prin_pmt) as prin_pmt,
                                    sum(int_pmt) as int_pmt,
                                    sum(cf) as cf
                               from cfe_alm_res_sum
                              where pk_alm_res_sum in
                                    (select pk_alm_res_sum
                                       from CFE_ALM_RES_SUM_PKS)
                                and pk_rm_coa in
                                    (select pk_coa
                                       from fc_map_coa
                                      where nvl(dr, 0) = 0
                                        and code like coa_row.code || '%'
                                        and pk_coasch = i_pk_coa_set)
                              group by data_date,
                                       pk_ir_gap_run,
                                       pk_prd_set,
                                       pk_org,
                                       pk_pmt_run,
                                       pk_coa_set,
                                       pk_rm_prd,
                                       branch_code,
                                       currency,
                                       asst_liab)) loop
      pks   := sys_guid();
      v_sql := 'insert into cfe_alm_res_sum_coal
          (
          PK_ALM_RES_SUM_COALEVEL,
          data_date,
           pk_ir_gap_run,
           pk_prd_set,
           pk_org,
            pk_pmt_run,
             pk_coa_set,

             branch_code,
             currency,
             asst_liab,
             prin_pmt,
             int_pmt,
             cf,
             pk_rm_coa,
             pk_rm_prd,
             acct_prd,
             is_total,
             coa_level,
             dr,
             ts
          )
          values (
          ''' || pks || ''',
          ''' || coal_row.data_date || ''',
           ''' || coal_row.pk_ir_gap_run || ''',
           ''' || coal_row.pk_prd_set || ''',
           ''' || coal_row.pk_org || ''',
            ''' || coal_row.pk_pmt_run || ''',
             ''' || coal_row.pk_coa_set || ''',
            ''' || coal_row.branch_code || ''',
             ''' || coal_row.currency || ''',
             ''' || coal_row.asst_liab || ''',
             ''' || coal_row.prin_pmt || ''',
             ''' || coal_row.int_pmt || ''',
             ''' || coal_row.cf || ''',
            ''' || coal_row.pk_rm_coa || ''',
            ''' || coal_row.pk_rm_prd || ''',
             ''' || coal_row.acct_prd || ''',
             ''' || coal_row.is_total || ''',
             ''' || coal_row.coa_level || ''',
             ''' || coal_row.dr || ''',
             ''' || coal_row.ts || '''
          )';

      execute immediate v_sql;
      --  dbms_output.put_line(v_sql);
      --    commit;
      v_sql2 := 'insert into cfe_alm_res_sum_coal_pks (pk_alm_res_sum_coalevel) values (''' || pks ||
                ''')';
      -- dbms_output.put_line(v_sql2);
      execute immediate v_sql2;

    end loop;
    commit;

  end loop;

end P_cfe_alm_ResSum_CoaLevel;