Data Base/PL SQL

231031 PL/SQL EXCEPTION(예외처리)

잇꼬 2023. 10. 31. 17:11
728x90
반응형
SMALL

■ exception (예외처리)

1. 실행 중에 발생한 PL/SQL 오류

2. Oracle에 의해서 암시적으로 발생.

3. 프로그램에 의해서 명시적으로 발생. 

 

DECLARE 
    v_id NUMBER := 300;
    v_rec hr.employees%rowtype;
BEGIN
    SELECT *
    INTO v_rec
    FROM hr.employees
    WHERE employee_id = v_id;
    
    dbms_output.put_line(v_rec.employee_id||' '||v_rec.last_name);
END;
/

 

# 예외 처리 

DECLARE 
    v_id NUMBER := 300;
    v_rec hr.employees%rowtype;
BEGIN
    SELECT *
    INTO v_rec
    FROM hr.employees
    WHERE employee_id = v_id; --오류발생 'NO_DATA_FOUND'
    
    dbms_output.put_line(v_rec.employee_id||' '||v_rec.last_name);

EXCEPTION --예외처리
    WHEN NO_DATA_FOUND/*오류명*/ THEN 
        dbms_output.put_line(v_id||' 사원은 존재하지 않습니다.');
END;
/

# 예외처리할 경우, 오류명으로만 작성할 것.

DECLARE 
    v_id NUMBER := 20;
    v_rec hr.employees%rowtype;
BEGIN
    SELECT *
    INTO v_rec
    FROM hr.employees
    WHERE department_id = v_id;
    
    dbms_output.put_line(v_rec.employee_id||' '||v_rec.last_name);

EXCEPTION --예외처리
    WHEN NO_DATA_FOUND/* 오류명으로만 가능, 오류번호X 오류메시지X*/ THEN 
        dbms_output.put_line(v_id||' 사원은 존재하지 않습니다.');
    WHEN TOO_MANY_ROWS THEN
        dbms_output.put_line(v_id||' 부서 사원들은 여러명입니다. 명시적인 커서를 이용해주세요.');
END;
/

 

# 첫 번째 예외처리가 거쳐가지 않으면, 'others'로 넘어간다. 

(작성하는 것은 필수X)

DECLARE 
    v_id NUMBER := 20;
    v_rec hr.employees%rowtype;
BEGIN
    SELECT *
    INTO v_rec
    FROM hr.employees
    WHERE department_id = v_id;
    
    dbms_output.put_line(v_rec.employee_id||' '||v_rec.last_name);

EXCEPTION --예외처리
    WHEN NO_DATA_FOUND THEN 
        dbms_output.put_line(v_id||' 사원은 존재하지 않습니다.');
    WHEN OTHERS THEN -- 필수X 
        dbms_output.put_line('오류가 발생했습니다.');
END;
/

 

# 오류코드, 오류메시지 출력

DECLARE 
    v_id NUMBER := 20;
    v_rec hr.employees%rowtype;
BEGIN
    SELECT *
    INTO v_rec
    FROM hr.employees
    WHERE department_id = v_id;
    
    dbms_output.put_line(v_rec.employee_id||' '||v_rec.last_name);

EXCEPTION --예외처리
    WHEN NO_DATA_FOUND THEN 
        dbms_output.put_line(v_id||' 사원은 존재하지 않습니다.');
    WHEN OTHERS THEN 
        dbms_output.put_line(SQLCODE); -- 오류코드
        dbms_output.put_line(SQLERRM); -- 오류메시지
END;
/

 

# 연습

DECLARE 
    v_id NUMBER := 300;
    v_rec hr.employees%rowtype;
BEGIN
    SELECT *
    INTO v_rec
    FROM hr.employees
    WHERE employee_id = v_id;
    
    dbms_output.put_line(v_rec.employee_id||' '||v_rec.last_name);

EXCEPTION --예외처리
    WHEN NO_DATA_FOUND THEN 
        dbms_output.put_line(v_id||' 사원은 존재하지 않습니다.');
    WHEN OTHERS THEN 
        dbms_output.put_line(SQLCODE); -- 오류코드
        dbms_output.put_line(SQLERRM); -- 오류메시지
END;
/

 

# 동일한 오류명으로 여러 코드를 작성하면 오류나니, 단 한번만 작성 할 것.

DECLARE 
    v_id NUMBER := 300;
    v_rec hr.employees%rowtype;
BEGIN
    SELECT *
    INTO v_rec
    FROM hr.employees
    WHERE employee_id = v_id;
    
    dbms_output.put_line(v_rec.employee_id||' '||v_rec.last_name);

EXCEPTION --예외처리
    WHEN NO_DATA_FOUND THEN 
        dbms_output.put_line(v_id||' 사원은 존재하지 않습니다.');
    WHEN NO_DATA_FOUND THEN 
        dbms_output.put_line(v_id||' 사원은 존재하지 않습니다.');
    WHEN OTHERS THEN 
        dbms_output.put_line(SQLCODE); -- 오류코드
        dbms_output.put_line(SQLERRM); -- 오류메시지
END;
/

#  프로그램이 비상적인 종료가 수행되면 transaction 은 자동 ROLLBACK 된다. (기본)

DECLARE 
    v_rec hr.employees%rowtype;
BEGIN
    UPDATE hr.employees
    SET salary = salary*1.1
    WHERE department_id = 20; -- Transaction 발생+진행중인 상태
    
    SELECT *
    INTO v_rec
    FROM hr.employees
    WHERE department_id = 20; -- 오류발생
    dbms_output.put_line(v_rec.employee_id||' '||v_rec.last_name);

END;
/

# 예외처리

1. 프로그램 내에서 오류발생 했으나, 그 오류에 따른 예외사항 처리를 수행하면 transaction 은 살아 있고 진행중인 상태. 

2. 꼭 프로그램 내에서 transaction 을 종료(COMMIT, ROLLBACK) 할지, 프로그램외 에서 할지 결정해야 함.

 

SELECT salary FROM hr.employees WHERE department_id = 20;
DECLARE 
    v_rec hr.employees%rowtype;
BEGIN
    UPDATE hr.employees
    SET salary = salary*1.1
    WHERE department_id = 20; -- Transaction 발생+진행중인 상태
    
    SELECT *
    INTO v_rec
    FROM hr.employees
    WHERE department_id = 20; -- 오류발생
    dbms_output.put_line(v_rec.employee_id||' '||v_rec.last_name);

EXCEPTION --예외처리
    WHEN NO_DATA_FOUND THEN 
        dbms_output.put_line(v_rec.employee_id||' 사원은 존재하지 않습니다.');
    WHEN OTHERS THEN 
        dbms_output.put_line(SQLCODE); -- 오류코드
        dbms_output.put_line(SQLERRM); -- 오류메시지

END;
/

 

# 확

SELECT  * 
FROM user_constraints
WHERE table_name IN ('EMPLOYEES', 'DEPARTMENTS');

SELECT  * 
FROM user_cons_columns
WHERE table_name IN ('EMPLOYEES', 'DEPARTMENTS');

 

오류: integrity constraint (HR.EMP_DEPT_FK) violated - child record found

DELETE FROM hr.departments WHERE department_id = 20;

# 예외처리 

- ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated - child record found

- 오류명이 없이 예외상항처리가 어렵다. 이럴 경우에는 오류명을 USER가 지정해 주면 된다.

BEGIN
    DELETE FROM hr.departments WHERE department_id = 20;
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line(SQLCODE); -- 오류코드
        dbms_output.put_line(SQLERRM); -- 오류메시지
END;
/

 

# 오라클의 오류번호와 예외사항 이름을 붙이는 작업(오류명이 없는 경우) 

- 예외처리: 변수처리 및 지정

- DECLARE 에서 예외변수 선언시, PRAGMA EXCEPTION_INIT(오류명 변수, 오류번호);

DECLARE
    pk_error EXCEPTION; -- 예외사항 이름 선언
    PRAGMA EXCEPTION_INIT(pk_error, -2292); -- 예외사항 변수 지정, 오류번호(음수) 지정
BEGIN
    DELETE FROM hr.departments WHERE department_id = 20;
EXCEPTION
    WHEN pk_error THEN
        dbms_output.put_line('이 값을 참조하고 있는 행들이 있습니다.');
    WHEN OTHERS THEN
        dbms_output.put_line(SQLCODE); -- 오류코드
        dbms_output.put_line(SQLERRM); -- 오류메시지
END;
/

# 예외처리를 EXCEPTION 에서 처리하지 않는 방법

BEGIN
    UPDATE hr.employees
    SET salary = salary*1.1
    WHERE employee_id = 300;
    
    IF sql%notfound THEN
        dbms_output.put_line('영향을 받은 행이 없습니다.');
    END IF;

END;
/

# 사용자가 정의한 예외사항( ex) 업무, 감사 )

DECLARE
    e_invalid EXCEPTION; -- 오류명 선언
BEGIN
    UPDATE hr.employees
    SET salary = salary*1.1
    WHERE employee_id = 300;
    
    IF sql%notfound THEN
        RAISE e_invalid; -- RAISE 오류명 : 무조건 EXCEPTION절 로 실행.
    END IF;

EXCEPTION
    WHEN e_invalid THEN
        dbms_output.put_line('영향을 받은 행이 없습니다.');    
END;
/

# 특정 data 를 조회 안되게 하는 방법

- IF문 + RAISE 을 활용하자!

    v_name VARCHAR2(30);
    v_dept_name VARCHAR2(30);
    
    emp_raise EXCEPTION;

BEGIN
    
    IF v_id  = 100 THEN
        RAISE emp_raise;
    END IF;
    
    SELECT e.last_name, d.department_name
    INTO v_name, v_dept_name
    FROM hr.employees e, hr.departments d 
    WHERE e.department_id = d.department_id
    AND e.employee_id = v_id;
    
    dbms_output.put_line(v_name||' '||v_dept_name);
    
EXCEPTION
    WHEN emp_raise THEN
        dbms_output.put_line(v_id||' 사원은 조회할 수 없습니다');
    WHEN NO_DATA_FOUND THEN 
        dbms_output.put_line(v_id||' 사원은 존재하지 않습니다.');
END;
/

 

# user 가 정의한 오류가 발생할 수 있는 프로시저, 프로그램은 비정상적인 종료

# RAISE_APPLICATION_ERROR(오류번호, 오류메시지, FALSE);

1. 오류번호 : -20000 ~ -20999

2. TRUE : 오라클의 오류와 user가 만든 오류가 같이 출력

3. FALSE : 기본값, user가 만든 오류만 출력, 생략 가능

DECLARE
    v_id NUMBER := 100; 
    v_name VARCHAR2(30);
    v_dept_name VARCHAR2(30);
    
BEGIN
    IF v_id  = 100 THEN
        RAISE_APPLICATION_ERROR(-20000, '조회할 수 없습니다.');
    END IF;
    
    SELECT e.last_name, d.department_name
    INTO v_name, v_dept_name
    FROM hr.employees e, hr.departments d 
    WHERE e.department_id = d.department_id
    AND e.employee_id = v_id;
    
    dbms_output.put_line(v_name||' '||v_dept_name);
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN 
        dbms_output.put_line(v_id||' 사원은 존재하지 않습니다.');
END;
/

 

# 오류번호, 오류메시지 변경(비정상적인 종료)

DECLARE
    v_id NUMBER := 300; 
    v_name VARCHAR2(30);
    v_dept_name VARCHAR2(30);
    
BEGIN
    IF v_id  = 100 THEN
        RAISE_APPLICATION_ERROR(-20000, '조회할 수 없다.');
    END IF;
    
    SELECT e.last_name, d.department_name
    INTO v_name, v_dept_name
    FROM hr.employees e, hr.departments d 
    WHERE e.department_id = d.department_id
    AND e.employee_id = v_id;
    
    dbms_output.put_line(v_name||' '||v_dept_name);
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN 
        RAISE_APPLICATION_ERROR(-20001, '조회된 데이터가 없습니다.');
END;
/

 

# RAISE_APPLICATION_ERRE(오류번호, '오류메시지', TRUE); = 기존 오류번호, 오류메시지 도 같이 출력

DECLARE
    v_id NUMBER := 300; 
    v_name VARCHAR2(30);
    v_dept_name VARCHAR2(30);
    
BEGIN
    IF v_id  = 100 THEN
        RAISE_APPLICATION_ERROR(-20000, '조회할 수 없다.');
    END IF;
    
    SELECT e.last_name, d.department_name
    INTO v_name, v_dept_name
    FROM hr.employees e, hr.departments d 
    WHERE e.department_id = d.department_id
    AND e.employee_id = v_id;
    
    dbms_output.put_line(v_name||' '||v_dept_name);
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN 
        RAISE_APPLICATION_ERROR(-20001, '조회된 데이터가 없습니다.', TRUE);
END;
/

 

 

728x90
반응형
LIST
저작자표시 비영리 동일조건

'Data Base > PL SQL' 카테고리의 다른 글

231102 PL/SQL 서브프로그램(Sub Program), 프로시저, ERROR  (0) 2023.11.02
231101 PL/SQL 프로시저 컴파일, FORMAL PARLACE 형식매개변수, IN MODE, OUT MODE, IN-OUT MODE  (0) 2023.11.01
231031 PL/SQL CURSOR  (0) 2023.10.31
231030 PL/SQL CURSOR, 명시적 CURSOR  (0) 2023.10.30
231030 PL/SQL 중첩테이블(NESTED TABLE) , VARRAY(VARIABL-SIZE ARRAY) 가변길이의 배열방식, 2차원 배열  (1) 2023.10.30

'Data Base/PL SQL'의 다른글

  • 현재글231031 PL/SQL EXCEPTION(예외처리)

관련글

티스토리툴바