패키지 사용
이제 실제로 패키지를 만들어 사용해 보자. 지금까지 많은 예제에서 사용했던 사원, 부서 테이블을 대상으로 비즈니스 로직을 처리하는 일련의 함수와 프로시저를 만들어 보자. 패키지의 이름을 어떻게 짓느냐는 함수나 프로시저와 마찬가지로 만드는 사람 마음이지만, 일반적으로 맨 앞에 ‘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,
pd_hire_date IN VARCHAR2 );
-- 퇴사 사원 처리
PROCEDURE retire_emp_proc ( pn_employee_id IN NUMBER );
END hr_pkg;
결과
PACKAGE 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_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_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;
결과
PACKAGE BODY HR_PKG이(가) 컴파일되었습니다.
패키지 본문 역시 성공적으로 컴파일되었다. 이제 패키지를 사용해 보자. 패키지는 “패키지명.서브프로그램명” 형태로 사용한다. 먼저 사원명을 가져오는 함수를 호출해 보자.
입력
SELECT hr_pkg.fn_get_emp_name (171)
FROM DUAL;
결과
HR_PKG.FN_GET_EMP_NAME(171)
------------------------------
William Smith
이번에는 신규로 사원을 등록해 보자. 신규사원을 입력하려면 사원명과 입사일자가 필요한데 입사일자를 누락하면 현재일자로 입력된다.
입력
EXEC hr_pkg.new_emp_proc ('Julia Roberts', '2014-01-10');
결과
익명 블록이 완료되었습니다.
실제로 입력됐는지 확인해 보자.
입력
SELECT employee_id, emp_name, hire_date, retire_date
FROM employees
WHERE emp_name like 'Julia R%';
결과
EMPLOYEE_ID EMP_NAME HIRE_DATERETIRE_DATE
----------- ---------------- ----------------------
207 Julia Roberts 2014/01/10
사번이 207번으로 신규로 입력되었음을 확인할 수 있다. 이제 다시 이 사원을 퇴사처리해 보자. 퇴사 처리를 하면 해당 사원 데이터를 삭제하는 것이 아니라 퇴사일자( retire_date) 값을 갱신한다.
입력
EXEC hr_pkg.retire_emp_proc (207);
결과
익명 블록이 완료되었습니다.
입력
SELECT employee_id, emp_name, hire_date, retire_date
FROM employees
WHERE emp_name like 'Julia R%';
결과
EMPLOYEE_ID EMP_NAME HIRE_DATERETIRE_DATE
----------- -------------- -------------------------
207 Julia Roberts 2014/01/10 2015-03-03