oracle 存储过程实例-1
create or replace
PROCEDURE misr016_RepaymentPlanSummary IS
/******************************************************************************
   NAME:       misr016_RepaymentPlanSummary
   PURPOSE:

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        2006-3-21   lulineng       1. Created this procedure.

   NOTES:
         only insert increment
         insert all tpa records
         then update the paid amount by caculating the CT transactions from PPR

******************************************************************************/

  TYPE TPA IS RECORD(
        ID       TMR016_REPAYMENTPLANSUMMARY.ID%TYPE,
        FREQ     TT05_TPA.CT05_INST_FREQ%TYPE,
        INST     TT05_TPA.CT05_NR_INST%TYPE,
        FIN      TT05_TPA.CR12_ACCOUNTS_NUMBER%TYPE,
        TAXYEAR  TT05_TPA.CT05_TAX_YEAR%TYPE,
        PRDFROM  TT05_TPA.CT05_PRD_FROM%TYPE,
        PRDTO    TT05_TPA.CT05_PRD_TO%TYPE
   );
  v_tpa          TPA;

TYPE vCurType  IS REF CURSOR;
  vSQL           VARCHAR2(1000);
  vPick          vCurType;

  v_Month         NUMBER(10,2);
  v_PaidAMT       NUMBER(19,2);

BEGIN

      --insert new tpa
      insert into tmr016_repaymentplansummary
        (id,
         cs05_office_id,
         cg02_tax_type,
         debt_amount,
         create_date,
         tpa_no)
        select mis_sequence.nextval,
               b.cs05_office_id,
               b.cg02_tax_type,
               a.ct05_tpa_amt,
               a.ct05_create_date,
               a.ct05_tpa_no
        from tt05_tpa a, tr12_accounts_tin b
        where a.cr12_accounts_number = b.cr12_accounts_number(+)
               and not exists (select 1
     &nbs