1. PL/SQL 예외란?
PL/SQL 블록이 PARSE 되는 동안에 오타 등으로 인하여 발생되는 에러를 컴파일 에러 (Compilation Error) 라고 부르며,
PL/SQL 블록이 실행되는 동안 발생되는 에러를 런타임 에러 (Run-Time Error) 라고 부르는데,
이 런타임 에러를 오라클에서는 예외 (Exception) 라고 부른다.
오라클의 예외 종류는 두가지이며 오라클 예외 (ORACLE Exception) 와 사용자 정의 예외 (User-defined Exception) 가 그것이다.
Predefined ORACLE Exception (미리 정의되어 있는 오라클 예외들)
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 문이 두 건이상 의 행을 리턴했을 때 발생되는 예외
VALUE_ERROR
ORA-06502
산술, 변환, 절삭 크기 제약에 에러가 생겼을 때 발생되는 예외
ZERO_DIVIDE
ORA-01476
0으로 나누려 했을 때 발생하는 예외
2. PL/SQL 블록 내의 예외처리부
문법
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 예외에서 처리된다.
예외처리 사용 예1. 오라클에서 사전 정의된 예외처리하기
사원명이 'A' 로 시작하는 사원을 조회하되 여러 건의 데이터가 나올 경우를 발생시키는 예외처리 부분을 포함하세요.
SELECT ename
FROM emp
WHERE ename LIKE 'A%' ;
A 로 시작하는 사원이 2명이라 명시적 커서를 사용해야 하지만 묵시적 커서를 사용하여 TO_MANY_ROWS 라는 예외를 발생시키는 예제
DECLARE
v_ename emp.ename%TYPE ;
BEGIN
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 TO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE ('사원이 두명 이상입니다.') ;
END ;
/
예외처리 사용 예2. 사용자 정의 예외처리
부서번호를 입력받아 해당 부서를 삭제하되 해당 부서에 소속된 사원이 있을 경우 '사원이 존재합니다' 라는 예외를 발생시키세요.
DECLARE
sawon_exits EXCEPTION ;
PRAGMA EXCEPTION_INIT(sawon_exist, -2292) ;
BEGIN
DELETE FROM dept
WHERE deptno =&dno ;
COMMIT ;
EXCEPTION
WHEN sawon_exits THEN
DBMS_OUTPUT.PUT_LINE('사원이 존재합니다') ;
END ;
/
PL/SQL 에서 PRAGMA EXCEPTION_INIT 은 컴파일러에게 예외 이름을 ORACLE 오류 번호를 연관시키도록 지시한다.
이렇게 하면 모든 내부 예외를 이름으로 참조하고 이 예외에 대한 특정 처리기를 작성할 수 있다.
CREATE TABLE t_pragma
(
no NUMBER PRIMARY KEY,
name VARCHAR2(10)
) ;
INSERT INTO t_pragma
VALUES (1, 'AAA') ;
INSERT INTO t_pragma
VALUES (1, 'BBB') ;
no 컬럼이 Primary Key 이므로 중복데이터가 들어갈 수 없기 때문에 ORA-00001번 에러가 난다.
에러메시지를 PRAGMA 기능을 이용하여 변경
DECLARE
new_msg EXCEPTION ;
PRAGMA EXCEPTION_INIT (new_msg, -1) ;
BEGIN
INSERT INTO t_pragma
VALUES (1, 'CCC') ;
EXCEPTION
WHEN new_msg THEN
DBMS_OUTPUT.PUT_LINE('존재하는 번호입니다!') ;
END ;
/
원하는 에러메시지를 출력할 수 있는 유용한 기능이다.
CREATE TABLE t_con
(
no NUMBER NOT NULL,
name VARCHAR2(10)
) ;
INSERT INTO t_con
VALUES (1, 'AAA') ;
INSERT INTO t_pragma
VALUES (null, 'BBB') ;
no 컬럼에 NOT NULL 제약조건이 걸려있으므로 ORA-01400 번 에러가 난다. 에러메시지 변경
DECLARE
new_msg EXCEPTION ;
PRAGMA EXCEPTION_INIT (new_msg, -1400) ;
BEGIN
INSERT INTO t_pragma
VALUES (null, 'BBB') ;
EXCEPTION
WHEN new_msg THEN
DBMS_OUTPUT.PUT_LINE('null 입력불가') ;
END ;
예외처리 사용 예3. RAISE 를 사용하여 예외처리하기
없는 사번을 입력하면 에러메시지 출력하기
DECLARE
v_empno emp.empno%TYPE ;
v_name emp.ename%TYPE ;
no_empno EXCEPTION ;
BEGIN
DELETE FROM EMP
WHERE empno =&empno ;
IF SQL%NOTFOUND THEN
RAISE no_empno ;
END IF ;
EXCEPTION
WHEN no_empno THEN
DBMS_OUTPUT.PUT_LINE('사원이 없습니다') ;
END ;
/
예외처리 사용 예4. RAISE_APPLICATION_ERROR 프로시저 사용하기
앞의 예는 오라클에서 제공하는 에러를 사용하여 예외처리부에서 각각의 예외를 처리하는 방식이었다.
RAISE_APPLICATION_ERROR 프로시저는 사용자가 에러를 정의하고 즉시 예외를 처리하는 방식이다.
사용가능한 에러번호는 20000 부터 20999 까지이다.
DECLARE
v_empno emp.empno%TYPE ;
v_name emp.ename%TYPE ;
BEGIN
DELETE FROM emp
WHERE empno =&empno ;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR (-20100, '존재하지 않는 사번입니다') ;
END IF ;
END ;
/
[출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저