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;