熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

PL/SQL中的幾種異常處理方法的源代碼

2013-11-13 22:11:47  來源: Oracle 

  用Fetch into a cursor%TYPE把要處理的數據放到記錄集裡當一條數據不符條件時用標簽<<NEXT_RECORD>>和GOTO NEXT_RECORD
  
   Function Name   : CalculateImportCharge
  
   Function Desc   : Calculate Import Charge
  
   Created by    : Author
  
   Created Date   : 
  
    FUNCTION CalculateImportCharge (
  
      p_i_job_id    IN VARCHAR
  
      p_i_as_of_date_id IN VARCHAR) RETURN NUMBER
  
    AS
  
      CURSOR cur_ShipBlHeader IS
  
        SELECT import_folder_no
  
        FROM GMY_SHIP_BL_HEADER
  
        WHERE CANCEL_FLG = GMY_GA_PKGBL_CANCEL_FLG_OFF;
  
      rec_ShipBlHeader    cur_ShipBlHeader%ROWTYPE;
  
    BEGIN
  
      OPEN cur_ShipBlHeader;
  
      FETCH cur_ShipBlHeader INTO rec_ShipBlHeader;
  
      WHILE cur_ShipBlHeader%FOUND LOOP
  
        x_num_error_code := GMY_GA_PKGCheckValidMasterBlNo (
  
          p_i_job_id
  
          p_i_as_of_date_id
  
          rec_ShipBlHeaderimport_folder_no
  
          x_vch_message);
  
        IF x_num_error_code
  
          IN (GMY_GA_PKGgn#NG GMY_GA_PKGINVALID_BL_NO) THEN
  
          x_vch_message :=
  
              p_i_job_id
  
              || WARNING: Function CheckValidMasterBlNo @
  
              || Import folder
  
              || rec_ShipBlHeaderimport_folder_no
  
              || Invalid BL No;
  
          COM_LOGPUTLINE (p_i_job_id x_vch_message);
  
          GOTO NEXT_RECORD;
  
        END IF;
  
        x_num_error_code := CheckExistsOfAccDate (
  
          p_i_job_id
  
          p_i_as_of_date_id
  
          rec_ShipBlHeaderimport_folder_no);
  
        IF x_num_error_code = GMY_GA_PKGgn#NG THEN
  
          GOTO NEXT_RECORD;
  
        END IF;
  
        COMMIT;
  
        <<NEXT_RECORD>>
  
        FETCH cur_ShipBlHeader INTO rec_ShipBlHeader;
  
      END LOOP;
  
      CLOSE cur_ShipBlHeader;
  
      RETURN GMY_GA_PKGgn#OK;
  
    EXCEPTION
  
      WHEN OTHERS THEN
  
        x_vch_message :=
  
            p_i_job_id
  
           || ERROR:  Function CalculateImportCharge @
  
           || SUBSTR (SQLERRM (SQLCODE) );
  
        COM_LOGPUTLINE (p_i_job_id x_vch_message);
  
        RETURN GMY_GA_PKGgn#NG;
  
  END CalculateImportCharge;
  
  當使用the Cursor FOR Loop循環時在Loop循環裡把會出問題的情況寫進一個獨立的block塊中這個塊包括完整的beginend部分及exception異常處理部分這樣即使一條數據出現異常也會繼續執行下一條
  
   Function Name   : GenerateInsCostInfRec
  
   Function Desc   : Generate records to transmit in INF table
  
   Created by    : SISS(AP)
  
   Created Date   :
  
    FUNCTION GenerateInsCostInfRec (
  
      p_i_job_id       IN    VARCHAR
  
      p_i_as_of_date_id   IN    VARCHAR) RETURN NUMBER
  
    AS
  
      CURSOR cur_cost IS
  
        SELECT costROWID costRowId
  
            costimport_folder_no
  
            costinsur_trans_id
  
        FROM GMY_COST_BL cost
  
           GMY_COMMON_MST mst
  
        WHERE costimport_folder_no=invheaderimport_folder_no
  
        AND costbilling_amt_num IS NOT NULL
  
        AND costbilling_amt_num!=
  
        AND costinsur_db_cr!=;
  
    BEGIN
  
      FOR rec_cost IN cur_cost LOOP
  
        BEGIN
  
          x_num_ret_value := GMY_GA_PKGCheckValidMasterBlNo(
  
                  p_i_job_id
  
                  p_i_as_of_date_id
  
                  rec_costimport_folder_no
  
                  x_vch_error_msg);
  
          IF x_num_ret_value = GMY_GA_PKGVALID_BL_NO THEN
  
            INSERT INTO GMY_COST_INS_INF(
  
              cost_trx_id
  
              created_by
  
              program_name)
  
            VALUES(
  
              GMY_COST_INS_INF_SNEXTVAL
  
              PRG_NAME
  
              PRG_NAME);
  
          ELSIF x_num_ret_value = GMY_GA_PKGINVALID_BL_NO THEN
  
            x_vch_error_msg := p_i_job_id
  
              || Import folder
  
              || rec_costimport_folder_no
  
              || has repeated BL No with other import folder
  
              || Failed in insurance cost transmission;
  
            COM_LOGPUTLINE(p_i_job_id x_vch_error_msg);
  
          END IF;
  
        EXCEPTION
  
          WHEN OTHERS THEN
  
            IF SQL%ROWCOUNT > THEN  check for too many rows
  
              x_vch_error_msg := p_i_job_id|| ||
  
                SUBSTR(SQLERRM(SQLCODE));
  
              COM_LOGPUTLINE(p_i_job_id x_vch_error_msg);
  
            ELSE
  
              x_vch_error_msg := p_i_job_id|| ||
  
                SUBSTR(SQLERRM(SQLCODE));
  
              COM_LOGPUTLINE(p_i_job_id x_vch_error_msg);
  
          END IF;
  
        END;
  
      END LOOP;
  
      COMMIT;
  
      RETURN GMY_GA_PKGgn#OK;
  
    EXCEPTION
  
     WHEN OTHERS THEN
  
       x_vch_error_msg := p_i_job_id|| ||SUBSTR(SQLERRM(SQLCODE));
  
       COM_LOGPUTLINE(p_i_job_id x_vch_error_msg);
  
       ROLLBACK;
  
       RETURN GMY_GA_PKGgn#NG;
  
  END GenerateInsCostInfRec;
  
  當使用the Cursor FOR Loop循環時在Loop循環裡把會出問題的情況拆分成子函數分別處理
  
   Function Name   : CopyDsToActualDs
  
   Function Desc   : Copy the records from DS DB to Actual DS DB
  
   Created by     : Author
  
   Created Date    : 
  
    FUNCTION CopyDsToActualDs (
  
      p_i_job_id     IN  VARCHAR
  
      p_i_as_of_date_id IN  VARCHAR) RETURN NUMBER
  
    IS
  
      CURSOR cur_DsScc IS
  
        SELECT *
  
        FROM  GMY_DS_SCC;
  
    BEGIN
  
      FOR rec_DsHead IN cur_DsScc LOOP
  
        x_num_error_code := InsToActualScc(
  

From:http://tw.wingwit.com/Article/program/Oracle/201311/18441.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.