② 예외 처리 루틴을 공통 모듈화하고, 발생된 예외 로그를 남기자
예외가 발생할 때 SQLCODE나 SQLERRM 함수를 이용해 정확히 발생된 예외 정보를 상세히 알 수 있다. 따라서 발생한 예외에 대한 로그 테이블을 만들어 예외가 생길 때마다 이 로그 테이블에 기록해 둔다면 관리하기 매우 편리할 것이다. 예컨대 간밤에 자동으로 수행된 프로시저에서 예외가 발생했다고 가정하면, 다음 날 출근해서 예외로그 테이블을 조회해 보면 발생된 예외 내역을 보고 문제가 된 부분을 조치할 수 있을 것이다. 에러로그 테이블은 다음과 같은 형태로 만든다.
입력
CREATE TABLE error_log (
error_seq NUMBER, -- 에러 시퀀스
prog_name VARCHAR2(80), -- 프로그램명
error_code NUMBER, -- 에러코드
error_message VARCHAR2(300), -- 에러 메시지
error_line VARCHAR2(100), -- 에러 라인
error_date DATE DEFAULT SYSDATE -- 에러발생일자
);
결과
table ERROR_LOG이(가) 생성되었습니다.
더 필요한 정보가 있으면 해당 컬럼을 추가하자. 위 테이블에 에러 정보를 입력할 때 error_seq 컬럼에 들어갈 시퀀스도 생성하자.
입력
CREATE SEQUENCE error_seq
INCREMENT BY 1
START WITH 1
MINVALUE 1
MAXVALUE 999999
NOCYCLE
NOCACHE;
결과
sequence ERROR_SEQ이(가) 생성되었습니다.
다음으로 예외가 발생했을 때 예외 로그 테이블에 에러 정보를 입력하는 프로시저를 만든다.
입력
CREATE OR REPLACE PROCEDURE error_log_proc (
p_prog_nameerror_log.prog_name%TYPE,
p_error_codeerror_log.error_code%TYPE,
p_error_messggeerror_log.error_message%TYPE,
p_error_lineerror_log.error_line%TYPE )
IS
BEGIN
INSERT INTO error_log (error_seq, prog_name, error_code, error_message, error_line)
VALUES ( error_seq.NEXTVAL, p_prog_name, p_error_code, p_error_messgge, p_error_line );
COMMIT;
END;
결과
PROCEDURE ERROR_LOG_PROC이(가) 컴파일되었습니다.
이제 익명 블록이나 다른 프로시저에서 예외가 발생했을 때, 예외처리 부분에서 위 프로시저를 호출하도록 한다. 예를 들어, ch10_ins_emp_proc와 동일한 형태의 ch10_ins_emp2_proc 프로시저에서 호출한다고 가정하면 다음과 같은 형태로 작성할 수 있을 것이다.
입력
CREATE OR REPLACE PROCEDURE ch10_ins_emp2_proc (
p_emp_nameemployees.emp_name%TYPE,
p_department_iddepartments.department_id%TYPE,
p_hire_monthVARCHAR2 )
IS
vn_employee_idemployees.employee_id%TYPE;
vd_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_err_codeerror_log.error_code%TYPE;
v_err_msgerror_log.error_message%TYPE;
v_err_lineerror_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_month, 5, 2) NOT BETWEEN '01' AND '12' THEN
RAISE ex_invalid_month; -- 사용자 정의 예외 발생
END IF;
-- employee_id의 max 값에 +1
SELECT MAX(employee_id) + 1
INTO vn_employee_id
FROM employees;
-- 사용자 예외처리 예제이므로 사원 테이블에 최소한 데이터만 입력함
INSERT INTO employees ( employee_id, emp_name, hire_date, department_id )
VALUES ( vn_employee_id, p_emp_name, TO_DATE(p_hire_month || '01'), p_department_id );
COMMIT;
EXCEPTION WHEN ex_invalid_depid THEN -- 사용자 정의 예외 처리
v_err_code := SQLCODE;
v_err_msg := '해당 부서가 없습니다';
v_err_line := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
ROLLBACK;
error_log_proc ( 'ch12_ins_emp2_proc', v_err_code, v_err_msg, v_err_line);
WHEN ex_invalid_month THEN -- 입사월 사용자 정의 예외 처리
v_err_code := SQLCODE;
v_err_msg := SQLERRM;
v_err_line := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
ROLLBACK;
error_log_proc ( 'ch12_ins_emp2_proc', v_err_code, v_err_msg, v_err_line);
WHEN OTHERS THEN
v_err_code := SQLCODE;
v_err_msg := SQLERRM;
v_err_line := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
ROLLBACK;
error_log_proc ( 'ch12_ins_emp2_proc', v_err_code, v_err_msg, v_err_line);
END;
결과
PROCEDURE CH10_INS_EMP2_PROC이(가) 컴파일되었습니다.
이제 예외를 발생시켜 보자.
입력
EXEC ch10_ins_emp2_proc ('HONG', 1000, '201401');
결과
익명 블록이 완료되었습니다.
입력
-- 잘못된 월
EXEC ch10_ins_emp2_proc ('HONG', 100, '201413');
결과
익명 블록이 완료되었습니다.
error_log 테이블을 조회해 보면 해당 오류 메시지가 입력되었음을 확인할 수 있다.
입력
SELECT *
FROM error_log ;
