Oracle PL/SQL 프로시저 [ 효율적인예외처리]

시스템 예외인 경우는 OTHERS 를 사용하자

미리 정의된 예외일 때는 예외명이 있어  EXCEPTION 절에 명시해 사용할수 있지만, 몇가지를 제외하고는 기억하기도 힘들고 사용하기가 번거롭다. 대신 OTHER는 모든 시스템 예외를 잡아내므로 OTHERS 와 SQLCODE, SQL ERRM을 적극 사용하자

 

예외처리 루틴을 모듈화 하고 발생된 예외 로그를 남기자

예외가 발생할때 SQLCODE나 SQLERRM 함수를 이용해 정확히 발생된 예외 정보를 상세히 알수 있다. 따라서 발생한 예외에 대한 로그 테이블을 만들어 예외가 생길 때마다 이 로그 테이블에 기록해 둔다면 관리하기 매우 편리할것이다.

 

CREATE TABLE ERROR_LOG(

    ERROR_SEQ NUMBER,
    PROG_NAME VARCHAR2(80),
    ERROR_CODE NUMBER,
    ERROR_MASSAGE VARCHAR2(300),
    ERROR_LINE VARCHAR2(100),
    ERROR_DATE DATE DEFAULT SYSDATE
    
    );
    
    COMMIT;
    
    CREATE SEQUENCE ERROR_SEQ
        INCREMENT BY 1
        START WITH 1
        MINVALUE 1
        MAXVALUE 99999
        NOCYCLE
        NOCACHE;
        
        COMMIT;
        

예외로그 테이블을 만들고 시퀀스를 생성해주자

 

그리고 이전에 신입사원 입력 하는 프로시저를 수정하여 예외가 발생되면 예외 테이블에 로그가 저장될수 있도록 했다.

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; -- 잘못된 부서 번호일 경우 예외처리
    PRAGMA EXCEPTION_INIT(EX_INVALID_DEPID,-20000);
    EX_INVALID_MONTH EXCEPTION;
    PRAGMA EXCEPTION_INIT(EX_INVALID_MONTH,-1843); -- 예외명과 예외코드 연결 
    
    V_ERORR_CODE ERROR_LOG.ERROR_CODE %TYPE;
    V_ERROR_MASSAGE ERROR_LOG.ERROR_MASSAGE%TYPE;
    V_ERROR_LINE ERROR_LOG.ERROR_LINE %TYPE;
   
    
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 --사용자 정의 예외처리 
            V_ERORR_CODE := SQLCODE;
            V_ERROR_MASSAGE := '헤당부서가없습니다';
            V_ERROR_LINE := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
            ROLLBACK;
            error_log_proc('INST_EMP',V_ERORR_CODE,V_ERROR_MASSAGE,V_ERROR_LINE);        
        WHEN EX_INVALID_MONTH THEN --입사월 사용자 정의 예외처리
            V_ERORR_CODE := SQLCODE;
            V_ERROR_MASSAGE := SQLERRM;
            V_ERROR_LINE := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
            ROLLBACK;
            error_log_proc('INST_EMP',V_ERORR_CODE,V_ERROR_MASSAGE,V_ERROR_LINE);        
        WHEN OTHERS THEN
            V_ERORR_CODE :=SQLCODE;
            V_ERROR_MASSAGE:= SQLERRM;
            V_ERROR_LINE:=DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
            ROLLBACK;
            error_log_proc('INST_EMP',V_ERORR_CODE,V_ERROR_MASSAGE,V_ERROR_LINE);        
END INST_EMP;

바뀐 부분은  IS 부분에 에러로그를 다을수 있는 변수와 EXCEPTION 부분에 예외처리 프로시저를 추가해주었다.

결과를 보자

SELECT * FROM ERROR_LOG;
    EXECUTE INST_EMP('Steven',1000,'201413');