[ORACLE] 패키지 221202-03

😀 패키지(PACKAGE)

  - 패키지는 논리적 관련성이 있는 PL/SQL 변수,상수,커서,서브프로그램 등을 묶어 놓은 객체
  - 컴파일되어 저장되며 다른 프로그램에서 패키지의 항목을 참조,공유, 실행할 수 있음
  - 모듈화 기능제공
  - 프로그램 설계의 용이성 제공:선언부와 실행부를 서로 다른 프로그램으로 구성
  - 캡슐화 기능제공-정보의 은닉성이 확보

 1)패키지의 구조
  - 패키지는 선언부와 본문으로 구성됨

  1)선언부
   - 패키지의 명세(Specification)
   - 패키지에 사용할 변수,상수,커서,서브프로그램의 선언부 만으로 구성 --서브프로그램:트리거,프로시져,함수 등등
   - 자바의 interface와 유사

(사용형식)
    CREATE [OR REPLACE] PACKAGE 패키지명 IS
     변수,상수,커서 등의 선언;
     
     FUNCTION 함수명(
       매개변수 IN|OUT|INOUT 데이터타입[:=default],
               :
       매개변수 IN|OUT|INOUT 데이터타입[:=default])
       RETURN 타입명;
       
      PROCEDURE 프로시져명 
       매개변수 IN|OUT|INOUT 데이터타입[:=default],
               :
       매개변수 IN|OUT|INOUT 데이터타입[:=default])
       
   END [패키지명];

1)본문부
   - 패키지에 사용할 변수,상수,커서,서브프로그램으로 구성
   - 세부적인 구현부분 포함
   - 패키지의 본문은 패키지의 소유자만 접근하여 수정 가능

(사용형식)
    CREATE [OR REPLACE] PACKAGE BODY 패키지명 IS
     변수,상수,커서 등의 선언;
     
     FUNCTION 함수명(
       매개변수 IN|OUT|INOUT 데이터타입[:=default],
               :
       매개변수 IN|OUT|INOUT 데이터타입[:=default])
       RETURN 타입명;
     IS
     
     BEGIN
        실행부
        RETURN expr;
     END 함수명; 
       
      PROCEDURE 프로시져명(
       매개변수 IN|OUT|INOUT 데이터타입[:=default],
               :
       매개변수 IN|OUT|INOUT 데이터타입[:=default])
     IS
     
     BEGIN
     
     END 프로시져명;
       
   END [패키지명];

사용예)EMP 테이블에서 사원번호를 입력받아 사원정보(이름, 부서명, 직무코드)를 조회하는 함수,
      신입사원정보를 저장하는 프로시져, 퇴직자를 처리하는 프로시져를 포함하는 패키지를 hrd_pkg로 구성하시오.

(선언부)
    CREATE OR REPLACE PACKAGE hrd_pkg IS
    
    -- 사원번호를 입력받아 사원정보(이름, 부서명, 직무코드)를 조회하는 함수
    FUNCTION fn_getinfo_emp(
        p_eid IN EMP.EMP_ID%TYPE)
        RETURN VARCHAR2;
        
    -- 신입사원정보를 저장하는 프로시져.
    PROCEDURE new_emp_proc(

        p_pname IN EMP.EMP_NAME%TYPE,

        p_did IN EMP.DEPT_ID%TYPE);
        
        
    -- 퇴직자를 처리하는 프로시져
    PROCEDURE retire_emp_proc(
     p_pid IN EMP.EMP_ID%TYPE);
     
  END hrd_pkg;

(본문부)
    CREATE OR REPLACE PACKAGE BODY hrd_pkg IS
    
    -- 사원번호를 입력받아 사원정보(이름, 부서명, 직무코드)를 조회하는 함수
    FUNCTION fn_getinfo_emp(
        p_eid IN EMP.EMP_ID%TYPE)
        RETURN VARCHAR2
       IS
         L_ENAME EMP.EMP_NAME%TYPE;
         L_DNAME DEPARTMENTS.DEPARTMENT_NAME%TYPE;
         L_JID EMP.JOB_ID%TYPE;
         L_RES VARCHAR2(500); -- 반환할 정보 저장
       BEGIN
         SELECT A.EMP_NAME,B.DEPARTMENT_NAME,A.JOB_ID INTO L_ENAME,L_DNAME,L_JID
           FROM EMP A, DEPARTMENTS B
          WHERE A.EMP_ID=p_eid
            AND A.DEPT_ID=B.DEPARTMENT_ID; --조인
          L_RES:=p_eid||' '||L_ENAME||' '||L_DNAME||' '||L_JID;  
          RETURN L_RES;
       END fn_getinfo_emp;
        
    -- 신입사원정보를 저장하는 프로시져.
    PROCEDURE new_emp_proc(
        p_pname IN EMP.EMP_NAME%TYPE,
        p_did IN EMP.DEPT_ID%TYPE)
        IS
          L_EID EMP.EMP_ID%TYPE;
        BEGIN
          SELECT MAX(EMP_ID)+1 INTO L_EID
            FROM EMP;
            
           INSERT INTO EMP(EMP_ID,EMP_NAME,HIRE_DATE,DEPT_ID)
            VALUES(L_EID, p_pname, SYSDATE, p_did);
            
            COMMIT;
            
        END new_emp_proc;
        
        
    -- 퇴직자를 처리하는 프로시져
    PROCEDURE retire_emp_proc(
     p_pid IN EMP.EMP_ID%TYPE)
     IS
        L_NAME EMP.EMP_NAME%TYPE;
        L_HDATE DATE;
        L_JID JOBS.JOB_ID%TYPE;
        L_DID EMP.DEPT_ID%TYPE;
        L_CNT NUMBER:=0;
        L_NO_DATA EXCEPTION; 
     BEGIN
        SELECT COUNT(*) INTO L_CNT
          FROM EMP
         WHERE EMP_ID=p_pid;
         
         IF L_CNT != 0 THEN
            SELECT EMP_NAME, HIRE_DATE, JOB_ID, DEPT_ID 
            INTO L_NAME, L_HDATE, L_JID, L_DID
            FROM EMP
            WHERE EMP_ID=p_pid; 
           
           INSERT INTO RETIRE VALUES (p_pid, L_NAME, L_HDATE, L_JID, L_DID);
           
           DELETE FROM EMP WHERE EMP_ID=p_pid;
           
           
         ELSE
           RAISE L_NO_DATA;
          END IF; 
          
          COMMIT;
          
          EXCEPTION WHEN L_NO_DATA THEN
           DBMS_OUTPUT.PUT_LINE(p_pid||'사원정보가 없습니다. ');
           ROLLBACK;
           
     END retire_emp_proc;

     
  END hrd_pkg;

[실행]  
  SELECT EMP_ID AS 사원번호,
         HRD_PKG.FN_GETINFO_EMP(EMP_ID) AS 사원정보
    FROM EMP
   WHERE DEPT_ID = 60; 

[실행] 신규사원등록
  사원명 =>'홍길순', 부서코드=>100
  EXECUTE hrd_pkg.new_emp_proc('홍길순',100);
  
[실행] 퇴직자처리
  사원번호 => 145  
  EXECUTE hrd_pkg.retire_emp_proc(145);