오라클 PL/SQL 예외처리(Exception) 종류부터 사용방법 정리

오라클 PL/SQL 에서는 Exception을 사용할 수 있는데

한글로 읽어보면 예외라는 뜻이 되겠다

 

함수나 프로시저 같은 PL/SQL 수행 시

숫자만 들어올 수 있는 컬럼에 문자를 넣는다거나

0으로 나눈다거나, 컬럼 사이즈보다 큰 값을 넣을 경우 등의

에러가 발생할 경우 EXCEPTION을 작성해 두면

에러가 발생하지 않고 EXCEPTION으로 빠지면서

설정한 쿼리를 수행하게 된다

 

EXCEPTION의 위치는

PL/SQL을 수행하는 BEGIN ~ END; 라인에서

END 바로 위에 작성하게 되는데

 

EXCEPTION

WHEN EXCEPTION EXCEPTION명 THEN

BEGIN

END;

WHEN OTHERS THEN (맞는 조건이 없을 경우, 필수 아님)

BEGIN

END;

 

식으로 사용하게 된다

 

실 사용시에는 잘못된 값을 넣어

에러가 발생할 경우 EXCEPTION으로 보내

뭐가 문제인지를 알려주게 하는 식으로 쓰는 경우가 많다

 

EXCEPTION에는 오라클에서 제공하는 Exception과

유저가 직접 만들어 쓰는 Exception이 있는데

먼저 오라클에서 제공하는 Exception은 다음과 같고

실 사용시에는 EXCEPTION명 위치에 아래 Exception을 넣어주면 된다

 

ACCESS_INTO_NULL	초기화하지 않은 객체 속성에 값을 할당하려고 할 경우
CASE_NOT_FOUND	어떤 CASE WHEN에도 해당하지 않고 ELSE도 작성하지 않았을 경우
COLLECTION_IS_NULL	초기화하지 않은 nested table 혹은 varray 에 컬렉션 메소드를 적용하려고 하거나 요소(element)에 값을 할당하려고 할 경우
CURSOR_ALREADY_OPEN	이미 열려있는 커서를 또 열려고 하는 경우
DUP_VAL_ON_INDEX	중복 금지(UNIQUE)인 컬럼에 중복값을 넣을 경우
INVALID_CURSOR	허용되지 않은 커서 작업 수행 시
INVALID_NUMBER	문자를 숫자로 변환 시 제대로 변환되지 않은 경우
LOGIN_DENIED	오라클에 틀린 아이디/패스워드로 로그인 시
NO_DATA_FOUND	SELECT 시 조회결과가 없을 경우
NOT_LOGGED_ON	오라클에 접속하지 않고 PL/SQL 호출 시
PROGRAM_ERROR	PL/SQL 내부적인 문제가 있을 시
ROWTYPE_MISMATCH	호스트 커서 변수, PL/SQL 커서 변수의 반환형이 호환이 되지 않을 경우
SELF_IS_NULL	프로그램이 멤버 메소드 호출 시 객체 유형의 인스턴스가 초기화되지 않았을 경우
STORAGE_ERROR	PL/SQL 수행 시 메모리가 부족하거나 손상 시
SUBSCRIPT_BEYOND_COUNT	nested table 혹은 varray 의 전체 인덱스보다 더 큰 값의 인덱스를 사용했을 경우
SUBSCRIPT_OUTSIDE_LIMIT	nested table 혹은 varray 에서 존재할 수 없는 인덱스를 사용한 경우(-1 같은.. 원래는 0부터 시작)
SYS_INVALID_ROWID	문자열을 ROWID로 변환할 수 없을 경우
TIMEOUT_ON_RESOURCE	리소스 대기시간을 초과할 경우
TOO_MANY_ROWS	SELECT INTO 시 2줄 이상을 INTO 해줄 경우
VALUE_ERROR	산술, 변환, 잘라내기, 크기 제한을 넘길 경우(컬럼 크기를 넘기거나, 숫자 컬럼에 문자를 넣는 등의..)
ZERO_DIVIDE	0으로 나누려 할 경우

 

다음으로는 직접 만들어 사용하는

커스텀 Exception 처리인데

Exception 선언

Exception 호출

Exception 수행

세 파트를 작성해야 한다

 

예제로 작성한 CUSTOM_PROC 프로시저인데

 

보면 처음 IS 아래에서

NUM_CHECKER EXCEPTION; 으로 EXCEPTION을 선언하고

NUM > 10 하단에

RAISE EXCEPTION명; 으로 EXCEPTION을 호출하고

 

하단의 EXCEPTION 아래의 WHEN EXCEPTION명 THEN

으로 해당 EXCEPTION일 시 작성한 쿼리를 수행하게 된다

 

위 예제의 프로시저는 숫자 NUM 파라미터를 받는데

NUM이 10보다 클 경우 NUM_CHECKER EXCEPTION을 호출하고

그렇지 않을 경우에는 NO_DATA_FOUND를 호출하는데

 

NO_DATA_FOUND는 EXCEPTION 부분에 따로 작성한 것이 없으니

WHEN OTHERS THEN으로 빠지게 된다

 

실제 디버거를 이용해 프로시저를 수행해 보면

NUM을 12를 넣으니 조건에서 걸려

NUM_CHECKER EXCEPTION을 호출하고

OUT_MSG에 '10이 넘어갑니다'를 할당하고

RETURN을 하며 프로시저가 끝나는 것이 보인다

 

오라클 설치 시에는

Oracle SQL Developer가 같이 설치되기 때문에

아래 링크를 통해 복붙만 해도 바로 테스트가 가능하다

DBeaver 오라클 펑션/프로시저 디버깅 방법

마지막으로 테스트에 사용한 예제는 아래와 같다

오라클에서 제공하는 EXCEPTION을 사용한 프로시저

 

create or replace
PROCEDURE PROC_TEST2(
  NUM IN NUMBER,
  OUT_MSG OUT VARCHAR2
)
IS
BEGIN -- PL/SQL, EXCEPTION
  INSERT INTO SHIT(ONE) VALUES('AA');
  
  EXCEPTION
    WHEN ACCESS_INTO_NULL THEN
      BEGIN
        OUT_MSG := 'EXCEPTION 발생';
      END;  
END;

 

커스텀 EXCEPTION을 사용한 프로시저

 

create or replace
PROCEDURE CUSTOM_PROC(
  NUM IN NUMBER, -- 가지고 들어올 파라미터
  OUT_MSG OUT VARCHAR2 -- 가지고 나갈 파라미터
)
IS
  NUM_CHECKER EXCEPTION; -- EXCEPTION 선언
BEGIN -- PL/SQL, EXCEPTION
  IF NUM > 10 THEN -- EXCEPTION 조건 설정
    RAISE NUM_CHECKER; -- EXCEPTION 호출
  END IF;
  
  RAISE NO_DATA_FOUND; -- OTHERS 확인용 호출
  
  EXCEPTION
    WHEN NUM_CHECKER THEN -- EXCEPTION 조건에 맞을 경우
      BEGIN
        OUT_MSG := '10이 넘어갑니다!';
        RETURN; -- RETURN이 없을 경우 쿼리를 계속 수행함
      END;
    WHEN OTHERS THEN  -- OHTER은 조건에 맞는 경우가 없을 경우
      BEGIN
        OUT_MSG := 'OHTER 확인용!';
      END;
      
  OUT_MSG := '프로시저 수행 완료!';
END;