231103 PL/SQL 함수의 부작용, LOCAL SUBPROGRAM

■ 함수의 부작용
- 동일한 TABLE 에 대해서 SELECT, DML 을 수행할 수 없다. 

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;

# 오류발생 
- 트리거, 타이밍(mutating) 오류발생(부작용) : 
1) 오라클입장에서 수정전 data, 수정후 data 어떤 data를 보여줘야 하는지 모르겠다.
2) 동시에 같은 table 에서는 안된다.
    - 해결 방법 : proceduce 으로 해결
- ORA-04091: table HR.EMPLOYEES is mutating, trigger/function may not see it

UPDATE hr.employees
SET salary = query_call(100)
WHERE employee_id = 100;

# SQL 표현식에서 '함수'를 호출할 때 부작용이 발생할 수 있다.
- 동일한 TABLE 에 대해서 SELECT, DML 을 수행할 수 없다. 
( = 함수를 통해서 SELECT 하고 있는데, 함수 안에 동일한 TABLE에 대해서 DML하면 부작용이 발생한다.)
- 함수를 통해서 DML 하려고 하는데 함수 안에 동일한 테이블에 대해서 SELECT 하면 부작용이 발생한다. 
- 단, 같은 테이블에 대해서 SELECT 상관없다.
( = 함수를 통해서 SELECT 하고 있는데 함수 안에 동일한 TABLE에 대해서 SELECT 하면 문제없다.)
▷ SELECT문+DML = 프로시저 사용

■ LOCAL SUBPROGRAM
# 함수+프로시저를 declare 선언

DECLARE
    TYPE emp_id_type IS TABLE OF NUMBER; -- 배열타입
    v_id emp_id_type := emp_id_type(100, 101, 102); --1차원 배열타입
    v_emp hr.employees%ROWTYPE; --레코드타입
    
    FUNCTION tax(p_salary IN NUMBER)
        RETURN NUMBER
    IS 
    BEGIN
        RETURN p_salary*0.8;
    END tax;
    
    PROCEDURE message
    IS
    BEGIN
        dbms_output.put_line('꿈을 이루자.');
    END message;
    
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 
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; --true
    ELSE
        RETURN true; -- false
    END IF;
END validation_comm;
/

 

CREATE OR REPLACE PROCEDURE reset_comm (
    p_comm IN NUMBER ) 
IS 
    g_comm NUMBER := 0.1; --초기값
BEGIN
    IF validation_comm(p_comm)/* 함수과의 종속관계 */ THEN
        -- true
        dbms_output.put_line('OLD : '||TO_CHAR(g_comm, '0.99'));
        g_comm := p_comm;
        dbms_output.put_line('NEW : '||g_comm);
    ELSE 
        --false
        RAISE_APPLICATION_ERROR(-20000, '기존 최고값을 넘을 수 없습니다.');
    END IF;
    
END reset_comm;
/

# 호출

BEGIN
    reset_comm(0.2);
END;
/

BEGIN
    reset_comm(0.3);
END;
/

BEGIN
    reset_comm(0.5);
END;
/