231103 PL/SQL PACKAGE, package overloading

■ PACKAGE
: 관련성있는 서브프로그램(프로시저, 함수), 변수, DATA TYPE(레코드타입, 데이터타입 등)을 모아놓은 프로그램
- 권한 : CREATE PROCEDURE
1. 글로벌 변수를 선언(표현)해야 할때
2. exception 표준화해야 할때
3. 오버로딩 해야 할때 : 형변화( ex) to_char )
4. 연관있는프로그램 + 유지관리

# SPEC(필수, 상수:글로벌변수, exception 선언) + BODY(옵션) 
1) SPEC(PUBLIC) --OPEN된 소스 + cmd에서도 적용, 선언만(header 부분만)
-- BEGIN 절 필수가 아니다.

CREATE OR REPLACE PACKAGE comm_pkg --생성자
IS
    g_comm NUMBER := 0.1; --글로벌 변수 선언 ex) 고정금리
    PROCEDURE reset_comm(p_comm IN NUMBER); --header 부분만 작성
    FUNCTION validation_comm (v_comm IN NUMBER) RETURN BOOLEAN;
END;
/

 

# 호출

execute dbms_output.put_line(comm_pkg.g_comm);

2) BODY(PRIVATE) -- 보안성 소스

CREATE OR REPLACE PACKAGE BODY comm_pkg
IS
    /* private function */
    FUNCTION validation_comm ( v_comm IN NUMBER ) 
        RETURN BOOLEAN 
    IS
        v_max_comm NUMBER; --local 변수
    BEGIN
        SELECT MAX(commission_pct) -- 0.4(미정)
        INTO v_max_comm
        FROM hr.employees;
    
        IF v_comm > v_max_comm THEN
            RETURN false;
        ELSE
            RETURN true;
        END IF;
    END validation_comm;    
    
    /* 글로벌변수 이용 : 지속적으로 사용
    global procedure */
    PROCEDURE reset_comm ( p_comm IN NUMBER ) 
    IS 
    BEGIN
        IF validation_comm(p_comm) THEN
            dbms_output.put_line('global 변수');
            dbms_output.put_line('OLD : '||to_char(g_comm, '0.99'));
            g_comm := p_comm;
            dbms_output.put_line('NEW : '||to_char(g_comm, '0.99'));
        ELSE 
            RAISE_APPLICATION_ERROR(-20000, '기존 최고값을 넘을 수 없습니다.');
        END IF;        
    END reset_comm;
END comm_pkg;
/

-- 실행 : 내 session에서만 

execute comm_pkg.reset_comm(0.1);
execute comm_pkg.reset_comm(0.2);
execute comm_pkg.reset_comm(0.3);

# BODY 주의할점 : PACKAGE BODY 에 기술되어 있는 서브프로그램은 전방참조만 가능하다.
- PLS-00313: 'VALIDATION_COMM' not declared in this scope
- FUNCTION header 부분만 갖고 오기

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

    PROCEDURE reset_comm ( p_comm IN NUMBER ) 
    IS 
    BEGIN
        IF validation_comm(p_comm) THEN
            dbms_output.put_line('OLD : '||to_char(g_comm, '0.99'));
            g_comm := p_comm;
            dbms_output.put_line('NEW : '||to_char(g_comm, '0.99'));
        ELSE 
            RAISE_APPLICATION_ERROR(-20000, '기존 최고값을 넘을 수 없습니다.');
        END IF;        
    END reset_comm;
    
    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;
        ELSE
            RETURN true;
        END IF;
    END validation_comm;
END comm_pkg;
/

# source 보기 

SELECT * FROM user_source WHERE name = 'COMM_PKG';

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

■ package overloading 
- 동일한 이름의 프로시저, 함수를 만들 수 있다. 
- 형식 매개변수의 개수, 순서, 데이터 유형, mode(in, out, in out)가 다를 경우 동일한 이름의 서브 프로그램을 생성할 수 있다. 

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

pack_over.init(날짜배열, 5);
pack_over.init(숫자배열, 5);

# overloading 화 

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));
        DBMS_OUTPUT.PUT_LINE(num_tab(i));
    END LOOP;
END;
/