[PL/SQL] 패키지(PACKAGE)

패키지(PACKAGE)

논리적 연관성이 있는 pl/sql타입, 변수, 상수, 서브 프로그램, 커서, 예외 등의 항목을 묶어 놓은 객체다.

패키지는 컴파일 과정을 거쳐 DB에 저장되며, 다른 프로그램에서 패키지의 항목을 참조, 공유,실행할 수 있다.

패키지의 장점

1. 모듈화 기능

업무적으로 연관성이 있거나 비슷한 기능을 수행하는 서브 프로그램이나 변수, 상수, 커서, 사용자 정의 타입들을 하나의 패키지에 담아두면 이해하기도, 관리하기도 쉽다.

2. 프로그램 설계의 용이성

패키지는 선언부(스펙)과 본문(바디)로 구성되는데, 선언부만 있어도 컴파일한 뒤 저장이 가능하다.

3. 캡슐화

패키지 선언부는 외부에 공개되지만, 패키지에 속한 커서, 함수, 프로시저의 세부 구현내용이 담겨있는 본문은 외부에서 볼 수 없다.

즉, 정보 은닉 기능이 지원되는 것이며, 외부 모듈에 영향을 주지 않고도 패키지 본문 내용은 언제든지 수정할 수 있다.

4. 보다 나은 성능

패키지에 있는 서브 프로그램을 호출하면 일단 해당 패키지 전체를 메모리에 올려놓는데, 이후 계속 호출하더라도 메모리에 올라가있는 상태이므로 더 나은 성능을 보인다.

패키지 구조

패키지 선언부

CREATE OR REPLACE PACKAGE 패키지명 
IS
TYPE구문;
상수명 CONSTANT 상수타입;
예외명 EXCEPTION;
변수명 변수타입;
커서구문;
    
FUNCTION 함수명 (매개변수1 IN 매개변수1타입, 매개변수2 IN 매개변수2타입, ...)
RETURN 반환타입;
 
PROCEDURE 프로시저명 (매개변수1 [IN, OUT, INOUT] 매개변수1타입, ...);
 
END 패키지명;

패키지 본문

CREATE OR REPLACE PACKAGE BODY 패키지명
IS
상수명 CONSTANT    상수타입;
변수명 변수타입;
커서 정의 구문;
 
FUNCTION 함수명 (매개변수1 IN 매개변수1타입, 매개변수2 IN 매개변수2타입, ...) RETURN 반환타입
IS
...
BEGIN
...
END 함수명;
 
PROCEDUER 프로시저명 (매개변수1 [IN, OUT, INOUT] 매개변수1타입, ...)
IS
...
BEGIN
...
END 프로시저명;
 
...
 
END 패키지명;

예제

패키지 선언

CREATE OR REPLACE PACKAGE HR_PKG
IS
    -- 사번을 받아 이름을 반환하는 함수
    FUNCTION FN_GET_EMP_NAME (PN_EMPLOYEE_ID IN NUMBER)
    RETURN VARCHAR2;
    -- 신규 사원 입력 프로시저
    PROCEDURE NEW_EMP_PROC (PS_EMP_NAME IN VARCHAR2, PD_HIRE_DATE IN VARCHAR2);
    -- 퇴사 사원 처리 프로시저
    PROCEDURE RETIRE_EMP_PROC (PN_EMPLOYEE_ID IN NUMBER);
END HR_PKG;

패키지 본문

CREATE OR REPLACE PACKAGE BODY HR_PKG IS
 
  -- 사번을 받아 이름을 반환하는 함수
  FUNCTION FN_GET_EMP_NAME ( PN_EMPLOYEE_ID IN NUMBER )
     RETURN VARCHAR2
  IS
    VS_EMP_NAME EMPLOYEES.EMP_NAME%TYPE;
  BEGIN
    -- 사원명을 가져온다. 
    SELECT EMP_NAME
      INTO VS_EMP_NAME
      FROM EMPLOYEES
     WHERE EMPLOYEE_ID = PN_EMPLOYEE_ID;
     
    -- 사원명 반환
    RETURN NVL(VS_EMP_NAME, '해당사원없음');
  
  END FN_GET_EMP_NAME;
     
  -- 신규 사원 입력  
  PROCEDURE NEW_EMP_PROC ( PS_EMP_NAME   IN VARCHAR2, 
                           PD_HIRE_DATE  IN VARCHAR2)
  IS
    
    VN_EMP_ID    EMPLOYEES.EMPLOYEE_ID%TYPE;
    VD_HIRE_DATE DATE := TO_DATE(PD_HIRE_DATE, 'YYYY-MM-DD');
  BEGIN
     -- 신규사원의 사번 = 최대 사번+1 
     SELECT NVL(MAX(EMPLOYEE_ID),0) + 1
       INTO VN_EMP_ID
       FROM EMPLOYEES;
  
    
    INSERT INTO EMPLOYEES (EMPLOYEE_ID, EMP_NAME,HIRE_DATE, CREATE_DATE, UPDATE_DATE)
                   VALUES (VN_EMP_ID, PS_EMP_NAME, NVL(VD_HIRE_DATE,SYSDATE), SYSDATE, SYSDATE );
                   
    COMMIT;
    
    EXCEPTION WHEN OTHERS THEN
              DBMS_OUTPUT.PUT_LINE(SQLERRM);
              ROLLBACK;               
  
  
  END NEW_EMP_PROC;
  
                               
  -- 퇴사 사원 처리                          
  PROCEDURE RETIRE_EMP_PROC ( PN_EMPLOYEE_ID IN NUMBER )
  IS
    VN_CNT NUMBER := 0;
    E_NO_DATA    EXCEPTION;
  BEGIN
    -- 퇴사한 사원은 사원테이블에서 삭제하지 않고 일단 퇴사일자(RETIRE_DATE)를 NULL에서 갱신한다.
    UPDATE EMPLOYEES
       SET RETIRE_DATE = SYSDATE
     WHERE EMPLOYEE_ID = PN_EMPLOYEE_ID
       AND RETIRE_DATE IS NULL;
       
    -- UPDATE된 건수를 가져온다.    
    VN_CNT := SQL%ROWCOUNT;
    
    -- 갱신된 건수가 없으면 사용자 예외처리 
    IF VN_CNT = 0 THEN 
       RAISE E_NO_DATA;
    END IF;
    
    COMMIT;
    
    EXCEPTION WHEN E_NO_DATA THEN
                   DBMS_OUTPUT.PUT_LINE (PN_EMPLOYEE_ID || '에 해당되는 퇴사처리할 사원이 없습니다!');
                   ROLLBACK;
              WHEN OTHERS THEN
                   DBMS_OUTPUT.PUT_LINE (SQLERRM);
                   ROLLBACK;              
  
  
  END RETIRE_EMP_PROC;
  
END  HR_PKG; 

실행

SELECT  HR_PKG.FN_GET_EMP_NAME(171)
FROM    DUAL;

EXEC HR_PKG.NEW_EMP_PROC('CRISTIANO RONALDO', '2014-01-10');
 
SELECT  *
FROM    EMPLOYEES
WHERE   EMP_NAME LIKE 'CR%';