(5) PL/SQL 에러 로그 테이블
PL/SQL의 예외처리 사용법
시작하기 전에
사용 교재 : 오라클SQL과 PL/SQL을 다루는 기술 (https://thebook.io/006696/)
예제 코드 : https://github.com/gilbutITbook/006696
SERVEROUTPUT 설정
PL/SQL 결과물을 보기 위해 아래의 실행문을 실행
SET SERVEROUTPUT ON;
효율적인 예외 처리 방법
1. 시스템 예외인 경우 OTHERS를 사용
OTHERS는 모든 시스템 예외를 잡아내므로, OTHERS와 SQLCODE, SQLERRM을 사용하자
2. 예외 처리 루틴을 공통 모듈화하고, 발생된 예외 로그를 남기자
SQLCODE와 SQLERRM을 활용해 얻은 정보를 따로 저장해 놓으면 사후관리가 편하다
에러 로그 테이블
ERROR_LOG_TB 만들기
CREATE TABLE error_log_tb (
-- 에러 시퀀스
error_seq NUMBER,
-- 프로그램명
prog_name VARCHAR2(80),
-- 에러코드
error_code NUMBER,
-- 에러 메시지
error_message VARCHAR2(300),
-- 에러 라인
error_line VARCHAR2(100),
-- 에러 발생일자
error_date DATE DEFAULT SYSDATE
);
ERROR 시퀀스 만들기
CREATE SEQUENCE error_seq
INCREMENT BY 1
START WITH 1
MINVALUE 1
MAXVALUE 999999
NOCYCLE
NOCACHE;
에러 테이블 프로시저 생성
예외가 발생할 때, error_log_tb 테이블에 에러 정보를 입력하는 프로시저를 생성
CREATE OR REPLACE PROCEDURE error_log_proc (
p_prog_name error_log_tb.prog_name%TYPE,
p_error_code error_log_tb.error_code%TYPE,
p_error_messgge error_log_tb.error_message%TYPE,
p_error_line error_log_tb.error_line%TYPE)
IS
BEGIN
INSERT INTO error_log_tb (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;
에러 테이블 활용하는 프로시저
사원 테이블에 사원을 INSERT할때 예외처리를 하고 에러 로그 테이블에 에러를 저장하는 프로시저
-- ins_emp2_proc
CREATE OR REPLACE PROCEDURE ins_emp2_proc (
p_emp_name employees.emp_name%TYPE,
p_department_id departments.department_id%TYPE,
p_hire_month VARCHAR2 )
IS
vn_employee_id employees.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);
-- 예외 관련 변수 선언 : error_log_tb TYPE
v_err_code error_log_tb.error_code%TYPE;
v_err_msg error_log_tb.error_message%TYPE;
v_err_line error_log_tb.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('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('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('ins_emp2_proc',
v_err_code,
v_err_msg,
v_err_line);
END;
/
에러 내보고 테이블 확인하기
- 잘못된 부서번호 데이터 INSERT
-- 부서번호 잘못 입력
EXEC ins_emp2_proc('HONG', 1000, '201401');
SELECT * FROM error_log_tb;
- 잘못된 월 데이터 INSERT
-- 잘못된 월
EXEC ins_emp2_proc('홍길동', 100, '202213');
SELECT * FROM error_log_tb;
Leave a comment