오라클 예외처리

 

//*****************************************
-- 예외처리
  - PL/SQL 을 실행 하는 동안에 발생하는 에러 처리

  - 예외 트랩(trap)
     만일 예외가 블록의 실행 가능한 섹션에서 발생한다면, 처리는 블록의 예외 섹션에서 해당 예외 처리기로 제어가 넘어 간다. PL/SQL 블록이 성공적으로 예외를 처리 한다면 이때 예외는 둘러싸는 블록이나 환경으로 전달 되지 않는다.

  -- 예외 전달
      예외를 처리하는 다른 방법은 실행 환경으로 예외를 전달하도록 하는 것이다. 예외가 블록의 실행부에서 발생하여 해당 예외 처리기가 없다면, PL/SQL 블록의 나머지 부분은 수행되지 못하고 종료된다.

  - 예외 검출
      예외가 블록의 실행부에서 발생하면 블록의 예외부에 있는 해당 예외 처리부로 제어가 넘어간다.

  - 예외의 유형
     실행 중에 ERROR가 발생하면 프로그램이 중단되지 않고 예외에 대한 프로그램을 할 수 있다.

    정의된 ORACLE SERVER ERROR : PL/SQL코드에서 자주 발생하는 ERROR을 미리 정의함(선언할 수 없고 ORACLE SERVER이 암시적으로 발생)
    정의되지 않은 ORACLE SERVER ERROR : 기타 표준 ORACLE SERVER ERROR(사용자가 선언하고 ORACLE SERVER이 그것을 암시적으로 발생)
   사용자 정의 ERROR : 프로그래머가 정한 조건이 만족되지 않을 경우 발생(사용자가 선언하고 명시적으로 발생한다.)

  - 예외정의
EXCEPTION
 WHEN  exception1 [OR exception2, . . . .] THEN
  statement1;
  statement2;
  . . . . . .
 [WHEN  exception2 [OR exception3, . . . .] THEN
  statement3;
  statement4;
  . . . . . .]
 [WHEN  OTHERS THEN
  statement5;
  statement6;
  . . . . . .]
END;

  OTHERS : 명시적으로 선언되지 않은 모든 예외를 트랩하는 예외 처리 절

//*****************************************
-- 정의돤 오라클 서버 에러
   NO_DATA_FOUND(ORA-01403) : 데이터를 RETURN하지 않는 SELECT문장
   TOO_MANY_ROWS(ORA-01422) : 단일 행 SELECT는 하나 이상의 행을 RETURN

----------------------------------------------------
DECLARE
    TYPE insa_rec IS RECORD (
        v_num insa.num%TYPE
        ,v_name insa.name%TYPE
        ,v_basicpay insa.basicpay%TYPE
    );
    rec insa_rec;
   
BEGIN
    SELECT num, name, basicpay INTO rec FROM INSA WHERE num=1001;
    -- SELECT num, name, basicpay INTO rec FROM INSA WHERE num=9001;
    -- SELECT num, name, basicpay INTO rec FROM INSA;
   
    IF rec.v_name = '홍길동' THEN
        UPDATE INSA SET name='김길동'  WHERE num=rec.v_num;
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('데이터가 없습니다.');
     WHEN TOO_MANY_ROWS THEN
        -- 단일행 SELECT문은 하나의 행만 리턴해야 한다.
        DBMS_OUTPUT.PUT_LINE('데이터가 여러개입니다.');  
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('기타 에러입니다.');
END;
/

SELECT * FROM INSA;
ROLLBACK;

SELECT * FROM INSA;

//*****************************************
-- 사용자 정의 예외
  -- 선언 절차
    1. 선언 섹션에서 사용자가 선언한다.
        exception_name EXCEPTION;

        exception_name : 예외 이름을 정의

    2. 실행 섹션에서 명시적으로 예외를 발생하기 위해 RAISE문장을 사용한다.
        RAISE exception_name;

        exception_name : 앞에서 선언된 예외 이름을 기술한다.

    3. 해당 예외 처리기 안에 선언된 예외를 참조한다.

----------------------------------------------------
DECLARE
    TYPE insa_rec IS RECORD (
        v_num insa.num%TYPE
        ,v_name insa.name%TYPE
        ,v_basicpay insa.basicpay%TYPE
    );
    rec insa_rec;
   
    insa_basicpay_check EXCEPTION;
   
BEGIN
    SELECT num, name, basicpay INTO rec FROM INSA WHERE num=1001;
    IF rec.v_basicpay > 2500000 THEN
        RAISE insa_basicpay_check;
    END IF;

    DBMS_OUTPUT.PUT_LINE(rec.v_name || '   ' || rec.v_basicpay);
EXCEPTION
    WHEN insa_basicpay_check THEN
        DBMS_OUTPUT.PUT_LINE('기본급이 250만원 이상입니다.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('기타 에러입니다.');
END;
/

//*****************************************
-- 예외 트래핑 함수
   에러가 발생 했을 때 두 함수를 사용하여 관련된 에러 코드 또는 메시지를 확인할 수 있다. 코드 또는 메시지에 따라 에러에 대해 취할 작업을 정할 수 있다.

  - SQLCODE : 에러코드
    0 : 에러 없이 정상적으로 실행되었음을 의미
    1 : 사용자가 정의한 에러가 발생했음을 의미
    +100 : 조건을 만족하는 행이 없음을 의미
    양수값 : 다른 오라클 에러가 발생했음을 의미
  - SQLERRM : 에러 메시지

----------------------------------------------------
DECLARE
    TYPE insa_rec IS RECORD (
        v_num insa.num%TYPE
        ,v_name insa.name%TYPE
        ,v_basicpay insa.basicpay%TYPE
    );
    rec insa_rec;
   
BEGIN
    SELECT num, name, basicpay INTO rec
                FROM INSA WHERE num=8001;
   
    IF rec.v_name = '홍길동' THEN
        UPDATE INSA SET name='김길동' WHERE num=rec.v_num;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('에러 코드 : ' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE('에러 메시지 : ' || SQLERRM);
END;
/

//*****************************************
-- RAISE_APPLICATION_ERROR
  - 형식
    RAISE_APPLICATION_ERROR(error_number, error_message);
    RAISE_APPLICATION_ERROR(error_number, error_message, keep_errors);
  - 표준화되지 않은 에러 코드와 에러 메시지를 리턴하기 위해 RAISE_APPLICATION_ERROR() 프로시져 사용
  - RAISE_APPLICATION_ERROR를 만나면 처리를 중단하며, 자바등의 어플리케이션에는 에러를 던져주어 어플리케이션에서는 SQLException을 이용해서 에러를 확인할 수가 있다.
  - error_message : 출력할 메시지
  - error_number : -20999~-20000사이의 수 사용
  - keep_errors : TRUE로 설정하면, stack 처럼 에러 리스트를 보존 가능하다. FALSE로 하면, 덮어쓰기를 한다. default로는 FALSE.

----------------------------------------------------
DECLARE
    v_num insa.num%TYPE;
BEGIN
    v_num := 8001;
    DELETE FROM insa WHERE num=v_num;
               
    IF SQL%NOTFOUND THEN
        RAISE_APPLICATION_ERROR(-20100, '자료가 없음');
    END IF;
END;
/