DBMS/오라클] 프로 시저 대신 패키지를 써야 하는 이유

출처 : http://mt1716.egloos.com/9176977

프로 시저 대신 패키지를 써야 하는 이유

오라클 패키지의 마법을 풀어봅시다 - 프로시저 대신 패키지를 써야 하는 이유

Advanced Oracle 2007/07/29 02:00

많은 오라클 전문가들이 프로시저대신 패키지를 사용할 것을 권장한다. 특히 패키지를 만든 오라클 사람들이...
하지만, 왜 그럴까? 많은 사람들이 이 사실을 모르고, 심지어 프로시저를 사용하면 되는데 패키지가 무슨 필요? 라며 잘못된 견해를 전파한다.
프로시저가 아닌패키지를 사용해야 하는 이유는, 결론부터 말하면 패키지의 향상된 의존성(Dependency)관리때문이다.
아래 간단한 패키지와 프로시저가 있다. 이 둘의 기능(하는 일)은 완전히 동일하다. 다만 하나는 패키지로 구현되어 있고, 다른 하나의 프로시저로 구현되어 있을 뿐이다.
-- 패키지
create or replace package pkgtest as
procedure pkgtest_proc(v_id int);
end pkgtest;
/
create or replace package body pkgtest as
procedure pkgtest_proc(v_id int)
is
v_name varchar2(1);
begin
select name into v_name from pkgtest_table;
end;
end pkgtest;
/
-- 프로시저
create or replace procedure nopkg_proc(v_id int)
is
v_name varchar2(1);
begin
select name into v_name from pkgtest_table;
end;
/
여기서 주목해야 할 것은 pkgtest 패키지와 nopkg_proc 프로시저가 모두 pkgtest_table에 대해 의존성(Dependency)를 가지고 있다는 사실이다. 여기에서 간혹 심각한 문제가 발생한다.
Pkgtest_table에 대해 DDL을 수행하게 되면 이 테이블을 참조하고 있는 모든 객체에 대해 무효화(Invalidation)가 수행된다.아래 스크립트를 보자
-- pkgtest_table에 대해 의존성을 가지는 패키지와 프로시저가 Valid 상태이다.
SQL> select object_name,object_type, status
from dba_objects where object_name in ('PKGTEST', 'NOPKG_PROC');

OBJECT_NAM OBJECT_TYPE STATUS
---------- -------------------- --------------
NOPKG_PROCPROCEDURE VALID
PKGTEST PACKAGE VALID
PKGTEST PACKAGE BODYVALID
-- Pkgtest_table에 대해 Alter를 수행하면?
SQL> alter table pkgtest_table add name2 varchar(1);

OBJECT_NAMOBJECT_TYPE STATUS
------------------------------ --------------
NOPKG_PROCPROCEDURE INVALID
PKGTEST PACKAGE VALID
PKGTESTPACKAGE BODYINVALID
위의 결과에서 다음과 같은 재밌는 사실을 발견할 수 있다.

• NOPKG_PROC 프로시저는 기대했던 대로 INVALID 상태가 되었음을 알 수 있다.
• PKGTEST 패키지는 좀 특이한다.
  • PKGTEST 패키지 바디(body)기대했던 대로 INVALID 상태가 되었음을 알 수 있다.
  • 반면 PKGTEST 패키지 자체는 놀랍게도 여전히 VALID 상태이다.

패 키지의 이러한 특징을 가리켜 흔히 "패키지는 의존성 체인을 깬다"라는 표현을 사용한다. 위의 예를 들면 pkgtest_table이 변경됨으로써 pkgtest 패키지가 무효화될 위기임에도 불구하고 중간에 패키지 바디라는 중간 객체만이 무효화되고 패키지 자체는 무효화되지 않는다.
이 패키지의 특정, 즉 의존성 체인을 깨는 특징이 왜 그렇게 중요할까? 그 이유는 하드 파싱과 관련이 있다.
만일 수십 개의 프로시저가 이 pkgtest_table에 대해 의존성을 가지는 상태에서 운영상의 이유로 pkgtest_table을 Alter했다고 가정해보자. 이수십 개의 프로시저가 모두 INVALID 상태가 될 것이고, 따라서 이 프로시저들을 수행하는 모든 쿼리는 재컴파일이 이루어져 한다. 붐~! 아마 library cache pin이라는 이름의 대기 현상의 증가하면서 자칫 시스템 장애를 불러일으킬 수 있다.
하지만 프로시저가 아닌 패키지로 되어 있었다면? 다행히 패키지 자체는 여전히 VALID 상태이이 때문에 이 패키지들을 사용하는 모든 쿼리 또한 재사용이 가능하 다. 실행 시점에 패키지 바디만 리컴파일해주면 된다. 옙!! 여러분은 방금 시스템 장애로부터 사장님을 구한 셈이다.
이것을 증명하기 위해 pkgtest_table을 Alter한 후, 다음과 같이 패키지와 프로시저의 수행 결과를 SQL Trace를 이용해 분석해보자.
-- 패키지를 참고하는 쿼리 문장은 하드 파싱을 수행하지 않는다.(즉, Library cache Miss가 발생하지 않으며 리컴파일또한 수행하지 않는다)
BEGIN pkgtest.pkgtest_proc(1); END;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.01 0 0 1 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.01 0 0 1 1
Misses in library cache during parse: 0 <-- 여기를 주목하세요!!Optimizer mode: ALL_ROWS
Parsing user id: 55
-- 하지만 프리시저를 사용하는 하드파싱을 수행한다.(즉, Library cache miss가 발생하고 리컴파일을 수행한다)
BEGIN nopkg_proc(1); END;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 1 0
Execute 1 0.00 0.00 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.01 0 0 1 1
Misses in library cache during parse: 1 <-- 여기를 주목. 모든 악의 근원!!!Optimizer mode: ALL_ROWS
Parsing user id: 55
왜 오라클이 굳이 패키지라는 복잡한 개념을 구현했는지 이해가 되는가? 프로그래밍의 간편함과 더불어 쿼리 재사용성 증가라는 탁월한 효과를 얻을 수 있기 때문이다.
이제 입질이 슬슬 오는가...?
PS)
SQL Server 2005 에서는 Statement Level의 Recompile을 지원함으로써 오라클의 패키지와 동일한 효과를 제공한다. 자세한 내용은 나중에...
하드파싱(리컴파일)에 의한 library cache pin 대기 현상에 대해서는 아래 필자의 책 참고~~

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);