Oracle PL/SQL[프로시저/예외처리]

PL/SQL 코드를 작성할 떄 발생할 수 있는 오류에는 크게 두가지가 있다 하나는 문법 오류로써 객체 (테이블,뷰,함수,프로시저 등) 나 키워드 이름을 잘못 참조하거나 함수나 프로시저의 매개변수를 잘못 명시했을 때 발생되는 오류로, 이들은 컴파일할 때 걸러진다. 두 번째는 컴파일 때는 아무런 문제가 없으나 실행, 즉 런타임 때 로직을 처리하면서 발생하는 오류인데 이를 예외라고 한다.

EXCEPTION WHEN 예외명1 THEN 예외처리 구문2
WHEN 예외명2 THEN 예외처리 구문2
WHEN OTHERS THEN 예외처리구문;

구문을 보면 CASE 문과 비슷한 구조임을 알 수 있다. 즉 처리할 예외명을 언급하고 그예외에 해당되면 예외처리 구문을 나열하는 식이다. WHEN 다음에 위치하는 예외명은 아무 이름이나 사용할 수는 없고 시스템 예외 중 일부와 사용자가 직접 정의한 예외명을 사용할 수 있다. 구체적인 예외명을 알 수 없을때는 OTHER를 사용하면 되는데, 이렇게 하면 오라클 시스템에서 PL/SQL 코드 상에서 발생한 런타임 예외를 자동으로 예외를 잡아주니 이때 처리할 수 있는 예외는 물론 시스템 예외에 한정된다.

여기서 시스템 예외는 나눗셈 연산을 할때 0 으로 나눈다거나 SELECT INTO 절을 사용해 데이터를 기져올때 조건에 만족하는 데이터가 없으면 발생하는 예외등이 있다. 즉 시스템 예외는 오라클 내부에 미리 정의된 예외라고 할수 있다.

DECLARE
    VI_NUM NUMBER := 0;
    
    BEGIN
     VI_NUM := 10/0;
     DBMS_OUTPUT.PUT_LINE('Success!');
     END;
     
     
     
     01476. 00000 -  "divisor is equal to zero"

10을 0 으로 나누려니 오류가 발생했다. 예외처리 구문을 사용하지 않으니 시스템에서 오류를 밷어낸것이다.

        
DECLARE
    VI_NUM NUMBER := 0;
    
    BEGIN
     VI_NUM := 10/0;
     DBMS_OUTPUT.PUT_LINE('Success!');
     
     EXCEPTION WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('오류가 발생했다');
     END;

예외처리를 함으로써 "오류가 발생했다" 란 메시지가 출력되었다. 이처럼 PL/SQL 코드의 실행부에서 예외가 발생하는 즉시 이후 코드는 처리되지 않고 제어권은 예외처리 구문으로 넘어간다.

자 여기서 왜 예외처리를 굳이 할까? 예외처리르 함으로써 정확한? 음~ 예외처리를 함으로써 좀더 세부적으로 볼수 있기 때문이다.

CREATE OR REPLACE PROCEDURE EXCEPTION01 
IS
    VI_NUM NUMBER := 0;
BEGIN
  VI_NUM := 10/0;
  DBMS_OUTPUT.PUT_LINE('Success');
  
  EXCEPTION WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('오류가 발생했다');
  DBMS_OUTPUT.PUT_LINE('SQL ERROR CODE: ' || SQLCODE);
  DBMS_OUTPUT.PUT_LINE('SQL ERROR MESSAGE: ' || SQLERRM);
  DBMS_OUTPUT.PUT_LINE(SYS.dbms_utility.format_error_backtrace);
END EXCEPTION01;

출력 내용 중 맨 마지막에 줄을 보면 소스코드의 5행에서 예외가 발생했다. 이 외에도 DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_CALL_STATCK 사용하면 예외에 대해 좀더 자세한 정보를 알수 있다.

CREATE OR REPLACE PROCEDURE EXCEPTION01 
IS
    VI_NUM NUMBER := 0;
BEGIN
  VI_NUM := 10/0;
  DBMS_OUTPUT.PUT_LINE('Success');
  
  EXCEPTION WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('오류가 발생했다');
  DBMS_OUTPUT.PUT_LINE('SQL ERROR CODE: ' || SQLCODE);
  DBMS_OUTPUT.PUT_LINE('SQL ERROR MESSAGE: ' || SQLERRM);
  DBMS_OUTPUT.PUT_LINE(SYS.dbms_utility.format_error_backtrace);
  DBMS_OUTPUT.PUT_LINE(SYS.dbms_utility.format_call_stack);
  DBMS_OUTPUT.PUT_LINE(SYS.dbms_utility.format_error_stack);
END EXCEPTION01;

시스템 예외

예외명	예외코드	설명
ACCESS_INTO_NULL	ORA-06530	LOB 과 같은 객체 초기화 되지 않은 상태에서 사용
CASE_NOT_FOUND	ORA-06592	CASE문 사용시 구문오류
CURSOR_ALREADY_OPEN	ORA-06511	커서가 이미 OPEN 상태인데 OPEN 하려고 시도
DUP_VAL_ON_INDEX	ORA-00001	유일 인덱스가 있는 컬럼에 중복 값으로 INSERT,UPDATE 수행
INVALID_NUMBER	ORA-01722	문자를 숫자로 변환할 때 실패할 경우
LOGIN_DENIED	ORA01017	잘못된 사용자 이름이나 비밀번호로 로그인을 시도
NO_LOGGED_ERROR	ORA- 06501	PL/SQL 코드상에서 내부 오류를 만났을때, 이 오류가 발생하면 '오라클의 문의' 란 메세지가 출력됨
PROGRAM_ERROR	ORA- 06500	프로그램 수행 시 메모리가 부족할 경우
TIMEOUT_ON_RESOURCE	ORA-00051	데이터 베이스 자원을 기다리는 동안 타임아웃 발생시
TOO_MANY_ROWS	ORA-01422	SELECT INTO 절 사용할때 결과가 한로우 이상일때
VALUE_ERROR	ORA-06502	수치 또는 값 오류
ZERO_DIVIDE	ORA-01476	0으로 나눌때

표를 보면 ZERO_DIVIDE 0으로 나눌때! OTHERS 대신 사용할수 있다.

CREATE OR REPLACE PROCEDURE EXCEPTION01 
IS
    VI_NUM NUMBER := 0;
BEGIN
  VI_NUM := 10/0;
  DBMS_OUTPUT.PUT_LINE('Success');
  
  EXCEPTION WHEN ZERO_DIVIDE THEN
  DBMS_OUTPUT.PUT_LINE('오류가 발생했다');
  DBMS_OUTPUT.PUT_LINE('SQL ERROR CODE: ' || SQLCODE);
  DBMS_OUTPUT.PUT_LINE('SQL ERROR MESSAGE: ' || SQLERRM);
  DBMS_OUTPUT.PUT_LINE(SYS.dbms_utility.format_error_backtrace);
  DBMS_OUTPUT.PUT_LINE(SYS.dbms_utility.format_call_stack);
  DBMS_OUTPUT.PUT_LINE(SYS.dbms_utility.format_error_stack);
END EXCEPTION01;

여러 개의 예외를 명시할때 OTHERS 는 반드시 맨 끝에 명시해야 한다. 이제 2개 이상의 예외명을 명시해 보자.

CREATE OR REPLACE PROCEDURE EXCEPTION01 
IS
    VI_NUM NUMBER := 0;
BEGIN
  VI_NUM := 10/0;
  DBMS_OUTPUT.PUT_LINE('Success');
  
  EXCEPTION WHEN ZERO_DIVIDE THEN
  DBMS_OUTPUT.PUT_LINE('오류가 발생했다');
  DBMS_OUTPUT.PUT_LINE('SQL ERROR CODE: ' || SQLCODE);
  DBMS_OUTPUT.PUT_LINE('SQL ERROR MESSAGE: ' || SQLERRM);
  WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(SYS.dbms_utility.format_error_backtrace);
  DBMS_OUTPUT.PUT_LINE(SYS.dbms_utility.format_call_stack);
  DBMS_OUTPUT.PUT_LINE(SYS.dbms_utility.format_error_stack);
END EXCEPTION01;

예외처리는 EXCEPTION 절에 명시한 순서대로 처리된다. 즉 맨 처음 명시한 예외가 발생하면 해당되는 로직을 처리하고 이후 예외는 무시하고 프로시저는 종료된다.

create or replace PROCEDURE JOB_ID_PROC (
    P_EMPLOYEE_ID IN EMPLOYEES.EMPLOYEE_ID%TYPE,
    P_JOB_ID IN JOBS.JOB_ID%TYPE)
    
    IS
    VN_CNT NUMBER := 0;
    
    BEGIN 
        SELECT COUNT(*)
        INTO VN_CNT
        FROM JOBS
        WHERE JOB_ID = P_JOB_ID;
        
        IF VN_CNT = 0 THEN    
           DBMS_OUTPUT.PUT_LINE('JOB_ID 가 없다');
           RETURN;    
        ELSE
            UPDATE EMPLOYEES
               SET JOB_ID = P_JOB_ID
             WHERE EMPLOYEE_ID = P_EMPLOYEE_ID;
        END IF;    
    COMMIT;
END;

위 쿼리는 job_id 를 매개변수로 받아서 해당 사원의 job_id 값을 갱신하는 프로시저를 만드는데 만약 job_id가 jobs 테이블에 존재하지 않으면 오류 메시지와 함께 프로시저를 만드는데, 만약 해당 job_id가 JOBS 테이블에 존재하지 않으면 오류 메세지와 함께 프로시저를 종료시킨다.

EXEC JOB_ID_PROC(200,'sm_job');

JOBS 테이블에는 sm_job 이라는 job_id 가 없는것을 확인할수 있다.

create or replace PROCEDURE JOB_ID_PROC (
    P_EMPLOYEE_ID IN EMPLOYEES.EMPLOYEE_ID%TYPE,
    P_JOB_ID IN JOBS.JOB_ID%TYPE)
    
    IS
    VN_CNT NUMBER := 0;
    
    BEGIN 
        SELECT COUNT(*)
        INTO VN_CNT
        FROM JOBS
        WHERE JOB_ID = P_JOB_ID;
        
        UPDATE EMPLOYEES
           SET JOB_ID = P_JOB_ID
         WHERE EMPLOYEE_ID = P_EMPLOYEE_ID;         
COMMIT;
    
EXCEPTION
WHEN NO_DATA_FOUND THEN
        dbms_output.put_line(SQLERRM);
        DBMS_OUTPUT.PUT_LINE(P_JOB_ID || '해당하는 job_id가 없습니다');
        WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('기타에러: ' || SQLERRM);
END;

미리 정의된 예외중 NO_DATA_FOUND라는 예외를 사용하니 굳이 IF 문을 사용할 필요가 없어 졌다.

맨처음 SELECT INTO 문에서 해당 job_id 를 발견하지 못하면 NO_DATA_FOUND예외가 발생해 제어권이 예외 처리부로 넘어가 메세지를 출력하고 프로시저가 종료된것이다.

사용자 정의 예외

지금까지 시스템 예외에 대해 살펴 봤는데, 사용자 즉 개발자가 직접 예외를 정의해서 사용할 때가 있을 수 있다. 예컨대 사원 테이블에 신규 사원을 입력하는 프로시저를 작성한다고 해보자. 이 프로시저는 사원명, 급여, 부서번호, 등을 매개변수로 전달받아야 한다

그런데 부서 테이블에 없는 부서번호를 전달 받았다고 한다면 어떻게 될까? 시스템 내부적으로는 오류가 발생하지 않는다.

왜냐하면 오라클은 부서 테이블에 해당 부서번호의 존재유무를 체크해야 한다는 사실을 알지 못하기 때문이다.

이런 경우에는 다음과 같이 사용자가 직접 처리해줘야한다.

SELECT COUNT(*)
INTO VN_CNT
FROM departments
WHERE DEPARTMENT_ID = P_DEPARTMENT_ID;

IF VN_CNT = 0 THEN
 DBMS_OUTPUT.PUT_LINE('부서번호가없다')
 RETURN;
 END IF;

위와 같이 처리해도 무방하지만, 이런 형태의 비즈니스 로직에 따른 예외처리도 오라클 시스템 예외처럼 다룰수 있다.

즉 사용자가 직접 예외를 정의하고 해당예외가 발생하면 EXCEPTION 절에서 처리할수 있다.

예외정의: 사용자_정의_예외명 EXCEPTION;

사용자 예외를 사용하려면 일단 변수나 상수처럼 PL/SQL 블록의 선언부에 예외를 정의해야한다.

예외발생시키키:RAISE 사용자_정의_예외명;

시스템 예외는 해당 예외가 자동으로 검출되지만 사용자 정의 예외는 직접 예외를 발생시켜야 하는데, 'RAISE예외명:' 형태로 사용한다.

발생된예외 처리 : EXCEPTION WHEN 사용자_정의_예외명 THEN

예외를 발생시키면 자동으로 제어권이 EXCEPTION 절로 넘어오므로 시스템 예외와 동일한 방식으로 처리해 주면된다.

CREATE OR REPLACE PROCEDURE INST_EMP (
    P_EMPLOYEE_NAME IN EMPLOYEES.FIRST_NAME%TYPE,
    P_DEPARTMENT_ID IN EMPLOYEES.DEPARTMENT_ID%TYPE)
    
IS  
    VN_EMPLOYEE_ID EMPLOYEES.EMPLOYEE_ID%TYPE;
    VN_CURR_DATE DATE := SYSDATE;
    VN_CNT NUMBER := 0;
    
    EX_INVALID_DEPID EXCEPTION; -- 예외선언 
BEGIN
    -- 부서 테이블 해당 부서번호 존재유무 체크
    SELECT COUNT(*)
    INTO VN_CNT
    FROM DEPARTMENTS
    WHERE DEPARTMENT_ID = P_DEPARTMENT_ID;
    IF VN_CNT = 0 THEN -- 사용자 정의 예외처리 
        RAISE EX_INVALID_DEPID;
        
    END IF;
    
    SELECT MAX(EMPLOYEE_ID)+1
    INTO VN_EMPLOYEE_ID
    FROM EMPLOYEES;
    
    --사용자예외처리 예제이므로 사원 테이블에 최소한 데이터만 입력 
    INSERT INTO EMPLOYEES(EMPLOYEE_ID,FIRST_NAME,HIRE_DATE,DEPARTMENT_ID)
            VALUES(VN_EMPLOYEE_ID,P_EMPLOYEE_NAME,VN_CURR_DATE,P_DEPARTMENT_ID);
            
            COMMIT;
            
        EXCEPTION
        WHEN EX_INVALID_DEPID THEN --사용자 정의 예외처리 
            DBMS_OUTPUT.PUT_LINE('해당 부서번호가 없습니다');
            WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE(SQLERRM);
END INST_EMP;

위 쿼리는 신규사원을 입력하는데 부서번호가 잘못들어온 경우를 처리하는 예외 로직이 담긴 프로시저이다.

EXEC INST_EMP('홍길동',9999);

시스템 예외에 이름 부여하기

사용자 정의 예외선언 : 사용자_정의_예외명 EXCEPTION;

선언부에서 사용자 정의 에외를 선언한다.

사용자 정의 예외명과 시스템 예외 코드 연결 : PRAGMA EXCEPTION_INIT (사용자_정의_예외명, 시스템_예외_코드);

발생된 예외 처리: EXCEPTION WHEN 사용자_정의_예외명 THEN

매개변수를 하나더 추가해서 입사월을 받아 사원을 입력할때 입사일자는 무조건 전달받은 입사월의 1일에 입력되어야 한다는 규칙이 정해졌다고 가정하자 잘못 된 입사월이 입력될때, ORA-01843 이라는 시스템 예외를 발생시켜보자.

create or replace PROCEDURE INST_EMP (
    P_EMPLOYEE_NAME IN EMPLOYEES.FIRST_NAME%TYPE,
    P_DEPARTMENT_ID IN EMPLOYEES.DEPARTMENT_ID%TYPE,
    P_HIRE_DATE VARCHAR2)
    
IS  
    VN_EMPLOYEE_ID EMPLOYEES.EMPLOYEE_ID%TYPE;
    VN_CURR_DATE DATE := SYSDATE;
    VN_CNT NUMBER := 0;
    
    EX_INVALID_DEPID EXCEPTION; -- 잘못된 부서 번호일 경우 예외처리
    EX_INVALID_MONTH EXCEPTION;
    PRAGMA EXCEPTION_INIT(EX_INVALID_MONTH,-1843); -- 예외명과 예외코드 연결 
BEGIN
    -- 부서 테이블 해당 부서번호 존재유무 체크
    SELECT COUNT(*)
    INTO VN_CNT
    FROM DEPARTMENTS
    WHERE DEPARTMENT_ID = P_DEPARTMENT_ID;
    
    IF VN_CNT = 0 THEN -- 사용자 정의 예외처리 
        RAISE EX_INVALID_DEPID;
        
    END IF;
    --입사월체크 (1 ~12월 범위를 벗어나는지 체크) 
    IF SUBSTR(P_HIRE_DATE, 5,2) NOT BETWEEN '01' AND '12' THEN
    RAISE EX_INVALID_MONTH; -- 잘못된 입사월 사용자 정의 예외 발생 
    END IF;
    
    SELECT MAX(EMPLOYEE_ID)+1
    INTO VN_EMPLOYEE_ID
    FROM EMPLOYEES;
    
    --사용자예외처리 예제이므로 사원 테이블에 최소한 데이터만 입력 
    INSERT INTO EMPLOYEES(EMPLOYEE_ID,FIRST_NAME,HIRE_DATE,DEPARTMENT_ID)
            VALUES(VN_EMPLOYEE_ID,P_EMPLOYEE_NAME,TO_DATE(P_HIRE_DATE || '01'),P_DEPARTMENT_ID);
            
            COMMIT;
            
        EXCEPTION
        WHEN EX_INVALID_DEPID THEN --사용자 정의 예외처리 
            DBMS_OUTPUT.PUT_LINE('해당 부서번호가 없습니다');
            WHEN EX_INVALID_MONTH THEN --입사월 사용자 정의 예외처리
                DBMS_OUTPUT.PUT_LINE(SQLCODE);
                DBMS_OUTPUT.PUT_LINE(SQLERRM);
                DBMS_OUTPUT.PUT_LINE('1~12월 범위를 벗어난 월입니다.');
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE(SQLERRM);
END INST_EMP;

EXEC INST_EMP('홍길동',110,'201314');

예상했던 대로 사용자 정의 예외에 ORA-01843 예외코드가 연결되어 처리된 것을 확인할 수 있다.

마지막으로 예외명을 정의한 뒤 시스템 예외코드를 연결해서 사용할때 주의해야 할 제약사항을 정리해 보자.

미리 정의된 예외중 NO_DATA_FOUND(-1403) 는 사용자 정의 예외명에 해당 코드(-1403) 을 연결할수 없다.
예외 코드로 0이나 100을 제외한 양수 , 그리고 -10000000 이하 값은 사용할 수 없다.
동일한 예외명으로 다른 예외코드를 2개 이상 연결하면, 맨 마지막에 연결한 코드가 적용된다.

'DB' 카테고리의 다른 글

Oracle PL/SQL 프로시저 [ 효율적인예외처리] (0)	2020.12.27
Oracle PL/SQL 프로시저[사용자 정의예외처리] (0)	2020.12.27
Oracle PL/SQL[프로시저] [RETURN] (0)	2020.12.26
Oracle PL/SQL[프로시저] (0)	2020.12.26
Oracle PL/SQL 의 사용자 정의함수 (0)	2020.12.24

안졸리나졸리나코딩

Oracle PL/SQL[프로시저/예외처리]

시스템 예외

사용자 정의 예외

시스템 예외에 이름 부여하기

'DB' 카테고리의 다른 글

티스토리툴바

Oracle PL/SQL[프로시저/예외처리]

시스템 예외

사용자 정의 예외

시스템 예외에 이름 부여하기

'DB' 카테고리의 다른 글

'DB' Related Articles

티스토리툴바