[Oracle] PL/SQL 기초9 - 예외처리(Exception)

[Oracle] PL/SQL 기초9 - 예외처리(Exception)

 

안녕하세요. 갓대희 입니다. 이번 포스팅은 [ [Oracle] PL/SQL 기초 - 예외처리(Exception) ] 입니다. : ) 

0.Oracle PL/SQL 예외 처리 (Exception Handling)

1) 컴파일 에러(Compilation Error)

 - PL/SQL 블록을 Parsing, 컴파일 하는동안 문법적인 오류로 발생 하는 동안 발생하는 에러

 

2) 런타임 에러 (Run-Time Error)
 - PL/SQL 블록을 실행하는 도중 발생하는 에러, 이를 Exception이라고 한다.
 - 즉 오라클에서는 런타임 에러를 예외(Exception)라고 부른다. 이 예외 처리에 대해 오늘은 정리해 보려고 한다.
 - 일반적인 SQL에서는 에러가 발생했을 때 처리할 수 있는 방법이 없지만, PL/SQL에서는 예외 발생 시 이를 처리(Handling)할 수 있는 예외 처리(Exception Handling)를 지원한다.

 

1. 예외 처리 발생 상황

1) Oracle 오류가 발생하면 관련 예외가 자동적, 암시적으로 발생한다.

ex)

DECLARE
    넘버변수 NUMBER(10);
    출력변수 NUMBER(10);
BEGIN
    넘버변수 := 10;
    출력변수 := 넘버변수 / 0;
    DBMS_OUTPUT.PUT_LINE(넘버변수);
    DBMS_OUTPUT.PUT_LINE(출력변수);
END;

위의 예시 PL/SQL문을 실행하면 암시적으로 다음 에러가 발생한다.
ORA-01476: divisor is equal to zero

 

2) PL/SQL 블록에 RAISE 문을 사용하여 명시적으로 발생시킬 수 있다.
 - 발생시키는 예외는 사용자가 정의한 예외 또는 미리 정의 예외일 수도 있는데, 이 부분은 좀더 자세히 다음 내용에서 알아보자.

 

2. 예외 처리 기본 문법

▶ 1. 예외 처리 기본 문법

 - 예외 처리는 EXCEPTION부터 시작 하여 예외가 발생하면 여러 개의 예외 처리부 중에 하나의 예외 처리부에서 트랩(Trap) 된다. 
 - WHEN OTHERS절은 맨 마지막 온다.

DECLARE
    -- 예외선언
    -- 예외 이름 EXCEPTION;
BEGIN
    -- 예외 발생
    -- RAISE 예외 이름;

EXCEPTION
WHEN 예외1(사전 정의된 오류 또는 사용자 정의 오류) THEN
    STATEMENT1 ...
WHEN 예외2(사전 정의된 오류 또는 사용자 정의 오류) THEN
    STATEMENT2 ...
WHEN OTHERS THEN
    STATEMENT3 ...
END;

 

 - 기본 프로그래밍 Exception 처리와 매우 유사한 점을 확인할 수 있다. 기본 Exception이 OTHERS와 동일한 처리라고 볼 수 있다.

try {
    프로그래밍 라인
    //... 이하 생략
} catch (IOException e) {
    e.printStackTrace();
} catch (NullPointerException e) {
    e.printStackTrace();
} catch (Exception e) {
    e.printStackTrace();
}

 

3. 예외의 종류

▶ 1. 오라클 예외 (Oracle Exception) (System Exception)

 - 사실 발생할 수 있는 Exception의 경우의 수는 너무 많기 때문에 모든 예외를 사용자가 미리 정의할 수 없다.
 - 이에 PL/SQL코드에서 자주 발생하는 ERROR을 미리 정의해 두었고, Predefined ORACLE Exception과 Non-Predefined ORACLE Exception이 있다.

 

1) 사전에 정해진 예외(Predefined ORACLE Exception)

 - PL/SQL에서 자주 발생하는 약20개의 오류를 미리 정의해 두었기 때문에 선언할 필요가 없으며, 예외 절로 자동 트랩(Trap) 된다.
 
 ※ 사전에 정의된 예외로 호출 시 SQLCODE에 반환되는 값인 오라클 오류 번호 및 간단한 설명
 ※ (참고)SQLCODE
 - 가장 마지막에 실행된 SQL이나 DML문의 상태 코드를 반환하는 PL/SQL 내장 함수.
 - 마지막 문장에 오류가 없다면 SQLCODE는 0을 반환한다.
 - 한 경우(100, NO_DATA_FOUND의 ANSI 표준 오류 번호)만 제외하고 SQLCODE 값은 오라클 오류 코드와 동일하다.

예외명	예외 코드	설명
ACCESS_INTO_NULL	ORA-06530	정의되지 않은 Obejct 속성에 Value을 할당 하고자 했을 때 발생되는 예외. 즉 객체 초기화 되지 않은 상태에서 사용했을떄 발생되는 예외.
CASE_NOT_FOUND	ORA-06592	CASE 문의 구문 오류. 예를 들어 CASE문의 WHEN 절에 해당되는 조건이 없고 ELSE 절도 없을 경우에 발생되는 예외.
INVALID_CURSOR	ORA-01001	존재하지 않는 커서를 참조하려 할 때 발생되는 예외. 일반적으로 커서 OPEN전에 FETCH하거나 CLOSE할때 발생 한다.
CURSOR_ALREADY_OPEN	ORA-06511	이미 열려진 커서를 열려고 시도 했을 때 발생되는 예외. 커서를 OPEN하거나 재 OPEN하기 위해 커서를 CLOSE해야한다.
DUP_VAL_ON_INDEX	ORA-00001	유일인덱스에 중복값을 입력(Insert)하거나 업데이트(Upldate)하는 경우 발생되는 예외.
INVALID_NUMBER	ORA-01722	문자열을 숫자로 변환하는 SQL문이 실패했을 경우 발생되는 예외.
LOGIN_DENIED	ORA-01017	잘못된 사용자 이름이나 비밀번호로 로그인을 시도 하였을때 발생되는 예외.
NO_DATA_FOUND	ORA-01403	결과가 없는 SELECT INTO문(묵시적 커서)을 실행할 때 발생되는 예외. PL/SQL 테이블의 초기화되지 않은 행을 참조할 때 발생되는 예외. 패키지 UTL_FILE로 파일의 내용이 끝난 다음에 내용을 읽어 들일 때 발생.
NOT_LOGGED_ON	ORA-01012	오라클 RDBMS에 접속하기 전(로그인 전)에 데이터베이스를 호출하는 경우 발생.
PROGRAM_ERROR	ORA-06501	PL/SQL 코드상에서 내부 오류를 만났을 때 발생. 이 오류가 발생하면 "오라클에 문의(Contact Oracle Support)"란 메시지 출력.
STORAGE_ERROR	ORA-06500	프로그램 수행 시 메모리가 부족할 경우(메모리 초과) 발생.
TIMEOUT_ON_RESOURCE	ORA-00051	데이터베이스 자원을 기다리는 동안 타임아웃 발생 시 발생.
SUBSCRIPT_BEYOND_COUNT	ORA-06533	컬렉션의 요소 개수보다 더 큰 첨자 값으로 참조한 경우 발생되는 예외.
SUBSCRIPT_OUTSIDE_LIMIT	ORA-06532	컬렉션의 범위 밖의 참조가 일어났을 때 발생되는 예외.
TOO_MANY_ROWS	ORA-01422	하나 이상의 결과 값을 반환하는 SELECT INTO문을 실행한 경우. (SELECT INTO는 한 행만 반환할 수 있다.)
VALUE_ERROR	ORA-06502	수치 또는 값 오류.
ZERO_DIVIDE	ORA-01476	0으로 나눌 때 발생하는 오류.
TRANSACTION_BACKED_OUT	ORA-00061	명시적으로 ROLLBACK을 실행했거나 다른 동작의 결과로 원격 트랜잭션 부분이 롤백되는 경우
ROWTYPE_MISMATCH	ORA-06504	할당문에서 호스트 커서 변수와 PL/SQL 커서 변수의 데이터 형이 불일치 할 때 발생되는 예외.

 

EX) 위의 예제에서 0으로 나눴을 경우 에러에 대한 예외 처리를 하지 않았을 경우를 먼저 다시 살펴보자.

DECLARE
    넘버변수 NUMBER(10);
    출력변수 NUMBER(10);
BEGIN
    넘버변수 := 10;
    출력변수 := 넘버변수 / 0;
    DBMS_OUTPUT.PUT_LINE(넘버변수);
    DBMS_OUTPUT.PUT_LINE(출력변수);
END;

ORA-01476: divisor is equal to zero

 

EX) 예외처리를 하였을 때 다양한 처리가 가능하다.

DECLARE
    넘버변수 NUMBER(10);
    출력변수 NUMBER(10);
BEGIN
    넘버변수 := 10;
    출력변수 := 넘버변수 / 0;
    DBMS_OUTPUT.PUT_LINE(넘버변수);
    DBMS_OUTPUT.PUT_LINE(출력변수);
    
    EXCEPTION
    WHEN ZERO_DIVIDE THEN
    	출력변수 := 0;
    	DBMS_OUTPUT.PUT_LINE('0으로 나눌 수 없습니다. 기본앖 0으로 치환 합니다.');
END;

>> 0으로 나눌 수 없습니다. 기본앖 0으로 치환 합니다.

 

EX) 사원조회가 되지 않는경우, 2명 이상인 경우 에러처리 예제

DECLARE
    V_EMPNO    EMP.EMPNO%TYPE ;
BEGIN
    SELECT 	EMPNO
    INTO 	V_EMPNO
    FROM 	EMP
    WHERE 	ENAME = '갓대희' ;

    DBMS_OUTPUT.PUT_LINE ('사원번호는 '|| V_EMPNO ||' 입니다.') ;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('해당하는 사원이 없습니다.') ;
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('사원이 두 명 이상입니다.') ;
END ;

 

2) 사전에 정해지지 않은 예외(Non-predefined ORACLE Exception)

 - 사전에 정의된 오라클 서버 오류(Predefined ORACLE Exception)를 제외한 모든 오류
 - 사용자가 선언부에서 예외명을 정의해야 하며, Oracle Server에서 제공하는 Error 번호를 사용하여, 정의된 예외와 연결한 후 EXCEPTION절에서 정의된 예외를 사용한다.
 - PRAGMA EXCEPTION_INIT문장으로 예외의 이름과 오라클 서버 오류 번호를 연결하여 예외 처리. (PRAGMA 란 키워드는 특정 에러코드와 예외 메시지를 연결해주는 역할을 함)
 - 위에서 표현한 SQLCODE 번호를 참고하여 사용하면된다. 예를 들어 이해하면 더 편할 것 같다.
 

EX)

DECLARE
    V_EMPNO    EMP.EMPNO%TYPE;
    EMP_NOT_EXIST 	EXCEPTION;
    PRAGMA EXCEPTION_INIT(EMP_NOT_EXIST,+100);
    MANY_EMP_EXIST 	EXCEPTION;
    PRAGMA EXCEPTION_INIT(MANY_EMP_EXIST,-1422);
    
BEGIN
    SELECT 	EMPNO
    INTO 	V_EMPNO
    FROM 	EMP
    WHERE 	ENAME = '갓대희' ;

    DBMS_OUTPUT.PUT_LINE ('사원번호는 '|| V_EMPNO ||' 입니다.') ;
    EXCEPTION
    WHEN EMP_NOT_EXIST THEN
        DBMS_OUTPUT.PUT_LINE('해당하는 사원이 없습니다.') ;
    WHEN MANY_EMP_EXIST THEN
        DBMS_OUTPUT.PUT_LINE('사원이 두 명 이상입니다.') ;
END ;

 

EX) 물론 다음과 같이 예외 처리 코드를 직접 처리할 수 있지만 명시적, 직관적이지 않기 때문에 위와 같이 처리하는 것이 좋을 것 같다.

DECLARE
    V_EMPNO    	EMP.EMPNO%TYPE;
BEGIN
    SELECT 	EMPNO
    INTO 	V_EMPNO
    FROM 	EMP
    WHERE 	ENAME = '갓대희';

    DBMS_OUTPUT.PUT_LINE ('사원번호는 '|| V_EMPNO ||' 입니다.');
    EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE = +100 THEN
            DBMS_OUTPUT.PUT_LINE('해당하는 사원이 없습니다.');
        END IF;
END;

 

▶2. 사용자 정의 예외(User-Defined Exceptions)

 - 선언부에서 예외를 직접 선언한 후 실행부 또는 예외처리부에서 RAISE문을 통해 명시적으로 예외 발생
 - RAISE_APPLICATION_ERROR 프로시저를 사용하여 오류코드 -20000부터 -20999의 범위 내에서 사용자 정의 예외를 만들수 있다.

 

1) 사용자 정의 예외 처리 문법

 - RAISE_APPLICATION_ERROR (error_number, message [, {TRUE | FALSE}];)
 - ERROR_NUMBER : 20000~20999까지의 에러 숫자는 비워져 있다. 해당 숫자 사용 가능
 - TRUE, FALSE는 이미 정의된 에러 메시지를 출력할 것인지 여부

CREATE OR REPLACE 프로시저명 (변수명%변수타입)
IS
-- 1.선언부에 예외의 이름을 선언
사용자정의예외 EXCEPTION;
BEGIN

IF 특정상황 THEN
	-- 2.RAISE문을 사용하여 명시적으로 예외를 발생시킨다.
	RAISE 사용자정의예외;

EXCEPTION
	-- 3.예외가 발생시 해당 예외 참조하여 예외 처리.
	WHEN 사용자정의예외 THEN
		DBMS_OUTPUT.PUT_LINE('내가 발생 시킨 예외');
END;

 

EX) 

DECLARE
	V_EMPNO    	EMP.EMPNO%TYPE;
    RESULT_CNT NUMBER := 0;
    NOT_EXIST_PHONE EXCEPTION;
BEGIN
	SELECT 	COUNT(*)
    INTO 	RESULT_CNT
	FROM 	EMP
	WHERE 	ENAME = '갓대희';
    
    IF RESULT_CNT > 0 THEN
    	SELECT 	EMPNO
        INTO 	V_EMPNO
        FROM 	EMP
        WHERE 	ENAME = '갓대희';
    
    	DBMS_OUTPUT.PUT_LINE ('사원번호는 '|| V_EMPNO ||' 입니다.');
    ELSE 
        RAISE NOT_EXIST_PHONE;
    END IF;

	EXCEPTION
    WHEN NOT_EXIST_PHONE THEN
    	DBMS_OUTPUT.PUT_LINE('해당하는 사원이 없습니다.');
END;