ORACLE EXCEPTION INTRODUCTION PLSQL 블록이 PARSE 되는동안에발생되는에러를컴파일에러 (Compilation Error) 라고부르며, PLSQL 블록이실행되는동안에발생되는에러를런타임에러 (Run-Time Error) 라고부르는데, 이런타임에러를오라클에서는예외 (Exception) 라고부른다. 오라클의예외 (Exception) 는크게두가지로구분된다. 하나는오라클에서제공하는오라클예외 (ORACLE Exception) 와다른하나는사용자에의해정의되는사용자정의예외 (User-defined Exception) 가있다. 이처럼오라클예외는예기치않던, 고의적이던 PLSQL 블록이실행될때발생된다. 오라클예외는오라클이정의한상황에묵시적 ( 자동적 ) 으로일어나는데, 이때해당오라클예외에이름이정해진예외를 Predefined ORACLE Exception 이라고하며, 오라클예외이지만예외명이명명되지않은예외를 Non-predefined ORACLE Exception 이라고한다. 사용자정의예외는선언부에서 exception 형으로예외변수를선언하여, 실행부또는예외처립부에서 RAISE 문을통해명시적으로예외를발생시킨다. 다른방법으로는 RAISE_APPLICATION_ERROR 프로시저를사용하기도한다. 이프로시저를사용하면예외를정확히호출환경으로전달할수있다. 실행부에서예외가발생하면예외가발생된 SQL 문은처리가중단되며, 제어의흐름은예외처리부가있다면예외처리부로, 그렇지않다면실패로 PLSQL 블록을종료하게된다. 예외처리부에서는예외에대한처리를할때, 예외의이름으로처리를한다. 그러므로 NonPredefined ORACLE Exception 은선언부에서임시적으로명명하여예외처리부에서사용한다. 이때선언부에서는예외변수와 PRAGMA 를사용하여명명한다. 오라클에서는예외에해당하는예외번호가할당되어있으며, 예외메시지와함께발생된다. 이예외번호는특수한경우의예외처리를위해유용하게이용된다. Written by AngelaLEE 4-1 www.muhanedu.net
>> 참고 : Predefined ORACLE Exception 예외명예외번호설명 ACCESS_INTO_NULL CASE_NOT_FOUND COLLECTION_IS_NULL CURSOR_ALREADY_OPEN DUP_VAL_ON_INDEX INVALID_CURSOR INVALID_NUMBER LOGIN_DENIED NO_DATA_FOUND NOT_LOGGED_ON PROGRAM_ERROR ROWTYPE_MISMATCH 06530 06592 06531 06511 00001 01001 01722 01017 01403 01012 06501 06504 정의되지않은오브젝트속성에값을할당하고자했을때발생되는예외. CASE 문의 WHEN 절에해당되는조건이없고 ELSE 절도없을경우에발생되는예외. 선언되지 않은 컬렉션 (nested table, varray) 에 EXISTS 이외의메소드를사용 했을때발생되는예외. 이미열려진커서를열려고시도했을때발생되는예외. 유일인덱스에중복값을입력했을경우발생되는예외. 잘못된커서조작이실행될때발생되는예외. 문자를숫자로의변환시실패가될때발생되는예외. 잘못된사용자명이나암호로로그인을시도했을때발생되는예외. PLSQL SELECT 문이한건도리턴하지못했을경우발생하는예외 접속되지않은상태에서데이터베이스에대한요청이 PLSQL 프로그램으로실행된경우발생되는예외. PLSQL 이내부적인문제를가지고있는경우발생되는예외 할당문에서호스트커서변수와 PLSQL 커서변수의데이터형이불일치할때발생되는예외 Written by AngelaLEE 4-2 www.muhanedu.net
예외명예외번호설명 STORAGE_ERROR SUBSCRIPT_BEYOND_COUNT SUBSCRIPT_OUTSIDE_LIMIT SYS_INVALID_ROWID TIMEOUT_ON_RESOURCE TOO_MANY_ROWS VALUE_ERROR ZERO_DIVIDE 06500 06533 06532 01410 00051 01422 06502 01476 PLSQL 이실행될때메모리가부족하거나메모리상에문제가일어났을때발생하는예외 컬렉션의요소개수보다더큰첨자값으로참조한경우발생되는예외. 컬렉션의첨자의한계를벗어난참조가일어났을때발생되는예외 문자열을 ROWID 로변환할때무효한문자열의표현일경우발생되는예외 자원에대한대기시간이초과했을때발생하는예외 PLSQL SELECT 문이두건이상의행을리턴했을때발생되는예외 산술, 변환, 절삭또는크기제약에에러가생겼을때발생되는예외 0 으로나누려했을때발생하는예외. Written by AngelaLEE 4-3 www.muhanedu.net
예외처리부예외처리를위해서 PLSQL 블록은예외처리부 (EXCEPTION) 가있다. 각각의 WHEN 절은상호배타적인예외로구분되어야한다. 즉, 예외처리부에서의 WHEN 절은단한개만수행된후 PLSQL 블록이종료되도록해야한다. > SYNTAX: EXCEPTION WHEN exception1 [OR exception2 ] THEN statement1 ; statement2 ; [ WHEN exception3 [OR exception4 ] THEN statement3 ; statement4 ; ] [WHEN OTHERS THEN statementn ; statementn+1 ; ] exception N 실행부에서발생한예외의이름들로해당되는 WHEN 절안의문장들을수행하게된다. OTHERS 기타예외명으로가장마지막에기술되는 WHEN 에만올수있다. 이전의 WHEN 에해당되지않는예외들은모두이 OTHERS 예외에서처리된다. IF 문의 ELSE 와비슷하다. Written by AngelaLEE 4-4 www.muhanedu.net
실습 ORACLE EXCEPTION 1. [Predefined ORACLE Exception I ] 이름에 A 가포함된사원을출력하는 PLSQL 블록. SET ECHO OFF SET SERVEROUTPUT ON DECLARE V_ENAME EMP.ENAME%TYPE ; SELECT ENAME INTO V_ENAME FROM EMP WHERE ENAME LIKE %A% ; DBMS_OUTPUT.PUT_LINE ( 사원의이름은 V_ENAME ) ; SET SERVEROUTPUT OFF SET ECHO ON 만약위의 PLSQL 블록은예외가발생되어실패가되었다면어떤경우인가? Written by AngelaLEE 4-5 www.muhanedu.net
2. [Predefined ORACLE Exception II ] 1 번의 PLSQL 블록에미리정의된예외에대비한예외처리부를추가하여수정하시오.. SET ECHO OFF SET SERVEROUTPUT ON DECLARE V_ENAME EMP.ENAME%TYPE ; SELECT ENAME INTO V_ENAME FROM EMP WHERE ENAME LIKE %A% ; DBMS_OUTPUT.PUT_LINE ( 사원의이름은 V_ENAME ) ; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ( 사원이없습니다. ) ; WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE ( 관련사원명이두건이상입니다. ) ; SET SERVEROUTPUT OFF SET ECHO ON Written by AngelaLEE 4-6 www.muhanedu.net
만약 PLSQL 블록실행부에서예외처리부에서기술된 WHEN 절에관련없는예외가발생했을경우에는실패로종료하게될것이다. 이때발생한예외의정보를출력하고정상적인종료로블록을실행하려면어떻게수정해야하는가? ( 힌트 : OTHERS, SQLCODE, SQLERRM) Written by AngelaLEE 4-7 www.muhanedu.net
3. [Non-Predefined ORACLE Exception I ] 부서코드를입력받아해당부서를삭제하는 PLSQL 블록. SET ECHO OFF DELETE DEPT WHERE DEPTNO = &p_dno ; COMMIT; SET ECHO ON 만약 DEPT 테이블의 DEPTNO 컬럼과 EMP 테이블의 DEPTNO 컬럼사이에참조무결성이존재하고입력된부서에사원이존재한다면, 위의 PLSQL 블록은실패로종료될것이다. 발생되는예외는무엇인가? Written by AngelaLEE 4-8 www.muhanedu.net
4. [Non-Predefined ORACLE Exception II ] 3 번의 PLSQL 블록에명명되지않은예외에대비한예외처리부를추가한 PLSQL 블록. SET ECHO OFF DECLARE e_child_exist EXCEPTION ; PRAGMA EXCETION_INIT ( e_child_exist, -2292) ; DELETE DEPT WHERE DEPTNO = &p_dno ; COMMIT; EXCEPTION WHEN e_child_exist THEN DBMS_OUTPUT.PUT_LINE ( 해당부서에사원이존재합니다. ) ; SET SERVEROUTPUT OFF SET ECHO ON 만약 PLSQL 실행부에서예외가발생되기전의명령들은정상적으로수행되어트랜잭션이발생했고, 예외처리부존재유무에관계없이 PLSQL 블록이실패로종료하게되었다면, 현재트랜잭션은어떻게되는가? 만약트랜잭션이활성화되어있다면그트랜잭션의처리는어떻게하는것이바람직한가? Written by AngelaLEE 4-9 www.muhanedu.net
5. [User-defined Exception I ] 소속사원이없는부서에대한삭제가실행되면강제적으로예외를발생하는 PLSQL 블록. SET ECHO OFF DECLARE e_wrong_del EXCEPTION ; DELETE DEPT WHERE DEPTNO = &p_dno ; IF SQL%NOTFOUND THEN RAISE e_wrong_del ; ELSE COMMIT ; END IF ; SET ECHO ON 위의 PLSQL 블록을실행할때부서코드 40 을입력하면사용자가정의한 e_wrong_del 예외가발생되면서블록은실패로종료될것이다. 만약이블록이어느상황에서도정상적인종료가이루어지게하기위해서는어떻게수정해야하는가? ( 힌트 : 예외처리부를추가, 02292 에대한처리는 4 번을참조 ) Written by AngelaLEE 4-10 www.muhanedu.net
6. [User-defined Exception II ] 사용자예외를발생할때 RAISE_APPLICATION_ERROR 프로시저를사용하는 PLSQL 블록. SET ECHO OFF DELETE DEPT WHERE DEPTNO = &p_dno ; IF SQL%NOTFOUND THEN RAISE_APPLICATION_ERROR(-20100, 소속사원이없습니다. ) ; ELSE COMMIT ; END IF ; SET ECHO ON 위의 PLSQL 블록이어느상황에서도정상적인종료가이루어지게하기위해서는어떻게수정해야하는가? 단, 02292 에대한예외명은 e_child_exist 로, 사용자예외에대한예외명은 e_emp_exist 로처리한다. ( 힌트 : 예외처리부를추가, 02292 와사용자예외에대한처리는 4 번을참조 ) Written by AngelaLEE 4-11 www.muhanedu.net
7. [ 예외의 Scope Rule 에대한이해 Propagating Exception] DECLARE e_no_rows exception ; e_integrity exception ; PRAGMA EXCEPTION_INIT(e_integrity, -2292) ; FOR c_record IN emp_cursor LOOP SELECT UPDATE IF SQL%NOTFOUND THEN RAISE e_no_rows ; END IF ; END LOOP ; EXCEPTION WHEN e_integrity THEN WHEN e_no_rows THEN 만약포함된내부블록의 SELECT 문장에서 NO_DATA_FOUND 예외가발생한다면, 위의 PLSQL 블록은어떻게되는가? 만약포함된내부블록의 UPDATE 문장에서변경된행이없다면, 위의 PLSQL 블록은어떻게되는가? 만약포함된내부블록의 UPDATE 문장에서 02292 예외가발생하면, 위의 PLSQL 블록은어떻게되는가? Written by AngelaLEE 4-12 www.muhanedu.net