[DATABASE/ORACLE] PRAGMA EXCEPTION_INIT 명령 : 커스텀 예외와 오류 번호 연결시키기

■ PRAGMA EXCEPTION_INIT 명령을 사용해 커스텀 예외와 오류 번호를 연결시키는 방법을 보여준다.

▶ 예제 코드 (SQL)


CREATE OR REPLACE PROCEDURE DeleteDepartment(P_DEPTNO IN DEPT.DEPTNO%TYPE)
IS
    STILL_HAVE_EMPLOYEES EXCEPTION;

    -- ORA-2292 violated integrity constraining (OWNER.CONSTRAINT) - child record found.
    PRAGMA EXCEPTION_INIT(STILL_HAVE_EMPLOYEES, -2292);
BEGIN
    DELETE FROM DEPT WHERE DEPTNO = P_DEPTNO;
EXCEPTION
    WHEN STILL_HAVE_EMPLOYEES
    THEN
        DBMS_OUTPUT.PUT_LINE('임직원 데이터를 먼저 삭제해주시기 바랍니다.');
END;

SET SERVEROUTPUT ON;

BEGIN
    DeleteDepartment(10);
END;

CREATE OR REPLACE PROCEDURE DeleteDepartment(P_DEPTNO IN DEPT.DEPTNO%TYPE)

STILL_HAVE_EMPLOYEES EXCEPTION;

-- ORA-2292 violated integrity constraining (OWNER.CONSTRAINT) - child record found.

PRAGMA EXCEPTION_INIT(STILL_HAVE_EMPLOYEES, -2292);

BEGIN

DELETE FROM DEPT WHERE DEPTNO = P_DEPTNO;

EXCEPTION

WHEN STILL_HAVE_EMPLOYEES

THEN

DBMS_OUTPUT.PUT_LINE('임직원 데이터를 먼저 삭제해주시기 바랍니다.');

END;

SET SERVEROUTPUT ON;

BEGIN

DeleteDepartment(10);

END;