26.plsql예외처리
/*
< 사용자 정의 예외 >
- 사용자가 예외를 직접 정의 하고 사용
- 필수값체크, 유효성검사등에 사용
- 예외이름을 부여하므로 코드를 읽기 편하게 해주고 로직파악도 수월 해짐
사용방법
1. EXCEPTION 타입의 변수를 선언 합니다.
2. 메세지 처리대신 RAISE EXCEPTION타입 변수명
3. EXCEPTION절 에서 처리
*/
create or replace PROCEDURE proc_ins_emp (
-- 매개변수와 매개변수타입
p_emp_id emp.emp_id%TYPE,
p_emp_name EMP.EMP_NAME%TYPE,
p_emp_no EMP.EMP_NO%TYPE,
p_job_code EMP.JOB_CODE%TYPE
)
IS
v_res VARCHAR2(100);
-- 1.사용자 정의 예외 선언
-- 변수명 EXCEPTION;
ex_invalid_job_code EXCEPTION;
-- PRAGMA EXCEPTION_INIT(ex_test, -20100);
v_cnt NUMBER:=0;
BEGIN
-- 유효성 체크
-- 파라메터로 넘어온 JOB_CODE가 JOB 테이블에 등록된 코드인지 확인
-- 조회된 데이터가 없는 경우 NO_DATA_FOUND 예외 발생
-- 오라클 예외코드를 조회해보면 어떤 오류가 있는지 알 수 있음
SELECT job_name INTO v_cnt
FROM JOB
WHERE job_code = p_job_code;
IF v_cnt = 0 THEN
-- 2.유효하지 않은 job_code 예외 발생
-- RAISE 예외명
-- RAISE ex_invalid_job_code;
RAISE_APPLICATION_ERROR(-20001, 'JOB_CODE를 확인해주세요');
END IF;
INSERT INTO emp(emp_id, emp_name, emp_no, job_code) VALUES(p_emp_id, p_emp_name, p_emp_no, p_job_code);
v_res := SQL%ROWCOUNT || '건 삽입되었습니다.';
INSERT INTO proc_res VALUES('proc_ins_emp', v_res, sysdate);
EXCEPTION
-- 예외이름
WHEN ex_invalid_job_code THEN
DBMS_OUTPUT.PUT_LINE('job_code를 확인해주세요');
-- 시스템예외 : 오라클에 이미 정의되어 있는 예외
-- 예외를 모를때 OTHERS
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(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
/
EXEC proc_ins_emp('502','공유','111111-1111111','J0');
/*
실습.
1. 사원테이블에 신규 사원을 추가하는 프로시저를 생성해봅시다
파라메터 : 사번, 이름, 주민번호, 직급코드, 부서번호
2. 부서번호가 없는경우 사용자 정의 예외를 발생 시켜 봅시다
예외이름) ex_invalid_job_code, ex_invalid_dept_code
*/
CREATE OR REPLACE PROCEDURE proc_ins_emp (
p_emp_id emp.emp_id%TYPE,
p_emp_name emp.emp_name%TYPE,
p_emp_no emp.emp_no%TYPE,
p_job_code emp.job_code%TYPE,
p_dept_code emp.dept_code%TYPE
)
IS
ex_invalid_job_code EXCEPTION;
PRAGMA exception_init(ex_invalid_job_code, -20000);
ex_invalid_dept_code EXCEPTION;
PRAGMA exception_init(ex_invalid_dept_code, -20001);
v_cnt NUMBER := 0;
BEGIN
-- 직급코드가 유효하지 않은 경우
SELECT count(*) INTO v_cnt
FROM job
WHERE job_code = p_job_code;
IF v_cnt = 0 THEN RAISE ex_invalid_job_code;
END IF;
-- 부서코드가 유효하지 않은 경우
SELECT count(*) INTO v_cnt
FROM dept
WHERE dept_id = p_dept_code;
IF v_cnt = 0 THEN RAISE ex_invalid_dept_code;
END IF;
INSERT INTO emp(emp_id, emp_name, emp_no, job_code, dept_code) VALUES(p_emp_id, p_emp_name, p_emp_no, p_job_code, p_dept_code);
EXCEPTION
WHEN ex_invalid_job_code
THEN PROC_ERROR_LOG('PROC_INS_EMP', SQLCODE, SQLERRM, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
WHEN ex_invalid_dept_code
THEN PROC_ERROR_LOG('PROC_INS_EMP', SQLCODE, SQLERRM, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
-- 예외를 모를때
WHEN OTHERS
THEN PROC_ERROR_LOG('PROC_INS_EMP', SQLCODE, SQLERRM, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
/
EXEC proc_ins_emp('503','공유','111111-1111111','J1','D0');
/*
시스템 오류처럼 코드와 메세지를 입력하는 방법
1. 파라메터 추가
입사일(YYYYMM)
2. MM > 12 예외발생
*/
CREATE OR REPLACE PROCEDURE proc_ins_emp (
p_emp_id emp.emp_id%TYPE,
p_emp_name emp.emp_name%TYPE,
p_emp_no emp.emp_no%TYPE,
p_job_code emp.job_code%TYPE,
p_dept_code emp.dept_code%TYPE,
-- YYYYMM
p_hire_date VARCHAR2
)
IS
ex_invalid_job_code EXCEPTION;
ex_invalid_dept_code EXCEPTION;
v_cnt NUMBER := 0;
BEGIN
-- 직급코드가 유효하지 않은 경우
SELECT count(*) INTO v_cnt
FROM job
WHERE job_code = p_job_code;
IF v_cnt = 0 THEN RAISE ex_invalid_job_code;
END IF;
-- 부서코드가 유효하지 않은 경우
SELECT count(*) INTO v_cnt
FROM dept
WHERE dept_id = p_dept_code;
IF v_cnt = 0 THEN RAISE ex_invalid_dept_code;
END IF;
-- 입사월의 유효성을 체크
-- 프로시저를 사용한 오류 처리
-- 파라메터로 오류코드와 메세지를 입력
-- 코드는 20000~20999까지 사용 가능
IF SUBSTR(p_hire_date,5,2) NOT BETWEEN '01' AND '12' THEN RAISE_APPLICATION_ERROR(-20000, '입사일자를 확인해주세요');
END IF;
INSERT INTO emp(emp_id, emp_name, emp_no, job_code, dept_code, hire_date) VALUES(p_emp_id, p_emp_name, p_emp_no, p_job_code, p_dept_code, TO_DATE(p_hire_date));
EXCEPTION
WHEN ex_invalid_job_code THEN DBMS_OUTPUT.PUT_LINE('job_code를 확인해주세요');
WHEN ex_invalid_dept_code THEN DBMS_OUTPUT.PUT_LINE('job_dept를 확인해주세요');
-- 예외를 모를때
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(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
/
EXEC proc_ins_emp('504','공유','111111-1111111','J1','D1','20231401');
SET SERVEROUTPUT ON;
/*
오류를 발생시키는 프로시저
*/
CREATE OR REPLACE PROCEDURE PROC_EXCEPTION
IS
-- 1.예외이름을 정의
ex_test EXCEPTION;
-- 2.예외이름에 예외코드 붙여주기
-- PRAGMA EXCEPTION_INIT(예외이름, 코드번호);
PRAGMA exception_init(ex_test, -20100);
-- 이미 정의되어있는 시스템 오류 코드번호
-- PRAGMA exception_init(ex_test, -1843);
BEGIN
-- 예외 발생
-- RAISE ex_test;
-- 오류코드에 메세지를 추가
RAISE_APPLICATION_ERROR(-20100, '오류발생 메세지 작성');
EXCEPTION
-- exception이 발생했을때 처리
WHEN ex_test
THEN -- 오류코드출력
--DBMS_OUTPUT.PUT_LINE('오류발생');
DBMS_OUTPUT.PUT_LINE(SQLCODE);
-- 오류메세지 출력
DBMS_OUTPUT.PUT_LINE(SQLERRM);
-- 오류가 발생한 라인 출력
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
-- 오류 로그를 테이블에 저장하는 프로시저를 호출
PROC_ERROR_LOG('PROC_EXCEPTION', SQLCODE, SQLERRM, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
/
-- 프로시저 실행(매개변수가 없는 경우 이름만 작성)
EXEC PROC_EXCEPTION;
/*
<현장 노하우>
예외처리의 공통모듈을 생성해봅시다
예외테이블을 정의하고 예외가 발생시 테이블에 로그를 남깁니다.
*/
/*
1. 테이블 정의
테이블명 : 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
2. 시퀀스 생성
시퀀스명 : SEQ_ERROR_LOG
3. 프로시저 생성
*/
-- 1.테이블생성
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 -- 에러발생일자
);
-- 2.시퀀스생성
CREATE SEQUENCE SEQ_ERROR_LOG;
-- 3.프로시저 생성
-- 프로그램명, 오류코드, 오류메시지, 오류라인을 파라메터로 받아서 예외테이블에 입력하는 프로시저를 작성
CREATE OR REPLACE PROCEDURE PROC_ERROR_LOG(
p_prog_name ERROR_LOG.PROG_NAME%TYPE,
p_error_code ERROR_LOG.ERROR_CODE%TYPE,
p_error_message ERROR_LOG.ERROR_MESSAGE%TYPE,
p_error_line ERROR_LOG.ERROR_LINE%TYPE
)
IS
v_err_msg ERROR_USER_DEFINE.ERROR_MESSAGE%TYPE;
BEGIN
-- 사용자 정의 예외코드를 조회하여 메세지를 변수에 담아줍니다.
-- 예외코드가 등록되지 않은 경우 예외가 발생되므로 블럭으로 감싸줍니다.
BEGIN
SELECT ERROR_MESSAGE INTO v_err_msg
FROM ERROR_USER_DEFINE
WHERE ERROR_CODE = p_error_code;
EXCEPTION
WHEN NO_DATA_FOUND THEN v_err_msg := p_error_message;
END;
INSERT INTO ERROR_LOG VALUES(seq_error_log.NEXTVAL, p_prog_name, p_error_code, v_err_msg, p_error_line, DEFAULT);
COMMIT;
DBMS_OUTPUT.PUT_LINE(p_prog_name);
DBMS_OUTPUT.PUT_LINE(p_error_code);
DBMS_OUTPUT.PUT_LINE(p_error_message);
DBMS_OUTPUT.PUT_LINE(p_error_line);
END;
/
EXEC PROC_ERROR_LOG('progname', 20000, 'errmsg', 'errline');
SELECT * FROM ERROR_LOG;
/*
오류코드와 메세지를 정의하여 사용
*/
CREATE TABLE ERROR_USER_DEFINE(
ERROR_CODE NUMBER, -- 에러코드
ERROR_MESSAGE VARCHAR2(300), -- 에러메시지
CREATE_DATE DATE DEFAULT SYSDATE, -- 등록일자
PRIMARY KEY (ERROR_CODE) -- 데이터 입력될때 이값이 유효한지 검사할수있음
);
INSERT INTO ERROR_USER_DEFINE VALUES(-20000, '직급코드를 확인해주세요', DEFAULT);
INSERT INTO ERROR_USER_DEFINE VALUES(-20001, '부서코드를 확인해주세요', DEFAULT);
INSERT INTO ERROR_USER_DEFINE VALUES(-20002, '월이 부적합합니다', DEFAULT);
SELECT ERROR_MESSAGE FROM ERROR_USER_DEFINE WHERE ERROR_CODE = -20000;