Oracle PL/SQL 프로시저[패키지]

패키지는 서로 관련 있는 프로시저, 함수, 타입들의 집합이다. 하나의 업무를 수행하기 위해 여러개의 프로시저, 함수, 타입이 필요할 경우 하나의 패키지로 묶어 생성한다면 관리하기도 쉽고 운영 안정성 측면에서도 유리하다.

 

패키지를 사용하려면 유지보수의 편이성이 높아지며, 프로그램 간 종속성 체인을 끊고 캡슐화를 지원하며 , 세션 지속형 변수를 지원하는 등 여러이점이 있으므로 프로그램의 품질 향상과 안정성을 위해 프로시저나 함수를 개별적으로 생성하여 사용하는 것보다 패키지를 사용하것이 좋다

 

패키지 생성

패키지 명세 (sercification)과 몸체(body) 두 부분으로 이루어져 있다. 명세는 패키지 내 어느 서브프로그램 에서도 사용될 수 있는 전역 변수를 선언하며, 프로그램을 어떻게 호출하고 커서를 어떻게 열어야 하는지를 정의하도록 한 부분이며, 몸체는 명세에 나열된 프로시저와 함수를 실제로 구현하는 부분이다.

 

패키지에서 사용 되는 변수와 서브프로그램은 공용(publice) 전용(priavete) 으로 구분할수 있는데 공용 코드는 패키지 명세 내에 정의되며 해당 캐지에 대한 EXECUTE 권한을 가진 모든 스키마에서 접근할 수 있다.

 

 

CREATE OR REPLACE PACKAGE<pkg_name> as
--전역 변수 선언
<global variable>

--공용 서브프로그램 선언
<public subprogram declaration>
END <pkg_name>

CREATE OR REPLACE PACKAGE<PKG_NAME> AS
--전용변수
<global variable>

--전용 서브프로그램 선언
<private subprogram declaration>

BEGIN
    --패키지 초기화 수행
    END<pkg_name>;

 

 

사원정보를 확인하는 패키지 명세서를 선언하는 내용

패키지에서 사용될 전역 변수,프로시저, 함수의 입출력 매개변수 정보 선언

create or replace PACKAGE EMP_INFO_PKG
IS
--타입선언
TYPE EMPRECTYP IS RECORD(EMPNO EMP.EMPNO%TYPE, ENAME EMP.ENAME%TYPE , SAL EMP.SAL%TYPE);
--함수선언
FUNCTION GET_ENAME_FUNC(P_EMPNO NUMBER)
    RETURN EMPRECTYP;
    --프로시저 선언
    PROCEDURE GET_DNAME_PROC(P_DEPTNO IN NUMBER, P_DNAME OUT VARCHAR2);
    PROCEDURE RAISE_SALARY_PROC(P_EMP_ID NUMBER, P_GRADE NUMBER,P_AMOUNT NUMBER);

    --전역 변수 선언
    G_HIRED_CNT NUMBER;
    --예외처리 변수
    invalid_salary EXCEPTION;

    END EMP_INFO_PKG;

 

패키지 명세를 생성하였으면 패키지에서 사용할 프로시저와 함수에 대한 자세한 로직을 구현하는 몸체를 생성해야한다

create or replace PACKAGE BODY EMP_INFO_PKG AS
--패키지 명세에서 선언한 함수 내용
--사원 정보 조회 함수
FUNCTION GET_ENAME_FUNC(P_EMPNO NUMBER)
    RETURN EMPRECTYP
    IS
        I_EMPREC EMPRECTYPE;
        BEGIN
        --암묵적 커서 선언하여 emprectype 레코드 타입 변수에 결과저장
        SELECT EMPNO,ENAME,SAL
        INTO I_EMPREC
        FROM EMP
        WHERE EMPNO = P_EMPNO;
        
        --함수가 호출될 때마다 전역 변수의 값이 1씩 증가
        G_HIRED_CNT := G_HIRED_CNT +1;
        --사원이름반환
        RETURN I_EMPREC;
        END GET_ENAME_FUNC;
        
    --프로시저 선언
    PROCEDURE GET_DNAME_PROC(P_DEPTNO IN NUMBER, P_DNAME OUT VARCHAR2)
    IS
    BEGIN
        SELECT DNAME
        INTO P_DEPTNO
        FROM DEPT
        WHERE DEPTNO = P_DEPTNO;
        END GET_DNAME_PROC;
        
    --패키지 내부에서만 사용 가능한 함수 선언
    --salary 의등급이 적절한지 여부 확인하여 Boolena 타입 변환
    FUNCTION sal_ok_func(p_grade NUMBER , P_salary NUMBER)
    RETURN BOOLEAN IS
        i_min_sal number;
        i_max_sal number;
        BEGIN
            SELECT losal,hisal
            INTO i_min_sal,i_max_sal
            FROM salgrade
            WHERE grade = p_grade;
            
            --범위 안에 p_salary 값이 있으면  True, 그렇지 않으면 false반환
            RETURN (p_salary >= i_min_sal) AND (P_salary <= i_max_sal);
            END sal_ok_func;
                  
    PROCEDURE RAISE_SALARY_PROC(P_EMP_ID NUMBER, P_GRADE NUMBER,P_AMOUNT NUMBER)
    IS
        I_SALARY NUMBER;
        BEGIN
            --SAL 정보를 저장
            SELECT SAL
            INTO I_SALARY
            FROM EMP
            WHERE EMPNO = P_EMP_ID;
            --SAL  등급을 확인하여 TRUE  이면 UPDATE 아니면 예외 발생 
            IF sal_ok_func(p_grade,i_salary + p_amount) THEN
            UPDATE EMP SET SAL = SAL + P_AMOUNT WHERE EMPNO = P_EMP_ID;
            ELSE
                RAISE INVALID_SALARY;
                END IF;
            END RAISE_SALARY_PROC;
            
            BEGIN
            --패키지 실행시 초기화
                g_hired_cnt := 0;
    END EMP_INFO_PKG;

sal_ok_func 함수의 경우 패키지 내부에서만 사용되기 떄문에 패키지 명세 부분에서 선안할 필요는 없으며 새로운 세션에서 실행될 때마다 g_hired_cnt 전역변수가 0으로 초기화 되도록 패키지 몸체의 BEGIN 문에서 초기화 하는 로직을 추가 했다.

 

패키지가 생성되었으면 < 패키지명.프로시저명> 또는 <패키지명.함수명> 을 사용하여 실행할 수 있습니다. 실행 방법은 프로시저나 함수의 실행 방법과 동일하다

 

DECLARE 
    --패미지에서 정의한 레코드 타입 선언
        emp_rec EMP_INFO_PKG.EMPRECTYP;
        BEGIN
        --레코드 타입 변수에 함수 결과 리턴
        emp_rec := EMP_INFO_PKG.GET_ENAME_FUNC(7369);
        
        DBMS_OUTPUT.PUT_LINE('EMPNO :' || EMP_REC.EMPNO);
        DBMS_OUTPUT.PUT_LINE('ENAME : ' || EMP_REC.ENAME);
        DBMS_OUTPUT.PUT_LINE('SAL : ' ||EMP_REC.SAL);
        
        DBMS_OUTPUT.PUT_LINE(EMP_INFO_PKG.G_HIRED_CNT);
        END;

 

 

전역 변수 선언 시 유의 사항

패키지 안에서 프로시저나 함수 간 변수의 내용을 공유해야 할 경우에는 패키지 명세에 전역 변수를 선언하여 사용할수 있다.

 

그러나 전역 변수가 사용될 경우 패키지와 의존 관계에 있는 오브젝트가 변경되면 에러가 발생한다 가급적이면 지역 변수가 사용되도록 하고 꼭 불가피한 경우에만 전역 변수를 사용하는 것이 좋다

 

프러시저 및 패키지 변경시 주의사항

프로시저 패키지 테이블 과 같은 오브젝트가 변경될 경우 의존 관계에 있는 다른 오브젝트들도 따라서 컴파일이 제수행 되어, 이로 인한 시스템 성능 저하가 발생할 수 있으므로 주의해야 한다

따라서 다른 프로그램에서 많이 호출되는 중요 프로시저,패키지, 테이블의 경우에는 사용 빈도가 적은 시간대에 변경을 수행하는 것이 좋다

 

패키지 컴파일

패키지 명세와 몸체를 한 번에 컴파일할 수 있으며 몸체만 별도로 컴파일이 가능하다 패키지의 경우 의존 관계에 있는 패키지,프로시저, 테이블이 변경되면 몸체부분만  INVALID(유효하지 않음)  되기 때문에 몸체만 별도로 컴파일할수 있다.