[Oracle] PL/SQL - 커서(cursor)와 예외 처리

2019. 10. 21. 19:43

공부했던 자료 정리하는 용도입니다.

재배포, 수정하지 마세요.

예제는 Oracle Database에서 기본으로 제공되는 SCOTT계정 데이터로 진행됩니다.

커서(cursor)

커서는 SQL문을 실행했을 때 해당 SQL문을 처리하는 정보를 저장한 메모리 공간을 의미한다. (메모리 공간은 Private SQL Area라고 부르며 커서는 이 메모리의 포인터를 말한다.) 커서를 사용하면 실행된 SQL문의 결과 값을 사용할 수 있다. 커서는 명시적 커서와 묵시적 커서 두 가지로 나누어진다.

명시적(explicit) 커서 : 사용자가 직접 커서를 선언하고 사용하는 커서
묵시적(implicit) 커서 / 암시적 커서 : 선언하지 않아도 SQL문을 사용했을 때 자동으로 선언되는 커서

SELECT INTO 방식

SELECT [열1], [열2] ..., [열n] INTO [변수1], [변수2] ..., [변수n]
FROM ...

(커서는 결과 행이 몇 개든 사용할 수 있지만) SELECT INTO 문은 조회되는 데이터가 단 하나의 행일 때만 사용 가능하다. SELECT INTO 문을 사용하면 SELECT 절에 명시한 각 열의 결과 값이 변수에 대입되는데, 이때 SELECT 절에 명시한 열과 INTO 절에 명시한 변수는 개수와 자료형이 일치해야 한다. INTO 절을 제외한 나머지 부분은 SELECT 문과 사용법이 같고 WHERE , GROUP BY 절도 사용 가능하다.

DECLARE
    V_DEPT_ROW DEPT%ROWTYPE; 	-- DEPT 테이블의 행 구조 전체를 참조하는 변수 V_DEPT_ROW 선언
BEGIN
    SELECT DEPTNO, DNAME, LOC INTO V_DEPT_ROW 	-- SEELCT INTO절로 SEELCT문의 결과값을 V_DEPT_ROW에 대입
    FROM DEPT
    WHERE DEPTNO = 40; 	
    DBMS_OUTPUT.PUT_LINE('DEPTNO : ' || V_DEPT_ROW.DEPTNO); 	-- 대입한 V_DEPT_ROW의 값 출력
    DBMS_OUTPUT.PUT_LINE('DNAME : ' || V_DEPT_ROW.DNAME);
    DBMS_OUTPUT.PUT_LINE('LOC : ' || V_DEPT_ROW.LOC);
END;
/

SELECT INTO 를 사용해서 단일행 데이터를 저장하는 예시이다.

명시적 커서 (Explicit cursor)

단계	명 칭	설 명
1단계	커서 선언 (declaration)	사용자가 직접 이름을 지정하여 사용할 커서를 SQL문과 함께 선언
2단계	커서 열기 (open)	커서를 선언할 때 작성한 SQL문을 실행 이때 실행한 SQL문에 영향을 받는 행을 active set이라 한다.
3단계	커서에서 읽어온 데이터 사용 (fetch)	실행된 SQL문의 결과 행 정보를 하나씩 읽어 와서 변수에 저장한 후, 필요한 작업을 수행. 각 행별로 공통 작업을 반복해서 실행하기 위해 여러 종류의 LOOP 문을 함께 사용할 수 있다.
4단계	커서 닫기 (close)	모든 행의 사용이 끝나고 커서를 종료함

DECLARE
    CURSOR [커서 이름] IS [SQL문]; 	-- 커서 선언(Declaration)
BEGIN
    OPEN  [커서 이름]; 			-- 커서 열기(Open)
    FETCH [커서 이름] INTO [변수]; 	-- 커서로 부터 읽어온 데이터 사용(Fetch)
    CLOSE [커서 이름]; 			-- 커서 닫기(Close)
END;

명시적 커서는 사용자가 직접 커서를 선언하고 사용하는 커서를 의미한다. 위의 표와 같이 4단계를 거쳐 사용한다.

■ 단일행 데이터를 저장하는 커서

DECLARE
    -- 커서 데이터를 입력할 변수 선언
    V_DEPT_ROW DEPT%ROWTYPE; 	-- DEPT 테이블의 행 구조 전체를 참조하는 변수 V_DEPT_ROW 선언
    
    -- 명시적 커서 선언(Declaration)
    CURSOR c1 IS 
        SELECT DEPTNO, DNAME, LOC
          FROM DEPT
         WHERE DEPTNO = 40;
        
BEGIN
    -- 커서 열기(Open)
    OPEN c1; 	-- c1 커서를 열어 Active Set을 식별
    
    -- 커서로부터 읽어온 데이터 사용(Fetch)
    FETCH c1 INTO V_DEPT_ROW; 	-- Active Set에서 결과 행을 추출하고 INTO절에 명시한 V_DEPT_ROW에 대입
    
    DBMS_OUTPUT.PUT_LINE('DEPTNO : ' || V_DEPT_ROW.DEPTNO); 	-- 대입한 V_DEPT_ROW의 데이터 출력
    DBMS_OUTPUT.PUT_LINE('DNAME : ' || V_DEPT_ROW.DNAME);
    DBMS_OUTPUT.PUT_LINE('LOC : ' || V_DEPT_ROW.LOC);
    
    -- 커서 닫기(Close)
    CLOSE c1; 	-- 이후 필요하다면 커서를 다시 열어서 사용 가능
END;
/

하나의 행만 조회되는 SELECT 문을 커서로 지정해서 사용할 경우 SELECT INTO 문 보다 더 복잡해진다.

(커서의 효용성은 조회되는 행이 여러 개일 때 극대화된다.)

■ 여러 행의 데이터를 커서에 저장해서 사용(LOOP문 이용)

DECLARE
    -- 커서 데이터를 입력할 변수 선언
    V_DEPT_ROW DEPT%ROWTYPE; 	-- DEPT 테이블의 행 구조 전체를 참조하는 변수 V_DEPT_ROW 선언
    
    -- 명시적 커서 선언(Declaration)
    CURSOR c1 IS
        SELECT DEPTNO, DNAME, LOC
        FROM DEPT;

BEGIN
    -- 커서 열기(Open)
    OPEN c1; 	-- c1 커서를 열어 Active Set을 식별
    
    LOOP
        -- 커서로 부터 읽어온 데이터 사용(Fetch)
        FETCH c1 INTO V_DEPT_ROW; 	-- FETCH문으로 Active Set에서 결과 행을 추출하고 INTO절에 명시한 V_DEPT_ROW에 대입(한 행씩 대입됨)
        
        -- 커서의 모든 행을 읽어오기 위해 %NOTFOUND 속성 지정
        EXIT WHEN c1%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE('DEPTNO : ' || V_DEPT_ROW.DEPTNO 	-- 대입한 V_DEPT_ROW의 데이터 출력(한 행씩 출력됨)
                            || ', DNAME : ' || V_DEPT_ROW.DNAME -- (루프 인덱스의 각 필드를 불러옴)
                            || ', LOC : ' || V_DEPT_ROW.LOC);
    END LOOP;

    -- 커서 닫기
    CLOSE c1; 	-- 이후 필요하다면 커서를 다시 열어서 사용 가능

END;
/

커서에 지정한 SELECT 문이 여러 행의 결과 값을 가질 경우에 여러 방식의 LOOP 문을 사용할 수 있다.

%NOTFOUND 를 EXIT-WHEN 에 사용했기 때문에 더 이상 추출한 데이터가 없으면 LOOP 를 벗어난다.

반복문 사용 시 사용할 수 있는 속성들

속 성	설 명
커서이름%NOTFOUND	수행된 FETCH 문을 통해 추출된 행이 없으면 true , 있으면 false 를 반환
커서이름%FOUND	수행된 FETCH 문을 통해 추출된 행이 있으면 true , 없으면 false 를 반환
커서이름%ROWCOUNT	현재까지 추출된 행 수를 반환
커서이름%ISOPEN	커서가 열려(open)있으면 true , 닫혀(close) 있으면 false 를 반환

■ FOR LOOP문을 활용한 커서 사용

FOR [루프 인덱스 이름] IN [커서 이름] LOOP
    [결과 행별로 반복 수행할 작업];
END LOOP;

커서에 FOR LOOP 문을 사용하면 OPEN , FETCH , CLOSE 문을 작성하지 않는다.
FOR LOOP 를 통해 각 명령어를 자동으로 수행하므로 사용 방법이 간단해지는 장점이 있다.

루프 인덱스(loop index) : 커서에 저장된 각 행이 저장되는 변수. 점(.)을 통해 행의 각 필드에 접근할 수 있다.
ex) c1_rec.DEPTNO

DECLARE
    -- 명시적 커서 선언(Declaration)
    CURSOR c1 IS
        SELECT DEPTNO, DNAME, LOC
          FROM DEPT;
          
BEGIN
    -- 커서 FOR LOOP 시작 (자동 Open, Fetch, Close)
    FOR c1_rec IN c1 LOOP
        DBMS_OUTPUT.PUT_LINE('DEPTNO : ' || c1_rec.DEPTNO 	-- 루프 인덱스 c1_rec을 이용해서 각 행의 필드값을 출력
                            || ', DNAME : ' || c1_rec.DNAME
                            || ', LOC : ' || c1_rec.LOC);
    END LOOP;

END;
/

명시적 커서와 FOR LOOP 문을 사용하는 예시이다.

■ 커서에 파라미터 사용하기

CURSOR [커서 이름]([파라미터 이름] [자료형], ...) IS
SELECT ...

고정값이 아닌 직접 입력한 값이나 상황에 따라 여러 값을 번갈아 사용하려면 커서에 파라미터를 지정하면 된다.

DECLARE
    -- 커서 데이터를 입력할 변수 선언
    V_DEPT_ROW DEPT%ROWTYPE;
    
    -- 명시적 커서 선언(Declaration)
    CURSOR c1 (p_deptno DEPT.DEPTNO%TYPE) IS 	-- 커서에 파라미터 p_deptno 선언
        SELECT DEPTNO, DNAME, LOC
          FROM DEPT
         WHERE DEPTNO = p_deptno; 	-- 지정한 파라미터가 WHERE절에서 DEPTNO의 비교값으로 사용됨

BEGIN
    -- 10번 부서 처리를 위해 커서 사용
    OPEN c1 (10);
        LOOP 
            FETCH c1 INTO V_DEPT_ROW;
            EXIT WHEN c1%NOTFOUND; 	-- c1에서 추출된 행이 없으면 LOOP문을 벗어남
            DBMS_OUTPUT.PUT_LINE('10번 부서 - DEPTNO : ' || V_DEPT_ROW.DEPTNO
                                || ', DNAME : ' || V_DEPT_ROW.DNAME
                                || ', LOC : ' || V_DEPT_ROW.LOC);
        END LOOP;
    CLOSE c1;
    
    -- 20번 부서 처리를 위해 커서 사용
    OPEN c1 (20);
        LOOP
            FETCH c1 INTO V_DEPT_ROW;
            EXIT WHEN c1%NOTFOUND; 	-- c1에서 추출된 행이 없으면 LOOP문을 벗어남
            DBMS_OUTPUT.PUT_LINE('20번 부서 - DEPTNO : ' || V_DEPT_ROW.DEPTNO
                                || ', DNAME : ' || V_DEPT_ROW.DNAME
                                || ', LOC : ' || V_DEPT_ROW.LOC);
        END LOOP;
    CLOSE c1;

END;
/

커서에 파라미터를 사용하는 예시이다.

DEPT 테이블의 DEPTNO 에 따라 다른 수행을 하려면 커서의 OPEN을 각각 명시하여 실행하면 된다.

c1(10) 일 때는 커서 선언부의 WHERE 절이 DEPTNO = 10 이 되고,

c1(20) 일 때는 커서 선언부의 WHERE 절이 DEPTNO = 20 이 되어 결과가 출력된다.

+ 커서에 사용할 파라미터 입력받기

파라미터 값을 사용자에게 직접 받고 싶다면 & 기호와 치환 변수를 사용하면 된다.

DECLARE
    -- 사용자가 입력한 부서 번호를 저장하는 변수 선언
    v_deptno DEPT.DEPTNO%TYPE;
    
    -- 명시적 커서 선언(Declaration)
    CURSOR c1 (p_deptno DEPT.DEPTNO%TYPE) IS 	-- 커서에 파라미터 p_deptno 선언
        SELECT DEPTNO, DNAME, LOC
          FROM DEPT
         WHERE DEPTNO = p_deptno; 	-- 지정한 파라미터가 WHERE절에서 DEPTNO의 비교값으로 사용됨
        
BEGIN
    -- INPUT_DEPTNO에 부서 번호 입력 받고 v_deptno에 대입
    v_deptno := &INPUT_DEPTNO; 	-- &INPUT_DEPTNO를 사용하면 사용자에게 값을 입력하도록 요구할 수 있다.
                                -- 입력한 값을 v_deptno에 대입
                                
    -- 커서 FOR LOOP 시작. c1 커서에 v_deptno를 대입
    FOR c1_rec IN c1(v_deptno) LOOP
        DBMS_OUTPUT.PUT_LINE('DEPTNO : ' || c1_rec.DEPTNO
                            || ', DNAME : ' || c1_rec.DNAME
                            || ', LOC : ' || c1_rec.LOC);
    END LOOP;
END;
/

입력한 값에 따라 결과가 달라지는 것을 확인할 수 있다.

+ 이외에도 커서를 사용하는 다양한 방식이 있다.

FOR UPDATE 절 : 커서를 사용할 때 커서 안의 행이 다른 세션에 의해 변경되지 않도록 잠금(lock) 기능을 제공
참고 블로그 : http://blog.daum.net/kwonmc/5918849?
WHERE CURRENT OF 절 : 커서를 통해 추출한 행에 DML 명령어를 사용
참고 블로그 : http://www.gurubee.net/lecture/1068

묵시적 커서 / 암시적 커서 (Implicit Cursor)

묵시적 커서는 별다른 선언 없이 SQL문을 사용했을 때 오라클에서 자동으로 선언되는 커서를 뜻한다. (사용자가 OPEN , FETCH , CLOSE 를 지정하지 않는다.) PL/SQL문 내부에서 DML 명령어나 SELECT INTO 문 등이 실행될 때 자동으로 생성 및 처리된다. (※ 여러 행의 결과를 가지는 커서는 명시적 커서로만 사용 가능하다.)

또한 묵시적 커서의 속성을 사용하면 현재 커서의 정보를 확인할 수 있다. 커서가 자동으로 생성되므로 커서 이름을 지정하지 않고 SQL키워드로 속성을 지정하며, 명시적 커서의 속성과 비슷한 기능을 갖는다.

묵시적 커서의 속성

속 성	설 명
SQL%NOTFOUND	묵시적 커서 안에 추출한 행이 없으면 true , 있으면 false 를 반환 DML 명령어로 영향을 받는 행이 없을 경우에도 true 반환
SQL%FOUND	묵시적 커서 안에 추출한 행이 있으면 true , 없으면 false 를 반환 DML 명령어로 영향을 받는 행이 있다면 true 반환
SQL%ROWCOUNT	묵시적 커서에 현재까지 추출한 행 수 또는 DML 명령어로 영향받는 행 수를 반환
SQL%ISOPEN	묵시적 커서는 자동으로 SQL문을 실행한 후 CLOSE 되므로 이 속성은 항상 false 를 반환한다.

BEGIN
    UPDATE DEPT SET DNAME = 'DATABASE'
     WHERE DEPTNO = 50;
     
    DBMS_OUTPUT.PUT_LINE('갱신된 행의 수 : ' || SQL%ROWCOUNT);
    
    IF (SQL%FOUND) THEN
        DBMS_OUTPUT.PUT_LINE('갱신 대상 행 존재 여부 : true');
    ELSE
        DBMS_OUTPUT.PUT_LINE('갱신 대상 행 존재 여부 : false');
    END IF;
    
    IF(SQL%ISOPEN) THEN
        DBMS_OUTPUT.PUT_LINE('커서의 OPEN 여부 : true');
    ELSE
        DBMS_OUTPUT.PUT_LINE('커서의 OPEN 여부 : false');
    END IF;
    
END;
/

묵시적 커서의 각 속성을 사용하여 현재 커서 상태를 확인하는 예제이다.

DEPTNO 가 50 인 부서는 없으므로 갱신되는 데이터는 없다.

예외 처리

SQL 또는 PL/SQL이 정상 수행되지 못하는 상황을 오류(error)라고 한다. 오류는 크게 두 가지로 구분되는데 컴파일 오류(문법 오류)와 런타임 오류(실행 오류)로 나뉜다. 그중에서도 프로그램이 실행되는 도중 발생하는 오류를 예외(exception)라고 한다.

PL/SQL 실행 중 예외로 인해 프로그램이 비정상 종료되는 것을 막기 위해 특정 명령어를 PL/SQL문 안에 작성하는 것을 '예외 처리'라고 한다. 예외 처리는 EXCEPTION 영역에 필요 코드를 작성해서 만든다. EXCEPTION 키워드 뒤에 예외 처리를 위해 작성한 코드 부분을 예외 처리부 또는 예외 처리절이라고 하며, 예외 처리부가 실행되면 예외가 발생한 코드 이후의 내용은 실행되지 않는다.

컴파일 오류(compile Error) / 문법 오류(syntax error) : 문법이 잘못되었거나 오타로 인한 오류
런타임 오류(runtime error) / 실행 오류(excute error) : 명령문의 실행 중 발생한 오류

DECLARE
    v_wrong NUMBER;
BEGIN
    SELECT DNAME INTO v_wrong 	-- NUMBER형인 v_wrong에 VARCHAR2형인 DNAME열 값 대입(예외발생)
    FROM DEPT
    WHERE DEPTNO = 10;
    
    -- 예외가 발생한 코드 이후의 내용은 실행되지 X
    DBMS_OUTPUT.PUT_LINE('예외가 발생하면 다음 문장은 실행되지 않는다.'); 	
    
EXCEPTION 	-- 예외 처리부
    WHEN VALUE_ERROR THEN
        DBMS_OUTPUT.PUT_LINE('예외 처리 : 수치 또는 값 오류 발생');
END;
/

예외 처리를 보여주는 간단한 예시이다. NUMBER 형 변수인 v_wrong 에 VARCHAR2 형의 데이터인 DNAME 열 값을 넣으려고 했기 때문에 예외가 발생하고 비정상 종료되었어야 했다. 그러나 EXCEPTION 에서 예외 처리부를 작성했기 때문에 오류가 발생해도 PL/SQL문이 정상 처리된다.

오류가 발생한 코드 이후의 내용은 실행되지 않기 때문에 '예외가 발생하면~'이라는 문구가 출력되지 않는 것을 확인할 수 있다.

■ 예외 종류

1) 사전 정의된 예외

사전 정의된 예외는 비교적 자주 발생하는 예외에 이름을 붙여놓은 것이다.

예외 이름	예외 번호(SQLCODE)	설 명
ACCESS_INTO_NULL	ORA-06530 : -6530	초기화 되지 않은 객체 속성 값 할당
CASE_NOT_FOUND	ORA-06592 : -6592	CASE 문의 WHERE 절에 조건이 없고 ELSE 절도 없을 경우
COLLECTION_IS_NULL	ORA-06531 : -6531	초기화 되지 않은 중첩 테이블, VARRAY 에 EXIT 외 컬렉션 메서드를 사용하려 할 경우 또는 초기화되지 않은 중첩 테이블이나 VARRAY 에 값을 대입하려 할 경우
CURSOR_ALREADY_OPEN	ORA-06511 : -6511	이미 OPEN 된 커서를 OPEN 시도할 경우
DUP_VAL_ON_INDEX	ORA-00001 : -1	UNIQUE 인덱스가 있는 열에 중복된 값을 저장하려고 했을 경우
INVALID_CURSOR	ORA-01001 : -1001	OPEN 되지 않은 커서를 CLOSE 시도하는 것과 같이 잘못된 커서 작업을 시도하는 경우
INVALID_NUMBER	ORA-01722 : -1722	문자 → 숫자로의 변환이 실패했을 경우
LOGIN_DENIED	ORA-01017 : -1017	사용자 이름이나 패스워드가 올바르지 않은 상태에서 로그인을 시도할 경우
NO_DATA_FOUND	ORA-01403 : +100	SELECT INTO 문에서 결과 행이 하나도 없을 경우
NOT_LOGGED_ON	ORA-01012 : -1012	데이터베이스에 접속되어 있지 않은 경우
PROGRAM_ERROR	ORA-06501 : -6501	PL/SQL 내부 오류가 발생했을 경우
ROWTYPE_MISMATCH	ORA-06504 : -6504	호스트 커서 변수와 PL/SQL 커서 변수의 자료형이 호환되지 않을 경우
SELF_IS_NULL	ORA-30625 : -30625	초기화되지 않은 오브젝트의 MEMBER 메서드를 호출한 경우
STORAGE_ERROR	ORA-06500 : -6500	PL/SQL 메모리가 부족하거나 문제가 발생한 경우
SUBSCRIPT_BEYOND_COUNT	ORA-06533 : -6533	컬렉션의 요소 수보다 큰 인덱스를 사용하여 중첩 테이블이나 VARRAY 의 요소 참조를 시도할 경우
SUBSCRIPT_OUTSIDE_LIMIT	ORA-06532 : -6532	정상 범위의 인덱스 번호를 사용하여 중첩 테이블이나 VARRAY 요소 참조를 시도할 경우
SYS_INVALID_ROWID	ORA-01410 : -1410	문자열을 ROWID 로 변환할 때 값이 적절하지 않은 경우
TIMEOUT_ON_RESOURCE	ORA-00051 : -51	자원 대기 시간을 초과했을 경우
TOO_MANY_ROWS	ORA-01422 : -1422	SELECT INTO 문의 결과 행이 여러 개일 경우
VALUE_ERROR	ORA-06502 : -6502	산술 · 변환 · 잘림 · 제약 조건 오류가 발생했을 경우
ZERO_DIVIDE	ORA-01476 : -1476	숫자 데이터를 0 으로 나누려고 했을 경우

2) 이름 없는 예외

이름 없는 예외는 ORA-XXXXX 식으로 예외 번호는 있지만 이름이 정해져 있지 않은 예외를 뜻한다. 이름이 없는 예외는 예외 처리부에서 사용하기 위해 이름을 직접 붙여서 사용한다.

■ 예외 처리부 작성

EXCEPTION
    WHEN [예외 이름1] [OR 예외이름 2 -] THEN
     	 [예외 처리에 사용할 명령어];
    WHEN [예외 이름3] [OR 예외 이름 4 -] THEN
    	 [예외 처리에 사용할 명령어];
    ...
    WHEN OTHERS THEN
    	 [예외 처리에 사용할 명령어];

예외 처리부는 EXCEPTION 절에 필요한 코드를 사용하여 작성한다. WHEN 으로 시작하는 절을 예외 핸들러(exception handler)라고 하며, 발생한 예외 이름과 일치하는 WHEN 절의 명령어를 수행한다. ( IF-THEN 문처럼 여러 예외 핸들러 중 일치하는 하나의 예외 핸들러 명령어만 수행한다.) OTHERS 는 IF 문의 ELSE 처럼 어느 예외와도 일치하는 예외가 없을 경우에 처리할 내용을 작성한다. 수행할 명령어에는 PL/SQL 실행부와 마찬가지로 여러 문법을 사용할 수 있다.

1) 사전 정의된 예외 사용

DECLARE
    v_wrong NUMBER;
BEGIN
    SELECT DNAME INTO v_wrong
      FROM DEPT
     WHERE DEPTNO = 10;
     
     
     DBMS_OUTPUT.PUT_LINE('예외가 발생하면 다음 문장은 실행되지 않는다.');
     
EXCEPTION
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('예외 처리 : 요구보다 많은 행 추출 오류 발생');
    WHEN VALUE_ERROR THEN
        DBMS_OUTPUT.PUT_LINE('예외 처리 : 수치 또는 값 오류 발생');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('예외 처리 : 사전 정의 외 오류 발생');
END;
/

예외 핸들러에 사전 정의된 예외만을 사용할 때는 일반적인 예외 처리부를 작성하는 것과 같이 발생할 수 있는 예외를 명시하면 된다.

2) 이름 없는 예외 사용

DECLARE
    [예외 이름 1] EXCEPTION;
    PRAGMA EXCEPTION_INIT([예외 이름 1], [예외 번호]);
.
.
.
EXCEPTION
    WHEN [예외 이름 1] THEN
    [예외 처리에서 사용할 명령어];
    ...
END;

이름이 없는 내부 예외는 이름을 직접 지정해 주어야 예외 처리부에서 사용할 수 있기 때문에 위와 같이 선언부에서 오라클 예외 번호와 함께 이름을 붙인다. 이름이 정해진 예외는 사전 정의된 예외를 사용할 때와 마찬가지로 예외 처리부에서 지정한 이름으로 예외 핸들러에 작성한다.

3) 사용자 정의 예외 사용

DECLARE
    [사용자 예외 이름] EXCEPTION;
    ...
BEGIN
    IF [사용자 예외를 발생시킬 조건] THEN
        RAISE [사용자 예외 이름]
    ...
    END IF;
EXCEPTION
    WHEN [사용자 예외 이름] THEN
       [예외 처리에 사용할 명령어];
    ...
END;

사용자 정의 예외는 오라클에 정의되지 않은 특정 상황을 직접 오류로 정의하는 방식이다. 위와 같이 선언부에서 예외 이름을 정해주고 실행부에서 직접 정의한 오류 상황이 생겼을 때 RAISE 키워드를 사용하여 예외를 직접 만들 수 있다. 이렇게 만든 예외 역시 앞의 예외들과 마찬가지로 예외 처리부에서 예외 이름을 통해 수행할 내용을 작성해서 처리할 수 있다.

오라클 저장 함수 RAISE_APPLICATION_ERROR 를 사용하면 ORA-XXXXX 처럼 오류코드를 지정하는 것도 가능하다. 숫자의 범위는 -20000 ~ -20999 범위 내에서 가능하다.

DECLARE
    v_wrong NUMBER;
BEGIN
    
    v_wrong := 80;
    
    IF v_wrong >= 80 THEN
        RAISE_APPLICATION_ERROR(-20099, 'v_wrong이 80이상인 경우');
    END IF;
    
     DBMS_OUTPUT.PUT_LINE('예외가 발생하면 다음 문장은 실행되지 않는다.');
     
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('SQLCODE : ' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

RAISE_APPLICATION_ERROR 를 이용해서 사용자 정의 예외를 지정하는 예시이다.

■ 오류 코드와 오류 메시지 사용

함 수	설 명
SQLCODE	오류 번호를 반환하는 함수
SQLERRM	오류 메시지를 반환하는 함수

PL/SQL문의 정상 종료 여부와 상관없이 발생한 오류 내역을 알고 싶을 때 SQLCODE 와 SQLERRM 함수를 사용한다.

이 둘은 PL/SQL에서만 사용 가능한 함수로 SQL문에서는 사용할 수 없다.

DECLARE
    v_wrong NUMBER;
BEGIN
    SELECT DNAME INTO v_wrong
      FROM DEPT
     WHERE DEPTNO = 10;
     
     DBMS_OUTPUT.PUT_LINE('예외가 발생하면 다음 문장은 실행되지 않는다.');
     
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('예외 처리 : 사전 정의 외 오류 발생');
        DBMS_OUTPUT.PUT_LINE('SQLCODE : ' || TO_CHAR(SQLCODE));
        DBMS_OUTPUT.PUT_LINE('SQLERRM : ' || SQLERRM);
END;
/

SQLCODE 와 SQLERRM 를 사용하는 예시이다.

저작자표시 비영리 변경금지

'Back end > Database' 카테고리의 다른 글

[Oracle] 저장 서브프로그램 - 패키지(package), 트리거(trigger) (0)	2019.10.23
[Oracle] 저장 서브프로그램 - 저장 프로시저(stored procedure), 함수(function) (0)	2019.10.22
[Oracle] 복합 자료형 (레코드, 컬렉션) (0)	2019.10.19
[Oracle] PL/SQL - 조건 제어문, 반복 제어문, 순차 제어문 (0)	2019.10.18
[Oracle] PL/SQL (0)	2019.10.16

pridiot