231106 PL/SQL PACKAGE 선언, 지시어, 표준화, PRAGMA

□ 패키지 안에 생성된 명시적 커서 지속상태

-- 필수: 패키지 선언
CREATE OR REPLACE PACKAGE pack_cursor 
IS
    PROCEDURE open;
    PROCEDURE next(p_num IN NUMBER);
    PROCEDURE close;
    
END pack_cursor;
/

SPEC 먼저 선언

SELECT * FROM user_source WHERE name = 'PACK_CURSOR';
SELECT * FROM user_objects WHERE object_name = 'PACK_CURSOR';

PACKAGE BODY : 옵션

CREATE OR REPLACE PACKAGE BODY pack_cursor
IS
    /* 패키지 body 변수 
       private cursor : pack body 에서는 사용가능 */
    CURSOR c1 IS
        SELECT employee_id, last_name
        FROM hr.employees
        ORDER BY 1 DESC;
        
    -- OPEN   
    PROCEDURE open
    IS 
    BEGIN
        IF NOT c1%ISOPEN THEN -- C1 가 오픈되어 있지 않는다면, 
            OPEN c1;
            dbms_output.put_line('c1 CURSOR OPEN');
        END IF;
    END open;
    
    PROCEDURE next(p_num IN NUMBER)
    IS
        -- next 변수 (로컬 변수)
        v_id NUMBER;
        v_name VARCHAR2(30);
    BEGIN
        LOOP 
            EXIT WHEN c1%ROWCOUNT >= p_num ;
            FETCH c1 INTO v_id, v_name;
            dbms_output.put_line('ID : '||v_id||', NAME : '||v_name );
        END LOOP;
    END next;
    
    -- CLOSE
    PROCEDURE close
    IS 
    BEGIN 
        IF c1%ISOPEN THEN 
            CLOSE c1;
            dbms_output.put_line('c1 CURSOR CLOSE');
        END IF;
    END close;
    
END pack_cursor;
/

 

-- cursor 열기
execute pack_cursor.open; 
-- fetch: next 값 입력
execute pack_cursor.next(3);
execute pack_cursor.next(6);
execute pack_cursor.next(9);
-- cursor 닫기 
execute pack_cursor.close;

# package 안에서 생성된 global, private 변수는 내 session 이 종료할 때까지 지속적으로 변경한 값을 사용한다.
1) SPEC(PUBLIC)

CREATE OR REPLACE PACKAGE comm_pkg --생성자
IS
    PROCEDURE reset_comm(p_comm IN NUMBER); --global procdure 
END;
/

2) BODY(PRIVATE)

CREATE OR REPLACE PACKAGE BODY comm_pkg
IS
     g_comm NUMBER := 0.1; -- private 변수(경우에 따라 변경가능)
    /* 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('private 변수');
            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);

g_comm := 0.1 -> 0.1 

execute comm_pkg.reset_comm(0.2);

 

g_comm := 0.1 -> 0.2

execute comm_pkg.reset_comm(0.3);

g_comm := 0.2 -> 0.3

# PRAGMA SERIALLY_REUSABLE(지시어) : package 내에서 생성된 생선자들의 변경한 값은 호출 동안에만 변경한 값을 사용하고 호출이 끝나면 원래 값으로 되돌아가는 기능
- PRAGMA SERIALLY_REUSABLE
1) SPEC(PUBLIC)

CREATE OR REPLACE PACKAGE comm_pkg
IS
    PRAGMA SERIALLY_REUSABLE; 
    PROCEDURE reset_comm(p_comm IN NUMBER); --global procdure 
END;
/

2) BODY(PRIVATE)

CREATE OR REPLACE PACKAGE BODY comm_pkg
IS
    PRAGMA SERIALLY_REUSABLE;
    g_comm NUMBER := 0.1; -- private 변수(경우에 따라 변경가능)
    /* 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('SERIALLY_REUSABLE 지시어');
            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;
/

execute comm_pkg.reset_comm(0.2);

 g_comm := 0.1 -> 0.2 

execute comm_pkg.reset_comm(0.3);

 

 g_comm := 0.1 -> 0.3 

execute comm_pkg.reset_comm(0.4);

<< 상수 표준화 >>
- BODY 없는 SPEC 만 선언할때

CREATE OR REPLACE PACKAGE global_consts
IS
    c_mile_2_kilo CONSTANT NUMBER := 1.6093;
    c_kilo_2_mile CONSTANT NUMBER := 0.6214;
    c_yard_2_meter CONSTANT NUMBER := 0.9144;
    c_meter_2_yard CONSTANT NUMBER := 1.0936;
    
END global_consts;
/

-- # 오류발생 
-- expression 'GLOBAL_CONSTS.C_MILE_2_KILO' cannot be used as an assignment target

execute global_consts.c_mile_2_kilo := 2.6093;

상수는 한번 입력한 값만 사용해야 한다.

 

# 호출

BEGIN
    dbms_output.put_line('20 mile = '||20*global_consts.c_mile_2_kilo||'km');    
END;
/

BEGIN
    dbms_output.put_line('20 kilo = '||20*global_consts.c_kilo_2_mile||'mi');
END;
/

BEGIN
    dbms_output.put_line('20 yard = '||20*global_consts.c_yard_2_meter||'m');
END;
/

BEGIN
    dbms_output.put_line('20 meter = '||20*global_consts.c_meter_2_yard||'yd');
END;
/

 

# FUNCTION 함수 호출

CREATE OR REPLACE FUNCTION meter_to_yard(p_m IN number)
    RETURN NUMBER
IS 
BEGIN 
    RETURN (p_m * global_consts.c_meter_2_yard);
end meter_to_yard;
/

# SELECT문 출력

SELECT meter_to_yard(100) FROM dual;

# 오류번호, 오류명

desc departments

DECLARE
    e_insert_null EXCEPTION; --오류번호 변수
    PRAGMA EXCEPTION_INIT(e_insert_null, -01400);
BEGIN
    INSERT INTO hr.departments (department_id, department_name)
    VALUES (280, null);

EXCEPTION
    WHEN e_insert_null THEN
        dbms_output.put_line('INSERT OPERATION FAILED');
        dbms_output.put_line(SQLERRM);
END;
/

SELECT * FROM hr.departments;

<< EXCEPTION 오류명: 표준화 >>

CREATE OR REPLACE PACKAGE err_pkg
IS 
    e_insert_null EXCEPTION; --오류 변수
    PRAGMA EXCEPTION_INIT(e_insert_null, -01400);
END err_pkg;
/

BEGIN
    INSERT INTO hr.departments (department_id, department_name)
    VALUES (280, null);
EXCEPTION
    WHEN err_pkg.e_insert_null THEN
        dbms_output.put_line('INSERT OPERATION FAILED');
        dbms_output.put_line(SQLERRM);
END;
/