18 PL/SQL EXCEPTION

ryunyryuny 2023. 10. 31. 16:51

[문제] 2006년도에 입사한 사원들의 근무 도시이름별 급여의 총액, 평균을 출력.

1)
DECLARE
    CURSOR city_cur IS
        SELECT city, sum(sumsal) sumsal, avg(avgsal) avgsal
        FROM(SELECT l.city, e.sumsal, e.avgsal
                FROM (SELECT department_id, sum(salary) sumsal, trunc(avg(salary)) avgsal
                        FROM hr.employees
                        WHERE hire_date >= to_date('2006-01-01','yyyy-mm-dd')
                        AND hire_date < to_date('2007-01-01','yyyy-mm-dd')
                        GROUP BY department_id) e, hr.departments d, hr.locations l
                WHERE e.department_id = d.department_id
                AND d.location_id = l.location_id)
        GROUP BY city;
        -- 2006년도 사원 정리 후 JOIN
    v_rec city_cur%rowtype;
BEGIN
    OPEN city_cur;
    LOOP
        FETCH city_cur INTO v_rec;
        EXIT WHEN city_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_rec.city||' 도시에 근무하는 사원들의 총액급여는 '||
                                ltrim(to_char(v_rec.sumsal,'L999,999'))||' 이고, 평균급여는 ' ||
ltrim(to_char(v_rec.avgsal,'L999,999'))||' 입니다.');
    END LOOP;
    CLOSE city_cur;
END;
/

2)
DECLARE
    CURSOR city_cur IS
        SELECT city, sum(sumsal) sumsal, avg(avgsal) avgsal
        FROM(SELECT l.city, e.sumsal, e.avgsal
                FROM (SELECT department_id, sum(salary) sumsal, trunc(avg(salary)) avgsal
                        FROM hr.employees
                        WHERE hire_date >= to_date('2006-01-01','yyyy-mm-dd')
                        AND hire_date < to_date('2007-01-01','yyyy-mm-dd')
                        GROUP BY department_id) e, hr.departments d, hr.locations l
                WHERE e.department_id = d.department_id
                AND d.location_id = l.location_id)
        GROUP BY city;
        -- 2006년도 사원 정리 후 JOIN
    v_rec city_cur%rowtype;
BEGIN
    FOR v_rec IN city_cur LOOP
        DBMS_OUTPUT.PUT_LINE(v_rec.city||' 도시에 근무하는 사원들의 총액급여는 '||
                                ltrim(to_char(v_rec.sumsal,'L999,999'))||' 이고, 평균급여는 '||
                                ltrim(to_char(v_rec.avgsal,'L999,999'))||' 입니다.');
    END LOOP;
END;
/

3)
BEGIN
    FOR v_rec IN (SELECT city, sum(sumsal) sumsal, avg(avgsal) avgsal
                    FROM(SELECT l.city, e.sumsal, e.avgsal
                            FROM (SELECT department_id, sum(salary) sumsal, trunc(avg(salary)) avgsal
                                    FROM hr.employees
                                    WHERE hire_date >= to_date('2006-01-01','yyyy-mm-dd')
                                    AND hire_date < to_date('2007-01-01','yyyy-mm-dd')
                                    GROUP BY department_id) e, hr.departments d, hr.locations l
                            WHERE e.department_id = d.department_id
                            AND d.location_id = l.location_id)
                    GROUP BY city) LOOP
    DBMS_OUTPUT.PUT_LINE(v_rec.city||' 도시에 근무하는 사원들의 총액급여는 '||
                            ltrim(to_char(v_rec.sumsal,'L999,999'))||' 이고, 평균급여는 '||
                            ltrim(to_char(v_rec.avgsal,'L999,999'))||' 입니다.');
    END LOOP;
END;
/

DECLARE
    CURSOR parm_cur_80 IS
        SELECT employee_id, last_name, job_id
        FROM hr.employees
        WHERE department_id = 80
        AND job_id = 'SA_MAN';

    CURSOR parm_cur_50 IS
        SELECT employee_id, last_name, job_id
        FROM hr.employees
        WHERE department_id = 50
        AND job_id = 'ST_MAN';

    v_rec1 parm_cur_80%rowtype;
BEGIN
    OPEN parm_cur_80;
    LOOP
        FETCH parm_cur_80 INTO v_rec1;
            EXIT WHEN parm_cur_80%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_rec1.last_name);
    END LOOP;
    CLOSE parm_cur_80;

DBMS_OUTPUT.NEW_LINE;

    FOR v_rec2 IN parm_cur_50 LOOP
        DBMS_OUTPUT.PUT_LINE(v_rec2.last_name);
    END LOOP;
END;
/

>> 데이터의 분포가 다를경우 느려짐 방지차 실행계획을 따로 해야한다.

# parameter를 포함한 cursor : 이유는 실행계획을 공유하기 위해서
# parameter 변수 선언시에 size는 표현하지 않습니다.

DECLARE
    CURSOR parm_cur(p_id number, p_job varchar2) IS
        SELECT employee_id, last_name, job_id
        FROM hr.employees
        WHERE department_id = p_id
        AND job_id = p_job;

    v_rec1 parm_cur%rowtype;
BEGIN
    OPEN parm_cur(80,'SA_MAN');
    LOOP
        FETCH parm_cur INTO v_rec1;
            EXIT WHEN parm_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_rec1.last_name);
    END LOOP;
    CLOSE parm_cur;

DBMS_OUTPUT.NEW_LINE;

    FOR v_rec2 IN parm_cur(50,'ST_MAN') LOOP
        DBMS_OUTPUT.PUT_LINE(v_rec2.last_name);
    END LOOP;
END;
/

■ EXCEPTION (예외처리)
- 실행중에 발생한 PL/SQL 오류
- 오라클에 의해 암시적으로 발생
- 프로그램에 의해 명시적으로 발생

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;
/
-- ORA-01403: no data found

↓

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||' 사원은 존재하지 않습니다.');
END;
/
-- PL/SQL 프로시저가 성공적으로 완료되었습니다.

DECLARE
    v_id number := 20;
    v_rec hr.employees%rowtype;
BEGIN
    SELECT *
    INTO v_rec
    FROM hr.employees
    WHERE department_id = v_id;     --> 오류 : 1개행 단일 데이터가 아님

    DBMS_OUTPUT.PUT_LINE(v_rec.department_id||' '||v_rec.last_name);

EXCEPTION
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE(v_id||' 부서 사원들이 여러명입니다. 명시적인커서를 이용해주세요.');
END;
/
-- ORA-01422: exact fetch returns more than requested number of rows

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.department_id||' '||v_rec.last_name);

EXCEPTION
    WHEN OTHERS THEN        -- 걸리는게 없을 때 마지막에 사용
        DBMS_OUTPUT.PUT_LINE('오류가 발생했습니다.');
        DBMS_OUTPUT.PUT_LINE(SQLCODE);  -- 오류코드
        DBMS_OUTPUT.PUT_LINE(SQLERRM);  -- 오류메세지
END;
/

SELECT salary FROM hr.employees WHERE department_id = 20;
13000
6000

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;
/
-- ORA-01422: exact fetch returns more than requested number of rows

SELECT salary FROM hr.employees WHERE department_id = 20; -- 업데이트 안됨
13000
6000

>>> 프로그램이 비정상적인 종료가 수행되면 transaction은 자동 ROLLBACK 된다.

↓

SELECT salary FROM hr.employees WHERE department_id = 20;
13000
6000

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(' 사원은 존재하지 않습니다.');

    WHEN OTHERS THEN        -- 작동
        DBMS_OUTPUT.PUT_LINE(SQLCODE);  -- 오류코드
        DBMS_OUTPUT.PUT_LINE(SQLERRM);  -- 오류메세지
END;
/

SELECT salary FROM hr.employees WHERE department_id = 20; -- 업데이트 됨
14300
6600

COMMIT;

>>> 프로그램안에서 오류가 발생했지만 그 오류에 따른 예외사항 처리를 수행하면 Transaction 은 살아 있다.
진행중인 상태로 간다. -> 다른 사람은 작업 대기 발생

★ 꼭 Transaction 을 종료(ROLLBACK,COMMIT)를 프로그램 안에서 할지 프로그램 바깥쪽에서 할지를 결정해야 한다.

# 사용자가 정의한 예외사항

DELETE FROM hr.departments WHERE department_id = 20;
-- ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated - child record found
-- 데이터가 연결되어있어 삭제 불가하다.

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

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

BEGIN
    DELETE FROM hr.departments WHERE department_id = 20;
EXCEPTION
    WHEN others THEN
        DBMS_OUTPUT.PUT_LINE(SQLCODE);
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/
>> ORA-02292에 해당하는 EXCEPTION 키워드가 없다.

↓

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;
/

BEGIN
    UPDATE hr.employees
    SET salary = salary * 1.1
    WHERE employee_id = 300;    -- 해당하는 값이 없다.

    IF sql%NOTFOUND THEN        -- FETCH 한 행이 없으면 TRUE
        DBMS_OUTPUT.PUT_LINE('영향을 받은 행이 없습니다.');
    END IF;
END;
/

↓

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;
/

# 유저가 정의한 오류가 발생할 수 있는 프로시저.
프로그램은 비정상적인 종료, Transaction 작업은 rollback
RAISE_APPLICATION_ERROR(오류번호,오류메세지,FALSE);
- 오류번호 : -20000 ~ -20999
- TRUE : 오라클의 오류와 내가 만든 오류가 같이 출력
- FALSE : 내가 만든 오류만 출력

특정사원 조회불가

DECLARE
    v_id number := 100;
    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;
/

↓

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
        RAISE_APPLICATION_ERROR(-20001,'조회된 데이터가 없습니다.');
END;
/
오류 보고 -
ORA-20000: 조회할 수 없다.

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;
/
오류 보고 -
ORA-20001: 조회된 데이터가 없습니다.    -- 내가 만든 오류
ORA-01403: no data found            -- 오라클 오류

[문제] 전체 사원 들의 사번, 이름, 급여, 입사일, 근무연수를 출력합니다.
또한 근무연수가 15년 이상이고 급여는 10000 미만인 사원들은 예외사항이 발생하도록 한 후
메시지 출력하고 프로그램 수행이 완료된 후에 분석할수있도록 years 테이블에 정보가 입력이 되도록 프로그램을 작성합니다.
근무연수는 소수점은 버리세요
<화면 출력>
....
201, Hartstein, 13000, 04/02/17, 12
202, Fay, 6000, 05/08/17, 10
203, Mavris, 6500, 02/06/07, 13
사원 203 근무연수는 13 년이고 급여는 6500 입니다.
204, Baer, 10000, 02/06/07, 13
205, Higgins, 12008, 02/06/07, 13
206, Gietz, 8300, 02/06/07, 13
사원 206 근무연수는 13 년이고 급여는 8300 입니다.
....

/* 테이블 생성 */
CREATE TABLE years(id number, name varchar2(30), sal number, year number);

DECLARE
    e_raise EXCEPTION;

BEGIN
    FOR emp_rec IN (SELECT employee_id, last_name, salary, hire_date, trunc(months_between(sysdate,hire_date)/12) year
                    FROM hr.employees) LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.employee_id||', '||emp_rec.last_name||', '||emp_rec.salary||', '||
                                emp_rec.hire_date||', '||emp_rec.year);

        /* ★ 서브 블럭에서 RAISE 사용 ★ */
        BEGIN
            IF emp_rec.year >= 15 AND emp_rec.salary < 10000 THEN
                RAISE e_raise;
            END IF;

        EXCEPTION
            WHEN e_raise THEN
                DBMS_OUTPUT.PUT_LINE('사원 '||emp_rec.employee_id||' 근무연수는 '||emp_rec.year||
                                        '년이고 급여는 '||emp_rec.salary||' 입니다.');
                INSERT INTO hr.years(id,name,sal,year) VALUES(emp_rec.employee_id,emp_rec.last_name,
                                                                emp_rec.salary,emp_rec.year);
                COMMIT;
        END;
/* 서브블럭이 끝나면 여기로 와서 다시 FOR문 반복 */
    END LOOP;
END;
/

SELECT *
FROM hr.years;

★ 반복사이에 EXCEPTION이 발생하고 계속 반복작업할 경우 서브블럭 활용 ★

DECLARE
    e_riase EXCEPTION;  -- 선언
    PRAGMA EXCEPTION_INIT(e_riase, -2292);     -- 오라클의 오류번호 예외사항 연결

BEGIN
    IF 조건 THEN
        RAISE e_riase;  -- exception으로 점프

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('데이터 없다');

    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('단일행 데이터가 아니다');

    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('오류발생');
        DBMS_OUTPUT.PUT_LINE(SQLCODE);  -- 오류코드
        DBMS_OUTPUT.PUT_LINE(SQLERRM);  -- 오류메세지

    WEHN e_riase THEN
        DBMS_OUTPUT.PUT_LINE('메세지남겨요');

        RAISE_APPLICATION_ERROR(-20000,'내가 만든 오류메세지');

END;

'Oracle SQL' 카테고리의 다른 글

20 PL/SQL PROCEDURE, FUNCTION (0)	2023.11.02
19 PL/SQL PROCEDURE (0)	2023.11.01
17 PL/SQL Nested Table, VARRAY, Explicit Cursor (0)	2023.10.30
16 PL/SQL RECORD, Array (0)	2023.10.27
15 PL/SQL WHILE LOOP, FOR, Implicit Cursor (1)	2023.10.26

'Oracle SQL'의 다른글

현재글18 PL/SQL EXCEPTION

Record.

18 PL/SQL EXCEPTION

'Oracle SQL' 카테고리의 다른 글

티스토리툴바