[Oracle] PL/SQL 예외처리

출처 : http://goalker.tistory.com/entry/PLSQL-%EC%98%88%EC%99%B8%EC%B2%98%EB%A6%AC

컴파일 에러 - PL/SQL 블록이 PARSE 되는 동안 오타 등으로 인해 발생되는 에러

런타임 에러 - PL/SQL 블록이 실행되는 동안에 발생되는 에러, 이 런타임 에러를 오라클에서 예외라고 부름

오라클의 예외 종류 - 오라클에서 미리 제공하는 오라클 예외

   - 사용자에 의해 정의되는 사용자 정의 예외

오라클 예외 : 오라클이 정의한 상황에서 자동적으로 발생 

- 이름이 정해진 예외 : Predefined ORACLE Exception

- 이름이 정해지지 않은 예외 : Non-predefined ORACLE Exception

사용자 정의 예외 : 선언부에서 exception 형으로 예외를 선언한 후 실행부 또는 예외처리부에서 RAISE문을 통해 

  명시적으로 예외 발생

예외 처리 사용하기

예외를 처리하기 위해서 PL/SQL 블록에는 예외처리부(EXCEPTION) 라는 부분이 있는데 이부분에 처리문장을 적으면 됨

문법 : 

EXCEPTION

WHEN exception1 [OR exception2 ...] THEN

statement1 ;

statement2 ;

....

[WHEN exception3 [OR exception4 ...] THEN

statement3 ;

statement4 ;

... ]

[WHEN OTHERS THEN

statementN ;

statementN+1 ;

... ]

- exception N

실행부에서 발생한 예외의 이름들로 해당되는 WHEN 절 안의 문장들을 수행

- OTHERS 

이전의 WHEN 에 해당하는 예외를 처리하지 않고 가장 마지막에 기술되는 WHEN 에 해당하는 예외 만 사용

예외처리 사용 예 1: 오라클에서 사전 정의된 예외 사용하기

사원명이 'A' 로 시작하는 사원을 조회하여 묵시적 커서를 사용하여 출력하되 여러 건의 데이터가 나올 경우 에러발생하는 예외처리 생성

SQL>SELECT ename

FROM emp

WHERE ename LIKE 'A%' ;

-> ENAME

    -------

ALLEN

ADAMS    -- A 로 시작하는 사원 2명 검색

사원의 데이터가 2인데 명시적 커서를 사용해 PL/SQL을 사용해야 하지만 묵시적 커서를 사용하여 TOO_MANY_ROWS 라는 예외가 발생, 이 경우 예외 처리 기능을 사용하여 보다 좋게 처리 하는 예

SQL>DECLARE

v_ename    emp.ename%TYPE ;

BEGIN

SELECT ename    INTO v_ename

FROM emp

WHERE ename LIKE 'A%' ;

DBMS_OUTPUT.PUT_LINE ('사원명은'||v_ename||' 입니다') ;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE(' 해당 사원이 없습니다') ;

WHEN TOO_MANY_ROWS THEN

DBMS_OUTPUT.PUT_LINE(' 사원이 두 명 이상입니다') ;

END ;

/

Enter value for dno : 10

해당 부서에 사원이 존재하므로 삭제할 수 없습니다

위에 굵은 글자가 예외 처리 부 이며 위와 같이 발생시킬 예외들을 지정해 주면 됨

예외처리 사용 예 2 : PRAGMA 를 사용하여 에러메시지 변경하기

PRAGMA 란 키워드는 특정 에러코드와 예외 메시지를 연결해주는 역할

두 개의 테이블이 서로 Foreign Key 가 설정되어 있는 상태에서 부모테이블에서 부서 번호를 입력 받아 해당 부서를 삭제하되 자식 테이블에 해당 부서에 소속된 사원이 있을 경우 '해당 부서에 사원이 존재하므로 삭제할 수 없습니다 ' 라는 예외를 발생시키는 예제.

SQL> DECLARE

2        sawon_exist EXCEPTION ;

3        PRAGMA EXCEPTION_INIT(sawon_exist,-2292) ;

4    BEGIN

5        DELETE FROM dept

6        WHERE deptno=&dno ;

7        COMMIT ;

8    EXCEPTION

9        WHEN sawon_exist THEN

10      DBMS_OUTPUT.PUT_LINE('해당 부서에 사원이 존재하므로 삭제할 수 없습니다') ;

11  END ;

12   /

-> PRAGMA EXCEPTION_INIT는 컴파일러에게 예외 이름을 Oracle 오류 번호와 연관시키도록 지시

이렇게 하면 모든 내부 예외를 이름으로 참조하고 이 예외에 대한 특정 처리기를 작성

3 번째 줄 끝에 -2292 는 에러 발생시 나오는 오라클 에러 번호

예외처리 사용 예 3: RAISE 를 사용하여 예외 상황 처리하기

아래의 예는 empno 를 입력 받은 후 emp 테이블에서 해당 사원을 지우는 작업을 수행

단, 없는 사원번호를 입력할 경우 '사원이 없습니다' 라는 예외 메시지 출력

SQL> DECLARE

no_empno EXCEPTION ;

   BEGIN

DELETE FROM EMP

WHERE empno = &empno ;

IF SQL%NOTFOUND THEN

RAIS no_empno ;

END IF ;

   EXCEPTION   

WHEN no_empno THEN

DBMS_OUTPUT.PUT_LINE('조회한 사번의 사원은 없습니다') ;

   END ;

          /

   Enter value for empno : 7901

   조회한 사번의 사원은 없습니다

예외처리 사용 예 4: RAISE_APPLICATION_ERROR 프로시저 사용하기

앞에서 사용한 3가지 예는 모두 예외 처리부에서 각각의 예외를 처리하는 방식

지금 살펴보는 예는 RAISE_APPLICATION_ERROR 프로시저를 사용하여 사용자가 특정 상황에 발생할 에러를 정의하고 예외 처리부를 사용하지 않고 실행부에서 즉시 예외처리 하는 방식.

이때 사용자가 임의로 지정 가능한 에러번호는 20000 ~ 20999 번 까지

SCOTT> SET VERIFY OFF

SCOTT> BEGIN

DELETE FROM emp WHERE empno = &empno ;

IF SQL%NOTFOUND THEN

RAISE_APPLICATION_ERROR(-20001, '존재하지 않는 사번입니다') ;

END IF ;

END ;

/

Enter value for empno : 7901

BEGIN

*

ERROR at line 1 :

ORA-20001 : 존재하지 않는 사번입니다

ORA-06512 : at line 4

4번 라인에 RAISE_APPLICATION_ERROR 프로시저를 사용하여 에러메시지를 정의.

위 에러 부분을 보면 RAISE_APPLICATION_ERROR 프로시저에 기록된 에러메시지가 보이는 것을 확인 할 수 있다

예외처리 사용 예 5: SQLCODE 와 SQLERRM 사용하기

어떤 에러가 발생했는지 확인하기위해 SQLCODE 와 SQLERRM 을 사용

아래 예는 사용자로부터 empno 를 입력받아서 해당 empno 의 ename을 화면에 출력하는 코드

이 때 사용자가 없는 empno 를 입력할 경우 발생하는 Error code 와 Error 내용을 확인하기 위해 SQLCODE 와 SQLERRM을 사용

SCOTT>SET VERIFY OFF

SCOTT>DECLARE

name    emp.ename%TYPE ;

v_code    NUMBER ;

v_errm    VARCHAR2(64) ;

BEGIN

SELECT ename INTO name FROM emp WHERE empno = &eno ;

 EXCEPTION

   WHEN OTHERS THEN

v_code := SQLCODE ;

v_errm := SUBSTR(SQLERRM,1,64) ;

DBMS_OUTPUT.PUT_LINE('The error code is '||v_code||'-'||v_errm) ;

END ;

/

Enter value for eno : 7902

-> 존재하는 empno 라서 에러가 생기지 않음

SCOTT>/

Enter value for eno : 7900

The error code is 100-ORA-01403:no data found   <-- 존재하지 않는 번호라서 에러 발생