存储过程实例1

create or replace procedure P_cfe_alm_ResSumTotal(pageNo in number,pageCount in number)
is

 v_sql  clob; ---自定义SQL语句
  cfe_row   cfe_alm_res_sum%ROWTYPE;
  cursor cfe_cursor is
  select * from cfe_alm_res_sum
          where   pk_alm_res_sum in (
          select pk_alm_res_sum from 
          (select pk_alm_res_sum,rownum as rm from CFE_ALM_RES_SUM_PKS)
             where rm>(pageNo-1)*pageCount and rm<=pageNo*pageCount  );
begin
  for cfe_row in cfe_cursor loop
  v_sql:='insert into cfe_alm_res_sum
  ( pk_alm_res_sum,
  acct_prd,
  asst_liab,
  branch_code,
  busi_type,
  counterparty,
  credit_rating,
  currency,
  cust_code,
  data_date,
  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,
  dr,
  ts,
  prin_pmt,
  int_pmt,
  cf)
  select
  sys_guid(),
  '''||cfe_row.acct_prd||''',
  '''||cfe_row.asst_liab||''',
  '''||cfe_row.branch_code||''',
  '''||cfe_row.busi_type||''',
  '''||cfe_row.counterparty||''',
  '''||cfe_row.credit_rating||''',
  '''||cfe_row.currency||''',
  '''||cfe_row.cust_code||''',
  '''||cfe_row.data_date||''',
  '''||cfe_row.exchange_rate||''',
  1,--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||''',
  0,--dr
  to_char(sysdate, ''yyyymmddhh24miss''),--ts
  prin_pmt,
  int_pmt,
  cf
  from
   (
     select
     sum(t.prin_pmt) as prin_pmt,
     sum(t.int_pmt) as int_pmt,
     sum(t.cf) as cf
     from cfe_alm_res_sum t
     where t.pk_rm_prd in
                      (select pk_prd
                              from fc_map_prd
                              where nvl(dr,0)=0
                              and acct_prd<='''||cfe_row.acct_prd ||'''
                              and pk_prd_sch='''||cfe_row.pk_prd_set||'''
                       )
       and t.is_total=0
       and t.data_date='''||cfe_row.data_date||'''
       and t.branch_code='''||cfe_row.branch_code||'''
       and t.currency='''||cfe_row.currency||'''
       and t.pk_ir_gap_run='''||cfe_row.pk_ir_gap_run||'''
       and t.pk_prd_set='''||cfe_row.pk_prd_set||'''
       and t.pk_org='''||cfe_row.pk_org||'''
       and t.pk_pmt_run='''||cfe_row.pk_pmt_run||'''
       and t.pk_coa_set='''||cfe_row.pk_coa_set||'''
       and t.pk_rm_coa='''||cfe_row.pk_rm_coa||'''
       and t.asst_liab='''||cfe_row.asst_liab||'''
    )';
       execute immediate v_sql;
  --  dbms_output.put_line(v_sql);

  end loop;
   commit;
end P_cfe_alm_ResSumTotal;