Chapter 6 PL SQL(Procedural Language/SQL) - 8

PLT 6.8 PL/SQL로 예외 처리

PL/SQL 코드를 실행할 때 error 발생하는 경우가 있다. Error는 예외(Exception)를 발생시켜PL/SQL 블록을 중지시키고 예외 처리기 부분으로 제어가 이동한다. Exception handler는Exception을 검출하고 조건에 따라 조치 작업을 할 수 있다.

예외 처리란

예외는 PL/SQL 블록의 실행 중에 발생하여 블록의 주요 부분을 중단 시킨다. 항상 PL/SQL 예외가 발생할 때 블록은 항상 종료되지만 마지막 조치 작업을 수행하도록 예외 처리 부분을 작성할 수 있다.

1.   예외란 무엇인가 ?

- PL/SQL을 실행 동안에 발생하는 error 처리를 의미한다.

2.   어떻게 발생되는가 ?

- Oracle 오류가 발생할 때

- 사용자가 직접 발생시킬 수 있다.

3.   처리하는 방법은 무엇인가 ?

- 처리기를 이용한다.

- 실행 환경에 전달한다.

예외를 발생시키는 두 가지 방법

Oracle 오류가 발생하면 관련된 예외가 자동적으로 발생한다. 예를 들어, 오류 ORA-014-3는 데이터베이스에서 검색된 행이 전혀 없을 때 발생하며 PL/SQL은 NO_DATA_FOUND라 는 예외를 발생시킨다.

블록에 RAISE문을 써서 명시적으로 예외를 발생시킬 수 있다. 발생하는 예외를 사용자가 정의한 것일 수도 있고 미리 정의된 것일 수도 있다.

예외 처리

예외 트랩(trap)

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

예외 전달

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

예외 검출

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

예외의 유형

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

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

예외 정의

PL/SQL블록의 예외 섹션 내에서 해당 루틴을 포함하므로 모든 에러를 처리할 수 있다. 각각의 에러 처리기는 WHERE절로 구성되는데 그 곳에 에러를 명시하고 WHERE절 뒤에는 예외가 발생했을 때 처리할 문장을 기술한다.

• EXCEPTION키워드로 블록의 예외 처리 섹션을 시작합니다.

• 블록에서 개별적인 작업에 대해 여러 예외 처리기를 정의합니다.

• 예외가 발생할 때 블록 종료 전에 PL/SQL은 하나의 처리기만 프로세스 합니다. 다른 모든 예외 처리 절 후에 OTHERS절을 넣습니다.

• 최대 하나의 OTHERS절을 가질 수 있습니다.

• 예외는 지정(assignment) 문장 또는 SQL문장에서 쓰일 수 없습니다.

EXCEPTION                  WHEN  exception1 [OR exception2, . . . .] THEN                                   statement1;                                   statement2;                                   . . . . . .                  [WHEN  exception2 [OR exception3, . . . .] THEN                                   statement3;                                   statement4;                                   . . . . . .]                  [WHEN  OTHERS THEN                                   statement5;                                   statement6;                                   . . . . . .] END; Exception : 선언섹션에서 선언된 미리 정의된 예외의 표준 이름 이거나 사용자 정의예외의 이름입니다. Statement : 하나 이상의 PL/SQL또는 SQL문장입니다. OTHERS : 명시적으로 선언되지 않은 모든 예외를 트랩하는 예외 처리 절입니다.

WHEN OTHERS 예외 처리기

예외 처리 섹션은 지정된 예외만 트랩(trap)합니다. OTHERS예외 처리기를 사용하지 않으면 다른 예외들은 트랩(trap) 되지 않습니다. 이것은 아직 처리되지 않은 모든 예외를 트랩합니다. 그러므로 OTHERS는 마지막에 정의되는 예외 처리기입니다. 일부 ORACLE 툴들은 어플리케이션에서 이벤트를 발생시키기 위해 일으키는 개별적인 미리 정의된 예외들을 가지고 있습니다. OTHERS는 또한 이 예외들도 트랩 합니다.

미리 정의된 ORACLE SERVER 에러

해당 예외 처리 루틴에서 표준 이름을 참조함으로써 미리 정의된 ORACLE SERVER 에러를 트랩(trap)합니다.

예외 이름	에러 번호	설     명
ACCESS_INTO_NULL	ORA-06530	초기화 되지않은 객체의 속성에 대해 값을 지정하는 것을 시도합니다.
COLLECTION_IS_NULL	ORA-06531	초기화되지 않은 중첩 테이블 대해EXISTS를 제외한 메쏘드 모음의 적용을 시도합니다.
CURSOR_ALREADY_OPEN	ORA-06511	이미 열린 CURSOR의 열기를 시도합니다.
DUP_VAL_ON_INDEX	ORA-00001	중복 값의 삽입을 시도합니다.
INVALID_CURSOR	ORA-01001	잘못된 CURSOR연산이 발생합니다.
INVALID_NUMBER	ORA-01722	수의 문자열 전환은 실패입니다.
LOGIN_DENIED	ORA-01017	잘못된 사용자명과 비밀 번호로ORACLE에 로그온합니다.
NO_DATA_FOUND	ORA-01403	데이터를 RETURN하지 않는 SELECT문장
NOT_LOGGED on	ORA-01012	PL/SQL프로그램은 ORACLE에 연결하지 않고 데이터베이스 호출을 발생합니다.
PROGRAM_ERROR	ORA-06501	PL/SQL은 내부 문제를 가지고 있습니다.
ROWTYPE_MISMATCH	ORA-06504	지정문에 포함된 호스트CURSOR변수와PL/SQL    CURSOR변수는 RETURN 유형이 다릅니다.
STORAGE_ERROR	ORA-06500	PL/SQL이 메모리를 다 써버리거나 또는 메모리가 훼손되었습니다.
SUBSCRIPT_BEYOND_COUNT	ORA-06533	모음의 요소 개수보다 더 큰 인덱스 개수를 사용하는 중첩 테이블 참조합니다.
SUBSCRIPT_OUTSIDE_LIMIT	ORA-06532	범위 밖의 인덱스 번호를 사용하여 중첩 테이블 참조 합니다.
TIMEOUT_ON_RESOURCE	ORA-00051	ORACLE이 리소스를 대기하는 동안 시간 초과가 발생합니다.
TOO_MANY_ROWS	ORA-01422	단일 행 SELECT는 하나 이상의 행을RETURN합니다.
VALUE_ERROR	ORA-06502	계산,변환,절단,또는 크기 제약 오류가 발생합니다.
ZERO_DIVIDE	ORA-01476	0으로 배분을 시도합니다.

미리 정의되지 않은 ORACLE SERVER 에러

우선 에러를 선언하고 나서 OTHERS에서 미리 정의되지 않은 ORACLE SERVER 에러를 처리(에러 번호 확인)합니다. 선언된 예외는 암시적으로 발생합니다. PL/SQL에서 PARAGMA와EXCEPTION_INT는 ORACLE 에러 번호와 예외 이름을 관련시키기 위해 컴파일러에게 알려줍니다. PARAGMA는 PL/SQL블록이 실행될 때 처리되지 않는 컴파일러 명령문임을 의미하는 키워드입니다. 블록 내에서 예외 이름이 발생되면 그것을 관련된 ORACLE SERVER 에러번호로 해독하기 위해 PL/SQL컴파일러에게 지시합니다.

선언 부분에서 예외 이름을 선언 exception_name    EXCEPTION; PRAGMA EXCEPTION_INIT문장을 사용하여 표준 에러 번호와 선언된 예외를 연결한다. PRAGMA          EXCEPTION_INIT(exception_name,  error_number);

ACCEPT  p_ename PROMPT '삭제하고자 하는 사원의 이름을 입력하시오 : ' DECLARE                  v_ename        emp.ename%TYPE := '&p_ename';                  v_empno        emp.empno%TYPE;                  emp_constraint       EXCEPTION;                  PRAGMA  EXCEPTION_INIT (emp_constraint, -2292); BEGIN                  SELECT empno                                   INTO v_empno                                   FROM emp                                    WHERE ename = UPPER(v_ename);                  DELETE emp                                   WHERE empno = v_empno; EXCEPTION                  WHEN NO_DATA_FOUND THEN                      DBMS_OUTPUT.PUT_LINE('&p_ename' || '는 자료가 없습니다.');                  WHEN TOO_MANY_ROWS THEN                      DBMS_OUTPUT.PUT_LINE('&p_ename' || '는 자료가 여러개 있습니다.');                  WHEN emp_constraint THEN                      DBMS_OUTPUT.PUT_LINE('&p_ename' || '는 삭제할 수 없습니다.');                  WHEN OTHERS THEN                      DBMS_OUTPUT.PUT_LINE('기타 에러입니다.'); END;

사용자 정의 예외

PL/SQL에서는 개별적으로 예외를 정의할 수 있습니다. 사용자 정의 PL/SQL 예외는PL/SQL 블록의 선언 섹션에서 선언 하고RAISE 문장으로 명시적으로 발생시킨다.

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

ACCEPT  p_deptno  PROMPT '조회하고자 하는 부서번호를 입력하시오 : ' DECLARE                  v_deptno        emp.deptno%TYPE := &p_deptno;                  CURSOR emp_cursor IS                                   SELECT empno,ename,job,sal                                                     FROM emp                                                     WHERE deptno = v_deptno;                  emp_deptno_ck      EXCEPTION; BEGIN                  IF v_deptno NOT IN (10,20,30) THEN                                   RAISE emp_deptno_ck;                  ELSE                                   DBMS_OUTPUT.PUT_LINE('사번   이  름   담당업무     급   여');                                   DBMS_OUTPUT.PUT_LINE('---- ---------- --------- ------------');                                   FOR emp_record IN emp_cursor LOOP                                       DBMS_OUTPUT.PUT_LINE(RPAD(emp_record.empno,4) || ' ' ||                                          RPAD(emp_record.ename,11) || RPAD(emp_record.job,10) ||                                              RPAD(TO_CHAR(emp_record.sal,'$999,990.00'),12));                                   END LOOP;                  END IF; EXCEPTION                  WHEN emp_deptno_ck THEN                                   DBMS_OUTPUT.PUT_LINE('&p_deptno' || '는 자료가 없습니다.');                  WHEN OTHERS THEN                                   DBMS_OUTPUT.PUT_LINE('기타 에러입니다.'); END;

예외 트래핑 함수

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

함  수	설     명
SQLCODE	에러 코드에 대한 숫자를 RETURN한다.
SQLERRM	에러 번호에 해당하는 MESSAGE를 RETURN한다.

SQL CODE 값

SQL CODE 값	설     명
0	예외가 없습니다.(NO ERROR)
1	사용자 정의 ERROR NUMBER
+100	NO_DATA_FOUND 예외
양의 정수	표준 에러 번호

ACCEPT  p_ename PROMPT '삭제하고자 하는 사원의 이름을 입력하시오 : ' DECLARE                  v_ename        emp.ename%TYPE := '&p_ename';                  v_empno        emp.empno%TYPE;                  v_err_code       NUMBER;                  v_err_msg       VARCHAR2(255); BEGIN                  SELECT empno                                   INTO v_empno                                   FROM emp                                   WHERE ename = UPPER(v_ename);                  DELETE emp                                   WHERE empno = v_empno; EXCEPTION                  WHEN OTHERS THEN                                   ROLLBACK;                                   v_err_code := SQLCODE;                                   v_err_msg := SQLERRM;                                   DBMS_OUTPUT.PUT_LINE('에러 번호 : ' || TO_CHAR(v_err_code));                                   DBMS_OUTPUT.PUT_LINE('에러 내용 : ' || v_err_msg); END;

RAISE_APPLICATION_ERROR

표준화 되지 않은 에러 코드와 에러 MESSAGE를 RETURN하는 RAISE_APPLICATION_ERROR 프로시저를 사용합니다. RAISE_APPLICATION_ERROR로 어플리케이션에 대한 에러를 제어할 수 있고 처리되지 않은 에러가 RETURN되지 않도록 합니다.

raise_application_error  (error_number, message[,{TRUE|FALSE}]); error_number : -20000과 20999사이의 예외에 대해 지정된 번호 message : 예외에 대한 사용자 지정 MESSAGE TRUE|FALSE : 선택적 BOOLEAN 매개변수로 TRUE면 에러는 이전의 에러 스택에 의치하고 FALSE(DEFAULT)면 에러는 모든 이전의 에러를 대치합니다.

ACCEPT  p_ename PROMPT '삭제하고자 하는 사원의 이름을 입력하시오 : ' DECLARE                  v_ename        emp.ename%TYPE := '&p_ename';                  v_err_code       NUMBER;                  v_err_msg       VARCHAR2(255); BEGIN                  DELETE emp                                   WHERE ename = v_ename;                  IF SQL%NOTFOUND THEN                                   RAISE_APPLICATION_ERROR(-20100,'no data found');                  END IF; EXCEPTION                  WHEN OTHERS THEN                                   ROLLBACK;                                   v_err_code := SQLCODE;                                   v_err_msg := SQLERRM;                                   DBMS_OUTPUT.PUT_LINE('에러 번호 : ' || TO_CHAR(v_err_code));                                   DBMS_OUTPUT.PUT_LINE('에러 내용 : ' || v_err_msg); END;