定义公共的package－－ERROR篇

create or replace PACKAGE "ERR"
AS
 -- AUTHOR: HWCHANG
 -- DATE: 16 JUN 2002
 -- ERROR CODE NUMBER
 UPDATED_BY_OTHER_USER CONSTANT NUMBER := -20001; -- ERROR CODE = ORA-20001
 DELETED_BY_OTHER_USER CONSTANT NUMBER := -20002; -- ERROR CODE = ORA-20002
 DUPLICATE_CODE CONSTANT NUMBER :=-20003;   -- ERROR CODE = ORA-20003
 INVALID_ERROR_CODE CONSTANT NUMBER := -20990;  -- ERROR CODE = ORA-20990
  UNIQUE_CONSTRAIT CONSTANT NUMBER := -00001;

 -- AUTHOR: danielang
 -- DATE: 11 NOV 2003
 -- CUSTOM EXCEPTION CORRESPONDING TO FOLLOWING
  --  ORA-00054: resource busy and acquire with NOWAIT specified
  RECORD_IS_LOCKED EXCEPTION;
 PRAGMA EXCEPTION_INIT(RECORD_IS_LOCKED, -54);

 PROCEDURE RAISE_ERR(error_code_in NUMBER);
 --FUNCTION GET_ERR_MSG(error_code_in NUMBER) RETURN VARCHAR2;

END;
-------------------

create or replace PACKAGE BODY          "ERR" AS
 -- AUTHOR: HWCHANG
 -- DATE: 16 JUN 2002

 PROCEDURE RAISE_ERR
 (
  error_code_in IN NUMBER
 )
 AS
  e_invalid_error_code EXCEPTION;
 BEGIN
    DBMS_OUTPUT.PUT_LINE('Error code is ' || error_code_in);
  IF error_code_in = NULL THEN
   RAISE e_invalid_error_code;
  END IF;
  IF error_code_in BETWEEN -20999 AND -00000 THEN
        RAISE_APPLICATION_ERROR(error_code_in, TO_CHAR(error_code_in));
  ELSE
   RAISE e_invalid_error_code;
  END IF;
 EXCEPTION
  WHEN e_invalid_error_code THEN
   RAISE_APPLICATION_ERROR(ERR.INVALID_ERROR_CODE, 'Invalid error code.');
 END;

END;
------------使用：

create or replace PROCEDURE "USP_APPR_DET_D"
/*
    Copyright(c) 1987-2005 eBworx,Inc.
    Remark : First Creation (guoxigui 20051205)
*/
(
    id_in           IN TBL_MT_APPR_DET.id%TYPE,
    version_in      IN TBL_MT_APPR_DET.version%TYPE,
    result_cursor   OUT TYPES.cursor_type
)
AS
    v_pk     TBL_MT_APPR_DET.id%TYPE;

BEGIN

    SELECT    id INTO v_pk
        FROM  TBL_MT_APPR_DET
        WHERE id      = id_in
        AND   version = version_in
        FOR UPDATE NOWAIT;

    DELETE FROM TBL_MT_APPR_DET
        WHERE   id      = id_in
        AND     version = version_in;

    OPEN result_cursor FOR
        SELECT 'success' AS status FROM dual;

EXCEPTION

    WHEN NO_DATA_FOUND THEN
        BEGIN
            SELECT    id INTO v_pk
                FROM  TBL_MT_APPR_DET
                WHERE id = id_in;
            ERR.RAISE_ERR(ERR.UPDATED_BY_OTHER_USER);

        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                ERR.RAISE_ERR(ERR.DELETED_BY_OTHER_USER);
        END;

    WHEN ERR.RECORD_IS_LOCKED THEN
        ERR.RAISE_ERR(ERR.UPDATED_BY_OTHER_USER);

    WHEN OTHERS THEN
        RAISE;

END;