21 PL/SQL PACKAGE

ryunyryuny 2023. 11. 3. 18:16

[문제] 사원번호를 입력값으로 받아서 사번, 이름, 부서이름을 출력하는 프로시저를 생성하세요.

CREATE OR REPLACE PROCEDURE id_proc (p_id IN number)
IS
    v_id number;
    v_name varchar2(30);
    v_dept_name varchar2(30);
BEGIN
    SELECT e.employee_id, e.last_name, d.department_name
    INTO v_id, v_name, v_dept_name
    FROM hr.employees e, hr.departments d
    WHERE e.department_id = d.department_id
    AND e.employee_id = p_id;

    DBMS_OUTPUT.PUT_LINE('사원번호 : '||v_id||' 사원이름 : '||upper(v_name)||' 부서이름 : '||v_dept_name);

EXCEPTION
    WHEN no_data_found THEN
        DBMS_OUTPUT.PUT_LINE(p_id||'사원은 존재하지 않습니다.');
    WHEN others THEN
        DBMS_OUTPUT.PUT_LINE(sqlerrm);
END;
/

/* 호출 */
EXECUTE id_proc(100)

EXECUTE id_proc(p_id => 100)

[문제] 부서코드를 입력하면 사원번호,이름,부서이름을 출력하는 프로시저를 생성해주세요.

CREATE OR REPLACE PROCEDURE dept_sawon(dept_id number)
IS
    CURSOR dept_cur IS
        SELECT employee_id, last_name, (SELECT department_name
                                        FROM hr.departments
                                        WHERE department_id = e.department_id) department_name
        FROM hr.employees e
        WHERE department_id = dept_id;

    v_rec dept_cur%rowtype;
    dept_null EXCEPTION;
BEGIN

    FOR v_rec IN dept_cur LOOP
        IF v_rec.employee_id IS NULL THEN
            RAISE dept_null;
        ELSE
            DBMS_OUTPUT.PUT_LINE(v_rec.employee_id||' '||v_rec.last_name||' '||v_rec.department_name);
        END IF;
    END LOOP;

EXCEPTION
    WHEN dept_null THEN
        DBMS_OUTPUT.PUT_LINE(v_rec.employee_id||' 부서가 없습니다.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END dept_sawon;
/
>>> EXCEPTION 발생을 안함.
FOR문은 결과가 없다면 END IF 로 간다. > 의미없이 돌아감

↓ 개선 : OPEN - FETCH - CLOSE 사용

CREATE OR REPLACE PROCEDURE dept_sawon(dept_id number)
IS
    CURSOR dept_cur IS
        SELECT employee_id, last_name, (SELECT department_name
                                        FROM hr.departments
                                        WHERE department_id = e.department_id) department_name
        FROM hr.employees e
        WHERE department_id = dept_id;

    v_rec dept_cur%rowtype;
    dept_null EXCEPTION;

BEGIN
    OPEN dept_cur;  -- 메모리할당 후 행을 데이터를 포인트 지정

    LOOP
        FETCH dept_cur INTO v_rec; -- 데이터를 조작하기 위해 변수에 대입

        IF v_rec.employee_id IS NULL THEN -- FETCH 후 데이터가 NULL >> EXCEPTION 이동
            RAISE dept_null;
        ELSIF dept_cur%NOTFOUND THEN -- FETCH 후 데이터가 끝나면 END IF <> NULL
            EXIT;
        ELSE
            DBMS_OUTPUT.PUT_LINE(v_rec.employee_id||' '||v_rec.last_name||' '||v_rec.department_name);
        END IF;
    END LOOP;

    CLOSE dept_cur;

EXCEPTION
    WHEN dept_null THEN
        DBMS_OUTPUT.PUT_LINE(dept_id||' 부서가 없습니다.');

    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END dept_sawon;
/

/* 호출 */
execute dept_sawon(20)
execute dept_sawon(200)

[문제] 자신의 부서 평균 급여보다 더 많이 받는 사원은 'YES' 아니면 'NO'를 리턴하는 함수를 작성해주세요.

CREATE OR REPLACE FUNCTION avg_dept(p_id IN number, p_sal IN number)
    RETURN varchar2
IS
    v_sal number;
    is_null EXCEPTION;

BEGIN
    SELECT avg(salary)
    INTO v_sal
    FROM hr.employees
    WHERE department_id = p_id;

    IF v_sal IS NULL THEN
        RAISE is_null;
    ELSIF p_sal > v_sal THEN
        RETURN 'YES';
    ELSE
        RETURN 'NO';
    END IF;

EXCEPTION
    WHEN is_null THEN
        DBMS_OUTPUT.PUT_LINE(p_id||' 부서 데이터가 없습니다.');
        RETURN NULL;

END avg_dept;
/

/* 호출 */
SELECT employee_id, salary, department_id, avg_dept(department_id,salary) avg_dept
FROM hr.employees;

BEGIN
DBMS_OUTPUT.PUT_LINE(avg_dept(300,10000));
END;
/

CREATE OR REPLACE FUNCTION query_call(p_id IN number)
    RETURN number
IS
    v_sal number;
    v_year number;
BEGIN
    SELECT salary, trunc(months_between(sysdate,hire_date)/12)
    INTO v_sal, v_year
    FROM hr.employees
    WHERE employee_id = p_id;

    IF v_year > 20 THEN
        RETURN v_sal * 1.2;
    ELSIF v_year < 20 AND v_year >= 17 THEN
        RETURN v_sal * 1.1;
    ELSE
        RETURN v_sal;
    END IF;

END query_call;
/

/* 호출 */
SELECT employee_id, salary, query_call(employee_id)
FROM hr.employees;

UPDATE hr.employees -- 업데이트하려는데
SET salary = query_call(100) -- query_call 함수 내 hr.employees 같은 테이블을 SELECT 작업
WHERE employee_id = 100;
-- ORA-04091: table HR.EMPLOYEES is mutating, trigger/function may not see it

>> query_call() 내 select가 업데이트 전인지 후인지 모르기 때문에 에러
>> 동일 테이블에서 동시에 SELECT + DML 작업이 안된다.

# SQL 표현식에서 함수를 호출할 때 부작용이 발생할 수 있다.
- 함수를 통해서 SELECT하고 있는데 함수 안에 동일한 테이블에 대해서 DML하면 부작용 발생한다.
- 함수를 통해서 DML하려고 하는데 함수 안에 동일한 테이블에 대해서 SELECT하면 부작용이 발생한다.
- 함수를 통해서 조회하고 있는데 함수안에 동일한 테이블에 대해서 SELECT하면 문제 없다.

■ LOCAL SUBPROGRAM

DECLARE
    TYPE emp_id_type IS TABLE OF number;    -- n배열, 중첩테이블 1번부터 시작
    v_id emp_id_type := emp_id_type(100,101,102);
    v_emp hr.employees%rowtype;             -- 레코드

    /* 프로그램 내에서만 사용하는 FUNCTION 생성 */
    FUNCTION tax(p_salary IN number)
        RETURN number
    IS
    BEGIN
        RETURN p_salary * 0.8;
    END;

    /* 프로그램 내에서만 사용하는 PROCEDURE 생성 */
    PROCEDURE message
    IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('꿈을 이루자.');
    END;

BEGIN
    FOR i IN v_id.FIRST..v_id.LAST LOOP
        SELECT *
        INTO v_emp
        FROM hr.employees
        WHERE employee_id = v_id(i);

        DBMS_OUTPUT.PUT_LINE('사원번호 : '||v_emp.employee_id||' Tax : '||tax(v_emp.salary));
    END LOOP;

    message;
END;
/

CREATE OR REPLACE FUNCTION validation_comm(v_comm IN number)
    RETURN BOOLEAN      -- TRUE, FALSE, NULL
IS
    v_max_comm number;
BEGIN
    SELECT max(commission_pct)
    INTO v_max_comm
    FROM hr.employees;

    IF v_comm > v_max_comm THEN
        RETURN FALSE;
    ELSE
        RETURN TRUE;
    END IF;
END validation_comm;
/

CREATE OR REPLACE PROCEDURE reset_comm(p_comm IN number)    -- 실행 시 validation_comm 함수 필요
IS
    g_comm number := 0.1;
BEGIN
    IF validation_comm(p_comm) THEN
        DBMS_OUTPUT.PUT_LINE('OLD : '||g_comm);
        g_comm := p_comm;
        DBMS_OUTPUT.PUT_LINE('NEW : '||g_comm);
    ELSE
        RAISE_APPLICATION_ERROR(-20000,'기존 최고값을 넘을 수 없습니다.');
    END IF;
END reset_comm;
/
>> validation_comm 함수와 reset_comm 프로시저는 종속관계

BEGIN
reset_comm(0.2);
END;
/

BEGIN
reset_comm(0.5); -- ORA-20000: 기존 최고값을 넘을 수 없습니다.
END;
/

■ PACKAGE 패키지
관련성있는 서브프로그램(프로시저, 함수), 변수, DATA TYPE을 모아놓은 프로그램

1. SPEC(PUBLIC) : 필수
CREATE OR REPLACE PACKAGE comm_pkg
IS
    g_comm number := 0.1;       -- 글로벌 변수선언. 어디서든 사용가능

    PROCEDURE reset_comm(p_comm IN number); -- OPEN하려는(밖에서 사용하고 싶다면) 헤더부분만 작성(IS절 위)
END;
/

2. BODY(PRIVATE) : 선택
★ Package Body에 기술되어있는 서브프로그램은 전방참조만 가능하다.

CREATE OR REPLACE PACKAGE BODY comm_pkg
IS
    FUNCTION validation_comm(v_comm IN number)
        RETURN BOOLEAN;

/* global procedure */
    PROCEDURE reset_comm(p_comm IN number)
    IS
    BEGIN
        IF validation_comm(p_comm) THEN
            DBMS_OUTPUT.PUT_LINE('OLD : '||g_comm);
            g_comm := p_comm; -- g_comm 변수가 글로벌로 선언되어있어 섹션 내 실행할때마다 누적 업데이트 됨
            DBMS_OUTPUT.PUT_LINE('NEW : '||g_comm);
        ELSE
            RAISE_APPLICATION_ERROR(-20000,'기존 최고값을 넘을 수 없습니다.');
        END IF;
    END reset_comm;

/* private function */
    FUNCTION validation_comm(v_comm IN number)
        RETURN BOOLEAN      -- TRUE, FALSE, NULL
    IS
        v_max_comm number; -- local 변수
    BEGIN
        SELECT max(commission_pct)
        INTO v_max_comm
        FROM hr.employees;

        IF v_comm > v_max_comm THEN
            RETURN FALSE;
        ELSE
            RETURN TRUE;
        END IF;
    END validation_comm;

END comm_pkg;
/

EXECUTE DBMS_OUTPUT.PUT_LINE(comm_pkg.g_comm)

EXECUTE comm_pkg.reset_comm(0.3)

SELECT *
FROM user_source
WHERE name = 'COMM_PKG'
AND TYPE = 'PACKAGE';

SELECT *
FROM user_source
WHERE name = 'COMM_PKG'
AND TYPE = 'PACKAGE BODY';

■ Package Overloading
- 동일한 이름의 프로시저, 함수를 만들 수 있다. to_char(숫자), to_char(날짜)
- 형식매개변수의 개수, 순서, 데이터 유형, 형식매개변수의 모드가 다를 경우 동일한 이름의 서브프로그램을 생성할 수 있다.

CREATE OR REPLACE PACKAGE pack_over
IS
    TYPE date_tab_type IS TABLE OF date INDEX BY pls_integer;
    TYPE num_tab_type IS TABLE OF number INDEX BY pls_integer;

    PROCEDURE init(tab OUT date_tab_type, n IN number);
    PROCEDURE init(tab OUT num_tab_type, n IN number);

END pack_over;
/

CREATE OR REPLACE PACKAGE BODY pack_over
IS
    PROCEDURE init(tab OUT date_tab_type, n IN number)
    IS
    BEGIN
        FOR i IN 1..n LOOP
            tab(i) := sysdate;
        END LOOP;
    END init;

    PROCEDURE init(tab OUT num_tab_type, n IN number)
    IS
    BEGIN
        FOR i IN 1..n LOOP
            tab(i) := i;
        END LOOP;
    END init;

END pack_over;
/

DECLARE
    date_tab pack_over.date_tab_type;
    num_tab pack_over.num_tab_type;
BEGIN
    pack_over.init(date_tab,5);
    pack_over.init(num_tab,5);

    FOR i IN 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE(date_tab(i));
    END LOOP;

    FOR i IN 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE(num_tab(i));
    END LOOP;
END;
/

23/11/03
23/11/03
23/11/03
23/11/03
23/11/03
1
2
3
4
5

CURSOR 선언 후 FOR문 사용 시 (OPEN-FETCH-CLOSE 미사용)
NULL값 = 결과가 없다면 END IF 로 반복문을 종료한다.
↓
LOOP + OPEN - FETCH - CLOSE 사용하자

OPEN dept_cur;  -- 메모리할당 후 행을 데이터를 포인트 지정
LOOP
    FETCH dept_cur INTO v_rec;    -- 데이터를 조작하기 위해 변수에 대입
    IF v_rec.employee_id IS NULL THEN    -- FETCH 후 데이터가 NULL >> EXCEPTION 이동
    ELSIF dept_cur%NOTFOUND THEN        -- FETCH 후 데이터가 끝나면 END IF 정상 종료 <> NULL과는 다르다
    END LOOP;
CLOSE dept_cur;

# 동일 테이블에서 동시에 SELECT + DML 작업이 안된다.
함수 내 select가 업데이트 전인지 후인지 모르기 때문에 에러

# PACKAGE 패키지
CREATE OR REPLACE PACKAGE comm_pkg
IS
    /* SPEC(PUBLIC) 필수 영역 */
    글로벌 변수 선언
    Overloading
END;
/

CREATE OR REPLACE PACKAGE BODY comm_pkg -- 명칭은 SPEC영역과 동일
IS
    /* BODY(PRIVATE) 선택 영역 */
    SPEC 부분의 소스
END;
/

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

23 PL/SQL TRIGGER (0)	2023.11.07
22 PL/SQL PACKAGE, TRIGGER (1)	2023.11.06
20 PL/SQL PROCEDURE, FUNCTION (0)	2023.11.02
19 PL/SQL PROCEDURE (0)	2023.11.01
18 PL/SQL EXCEPTION (1)	2023.10.31

'Oracle SQL'의 다른글

현재글21 PL/SQL PACKAGE

Record.

21 PL/SQL PACKAGE

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

티스토리툴바