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

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

2022-06-13   來源: Oracle 

  PL/SQL裡有三種方法可以在處理大批量數據時不會因為一條或幾條數據錯誤而導致異常中止程序
  
  用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

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