오라클 패키지와 함수로 SQL 확장하기

데이터베이스 관리 시스템(DBMS)은 단순 데이터 저장소를 넘어, 복잡한 로직 처리를 지원하기 위해 다양한 확장 기능을 제공합니다. 이 중에서 패키지와 사용자 정의 함수는 SQL 기능을 확장하고, 코드 재사용성을 높이며, 성능을 향상시키는 핵심 도구입니다. 이번 포스팅에서는 패키지와 함수의 개념, 작성 방법, 실무 활용 예제를 통해 이 기능들을 효과적으로 사용하는 방법을 알아보겠습니다.

---

1. 패키지란 무엇인가?

**패키지(Package)**는 PL/SQL에서 제공하는 기능으로, 서로 연관된 프로시저, 함수, 변수, 커서 등을 하나로 묶어 모듈화한 객체입니다. 패키지를 사용하면 관련된 로직을 한 곳에 모아 관리할 수 있어 코드의 가독성, 유지보수성, 재사용성을 크게 향상시킵니다.

패키지 구성 요소:

1. 패키지 사양 (Specification): 패키지의 인터페이스를 정의하며, 외부에서 호출 가능한 프로시저와 함수를 선언합니다.
2. 패키지 본문 (Body): 패키지 사양에 정의된 프로시저와 함수의 실제 구현을 포함합니다.

패키지 작성 예제:

패키지 사양:

CREATE OR REPLACE PACKAGE employee_pkg AS
    PROCEDURE update_salary(emp_id NUMBER, new_salary NUMBER);
    FUNCTION get_department_name(dept_id NUMBER) RETURN VARCHAR2;
END employee_pkg;
/

패키지 본문:

CREATE OR REPLACE PACKAGE BODY employee_pkg AS
    -- 직원 급여 업데이트 프로시저
    PROCEDURE update_salary(emp_id NUMBER, new_salary NUMBER) IS
    BEGIN
        UPDATE employees
        SET salary = new_salary
        WHERE employee_id = emp_id;
    END update_salary;

    -- 부서 이름 반환 함수
    FUNCTION get_department_name(dept_id NUMBER) RETURN VARCHAR2 IS
        dept_name VARCHAR2(100);
    BEGIN
        SELECT department_name
        INTO dept_name
        FROM departments
        WHERE department_id = dept_id;
        RETURN dept_name;
    END get_department_name;
END employee_pkg;
/

패키지 사용 예제:

BEGIN
    -- 급여 업데이트
    employee_pkg.update_salary(101, 7000);

    -- 부서 이름 조회
    DBMS_OUTPUT.PUT_LINE(employee_pkg.get_department_name(10));
END;
/

---

2. 사용자 정의 함수란 무엇인가?

**사용자 정의 함수(User-Defined Function)**는 SQL 쿼리에서 사용할 수 있는 사용자 지정 기능을 제공합니다. 복잡한 계산이나 데이터 변환이 필요할 때 간단하게 재사용할 수 있습니다.

기본 작성 형식:

CREATE OR REPLACE FUNCTION function_name 
(parameter_name datatype, ...)
RETURN return_datatype IS
    -- 변수 선언
BEGIN
    -- 로직 구현
    RETURN result_value;
END;
/

사용자 정의 함수 작성 예제:

문자열 포맷팅 함수:

CREATE OR REPLACE FUNCTION format_name(first_name VARCHAR2, last_name VARCHAR2)
RETURN VARCHAR2 IS
BEGIN
    RETURN INITCAP(last_name) || ', ' || INITCAP(first_name);
END;
/

사용 예제:

SELECT employee_id, format_name(first_name, last_name) AS formatted_name
FROM employees;

---

3. 패키지와 함수의 실무 활용

예제 1: 급여 계산 패키지
직원의 급여를 계산하고, 특정 급여 조건에 따른 데이터를 필터링하는 패키지입니다.

패키지 사양:

CREATE OR REPLACE PACKAGE salary_pkg AS
    FUNCTION calculate_bonus(salary NUMBER, bonus_rate NUMBER) RETURN NUMBER;
    FUNCTION is_high_salary(salary NUMBER, threshold NUMBER) RETURN BOOLEAN;
END salary_pkg;
/

패키지 본문:

CREATE OR REPLACE PACKAGE BODY salary_pkg AS
    -- 보너스 계산 함수
    FUNCTION calculate_bonus(salary NUMBER, bonus_rate NUMBER) RETURN NUMBER IS
    BEGIN
        RETURN salary * bonus_rate;
    END calculate_bonus;

    -- 높은 급여 여부 판단 함수
    FUNCTION is_high_salary(salary NUMBER, threshold NUMBER) RETURN BOOLEAN IS
    BEGIN
        RETURN salary > threshold;
    END is_high_salary;
END salary_pkg;
/

사용 예제:

SELECT employee_id, 
       salary_pkg.calculate_bonus(salary, 0.1) AS bonus
FROM employees
WHERE salary_pkg.is_high_salary(salary, 5000);

예제 2: 고객 등급 관리 함수
고객의 구매 금액에 따라 등급을 반환하는 함수입니다.

CREATE OR REPLACE FUNCTION get_customer_grade(purchase_amount NUMBER) 
RETURN VARCHAR2 IS
BEGIN
    IF purchase_amount >= 10000 THEN
        RETURN 'VIP';
    ELSIF purchase_amount >= 5000 THEN
        RETURN 'Gold';
    ELSE
        RETURN 'Silver';
    END IF;
END;
/

사용 예제:

SELECT customer_id, 
       get_customer_grade(total_purchase) AS grade
FROM customers;

---

4. 패키지와 함수 사용 시 주의사항

1. 성능 고려
   패키지와 함수는 유연하지만, 반복 호출 시 성능 저하가 발생할 수 있습니다. 특히, SQL 내에서 반복적으로 호출되는 함수는 PL/SQL과 SQL 간의 컨텍스트 전환 비용이 발생합니다.
2. 재사용성 극대화
   패키지와 함수는 재사용 가능한 코드를 작성하기 위한 도구입니다. 따라서, 특정 테이블에 국한되지 않고 다양한 테이블에서 사용할 수 있도록 설계하는 것이 좋습니다.
3. 에러 처리
   패키지와 함수 내에서 예외 상황을 처리하는 로직을 포함해야 예상치 못한 오류로 인한 시스템 장애를 방지할 수 있습니다.
4. 보안
   패키지와 함수에 대한 접근 권한을 관리하여, 불필요한 데이터 접근을 방지하고 시스템 안정성을 유지해야 합니다.

---

5. 결론

패키지와 함수는 SQL의 기능을 확장하여 복잡한 로직을 효과적으로 처리할 수 있도록 돕는 도구입니다. 이를 활용하면 코드의 재사용성을 높이고, 데이터베이스 중심의 애플리케이션 개발이 한층 더 유연해질 수 있습니다. 실무에서 자주 사용되는 패턴을 익히고, 요구사항에 맞는 맞춤형 SQL 확장 솔루션을 구현해 보세요!