Database package

오라클과 SQL과 PL/SQL을 다루는 기술 보면서 공부

 

- 패키지란?: 논리적 연관성이 있는 PL/SQL 타입, 변수, 상수, 서브 프로그램, 커서, 예외 등의 항목을 묶어 놓은 객체다. 패키지는 컴파일 과정을 거쳐 DB에 저장되며, 다른 프로그램(다른 패키지나 프로시저, 외부 프로그램)에서 패키지의 항목을 참조, 공유, 실행 할 수 있다.

 

- 함수, 프로시저 같은 서브 프로그램과 차별화된 패키지의 장점?

1) 모듈화 기능

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

패키지는 선언부(스펙)와 본문(바디), 두 부분으로 구성된다. 선언부는 패키지에서 사용할 각종 변수, 상수, 타입, 커서와 함수, 프로시저를 선언하는 부분이고, 본문은 함수와 프로시저를 구현한 부분이다. 그런데 패키지는 선언부만 있어도 컴파일한 뒤 저장이 가능하다. 즉 본문은 나중에 작성해도 된다는 뜻이다. 어떤 이유에서 이것이 장점이라는 걸까? 다음과 같은 상황을 가정해 보자.

프로젝트를 수행하는 팀이 여러 개인데, 그 중에 공통 모듈을 담당하는 팀이 있다고 해보자. 공통 모듈 팀은 다른 팀에서 사용할 수 있는 공통 테이블, 즉 마스터나 코드성 테이블을 관리하는 프로그램을 개발하고 있다. 이러한 테이블은 잘못 손대면 안 되기 때문에 공통팀만 접근 권한이 있다. 하지만 다른 팀에서도 이 테이블을 참조하고 조작할 필요가 있다. 그래서 공통 모듈 팀은 다른 팀을 위해 common이란 패키지에 code_proc란 프로시저를 만들기로 했다. 그런데 이 패키지가 완성이 되어야 다른 팀들도 이 패키지로 자신들의 패키지나 프로시저를 작성할 수 있는데, 아직 공통 모듈 팀에서는 설계만 한 상태로 구현은 시작하지도 못했다. 이때 common 패키지에서 code_proc 프로시저의 매개변수의 유형과 개수, 처리할 내용만 협의한 뒤 공통 모듈 팀에서 패키지 선언부만 일단 작성해 컴파일해 놓으면, 다른팀에서는 자신들이 작성하는 프로그램에서 common 패키지의 code_proc를 사용하더라도 컴파일 오류 없이 진도를 나갈 수 있다. 뿐만 아니라 나중에 프로시저의 구현부가 완성된 뒤에도 자신들이 작성한 패키지나 프로시저를 다시 컴파일할 필요가 없다.

3) 캡슐화

4) 보다 나은 성능

 

- 패키지 구조

패키지 선언부

패키지는 선언부와 본문으로 구성된다.

선언부는 스펙 혹은 명세라고도 하는데, 패키지에서 사용할 사용자 정의 타입, 변수, 상수, 예외, 그리고 서브 프로그램의 골격을 선언해 놓은 부분으로 그 구문은 다음과 같다.

패키지 선언부 구문

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

패키지 선언부는 크게 데이터와 서브 프로그램 영역으로 나눌 수 있다.

서브 프로그램에는 함수와 프로시저가 있는데, 패키지 선언부에서는 이 두 객체의 명세, 즉 서브 프로그램명과 매개변수를 명시한다.(함수는 반환타입까지 명시해야 한다.) 해당 패키지 소유자 외의 다른 사용자는 선언부의 내용만 볼 수 있지만, 사용하려는 함수나 프로시저에 대해 필요한 모든 정보(서브 프로그램명, 매개변수 개수와 타입, 반환 타입)를 참조할 수 있으므로 사용하는데 전혀 문제가 없다.

 

패키지 본문

패키지는 선언부만 있어도 컴파일한 뒤 사용할 수 있다. 하지만 선언부에서 커서나 서브 프로그램을 선언했다면 이들에 대한 구현 내용이 필요한데, 이를 패키지 본문에서 작성한다. 패키지 본문을 생성하는 구문은 다음과 같다.

패키지 본문 구문

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

패키지 본문에서도 선언부에서와 마찬가지로 상수, 변수 등을 선언할 수 있다. 패키지 본문에서 선언한 상수나 변수는 외부에서 참조할 수 없다. 그리고 선언부에서 커서나 서브 프로그램을 선언했다면 이들의 세부적인 구현 부분은 패키지 본문에서 작성한다. 패키지 본문의 내용은 패키지 작성자(소유자)만 접근해 수정할 수 있으며, 다른 사용자는 전혀 볼 수가 없다. 패키지 내의 함수나 프로시저의 구현 내용은 일반적인 함수나 프로시저를 생성할 때와 구문이 같다.

 

패키지 사용

일반적으로 맨 앞에 ‘pg’를 붙이거나 맨 끝에 ‘pkg’를 붙인다. 여기서는 사원과 관련된 내용을 처리하는 패키지이므로 hr_pkg라고 이름을 짓도록 하고, 이 패키지의 대략적 구조를 정리하면 다음과 같다.

패키지명: hr_pkg

-       Fn_get_emp_name -> 사번을 전달받아 이름을 반환하는 함수

-       New_emp_proc -> 신규사원을 등록하는 프로시저

-       Retire_emp_proc -> 퇴사한 사원을 처리하는 프로시저

Hr_pkg 패키지는 함수 1개와 프로시저 2개로 시작하지만, 나중에 사원에 관해 처리해야 할 서브 프로그램이 발생하더라도 새로 추가하기만 하면 된다. 사원과 관련된 내용을 처리하는 서브 프로그램들을 hr_pkg 패키지에 담아둠으로써, 이 패키지를 개발한 본인이나 다른 개발자들도 인사관련 업무 처리를 참조하려면 이 패키지만 참조하면 된다.

-- 패키지 -- hr_pkg의 선언부 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, hd_hire_dateIN VARCHAR2);   -- 퇴사 사원 처리 procedure retire_emp_proc(pn_employee_id in number);   end hr_pkg;

 

 

DECLARE emp employees.emp_name%TYPE; dep departments.department_name%TYPE; begin select a.emp_name, b.department_name into emp, dep from employees a, departments b where a.department_id = b.department_id and a.employee_id = 100;   dbms_output.put_line(emp ||'-' ||dep); end;   -- 패키지 -- hr_pkg의 선언부 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, hd_hire_dateIN VARCHAR2);   -- 퇴사 사원 처리 procedure retire_emp_proc(pn_employee_id in number);   end hr_pkg;

 

create or replace package body hr_pkg is   -- 사번을 받아 이름을 반환하는 함수 funtion fn_get_emp_name(pn_employee_id in number) return varchar2 is vs_emp_nameemployees.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 invarchar2) is vn_emp_idemployees.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_emmployee_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;

패키지 사용하는 방법 “패키지명.서브프로그램명” 형태로 사용한다.