[SQL&PL/SQL] 22.Handling Exceptions

1. Exception Types

   - Predefined Oracle Server
     ; 오라클에서 미리 정한 ERROR...(NO_DATA_FOUND등...)
   - Non-predefined Oracle Server
     ; 자주일어나는 error가 아니어서 미리 define되어 있지 않은 error
   - User-defined
     ; 오라클 서버 error가 아니라 user가 업무상 rule에 위배되는 사항을 exception처리 하는 것.
     
2. Predefined Exception Names

Exeption Name	Oracle Server Error Number	Description
ACCESS_INTO_NULL	ORA-06530	초기화되지 않은 object의 속성에 값을 assign하라.
COLLECTION_IS_NULL	ORA-06531	초기화되지 않은 nested table에 EXISTS와 다른 method를  적용시켜라.
CURSOR_ALREADY_OPEN	ORA-06511	OPEN CURSOR가 이미 open되어 있다.
DUP_VAL_ON_INDEX	ORA-00001	중복된 값을 insert하려고 한다.
INVALID_CURSOR	ORA-01001	잘못된 cursor연산자를 발생시켰다.
INVALID_NUMBER	ORA-01722	문자열을 number type으로 convertion 실패
LOGIN_DENIED	ORA-01017	오라클 login시 username or password를 잘못 입력했다.
NO_DATA_FOUND	ORA-01403	single row SELECT된 데이터가 없다.
NOT_LOGGED_ON	ORA-01012	PL/SQL이 오라클에 connect되지 않은 상태에서 database를  호출하려고 한다.
PROGRAM_ERROR	ORA-06501	PL/SQL이 내부적인 문제가 있다.
ROWTYPE_MISMATCH	ORA-06504	Host cursor변수와 PL/SQL cursor변수가 return type과  일치하지 않는다.
STORAGE_ERROR	ORA-06500	PL/SQL을 실행중 memory를 손상시켰다.
SUBSCRIPT_BEYOND_COUNT	ORA-06533	nested table을 참조하는 element가 너무 큰 index를  사용했다.
SUBSCRIPT_OUTSIDE_LIMIT	ORA-06532	nested table을 참조하는 element가 정상적인 범위의  벗어난index를 사용했다.
TIMEOUT_ON_RESOURCE	ORA-00051	resource를 기다리는 동안 time-out이 발생했다.
TOO_MANY_ROWS	ORA-01422	single row SELECT된 데이터가 너무많은 row를 갖고있다.
VALUE_ERROR	ORA-06502	Arithmetic, conversion, truncation, size constraint  ERROR
ZERO_DIVIDE	ORA-01467	0으로 나누기를 실행했다.

    
3. Predefined Exception
   
   BEGIN SELeCT ... COMMIT ;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
           statement ;
      WHEN TOO_MANY_ROWS THEN
           statement ;
      WHEN OTHERS THEN
           statement ;
   END ;
   
4. Non-Predefined Error

   DECLARE
      e_products_invalid  EXCEPTION ;
      PRAGMA EXCEPTION_INIT (e_products_invalid, -2292 ) ;
      v_message VARCHAR2(50) ;
   BEGIN
   ...
   EXCEPTION
      WHEN e_products_invalid THEN
           :g_message := 'Product code specified is not valid.' ;
   ...
   END ;
   
   --> -2292 error code를 e_products_invalid 라는 이름으로 정의 하겠다.
   
5. User-Defined Exception

   DECLARE
      e_amount_remaining EXCEPTION ;
      ...
   BEGIN
      ...
      RAISE e_amount_remaining ;  -- 여기서부터 e_amount_remaining을 발생 시킨다.
      ...
   EXCEPTION
      WHEN e_amount_remaining THEN
           :g_message := 'There is still an amount in stock.' ;
      ...
   END ;
   
6. SQLCODE 와 SQLERRM

   - SQLCODE : error code를 return한다.
   - SQLERRM : error message를 return한다.
   
   DECLARE
      v_error_code      NUMBER ;
      v_error_message   VARCHAR2(255) ;
   BEGIN
      ...
   EXCEPTION
      ...
      WHEN OTHERS THEN
         ROLLBACK ;
         v_error_code := SQLCODE ;
         v_error_message := SQLERRM ;
         INSERT INTO errors VALUES (v_error_code, v_error_message) ;
   END ;
   
7. RAISE_APPLICATION_ERROR
   ; error message를 user가 define하고 싶을때 사용한다.
     error code범위는 ( -20000 ~ -20999 )
     
   예1) EXCEPTION
           WHEN NO_DATA_FOUND THEN
              RAISE_APPLICATION_ERROR (-20201, 'Manager is not a valid employee.') ;
        END ;
  
   예2) DELETE FROM emp
        WHERE mgr = v_mgr ;
        IF SQL%NOTFOUND THEN
           RAISE_APPLICATION_ERROR(-20202,'This is not a valid manager') ;
        END IF ;
        ...