2010. 9. 1. 13:47

(oracle)PL/SQL로 예외 처리


PL/SQL 코드를 실행할 때 error 발생하는 경우가 있다. Error는 예외(Exception)를 발생시켜 PL/SQL 블록을 중지시키고 예외 처리기 부분으로 제어가 이동한다. Exception handler Exception을 검출하고 조건에 따라 조치 작업을 할 수 있다.
 
예외 처리란
예외는 PL/SQL 블록의 실행 중에 발생하여 블록의 주요 부분을 중단 시킨다. 항상 PL/SQL 예외가 발생할 때 블록은 항상 종료되지만 마지막 조치 작업을 수행하도록 예외 처리 부분을 작성할 수 있다.
 
1.        예외란 무엇인가 ?
- PL/SQL을 실행 동안에 발생하는 error 처리를 의미한다.
2.        어떻게 발생되는가 ?
- Oracle 오류가 발생할 때
- 사용자가 직접 발생시킬 수 있다.
3.        처리하는 방법은 무엇인가 ?
- 처리기를 이용한다.
- 실행 환경에 전달한다.
 
예외를 발생시키는 두 가지 방법
n         Oracle 오류가 발생하면 관련된 예외가 자동적으로 발생한다. 예를 들어, 오류 ORA-014-3는 데이터베이스에서 검색된 행이 전혀 없을 때 발생하며 PL/SQL NO_DATA_FOUND라 는 예외를 발생시킨다.
n         블록에 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절 뒤에는 예외가 발생했을 때 처리할 문장을 기술한다.
 
n        EXCEPTION키워드로 블록의 예외 처리 섹션을 시작합니다.
n        블록에서 개별적인 작업에 대해 여러 예외 처리기를 정의합니다.
n        예외가 발생할 때 블록 종료 전에 PL/SQL은 하나의 처리기만 프로세스 합니다. 다른 모든 예외 처리 절 후에 OTHERS절을 넣습니다.
n        최대 하나의 OTHERS절을 가질 수 있습니다.
n        예외는 지정(assignment) 문장 또는 SQL문장에서 쓰일 수 없습니다.
 
EXCEPTION
                  WHEN  exception1 [OR exception2, . . . .] THEN
                                   statement1;
                                   statement2;
                                   . . . . . .
                  [WHEN  exception2 [OR exception3, . . . .] THEN
                                   statement3;
                                   statement4;
                                   . . . . . .]
                  [WHEN  OTHERS THEN
                                   statement5;
                                   statement6;
                                   . . . . . .]
END;
 
n         Exception : 선언섹션에서 선언된 미리 정의된 예외의 표준 이름 이거나 사용자 정의예외의 이름입니다.
n         Statement : 하나 이상의 PL/SQL또는 SQL문장입니다.
n         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}]);
 
n         error_number : -20000 20999사이의 예외에 대해 지정된 번호
n         message : 예외에 대한 사용자 지정 MESSAGE
n         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;