③ 사용자 정의 예외도 별도의 테이블로 미리 만들어 관리하자

보통 사용자 정의 예외는 구축하는 시스템에 특화된 예외일 때가 대부분이다. 개발자마다 작성하는 프로시저에서 사용자 정의 예외를 정의하고 사용하다 보면, 동일한 예외 코드를 사용하여 중복이 발생할 소지가 매우 많다. 따라서 사용자 정의 예외 테이블을 만들어 놓고, 실제 해당 예외가 발생하면 이 테이블에서 예외코드와 번호 등을 읽어오는 식으로 처리하면 관리하기가 쉬워질 것이다. 다음과 같은 형태로 사용자 정의 예외 테이블을 만들어 보자.

입력

    CREATE TABLE app_user_define_error (
      error_code    NUMBER,               -- 에러코드
      error_message VARCHAR2(300),        -- 에러 메시지
      create_date   DATE DEFAULT SYSDATE, -- 등록일자
                   PRIMARY KEY (error_code)
      );

결과

    table APP_USER_DEFINE_ERROR이(가) 생성되었습니다.

[그림 10-1]에 있는 예외를 등록해 보자.

입력

    INSERT INTO app_user_define_error ( error_code, error_message )  
      VALUES (-1843, '지정한 월이 부적합합니다');
    INSERT INTO app_user_define_error ( error_code, error_message )
      VALUES (-20000, '해당 부서가 없습니다');

    COMMIT;

결과

    1개 행 이(가) 삽입되었습니다.
    1개 행 이(가) 삽입되었습니다.
    커밋되었습니다.

이제 error_log_proc 프로시저를 수정해 보자.

입력

    CREATE OR REPLACE PROCEDURE error_log_proc(
      p_prog_nameerror_log.prog_name%TYPE,
      p_error_codeerror_log.error_code%TYPE,
      p_error_messggeerror_log.error_message%TYPE,
      p_error_lineerror_log.error_line%TYPE  )
    IS
      vn_error_codeerror_log.error_code%TYPE := p_error_code;
      vn_error_messageerror_log.error_message%TYPE := p_error_messgge;

    BEGIN

      -- 사용자 정의 에러 테이블에서 에러 메시지를 받아오는 부분을 BLOCK으로 감싼다.
      -- 해당 메시지가 없을 경우 처리를 위해서....
      BEGIN

        SELECT error_message
          INTO vn_error_message
          FROM app_user_define_error
         WHERE error_code = vn_error_code;

      -- 해당 에러가 테이블에 없다면 매개변수로 받아온 메시지를 그대로 할당한다.
      EXCEPTION WHEN NO_DATA_FOUND THEN
        vn_error_message :=  p_error_messgge;
      END;

      INSERT INTO error_log (error_seq, prog_name, error_code, error_message, error_line)
      VALUES ( error_seq.NEXTVAL, p_prog_name, vn_error_code, vn_error_message, p_error_line );

      COMMIT;
    END;

결과

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

ch10_ins_emp2_proc 프로시저의 예외처리 부분을 다음과 같이 수정한다.

입력

    CREATE OR REPLACE PROCEDURE ch10_ins_emp2_proc (
      p_emp_nameemployees.emp_name%TYPE,
      p_department_iddepartments.department_id%TYPE,
      p_hire_monthVARCHAR2 )
    IS
    ...
    ...

    EXCEPTION WHEN ex_invalid_depid THEN -- 사용자 정의 예외 처리
      v_err_code := SQLCODE;
      v_err_line := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
      ROLLBACK;
      error_log_proc ( 'ch12_ins_emp2_proc', v_err_code, v_err_msg, v_err_line);
    WHEN ex_invalid_month THEN -- 입사월 사용자 정의 예외 처리
      v_err_code := SQLCODE;
      v_err_line := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
      ROLLBACK;
      error_log_proc ( 'ch12_ins_emp2_proc', v_err_code, v_err_msg, v_err_line);
    WHEN OTHERS THEN
      v_err_code := SQLCODE;
      v_err_msg  := SQLERRM;
      v_err_line := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
      ROLLBACK;
      error_log_proc ( 'ch10_ins_emp2_proc', v_err_code, v_err_msg, v_err_line);
    END;

결과

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

예외를 발생시켜 보자.

입력

    -- 잘못된 부서
    EXEC ch10_ins_emp2_proc ('HONG', 1000, '201401');

결과

    익명 블록이 완료되었습니다.

입력

    -- 잘못된 월
    EXEC ch10_ins_emp2_proc ('HONG', 100, '201413');

결과

    익명 블록이 완료되었습니다.

입력

    SELECT *
      FROM  error_log ;

이런 식으로 예외처리를 하기 위해서는 시스템 개발 초기에 발생 가능한 사용자 정의 예외를 미리 정의한 뒤 app_user_define_error 와 같은 사용자 예외 정의 테이블에 등록해서 사용하는 것이 좋다.