oracle存储过程cursor

/*遍历gws_payment_notice表,根据Wfr_No查询gws_write_off_record
根据gws_write_off_record的source_id查询gws_payment_notice_detail,
如果不存在,则对gws_payment_notice_detail作添加操作,对gws_payment_notice作更新操作*/
create or replace procedure proc_init_paymentNotice as


  CURSOR c_paymentNotice is
    select * from gws_payment_notice t_pn where t_pn.is_deleted = 'n';
  /*付款通知单记录变量*/
  r_paymentNotice gws_payment_notice%rowtype;
  /*核销记录变量*/
  c_wfr gws_write_off_record%rowtype;


  /*根据sourceid查询gws_payment_notice_detail记录数*/
  i_num number(10) := 0;


begin


  OPEN c_paymentNotice;


  LOOP
    fetch c_paymentNotice
      into r_paymentNotice;
  
    exit when c_paymentNotice%notfound;
  
    begin
      select *
        into c_wfr
        from gws_write_off_record t_wfr
       where t_wfr.wfr_no = r_paymentNotice.Wfr_No;
    
      /*查询是否已存在gws_payment_notice_detail*/
      select count(1)
        into i_num
        from gws_payment_notice_detail t
       where t.source_id = c_wfr.source_id;
    
      /*不存在gws_payment_notice_detail,才作添加,更新操作*/
      if i_num = 0 then
        /*添加gws_payment_notice_detail表*/
        insert into gws_payment_notice_detail
          (ID,
           GMT_CREATE,
           GMT_MODIFIED,
           PAYMENT_NOTICE_ID,
           SOURCE_ID,
           AMOUNT,
           UN_EXCHANGE_AMOUNT,
           CREATOR,
           MODIFIER,
           IS_DELETED)
        values
          (seq_gws_payment_notice_detail.nextval,
           sysdate,
           sysdate,
           r_paymentNotice.ID,
           c_wfr.source_id,
           r_paymentNotice.Amount,
           r_paymentNotice.Un_Exchange_Gain_Loss,
           'wb_cj189958',
           'wb_cj189958',
           'n');
      
        /*更新paymentNotice表*/
        update gws_payment_notice
           set exchange_date = c_wfr.exchange_date,
               exchange_type = c_wfr.exchange_type,
               exchange_rate = c_wfr.exchange_rate,
               source_type   = c_wfr.source_type,
               gmt_modified  = sysdate,
               modifier      = 'wb_cj189958'
         where ID = r_paymentNotice.ID;
      end if;
    exception
      when no_data_found then
        dbms_output.put_line('未查到核销记录,wfr_no='||r_paymentNotice.Wfr_No);
      
      when too_many_rows then
        dbms_output.put_line('查到多条核销记录,wfr_no='||r_paymentNotice.Wfr_No);
      
      when others then
        dbms_output.put_line('系统异常');
      
    end;
  END LOOP;
  close c_paymentNotice;


  begin
    commit; /*提交*/
    dbms_output.put_line('存储过程执行成功');
  exception
    when others then
      rollback; /*回滚*/
      dbms_output.put_line('存储过程执行失败');
  end;


end proc_init_paymentNotice;