CSDN博客

img menlion

PL/SQL中的几种异常处理方法

发表于2003/5/20 12:32:00  724人阅读

分类: 数据库

这是Pona的文章,我斗胆将其贴上来,Pona不要介意哦!^_^

 

PL/SQL里,有三种方法可以在处理大批量数据时不会因为一条或几条数据错误而导致异常中止程序。

 

1、用Fetch into a cursor%TYPE把要处理的数据放到记录集里。当一条数据不符条件时,用标签<<NEXT_RECORD>>和GOTO NEXT_RECORD跳转语句使程序忽略这一条,转到下一条继续处理。

-------------------------------------------------------------------------------

-- Function Name     :  CalculateImportCharge

-- Function Desc     :  Calculate Import Charge

-- Created by        :  Author

-- Created Date      :  2003-05-16

-------------------------------------------------------------------------------

    FUNCTION CalculateImportCharge (

        p_i_job_id        IN VARCHAR2,

        p_i_as_of_date_id IN VARCHAR2) RETURN NUMBER

    AS

        CURSOR cur_ShipBlHeader IS

            SELECT import_folder_no

            FROM GMY_SHIP_BL_HEADER

            WHERE CANCEL_FLG = GMY_GA000_PKG.BL_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_GA000_PKG.CheckValidMasterBlNo (

                p_i_job_id,

                p_i_as_of_date_id,

                rec_ShipBlHeader.import_folder_no,

                x_vch_message);

            IF x_num_error_code

                IN (GMY_GA000_PKG.gn#NG, GMY_GA000_PKG.INVALID_BL_NO) THEN

                x_vch_message :=

                        p_i_job_id

                       || ' WARNING: Function CheckValidMasterBlNo @'

                       || ' Import folder '

                       || rec_ShipBlHeader.import_folder_no

                       || ' - Invalid BL No.';

                COM_LOG.PUTLINE (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_ShipBlHeader.import_folder_no);

            IF x_num_error_code = GMY_GA000_PKG.gn#NG THEN

                GOTO NEXT_RECORD;

            END IF;

            COMMIT;

            <<NEXT_RECORD>>

            FETCH cur_ShipBlHeader INTO rec_ShipBlHeader;

        END LOOP;

        CLOSE cur_ShipBlHeader;

        RETURN GMY_GA000_PKG.gn#OK;

    EXCEPTION

        WHEN OTHERS THEN

            x_vch_message :=

                    p_i_job_id

                 || ' ERROR:   Function CalculateImportCharge @ '

                 || SUBSTR (SQLERRM (SQLCODE), 1, 100);

            COM_LOG.PUTLINE (p_i_job_id, x_vch_message);

            RETURN GMY_GA000_PKG.gn#NG;

END CalculateImportCharge;

2、当使用the Cursor FOR Loop循环时,在Loop循环里,把会出问题的情况写进一个独立的block块中,这个块包括完整的begin、end部分及exception异常处理部分。这样即使一条数据出现异常,也会继续执行下一条。

-------------------------------------------------------------------------------

-- Function Name     : GenerateInsCostInfRec

-- Function Desc     : Generate records to transmit in INF table

-- Created by        : SISS(AP)

-- Created Date      : 2003-03-26

-- ----------------------------------------------------------------------------

    FUNCTION GenerateInsCostInfRec (

        p_i_job_id             IN       VARCHAR2,

        p_i_as_of_date_id      IN       VARCHAR2) RETURN NUMBER

    AS

        CURSOR cur_cost IS

            SELECT cost.ROWID costRowId,

                   cost.import_folder_no,,

                   cost.insur_trans_id

            FROM GMY_COST_BL cost,

                 GMY_COMMON_MST mst

            WHERE cost.import_folder_no=invheader.import_folder_no

            AND cost.billing_amt_num IS NOT NULL

            AND cost.billing_amt_num!=0

            AND cost.insur_db_cr!=0;

    BEGIN

        FOR rec_cost IN cur_cost LOOP

            BEGIN

                x_num_ret_value := GMY_GA000_PKG.CheckValidMasterBlNo(

                                p_i_job_id,

                                p_i_as_of_date_id,

                                rec_cost.import_folder_no,

                                x_vch_error_msg);

                IF x_num_ret_value = GMY_GA000_PKG.VALID_BL_NO THEN

                    INSERT INTO GMY_COST_INS_INF(

                        cost_trx_id,,

                        created_by,

                        program_name)

                    VALUES(

                        GMY_COST_INS_INF_S.NEXTVAL,

                        PRG_NAME,

                        PRG_NAME);

                ELSIF x_num_ret_value = GMY_GA000_PKG.INVALID_BL_NO THEN

                    x_vch_error_msg := p_i_job_id

                        || ' Import folder '

                        || rec_cost.import_folder_no

                        || ' has repeated BL No. with other import folder.'

                        || ' Failed in insurance cost transmission.';

                    COM_LOG.PUTLINE(p_i_job_id, x_vch_error_msg);

                END IF;

            EXCEPTION

                WHEN OTHERS THEN

                    IF SQL%ROWCOUNT > 0 THEN  -- check for 'too many rows'

                       x_vch_error_msg := p_i_job_id||' '||

                           SUBSTR(SQLERRM(SQLCODE),1,100);

                       COM_LOG.PUTLINE(p_i_job_id, x_vch_error_msg);

                    ELSE

                       x_vch_error_msg := p_i_job_id||' '||

                           SUBSTR(SQLERRM(SQLCODE),1,100);

                       COM_LOG.PUTLINE(p_i_job_id, x_vch_error_msg);

                END IF;

            END;

        END LOOP;

        COMMIT;

        RETURN GMY_GA000_PKG.gn#OK;

    EXCEPTION

      WHEN OTHERS THEN

          x_vch_error_msg := p_i_job_id||' '||SUBSTR(SQLERRM(SQLCODE),1,100);

          COM_LOG.PUTLINE(p_i_job_id, x_vch_error_msg);

          ROLLBACK;

          RETURN GMY_GA000_PKG.gn#NG;

END GenerateInsCostInfRec;

3、当使用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       :  2003-02-20

----------------------------------------------------------------------------

   FUNCTION CopyDsToActualDs (

        p_i_job_id         IN   VARCHAR2,

        p_i_as_of_date_id  IN   VARCHAR2)  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(

                        p_i_job_id,

                        p_i_as_of_date_id,

                        rec_DsHead.order_by_code,

                        rec_DsHead.po_code,

                        rec_DsHead.wh);

        END LOOP;

    EXCEPTION

        WHEN OTHERS THEN

            x_vch_error_msg := p_i_job_id

                ||' Function Name: CopyDsToActualDs';

            COM_LOG.PUTLINE(p_i_job_id,x_vch_error_msg);

            x_vch_error_msg:=p_i_job_id||' '||SUBSTR(SQLERRM(SQLCODE),1,100);

            COM_LOG.PUTLINE(p_i_job_id, x_vch_error_msg);

            ROLLBACK;

        RETURN GMY_GA000_PKG.gn#NG;

    END CopyDsToActualDs;

----------------------------------------------------------------------------

-- Function Name      :  InsToActualScc

-- Function Desc      :  Deal with insert section.

-- Created by         :  Author

-- Created Date       :  2003-03-13

----------------------------------------------------------------------------

    FUNCTION InsToActualScc(

        p_i_job_id                      IN       VARCHAR2,

        p_i_as_of_date_id               IN       VARCHAR2,

        p_i_order_by_code               IN       VARCHAR2,

        p_i_po_code                     IN       VARCHAR2,

        p_i_wh                          IN       VARCHAR2

    ) RETURN NUMBER

    IS

        x_vch_error_msg VARCHAR2(255);

    BEGIN

        INSERT INTO GMY_ACTUAL_DS_SCC(

                order_by_code,

                po_code,

                wh )

        VALUES( p_i_order_by_code,

                p_i_po_code,

                p_i_wh);

        COMMIT;

        RETURN GMY_GA000_PKG.gn#OK;

    EXCEPTION

        WHEN OTHERS THEN

            x_vch_error_msg := p_i_job_id||' Function Name: InsToActualScc';

            COM_LOG.PUTLINE(p_i_job_id,x_vch_error_msg);

            x_vch_error_msg := p_i_job_id

                ||' The key of the record that failed to insert is: ';

            COM_LOG.PUTLINE(p_i_job_id,x_vch_error_msg);

            ROLLBACK;

        RETURN GMY_GA000_PKG.gn#NG;

    END InsToActualScc;

0 0

相关博文

我的热门文章

img
取 消
img