8장 .예외처리

8. 예외 처리

SQL> ALTER TABLE emp

ADD CONSTRAINT emp_ck CHECK ( sal > 0 ) ;  --제약조건 추가하고 실습시작.

 

SQL>

SELECT empno, ename, sal

FROM emp

WHERE deptno = 10 ;

 

EMPNO ENAME SAL

---------- ---------- ----------

7782 CLARK 2450

7839 KING 5000

7934 MILLER 1300

--------------------------------

 

BEGIN

             UPDATE emp

             SET sal = 3000

             WHERE empno = 7782 ;    --정상 수행 가능한 문장.

 

             UPDATE emp

             SET sal = 0

             WHERE empno = 7934 ;    --제약조건 위배, Error문장.

END ;

/

ERROR at line 1:

ORA-02290: check constraint (ORA1.EMP_CK) violated

ORA-06512: at line 6

 

→ PL/ SQL 블럭중에서 하나라도 실패하는게 있다면, 정상 수행되는 문장도 수행되지 않는다.

 

SQL> SELECT empno, ename, sal

FROM emp

WHERE deptno = 10 ;

EMPNO ENAME SAL

---------- ---------- ----------

7782 CLARK 2450

7839 KING 5000

7934 MILLER 1300

 

#TX-0 OPEN

             UPDATE emp

             SET sal = 9999

             WHERE empno = 7782 ;

BEGIN

             UPDATE emp

             SET sal = 3000

             WHERE empno = 7782 ;    --정상 수행 가능한 문장.

 

             UPDATE emp

             SET sal = 0

             WHERE empno = 7934 ;    --제약조건 위배, Error문장.

END ;

/

 

SQL> SELECT empno, ename, sal

FROM emp

WHERE deptno = 10 ;

EMPNO ENAME SAL

---------- ---------- ----------

7782 CLARK 9999

7839 KING 5000

7934 MILLER 1300

 

→ PL/SQL블럭이 성공이 되든 실패가 되든 사용자가 COMMIT/ ROLLBACK 하지 않으면

트랜잭션은 종료되어있지 않다. update문은 성공하고 PL/SQL블럭은 실패한 결과를 보여준다.

 

 

Ex1) 모든 ERROR상황은 OTHERS를 통해 잡을 수 있다.

SET SERVEROUTPUT ON

BEGIN

 UPDATE EMP

 SET SAL = 3000

 WHERE EMPNO = 7782;

 

 UPDATE EMP

 SET SAL = 0

 WHERE EMP = 7934 ;

 

EXCEPTION

 WHEN OTHERS THEN

   DBMS_OUTPUT.PUT_LINE('ERROR');

END;

/

 

EX2)

SET SERVEROUTPUT ON

BEGIN          --암시적으로 세이브 포인트가 생긴다.

 UPDATE EMP

 SET SAL = 3000

 WHERE EMPNO = 7782;       --실행됨.

 

 UPDATE EMP

 SET SAL = 0

 WHERE EMP = 7934 ;           --실행안됨 Exception 절로넘어가서 필요한 처리기로간다.

 

 UPDATE EMP

 SET SAL = 3000

 WHERE EMPNO = 7782;      --시도조차 하지 않는다.

 

 

EXCEPTION

 WHEN NO_DATA_FOUND THEN

  DMBS_OUTPUT.PUT_LINE('NO DATA');

 

 WHEN OTHERS THEN

  DBMS_OUTPUT.PUT_LINE('ERROR');   --에러처리를 한다.

END;

/

 

#에러를 핸들링 하지 못한다면 해당 PL/SQL블럭은 취소가 된다.

 

 

 

EX)오라클 서버로부터 만들어진 암시적 에러

SET SERVEROUTPUT ON

DECLARE

 V_SAL NUMBER ;

BEGIN

 SELECT SAL INTO V_SAL

 FROM EMP

 WHERE EMPNO= 1234 ;

END ;

/

 

ERROR at line 1:

ORA-01403: no data found

ORA-06512: at line 4

 

Ex) SQLERRMM 을 이용한 에러 확인.

 

SQL> SET SERVEROUTPUT ON

BEGIN

UPDATE emp

SET sal = 3000

WHERE empno = 7782 ;

UPDATE emp

SET sal = 0

WHERE empno = 7934 ;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE ( SQLERRM ) ;

END ;

/

ORA-02290: check constraint (ORA1.EMP_CK) violated

PL/SQL procedure successfully completed.

 

※ 미리 정의된 예외 처리

ex)

SET SERVEROUTPUT ON

DECLARE

  emp_rec emp%ROWTYPE ;

BEGIN

  SELECT * INTO emp_rec FROM emp WHERE ename = UPPER('&name') ;

  DBMS_OUTPUT.PUT_LINE ( emp_rec.sal ) ;

EXCEPTION

WHEN NO_DATA_FOUND THEN

  DBMS_OUTPUT.PUT_LINE ('NO_DATA_FOUND') ;

WHEN TOO_MANY_ROWS THEN

  DBMS_OUTPUT.PUT_LINE ('TOO_MANY_ROWS') ;

WHEN OTHERS THEN

  DBMS_OUTPUT.PUT_LINE ('OTHERS') ;

END ;

/

Enter value FOR name: RYU OLD 6: WHERE ename = UPPER('&name') ;

NEW 6: WHERE ename                           = UPPER('RYU') ;

NO_DATA_FOUND PL/SQL

PROCEDURE successfully completed.

 

 

 

 

 

 

※ 미리 정의되지 않은 예외 처리

ex)

 

SET SERVEROUTPUT ON

DECLARE

  emp_rec emp%ROWTYPE ;

  e_ck EXCEPTION ;

  PRAGMA EXCEPTION_INIT (e_ck , -2290) ;  --예외생성.

BEGIN

  SELECT * INTO emp_rec FROM emp WHERE ename = UPPER('&name') ;

  IF emp_rec.sal < 2000 THEN

    UPDATE emp SET sal = &salary WHERE empno = emp_rec.empno ;

  END IF ;

EXCEPTION

WHEN NO_DATA_FOUND THEN

  DBMS_OUTPUT.PUT_LINE ('NO DATA') ;

WHEN E_CK THEN

  DBMS_OUTPUT.PUT_LINE ('Invalid Salary') ;

WHEN OTHERS THEN

  DBMS_OUTPUT.PUT_LINE (SQLERRM) ;

END ;

/

 

※ 사용자 정의 예외

 

SET SERVEROUTPUT ON

DECLARE

  v_deptno             NUMBER       := 50 ;

  v_name               VARCHAR2(20) := 'Testing' ;

  e_invalid_department EXCEPTION;

BEGIN

  UPDATE dept SET dname = v_name WHERE deptno = v_deptno ;

  IF SQL%NOTFOUND THEN

    RAISE e_invalid_department ;

  END IF;

  COMMIT;

EXCEPTION

WHEN e_invalid_department THEN

  DBMS_OUTPUT.PUT_LINE('No such department id.');

END;

/

No such department id. PL/SQL

PROCEDURE successfully completed.

 

※ RASE_APPLICATION_ERROR 사용

 

– 마치 오라클 서버에서 만들어진것 처럼 ERROR코드와 ERROR메세지가 나온다.

– 비정상종료를 강제로 유발시키고자할때 사용한다(암시적인 에러와 동일하다)

– 이전에 정상실행된 SQL문이 있더라도 ROLLBACK된다.

 

BEGIN

  UPDATE dept SET dname = 'Testing' WHERE deptno = 50 ;

  IF SQL%NOTFOUND THEN

    RAISE_APPLICATION_ERROR ( -20001, 'No such department id.' ) ;

  END IF;

END;

/

 

ERROR at line 1:

ORA-20001: No such department id.

ORA-06512: at line 7

 

ex2) 사용자 정의 에러문장은 보여주지만, 정상종료된다.

 

BEGIN

  UPDATE dept SET dname = 'Testing' WHERE deptno = 50 ;

  IF SQL%NOTFOUND THEN

    RAISE_APPLICATION_ERROR ( -20001, 'No such department id.' ) ;

  END IF;

EXCEPTION

WHEN OTHERS THEN

  DBMS_OUTPUT.PUT_LINE(SQLERRM) ;

END;

   /

ORA-20001: No such department id. PL/SQL

PROCEDURE successfully completed.

 

Ex3)

DECLARE

  emp_rec emp%ROWTYPE ;

BEGIN

  SELECT * INTO emp_rec FROM emp WHERE deptno = 10 ;

EXCEPTION

WHEN TOO_MANY_ROWS THEN

  RAISE_APPLICATION_ERROR ( -20001, 'Too Many Rows', TRUE ) ;

                                                                                      --FALSE 로 변경하면, 원래에러메시지만 나온다.

END ;

                    /

ERROR at line 1: ORA-20001: Too Many Rows ORA-06512: at line 9 ORA-01422: exact

FETCH returns more than requested NUMBER OF rows

 

 

※ 예외 전달

ex1)

BEGIN

  UPDATE emp SET sal = 7777 WHERE empno = 7788 ;

  BEGIN

    UPDATE emp SET sal = 9999 WHERE empno = 7566 ;

    UPDATE emp SET sal = 0 WHERE empno = 7839 ;

    UPDATE emp SET sal = 9999 WHERE empno = 7499 ;

  END ;        – ERROR1

  UPDATE emp SET sal = 7777 WHERE empno = 7369 ; 

END ;

/

ERROR1은 그 호출한 바깥으로 에러를 전달한다, 바깥쪽 블럭에도 EXCEPTION이 없기때문에 예외처리 되지 않으며, 어떠한 결과도 변경되지 않는다.

 

ERROR at line 1:

ORA-02290: check constraint (ORA1.EMP_CK) violated

ORA-06512: at line 11

SQL> SELECT empno, ename, sal

FROM emp

WHERE empno IN (7788, 7566, 7839, 7369, 7499) ;

 EMPNO ENAME SAL

---------- ---------- ----------

7369 SMITH 800

7499 ALLEN 1600

7566 JONES 2975

7788 SCOTT 3000

7839 KING 5000

EX2)

 

SQL> SET SERVEROUTPUT ON

BEGIN

  UPDATE emp SET sal = 7777 WHERE empno = 7788 ;

  BEGIN

    UPDATE emp SET sal = 9999 WHERE empno = 7566 ;

    UPDATE emp SET sal = 0 WHERE empno = 7839 ;        → Exception절로 이동한다.

    UPDATE emp SET sal = 9999 WHERE empno = 7499 ;  → 시도하지 않는다.

  EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE (SQLERRM) ;

  END ;

  UPDATE emp SET sal = 7777 WHERE empno = 7369 ;  → 예외가 처리되었으므로 실행된다.

END ;

   /

ORA-02290: CHECK CONSTRAINT (ORA1.EMP_CK) violated PL/SQL

PROCEDURE successfully completed. SQL>

 

 

 

SELECT empno,

    ename,

    sal

FROM emp

WHERE empno IN (7788, 7566, 7839, 7369,7499) ;

 

  EMPNO ENAME SAL

  ---------- ---------- ----------

  7369 SMITH 7777 7499 ALLEN 1600 7566 JONES 9999 7788 SCOTT 7777 7839 KING 5000 SQL> ROLLBACK ;

 

 

EX3)

SQL>

SET SERVEROUTPUT ON

BEGIN

  UPDATE emp SET sal = 7777 WHERE empno = 7788 ;

  BEGIN

    UPDATE emp SET sal = 9999 WHERE empno = 7566 ;

    UPDATE emp SET sal = 0 WHERE empno = 7839 ;       → 에러 발생.

    UPDATE emp SET sal = 9999 WHERE empno = 7499 ;

  END ;

  UPDATE emp SET sal = 7777 WHERE empno = 7369 ;

EXCEPTION

WHEN OTHERS THEN

  DBMS_OUTPUT.PUT_LINE (SQLERRM) ;        → 에러처리.

END ;

/

 ORA-02290: check constraint (ORA1.EMP_CK) violated

PL/SQL procedure successfully completed.

SQL> SELECT empno, ename, sal

FROM emp

WHERE empno IN (7788, 7566, 7839, 7369,7499) ;

 EMPNO ENAME SAL

---------- ---------- ----------

7369 SMITH 800

7499 ALLEN 1600

7566 JONES 9999

7788 SCOTT 7777

7839 KING 5000

 

#정리다시해야함.

중첩된 데이터의 피엘에스큐엘을 사용하면 정상 적으로 되다가 서브블록이 호출되면

자기불럭의 익셉션 절을 찾고, 핸들링했다면 메인블럭으로 돌아와서 나머지명령을 실행한다.

서브블록에서 에러를 핸들링 하지못했다면(익셉션절이 없거나, 있지만 처리기가 제대로 되지않을경우)

메인절의 익셉션 절로 넘어가고 그 사이에있는 것은 실행조차 되지 않는다.

메인절에서 처리기를 정상 처리해줬다면 서브블럭은 정상처리된다.

 

BEGIN

 

   SQL 1

 

   SQL 2   - ERROR  EXCEPTION절로 넘어간다.

 

   SQL 3   -실행조차 하지않는다.

 

EXCEPTION

 

END ;

 

 

Q> 만약  SQL 3번을 꼭 사용해야한다면. 중첩된 블록을 이용한다.

BEGIN

 

   SQL 1

 

   BEGIN

            SQL 2 

   EXCEPTION

   END ;

            

   SQL 3                 

 

EXCEPTION

 

END ;