5장. ORACLE EXCEPTION(예외처리)
1. PL/SQL 예외란?
: pl/sql블록이 parse되는 동안 오타로 인해 발생되는 에러를 컴파일에러라고 부르고,
pl/sql블록이 실행되는 동안 발생되는 에러가 런타임 에러라고 부르는데, 오라클에서는 이를 예외(exception)이라고 부른다.
* 오라클의 예외종류
① oracle exceptionl (오라클 예외)
② User-defined Exception (사용자 정의 예외) (ex.과장급 아래인 일반사원이 조회하면 '권한이 없습니다.'와 같은 에러를 띄우는 것.)
* 미리 정의되어 있는 주요 오라클 예외 (참고만 할 것.)
|
예외명: ACCESS_INTO_NULL
예외번호: ORA-06530
설명: 정의되지 않은 오브젝트 속성에 값을 할당하고자 했을 때 발생되는 예외. |
|
예외명: CASE_NOT_FOUND
예외번호: ORA-06592
설명: CASE 문의 WHEN 절에 해당되는 조건이 없고 ELSE 절도 없을 경우에 발생되는 예외. |
|
예외명: COLLECTION_IS_NULL
예외번호: ORA-06531
설명: 선언되지 않은 컬렉션 (nested table, varray)에 EXISTS 이외의 메소드를 사용했을 때 발생되는 예외. |
|
예외명: CURSOR_ALREADY_OPEN
예외번호: ORA-06511
설명: 이미 열려진 커서를 열려고 시도 했을 때 발생되는 예외. |
|
예외명: DUP_VAL_ON_INDEX
예외번호: ORA-00001
설명: 유일인덱스에 중복값을 입력했을 경우 발생되는 예외. |
|
예외명: INVALID_CURSOR
예외번호: ORA-01001
설명: 잘못된 커서 조작이 실행될 때 발생되는 예외. |
|
예외명: INVALID_NUMBER
예외번호: ORA-01722
설명: 문자를 숫자로의 변환 시 실패가 될 때 발생되는 예외. |
|
예외명: LOGIN_DENIED
예외번호: ORA-01017
설명: 잘못된 사용자명 이나 암호로 로그인을 시도했을 때 발생되는 예외. |
|
예외명: NO_DATA_FOUND
예외번호: ORA-01403
설명: PL/SQL SELECT문이 한 건도 리턴 하지 못했을 경우 발생하는 예외 |
|
예외명: NOT_LOGGED_ON
예외번호: ORA-01012
설명: 접속되지 않은 상태에서 데이터베이스에 대한 요청이 PL/SQL 프로그램으로 실행된 경우 발생되는 예외. |
|
예외명: PROGRAM_ERROR
예외번호: ORA-06501
설명: PL/SQL 이 내부적인 문제를 가지고 있는 경우 발생되는 예외 |
|
예외명: ROWTYPE_MISMATCH
예외번호: ORA-06504
설명: 할당문에서 호스트 커서 변수와 PL/SQL 커서 변수의 데이터 형이 불일치 할 때 발생되는 예외 |
|
예외명: STORAGE_ERROR
예외번호: ORA-06500
설명: PL/SQL 이 실행될 때 메모리가 부족하거나 메모리상에 문제가 일어났을 때 발생하는 예외 |
|
예외명: SUBSCRIPT_BEYOND_COUNT
예외번호: ORA-06533
설명: 컬렉션의 요소 개수보다 더 큰 첨자 값으로 참조한 경우 발생되는 예외. |
|
예외명: SUBSCRIPT_OUTSIDE_LIMIT
예외번호: ORA-06532
설명: 컬렉션의 첨자의 한계를 벗어난 참조가 일어났을 때 발생되는 예외 |
|
예외명: SYS_INVALID_ROWID
예외번호: ORA-01410
설명: 문자열을 ROWID 로 변환할 때 무효한 문자열의 표현일 경우 발생되는 예외 |
|
예외명: TIMEOUT_ON_RESOURCE
예외번호: ORA-00051
설명: 자원에 대한 대기시간이 초과했을 때 발생하는 예외 |
|
예외명: TOO_MANY_ROWS
예외번호: ORA-01422
설명: PL/SQL SELECT문이 두 건 이상의 행을 리턴 했을 때 발생되는 예외 |
|
예외명: ZERO_DIVIDE
예외번호: ORA-01476
설명: 0으로 나누려 했을 때 발생하는 예외. |
2. 예외 처리 사용하기
[문 법]
|
EXCEPTION
WHEN exception1 [OR exception2 …] THEN
statement1 ;
statement2 ;
…
[ WHEN exception3 [OR exception4 …] THEN
statement3 ;
statement4 ;
… ]
[WHEN OTHERS THEN
statementN ;
statementN+1 ;
… ]
|
- OTHERS : 이전의 WHEN 에 해당되지 않는 예외들은 모두 이 OTHERS 예외에서 처리.
[예외처리 사용 예 1: 오라클에서 사전 정의된 예외 사용하기 ]
사원명이 ‘A’ 로 시작하는 사원을 조회하여 묵시적 커서를 사용하여 출력하되 여러 건의 데이터가 나올 경우 에러를 발생시키는 예외 처리를 생성합니다.
|
SQL> SELECTename
2 FROM emp
3 WHERE ename LIKE 'A%';
ENAME
--------------------
ALLEN
ADAMS -- A 로 시작하는 사원이 2명 검색됩니다.
이 사원들의 이름을 출력하는 PL/SQL 을 작성하는데 데이터가 2건 이므로 명시적 커서를 사용해야 하지만 묵시적 커서를 사용하여
TOO_MANY_ROWS 라는 예외가 발생한다.
SQL> DECLARE
2 v_ename emp.ename%TYPE;
3 BEGIN
4 SELECT ename INTO v_ename
5 FROM emp
6 WHERE ename LIKE 'A%';
7 DBMS_OUTPUT.PUT_LINE ('사원명은' ||v_ename||' 입니다');
8 EXCEPTION -- 이 에러를 쓰지 않으면 오라클이 미리 정해놓은 에러인 Too_many_rows를 띄운다.
9 WHEN NO_DATA_FOUND THEN
10 DBMS_OUTPUT.PUT_LINE(' 해당 사원이 없습니다');
11 WHEN TOO_MANY_ROWS THEN
12 DBMS_OUTPUT.PUT_LINE(' 사원이 두 명 이상입니다');
13 END;
14 /
|
[예외처리 사용 예 2. PRAGMA 를 사용하여 에러메시지 변경하기]
: pragma란? 특정 에러코드를 예외 메시지로 바꿔주는 역할.
두 개의 테이블이 서로 Foreign Key 가 설정되어 있는 상태에서 부모테이블에서 부서번호를 입력 받아 해당 부서를 삭제하되 자식 테이블에 해당 부서에 소속된 사원이 있을 경우 ‘ 해당 부서에 사원이 존재하므로 삭제할 수 없습니다’ 라는 예외를 발생시키는 예제.
|
SQL> DECLARE
2 sawon_exist EXCEPTION;
3 PRAGMA EXCEPTION_INIT(sawon_exist,-2292); --2292에러코드가 뜨면 sawon_exist 메시지를 실행한다.
4 BEGIN
5 DELETE FROM dept
6 WHERE deptno =&dno;
7 COMMIT;
8 EXCEPTION
9 WHEN sawon_exist THEN
10 DBMS_OUTPUT.PUT_LINE('해당 부서에 사원이 존재하므로 삭제할 수 없습니다');
11 END ;
12 /
Enter value for dno: 10
해당 부서에 사원이 존재하므로 삭제할 수 없습니다
|
no 컬럼에 PRIMARY KEY 를 설정해서 중복되는 값과 NULL 값이 입력되지 않도록 설정 한 후 일부러 중복되는 값을 입력해서 ORA-00001 번 에러를 발생시키는 예제.
|
SCOTT>CREATE TABLE t_pragma
2 (no NUMBER PRIMARY KEY ,
3 name VARCHAR2(10));
Table created.
SCOTT>INSERT INTO t_pragma
2 VALUES(1,'AAA') ;
1 row created.
SCOTT>INSERT INTO t_pragma
2 VALUES(1,'BBB');
INSERT INTO t_pragma
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C0013626) violated --NO컬럼이 Primary key 이므로 중복된 데이터가 들어갈 수 없어서.
SCOTT> DECLARE
2 new_msg EXCEPTION;
3 PRAGMA EXCEPTION_INIT(new_msg, -1); --1번 에러 발생하면 new_msg에러메시지 호출해라.
4 BEGIN
5 INSERT INTO t_pragma
6 VALUES(1,'CCC');
7 EXCEPTION
8 WHEN new_msg THEN
9 DBMS_OUTPUT.PUT_LINE('이미 존재하는 번호입니다!');
10 END ;
11 /
|
NOT NULL 제약 조건이 설정되어 있는 컬럼에 NULL 값을 입력해서 ORA-01400 에러를 발생.
|
SCOTT>CREATE TABLE t_con
2 ( no NUMBER NOT NULL ,
3 name VARCHAR2(10));
Table created.
SCOTT>INSERT INTO t_con
2 VALUES(1,'AAA');
1 row created.
SCOTT>INSERT INTO t_con
2 VALUES(null,'BBB');
VALUES(null,'BBB')
*
ERROR at line 2:
ORA-01400: cannot insert NULL into ("SCOTT"."T_CON"."NO") --no칼럼에 not null 제약조건이 걸려 있는데, null값 입력해서 에러발생
SCOTT>DECLARE
2 new_msg EXCEPTION;
3 PRAGMA EXCEPTION_INIT(new_msg, -1400);
4
5 BEGIN
6 INSERT INTO t_con
7 VALUES(null,'BBB');
8
9 EXCEPTION
10 WHEN new_msg THEN
11 DBMS_OUTPUT.PUT_LINE('null 값은 입력할 수 없습니다');
12 END;
13 /
null 값은 입력할 수 없습니다
PL/SQL procedure successfully completed.
|
* 특정 상황이 되면 예외처리를 하는 경우들은 RAISE 나 RAISE_APPLICATION_ERROR 프로시저를 사용한다.
[ 예외 처리 사용 예 3: RAISE 를 사용하여 예외 상황 처리하기 ]
empno 를 입력 받은 후 emp 테이블에서 해당 사원을 지우는 작업을 수행합니다. 단, 없는 사원번호를 입력할 경우 ‘사원이 없습니다’ 라는 예외 메시지를 출력.
|
SCOTT>DECLARE
2 no_empno EXCEPTION ;
3 BEGIN
4 DELETE FROM EMP
5 WHERE empno =&empno;
6 IF SQL%NOTFOUND THEN
7 RAISE no_empno ;
8 END IF;
9 EXCEPTION
10 WHEN no_empno THEN
11 DBMS_OUTPUT.PUT_LINE('조회한 사번의 사원은 없습니다');
12 END ;
13 /
Enter value for empno: 7901
조회한 사번의 사원은 없습니다
|
[예외 처리 사용 예 4 : RAISE_APPLICATION_ERROR 프로시저 사용하기 ]
RAISE_APPLICATION_ERROR 프로시저란? : 사용자가 특정 상황에 발생할 에러를 정의하고 예외 처리부를 사용하지 않고, 실행부에서 즉시 예외를 처리하는 방식. -> 에러 코드번호를 사용자가 직접 지정해서 출력할 수 있는데 이때 사용자가 임의로 지정이 가능한 에러 번호는 20000 번부터 20999 번 까지이다.
|
SCOTT>SET VERIFY OFF
SCOTT>BEGIN
2 DELETE FROM emp WHERE empno=&empno ;
3 IF SQL%NOTFOUND THEN
4 RAISE_APPLICATION_ERROR(-20001,'존재하지 않는 사번입니다');
5 END IF;
6 END ;
7 /
Enter value for empno: 7901
BEGIN
*
ERROR at line 1:
ORA-20001: 존재하지 않는 사번입니다
ORA-06512: at line 4
|
[ 예외처리 사용 예 5: SQLCODE 와 SQLERRM 사용하기]
* 에러 내역 확인 : SQLCODE , SQLERRM
모든 예외를 일일이 예외 처리부에 다 기록할 수 없기 때문에 SQLCODE 와 SQLERRM을 사용하여 에러를 찾아서 보여주는 경우도 많이 있다.
사용자로부터 empno 를 입력 받아서 해당 empno 의 ename 을 화면에 출력하는 코드. 이 때, 존재하지 않는 empno 를 입력할 경우 발생하는 Error code 와 Error 내용을 확인.
|
SCOTT>SET VERIFY OFF
SCOTT>DECLARE
2 name emp.ename%TYPE;
3 v_code NUMBER;
4 v_errm VARCHAR2(64);
5 BEGIN
6 SELECT ename INTO name FROM emp WHERE empno = &eno;
7 EXCEPTION
8 WHEN OTHERS THEN
9 v_code := SQLCODE;
10 v_errm := SUBSTR(SQLERRM, 1 , 64);
11 DBMS_OUTPUT.PUT_LINE('The error code is ' || v_code || '- ' || v_errm);
12 END ;
13 /
Enter value for eno: 7902
PL/SQL procedure successfully completed. <-- 존재하는 empno 라서 에러 없습니다.
SCOTT>/
Enter value for eno: 7900
The error code is 100- ORA-01403: no data found <-- 존재하지 않는 번호라서 에러 발생합니다.
|
Posted by JJ*