[PL/SQL] 패키지 데이터

패키지 데이터

패키지에는 서브 프로그램뿐만 아니라 상수, 변수, 커서, 레코드, 컬렉션, 예외까지 선언해서 사용할 수 있다.

굳이 상수나 변수, 커서 등을 패키지에서 선언해 사용할 필요가 있을까?

상수와 변수 선언

패키지 안에 상수나 변수를 선언하게 되면 이들의 생존주기는 세션 단위로, 한 세션이 살아있는 동안에는 그 값이 메모리 상에 유지된다.

즉, 한 번 로그인한 뒤 로그아웃을 하기 전까지는 그 값이 공유된다. 변수의 경우, 해당 패키지의 사용이 끝났더라도 같은 세션에서는 그 값을 공유할 수 있다.

CREATE OR REPLACE PACKAGE CH12_VAR IS
    C_TEST  CONSTANT    VARCHAR2(10)    := 'TEST';
    V_TEST  VARCHAR2(10);
END;

BEGIN
    DBMS_OUTPUT.PUT_LINE('상수 : ' || CH12_VAR.C_TEST);
    DBMS_OUTPUT.PUT_LINE('변수 : ' || CH12_VAR.V_TEST);
END;

BEGIN
    DBMS_OUTPUT.PUT_LINE('값 설정 전 : ' || CH12_VAR.V_TEST);
    
    CH12_VAR.V_TEST := 'FIRST';
    
    DBMS_OUTPUT.PUT_LINE('값 설정 후 : ' || CH12_VAR.V_TEST);
END;

패키지 선언부에 선언한 변수는 외부에서 접근하고 수정할 수 있는데, 이를 공유 항목이라고도 한다.

위 익명 블록에서 변수의 값을 설정을 했으면, 익명 블록이 종료되더라도 세션이 살아있다면 변수의 값은 다시 조회해도 동일할 것이다. 하지만 세션을 종료하면 원래의 값으로 돌아간다.

BEGIN
    DBMS_OUTPUT.PUT_LINE('변수 : ' || CH12_VAR.V_TEST);
END;

패키지 선언부가 아닌 본문에서 상수나 변수를 선언해 값을 할당할 수 있는데, 이 경우 외부에서 이 상수나 변수를 참조할 수 없다. 이를 내부(전용)항목이라고 한다.

하지만, 직접 참조는 불가능하나 서브 프로그램을 통해 간접적으로 참조하거나 값을 변경할 수 있다.

CREATE OR REPLACE PACKAGE BODY CH12_VAR IS
    C_TEST_BODY CONSTANT    VARCHAR2(10)    := 'TEST';
    V_TEST_BODY VARCHAR2(10);
END CH12_VAR;

직접 참조를 시도해보자.

BEGIN
    DBMS_OUTPUT.PUT_LINE('상수 : ' || CH12_VAR.C_TEST_BODY);
    DBMS_OUTPUT.PUT_LINE('변수 : ' || CH12_VAR.V_TEST_BODY);
END;

오류가 발생하는 것을 확인할 수 있다. 그렇다면, 이번에는 내부변수와 상수를 처리하는 함수와 프로시저 추가하고 실행해보자.

CREATE OR REPLACE PACKAGE CH12_VAR IS
    C_TEST_BODY CONSTANT    VARCHAR2(10)    := 'TEST';
    V_TEST_BODY VARCHAR2(10);
    
    FUNCTION    FN_GET_VALUE    RETURN  VARCHAR2;
    PROCEDURE   SP_SET_VALUE (PS_VALUE  VARCHAR2);
END CH12_VAR;

CREATE OR REPLACE PACKAGE BODY CH12_VAR IS
    C_TEST_BODY CONSTANT    VARCHAR2(10)    := 'TEST';
    V_TEST_BODY VARCHAR2(10);
    
    FUNCTION    FN_GET_VALUE    RETURN  VARCHAR2
    IS
    
    BEGIN
        RETURN NVL(V_TEST_BODY, 'NULL입니다');
    END FN_GET_VALUE;
    
    PROCEDURE   SP_SET_VALUE (PS_VALUE  VARCHAR2)
    IS
    
    BEGIN
        V_TEST_BODY := PS_VALUE;
    END SP_SET_VALUE;
END CH12_VAR;

DECLARE
    VS_VALUE    VARCHAR2(10);
BEGIN
    CH12_VAR.SP_SET_VALUE('EXTERNAL');
    
    VS_VALUE := CH12_VAR.FN_GET_VALUE;
    DBMS_OUTPUT.PUT_LINE(VS_VALUE);
END;

위에서 공유 항목은 세션이 유지되어있으면 값이 유지된다고 했다. 세션을 유지한 채로 아래 쿼리를 실행해보자. 세션 유지라고해서 별거 없다. 접속 종료만 하지 않으면 된다.

BEGIN
    DBMS_OUTPUT.PUT_LINE(CH12_VAR.FN_GET_VALUE);
END;

커서

명시적 커서를 패키지에서 선언하고 사용할 수 있는데, 변수나 상수와 같이 패키지 내에서 선언한 커서는 세션이 살아있는 동안 유지된다.

변수는 값이 유지되지만, 커서는 그 상태까지 유지된다. 패키지에서는 두가지 형태로 명시적 커서를 선언할 수 있다.

패키지 선언부에서 커서 전체를 선언하는 형태

CREATE OR REPLACE PACKAGE CH12_CUR_PKG IS
    CURSOR  PC_EMPDEP_CUR (DEP_ID IN DEPARTMENTS.DEPARTMENT_ID%TYPE)
    IS
        SELECT  A.EMPLOYEE_ID, A.EMP_NAME, B.DEPARTMENT_NAME
        FROM    EMPLOYEES A, DEPARTMENTS B
        WHERE   A.DEPARTMENT_ID = DEP_ID
        AND     A.DEPARTMENT_ID = B.DEPARTMENT_ID;
END CH12_CUR_PKG;

BEGIN
    FOR REC IN CH12_CUR_PKG.PC_EMPDEP_CUR(30)
    LOOP
        DBMS_OUTPUT.PUT_LINE(REC.EMP_NAME || ' - ' || REC.DEPARTMENT_NAME);
    END LOOP;
END;

쿼리를 제외한 커서 헤더부분만 선언하는 형태

커서 헤더만 선언부에 명시하면 해당 커서의 쿼리는 패키지 본문에서 작성해야한다. 따라서, 커서 구현부 쿼리는 외부로부터 숨길 수 있다.

또한, 헤더 부분만 선언할 때는 커서가 반환,패치하는 데이터를 가리키는 RETURN절을 명시해야한다.

즉 구현부의 쿼리 결과로 반환되는 컬럼의 타입을 RETURN절과 함께 명시해야한다는 의미이다.

CREATE OR REPLACE PACKAGE CH12_CUR_PKG IS
    CURSOR  PC_DEPNAME_CUR (DEP_ID IN DEPARTMENTS.DEPARTMENT_ID%TYPE)
            RETURN  DEPARTMENTS%ROWTYPE;
END CH12_CUR_PKG;

CREATE OR REPLACE PACKAGE BODY CH12_CUR_PKG IS
    CURSOR  PC_DEPNAME_CUR (DEP_ID IN DEPARTMENTS.DEPARTMENT_ID%TYPE)
        RETURN  DEPARTMENTS%ROWTYPE
    IS
        SELECT  *
        FROM    DEPARTMENTS
        WHERE   DEPARTMENT_ID = DEP_ID;
END CH12_CUR_PKG;

BEGIN
    FOR REC IN CH12_CUR_PKG.PC_DEPNAME_CUR(30)
    LOOP
        DBMS_OUTPUT.PUT_LINE(REC.DEPARTMENT_ID || ' - ' || REC.DEPARTMENT_NAME);
    END LOOP;
END;

CREATE OR REPLACE PACKAGE CH12_CUR_PKG IS
    TYPE    EMP_DEP_RT  IS RECORD 
    (   EMP_ID      EMPLOYEES.EMPLOYEE_ID%TYPE,
        EMP_NAME    EMPLOYEES.EMP_NAME%TYPE,
        JOB_TITLE   JOBS.JOB_TITLE%TYPE    );
        
    CURSOR  PC_EMPDEP2_CUR  (P_JOB_ID IN JOBS.JOB_ID%TYPE)
            RETURN EMP_DEP_RT;
END;

CREATE OR REPLACE PACKAGE BODY CH12_CUR_PKG IS
    CURSOR  PC_EMPDEP2_CUR  (P_JOB_ID IN JOBS.JOB_ID%TYPE)
        RETURN EMP_DEP_RT
    IS
        SELECT  A.EMPLOYEE_ID, A.EMP_NAME, B.JOB_TITLE
        FROM    EMPLOYEES A, JOBS B
        WHERE   A.JOB_ID = P_JOB_ID
        AND     A.JOB_ID = B.JOB_ID;
END;

BEGIN
    FOR REC IN CH12_CUR_PKG.PC_EMPDEP2_CUR('FI_ACCOUNT')
    LOOP   
        DBMS_OUTPUT.PUT_LINE(REC.EMP_ID || ' - ' || REC.EMP_NAME || ' - ' || REC.JOB_TITLE);
    END LOOP;
END;

패키지 커서 사용시 주의점

LOOP나 WHILE문을 사용할 때는 '커서열기-패치-닫기' 과정을 직접 명시해줘야한다.

패키지 커서는 커서를 닫는 것을 누락하면 같은 세션에서 해당 커서를 재사용할 때 문제가 발생한다.

DECLARE
    DEP_CUR     CH12_CUR_PKG.PC_EMPDEP2_CUR%ROWTYPE;
BEGIN
    OPEN    CH12_CUR_PKG.PC_EMPDEP2_CUR('FI_ACCOUNT');
    
    LOOP
        FETCH   CH12_CUR_PKG.PC_EMPDEP2_CUR INTO DEP_CUR;
        EXIT    WHEN CH12_CUR_PKG.PC_EMPDEP2_CUR%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(DEP_CUR.EMP_ID || ' - ' || DEP_CUR.EMP_NAME || ' - ' || DEP_CUR.JOB_TITLE);
    END LOOP;
END;

위 쿼리를 한 번 더 실행해보자.

커서를 닫지 않았기 때문에 오류가 발생한다.

레코드와 컬렉션

CREATE OR REPLACE PACKAGE CH12_COL_PKG IS
    TYPE NT_DEP_NAME IS TABLE OF VARCHAR2(30);
    
    PV_NT_DEP_NAME  NT_DEP_NAME := NT_DEP_NAME();
    
    PROCEDURE MAKE_DEP_PROC (P_PAR_ID IN NUMBER);
END CH12_COL_PKG;

CREATE OR REPLACE PACKAGE BODY CH12_COL_PKG IS
 
    PROCEDURE MAKE_DEP_PROC (P_PAR_ID IN NUMBER)
    IS
    BEGIN
        FOR REC IN (SELECT  DEPARTMENT_NAME
                    FROM    DEPARTMENTS
                    WHERE   PARENT_ID = P_PAR_ID)
        LOOP
            PV_NT_DEP_NAME.EXTEND();
            PV_NT_DEP_NAME(PV_NT_DEP_NAME.COUNT) := REC.DEPARTMENT_NAME;
        END LOOP;
    END MAKE_DEP_PROC;
END CH12_COL_PKG;

BEGIN
    CH12_COL_PKG.MAKE_DEP_PROC(100);
    
    FOR IDX IN 1..CH12_COL_PKG.PV_NT_DEP_NAME.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE(CH12_COL_PKG.PV_NT_DEP_NAME(IDX));
    END LOOP;
END;

같은 값이 공유되고 있음을 아래 쿼리로 확인해보자.

BEGIN
    FOR IDX IN 1..CH12_COL_PKG.PV_NT_DEP_NAME.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE(CH12_COL_PKG.PV_NT_DEP_NAME(IDX));
    END LOOP;
END;