BS Blog: DBMS/오라클] 패키지 문법

2012-10-06

DBMS/오라클] 패키지 문법

원본출처 : http://www.oracleclub.com/lecture/1075

Package(패키지)

패키지(package)는 오라클 데이터베이스에 저장되어 있는 서로 관련있는 PL/SQL 프로지져와 함수들의 집합 이다.

패키지는 선언부와 본문 두 부분으로 나누어 진다.

패키지 선언절 문법

- 선언절은 패키지에 포함될 PL/SQL 프로시저나, 함수, 커서, 변수, 예외절을 선언 한다.
- 패키지 선언부에서 선언한 모든 요소들은 패키지 전체에 적용 된다.
- 즉 선언부에서 선언한 변수는 PUBLIC 변수로 사용 된다.

패키지 본문 문법

- 패키지 본문은 패키지에서 선언된 부분의 실행을 정의 한다.
- 즉 실재 프로시져나 함수의 내용에 해당하는 부분이 온다.

아래는 네 개의 프로시저를 하나의 패키지로 생성하는 아주 간단한 예제이다.

프로시저명	프로시저 기능
all_emp_info	모든 사원의 사원 정보 (사번, 성명, 입사일)
all_sal_info	모든 사원의 급여 정보 (평균급여, 최고급여, 최소급여)
dept_emp_info	특정 부서의 사원 정보 (사번, 성명, 입사일)
dept_sql_info	특정 부서의 급여 정보 (평균급여, 최고급여, 최소급여)

패키지 선언부 생성 예제

 
SQL> CREATE OR REPLACE PACKAGE emp_info AS

       PROCEDURE all_emp_info;   -- 모든 사원의  사원 정보
        
        PROCEDURE all_sal_info;   -- 모든 사원의  급여 정보

        -- 특정 부서의  사원 정보
        PROCEDURE dept_emp_info (v_deptno IN  NUMBER) ;

        -- 특정 부서의  급여 정보
        PROCEDURE dept_sal_info (v_deptno IN  NUMBER) ;

    END emp_info;
    /

패키지 본문 생성 예제

 
SQL> CREATE OR REPLACE PACKAGE BODY emp_info AS

     -- 모든 사원의  사원 정보 
     PROCEDURE all_emp_info
     IS

         CURSOR emp_cursor IS
         SELECT empno, ename, to_char(hiredate, 'RRRR/MM/DD') hiredate
         FROM emp
         ORDER BY hiredate;

     BEGIN

         FOR  aa  IN emp_cursor LOOP

             DBMS_OUTPUT.PUT_LINE('사번 : ' || aa.empno);
             DBMS_OUTPUT.PUT_LINE('성명 : ' || aa.ename);
             DBMS_OUTPUT.PUT_LINE('입사일 : ' || aa.hiredate);

         END LOOP;

         EXCEPTION
           WHEN OTHERS THEN
             DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생 ');

     END all_emp_info;


     -- 모든 사원의  급여 정보 
     PROCEDURE all_sal_info
     IS
    
         CURSOR emp_cursor IS
         SELECT round(avg(sal),3) avg_sal, max(sal) max_sal, min(sal) min_sal
         FROM emp;
    
     BEGIN

         FOR  aa  IN emp_cursor LOOP
 
             DBMS_OUTPUT.PUT_LINE('전체급여평균 : ' || aa.avg_sal);
             DBMS_OUTPUT.PUT_LINE('최대급여금액 : ' || aa.max_sal);
             DBMS_OUTPUT.PUT_LINE('최소급여금액 : ' || aa.min_sal);
         
         END LOOP;


         EXCEPTION
            WHEN OTHERS THEN
               DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생 ');
     END all_sal_info;


     --특정 부서의  사원 정보
     PROCEDURE dept_emp_info (v_deptno IN  NUMBER)
     IS

         CURSOR emp_cursor IS
         SELECT empno, ename, to_char(hiredate, 'RRRR/MM/DD') hiredate
         FROM emp
         WHERE deptno = v_deptno
         ORDER BY hiredate;

     BEGIN

         FOR  aa  IN emp_cursor LOOP

             DBMS_OUTPUT.PUT_LINE('사번 : ' || aa.empno);
             DBMS_OUTPUT.PUT_LINE('성명 : ' || aa.ename);
             DBMS_OUTPUT.PUT_LINE('입사일 : ' || aa.hiredate);

         END LOOP;

        EXCEPTION
            WHEN OTHERS THEN
               DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생 ');

     END dept_emp_info;


     --특정 부서의  급여 정보
     PROCEDURE dept_sal_info (v_deptno IN  NUMBER)
     IS
    
         CURSOR emp_cursor IS
         SELECT round(avg(sal),3) avg_sal, max(sal) max_sal, min(sal) min_sal
         FROM emp 
         WHERE deptno = v_deptno;
             
     BEGIN

         FOR  aa  IN emp_cursor LOOP 
 
             DBMS_OUTPUT.PUT_LINE('전체급여평균 : ' || aa.avg_sal);
             DBMS_OUTPUT.PUT_LINE('최대급여금액 : ' || aa.max_sal);
             DBMS_OUTPUT.PUT_LINE('최소급여금액 : ' || aa.min_sal);
         
         END LOOP;

         EXCEPTION
             WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생 ');

     END dept_sal_info;        
    
  END emp_info;
  /

패키지 실행

패키지의 실행은 패키지 명 다음에 점(.)을 찍고 프로시저냐 함수 명을 적어주면 된다.

 
-- DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용
SQL> SET SERVEROUTPUT ON ; 

-- 패키지 실행
SQL> EXEC emp_info.all_emp_info;

SQL> EXEC emp_info.all_sal_info;

SQL> EXEC emp_info.dept_emp_info(10);

SQL> EXEC emp_info.dept_sal_info(10);

BS Blog