시스템 예외에 이름 부여하기

시스템 예외에는 ZERO_DIVIDE, INVALID_NUMBER 같이 미리 정의된 예외가 있었다. 하지만 이들처럼 예외명이 부여된 것은 시스템 예외 중 극소수이고 나머지는 예외코드만 존재한다. 예외 이름이 없는 이런 예외에 이름을 부여하면 코드가 훨씬 읽기 편할 뿐만 아니라, 해당 코드의 로직을 파악하는 것도 아주 쉬워진다. 그렇다면 예외 이름은 어떻게 부여하는 것일까? 그 방법을 정리하면 다음과 같다.

① 사용자 정의 예외 선언: 사용자_정의_예외명 EXCEPTION;

선언부에서 사용자 정의 예외를 선언한다.

② 사용자 정의 예외명과 시스템 예외 코드 연결: PRAGMA EXCEPTION_INIT (사용자_정의_예외명, 시스템_예외_코드);

8장에서 배웠던 PRAGMA EXCEPTION_INIT를 사용하는데, ①에서 선언한 예외명과 시스템 예외코드를 명시한다.

③ 발생된 예외 처리: EXCEPTION WHEN 사용자_정의_예외명 THEN ….

ch10_ins_emp_proc 프로시저에 매개변수를 하나 더 추가해 보자. 입사월을 받아 사원을 입력할 때 입사일자는 무조건 전달받은 입사월의 1일에 입력되어야 한다는 규칙이 정해졌다고 가정하고, 잘못된 입사월이 입력될 때, ORA-01843(not a valid month, 지정한 월이 부적합합니다)라는 시스템 예외를 발생시켜 보자.

입력

    CREATE OR REPLACE PROCEDURE ch10_ins_emp_proc (
      p_emp_name employees.emp_name%TYPE,
      p_department_id departments.department_id%TYPE,
      p_hire_month  VARCHAR2  )
    IS
      vn_employee_id employees.employee_id%TYPE;
      vd_curr_date    DATE := SYSDATE;
      vn_cnt          NUMBER := 0;

      ex_invalid_depid EXCEPTION; -- 잘못된 부서번호일 경우 예외 정의

      ex_invalid_month EXCEPTION; -- 잘못된 입사월인 경우 예외 정의
      PRAGMA EXCEPTION_INIT ( ex_invalid_month, -1843); -- 예외명과 예외코드 연결
    BEGIN
      -- 부서테이블에서 해당 부서번호 존재유무 체크
      SELECT COUNT(*)
        INTO vn_cnt
        FROM departments
       WHERE department_id = p_department_id;

      IF vn_cnt = 0 THEN
         RAISE ex_invalid_depid; -- 부서코드 사용자 정의 예외 발생
      END IF;

      -- 입사월 체크(1~12월 범위를 벗어났는지 체크)
      IF SUBSTR(p_hire_month, 5, 2) NOT BETWEEN '01' AND '12' THEN
        RAISE ex_invalid_month; -- 잘못된 입사월 사용자 정의 예외 발생
      END IF;

      -- employee_id의 max 값에 +1
      SELECT MAX(employee_id) + 1
        INTO vn_employee_id
        FROM employees;

      -- 사용자예외처리 예제이므로 사원 테이블에 최소한 데이터만 입력함
      INSERT INTO employees ( employee_id, emp_name, hire_date, department_id )
                  VALUES ( vn_employee_id, p_emp_name, TO_DATE(p_hire_month || '01'), p_department_id );

      COMMIT;

    EXCEPTION 
    WHEN ex_invalid_depid THEN -- 사용자 정의 예외 처리
      DBMS_OUTPUT.PUT_LINE('해당 부서번호가 없습니다');
    WHEN ex_invalid_month THEN -- 입사월 사용자 정의 예외 처리
      DBMS_OUTPUT.PUT_LINE(SQLCODE);
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
      DBMS_OUTPUT.PUT_LINE('1~12월 범위를 벗어난 월입니다');
    WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE(SQLERRM);
    END;

결과

    PROCEDURE CH10_INS_EMP_PROC이(가) 컴파일되었습니다.

입사월 매개변수를 ‘201314’로 전달해 프로시저를 실행해 보자.

입력

    EXEC ch10_ins_emp_proc ('홍길동', 110, '201314');

결과

    -1843
    ORA-01843: 지정한 월이 부적합합니다.
    1~12월 범위를 벗어난 월입니다

예상했던 대로 사용자 정의 예외에 ORA-01843 예외코드가 연결되어 처리된 것을 확인할 수 있다. 마지막으로 예외명을 정의한 뒤 시스템 예외코드를 연결해서 사용할 때 주의해야 할 제약사항을 정리해 보자.

• 미리 정의된 예외 중 NO_DATA_FOUND(-1403)는 사용자 정의 예외명에 해당 코드(-1403)을 연결할 수 없다. 만약 연결할 경우 컴파일할 때 다음과 같은 오류가 발생한다. 예) PRAGMA EXCEPTION_INIT에 대한 부당한 ORACLE 오류번호(-1403)입니다

• 예외 코드로 0이나 100을 제외한 양수, 그리고 -10000000 이하 값은 사용할 수 없다.

• 동일한 예외명으로 다른 예외 코드를 2개 이상 연결하면, 맨 마지막에 연결한 코드가 적용된다.