存储过程实例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;