시스템 예외

예외처리 시 OTHERS 외에도 시스템 예외명을 사용할 수 있는데, 이를 미리 정의된 예외_{predefined exception}라 한다. 즉 고유의 예외명칭을 사용할 수 있다는 것인데 이들 중 대표적인 예외를 정리하면 다음과 같다.

예외명	예외 코드	설명
ACCESS_INTO_NULL	ORA-06530	LOB과 같은 객체 초기화 되지 않은 상태에서 사용
CASE_NOT_FOUND	ORA-06592	CASE문 사용시 구문 오류
CURSOR_ALREADY_OPEN	ORA-06511	커서가 이미 OPEN된 상태인데 OPEN 하려고 시도
DUP_VAL_ON_INDEX	ORA-00001	유일 인덱스가 있는 컬럼에 중복값으로 INSERT, UPDATE 수행
INVALID_CURSOR	ORA-01001	존재하지 않는 커서를 참조
INVALID_NUMBER	ORA-01722	문자를 숫자로 변환할 때 실패할 경우
LOGIN_DENIED	ORA-01017	잘못된 사용자 이름이나 비밀번호로 로그인을 시도
NO_DATA_FOUND	ORA-01403	SELECT INTO 시 데이터가 한 건도 없을 경우
NOT_LOGGED_ON	ORA-01012	로그온되지 않았는데 DB를 참조할 때
PROGRAM_ERROR	ORA-06501	PL/SQL 코드상에서 내부 오류를 만났을 때, 이 오류가 발생하면 “오라클에 문의(Contact Oracle Support)”란 메시지가 출력됨
STORAGE_ERROR	ORA-06500	프로그램 수행 시 메모리가 부족할 경우
TIMEOUT_ON_RESOURCE	ORA-00051	데이터베이스 자원을 기다리는 동안 타임아웃 발생 시
TOO_MANY_ROWS	ORA-01422	SELECT INTO 절 사용할 때 결과가 한 로우 이상일 때
VALUE_ERROR	ORA-06502	수치 또는 값 오류
ZERO_DIVIDE	ORA-01476	0으로 나눌 때

[표 10-1]을 보면 젯수가 0일 때의 예외가 ‘ZERO_DIVIDE’이므로, 예외처리 구문에서 OTHERS 대신 사용할 수 있다.

입력

    CREATE OR REPLACE PROCEDURE ch10_exception_proc
    IS
      vi_num NUMBER := 0;
    BEGIN
      vi_num := 10 / 0;
      DBMS_OUTPUT.PUT_LINE('Success!');

    EXCEPTION WHEN ZERO_DIVIDE THEN
      DBMS_OUTPUT.PUT_LINE('오류가 발생했습니다');
      DBMS_OUTPUT.PUT_LINE('SQL ERROR CODE: ' || SQLCODE);
      DBMS_OUTPUT.PUT_LINE('SQL ERROR MESSAGE: ' || SQLERRM);
    END;

결과

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

미리 정의된 예외는 그 수가 제한되어 있는 반면, 오라클 내부에서 처리하는 예외의 수는 매우 많다. 따라서 보통 예외처리를 할 때 미리 정의된 예외를 먼저, 맨 마지막에 OTHERS를 명시하는 형태로 사용한다. 여러 개의 예외를 명시할 때 OTHERS는 반드시 맨 끝에 명시해야 한다. 이제 2개 이상의 예외명을 명시해 보자.

입력

    CREATE OR REPLACE PROCEDURE ch10_exception_proc
    IS
      vi_num NUMBER := 0;
    BEGIN
      vi_num := 10 / 0;
      DBMS_OUTPUT.PUT_LINE('Success!');

    EXCEPTION 
    WHEN ZERO_DIVIDE THEN
      DBMS_OUTPUT.PUT_LINE('오류1');
      DBMS_OUTPUT.PUT_LINE('SQL ERROR MESSAGE1: ' || SQLERRM);
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('오류2');
      DBMS_OUTPUT.PUT_LINE('SQL ERROR MESSAGE2: ' || SQLERRM);
    END;

결과

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

입력

    EXEC ch10_exception_proc;

결과

    오류1
    SQL ERROR MESSAGE1: ORA-01476: 제수가 0입니다.

결과를 보면 알겠지만 예외처리는 EXCEPTION 절에 명시한 순서대로 처리된다. 즉 맨 처음 명시한 예외가 발생하면 해당되는 로직을 처리하고 이후 예외는 무시하고 프로시저는 종료된다.

또 다른 예제를 살펴 보자. 이번에는 사원번호와 job_id를 매개변수로 받아 해당 사원의 job_id 값을 갱신하는 프로시저를 만드는데, 만약 해당 job_id가 JOBS 테이블에 존재하지 않으면 오류 메시지와 함께 프로시저를 종료시키자. 이전 장까지 학습한 내용을 토대로 프로시저를 작성한다면, 아마 다음과 같은 형태가 될 것이다.

입력

    CREATE OR REPLACE PROCEDURE ch10_upd_jobid_proc
    ( p_employee_id employees.employee_id%TYPE,
      p_job_id jobs.job_id%TYPE )
    IS
      vn_cnt NUMBER := 0;
    BEGIN
      SELECT COUNT(*)
        INTO vn_cnt
        FROM JOBS
       WHERE JOB_ID = p_job_id;

      IF vn_cnt = 0 THEN
         DBMS_OUTPUT.PUT_LINE('job_id가 없습니다');
         RETURN;
      ELSE
         UPDATE employees
            SET job_id = p_job_id
          WHERE employee_id = p_employee_id;
      END IF;

      COMMIT;
    END;

결과

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

JOBS 테이블에 없는 job_id를 매개변수로 넘겨 프로시저를 실행해 보자.

입력

    EXEC ch10_upd_jobid_proc (200, 'SM_JOB2');

결과

    job_id가 없습니다.

이번에는 다음과 같이 예외처리 로직을 심어보자.

입력

    CREATE OR REPLACE PROCEDURE ch10_upd_jobid_proc
    ( p_employee_id employees.employee_id%TYPE,
      p_job_idjobs.job_id%TYPE)
    IS
      vn_cnt NUMBER := 0;
    BEGIN
      SELECT 1
        INTO vn_cnt
        FROM JOBS
       WHERE JOB_ID = p_job_id;

      UPDATE employees
         SET job_id = p_job_id
      WHERE employee_id = p_employee_id;

      COMMIT;

    EXCEPTION 
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
      DBMS_OUTPUT.PUT_LINE(p_job_id ||'에 해당하는 job_id가 없습니다');
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('기타 에러: ' || SQLERRM);
    END;

결과

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

입력

    EXEC ch10_upd_jobid_proc (200, 'SM_JOB2');

결과

    ORA-01403: 데이터를 찾을 수 없습니다.
    SM_JOB2에 해당하는 job_id가 없습니다

미리 정의된 예외 중 NO_DATA_FOUND라는 예외를 사용하니 굳이 IF문을 사용할 필요가 없어졌다. 맨 처음 SELECT INTO문에서 해당 job_id를 발견하지 못하면 NO_DATA_FOUND 예외가 발생해 제어권이 예외 처리부로 넘어가 메시지를 출력하고 프로시저가 종료된 것이다. 그리고 마지막에 OTHERS를 사용한 것은, NO_DATA_FOUND 이외의 다른 예외가 발생할 것을 염두에 두고 처리한 것이다.