PL/SQL 하위 프로그램 및 패키지 개요

오라클 데이터베이스에서 PL/SQL은 강력한 애플리케이션 개발의 핵심 요소입니다. 쿼리 최적화 및 데이터베이스 성능 향상을 위해 PL/SQL 하위 프로그램과 패키지를 효과적으로 활용하는 방법을 상세히 살펴보겠습니다.

PL/SQL 하위 프로그램: 기능 및 이점

PL/SQL 하위 프로그램은 특정 작업을 수행하는 명명된 PL/SQL 블록입니다. 하위 프로그램은 함수(FUNCTION)와 프로시저(PROCEDURE) 두 가지 주요 유형으로 나뉩니다.

  • 함수 (FUNCTION): 값을 반환하는 하위 프로그램. 주로 계산 또는 데이터 변환에 사용됩니다.
  • 프로시저 (PROCEDURE): 특정 작업을 수행하지만 반드시 값을 반환하지는 않는 하위 프로그램. 데이터 조작, 트랜잭션 관리 등에 사용됩니다.

이점:

  • 모듈화: 코드를 작은 모듈로 분할하여 가독성과 유지보수성을 향상시킵니다.
  • 재사용성: 여러 번 반복되는 코드를 하위 프로그램으로 만들어 코드 중복을 줄입니다.
  • 성능 향상: PL/SQL 엔진 내에서 실행되므로 네트워크 오버헤드를 줄여줍니다.
  • 보안 강화: 데이터 접근 권한을 제한하여 무단 접근을 방지합니다.

PL/SQL 패키지: 모듈화 및 캡슐화

PL/SQL 패키지는 PL/SQL 유형, 변수, 상수, 하위 프로그램 및 커서와 같은 관련 PL/SQL 구성 요소를 논리적으로 그룹화하는 스키마 객체입니다. 패키지는 명세부(specification)와 본체부(body)로 구성됩니다.

  • 명세부 (Specification): 패키지의 공개 인터페이스를 정의합니다. 외부에서 접근 가능한 유형, 변수, 하위 프로그램 등을 선언합니다.
  • 본체부 (Body): 하위 프로그램의 실제 구현을 포함합니다. 외부에서는 명세부에 정의된 인터페이스를 통해서만 접근할 수 있습니다.

이점:

  • 모듈화 및 캡슐화: 관련 코드를 논리적으로 그룹화하여 코드의 구조를 개선하고 유지보수성을 높입니다.
  • 정보 은닉: 구현 세부 사항을 숨기고 공개 인터페이스만 제공하여 코드의 복잡성을 줄입니다.
  • 보안 강화: 패키지 내에서 데이터 접근 권한을 제어하여 무단 접근을 방지합니다.
  • 성능 향상: 한 번 로드된 패키지는 세션 동안 메모리에 유지되므로 반복적인 컴파일 시간을 절약합니다.

실무 예제

다음 예제는 급여를 업데이트하고 감사 로그를 기록하는 PL/SQL 패키지를 보여줍니다.


CREATE OR REPLACE PACKAGE emp_mgmt AS
 TYPE emp_rec IS RECORD (
 employee_id NUMBER,
 salary NUMBER
 );

 FUNCTION get_employee(p_emp_id IN NUMBER) RETURN emp_rec;
 PROCEDURE update_salary (p_emp_id IN NUMBER, p_new_salary IN NUMBER);
END emp_mgmt;
/

CREATE OR REPLACE PACKAGE BODY emp_mgmt AS

 FUNCTION get_employee(p_emp_id IN NUMBER) RETURN emp_rec IS
 v_emp_rec emp_rec;
 BEGIN
 SELECT employee_id, salary INTO v_emp_rec
 FROM employees
 WHERE employee_id = p_emp_id;
 RETURN v_emp_rec;
 EXCEPTION
 WHEN NO_DATA_FOUND THEN
 RETURN NULL;
 END get_employee;

 PROCEDURE update_salary (p_emp_id IN NUMBER, p_new_salary IN NUMBER) IS
 old_salary NUMBER;
 BEGIN
 SELECT salary INTO old_salary FROM employees WHERE employee_id = p_emp_id;

 UPDATE employees SET salary = p_new_salary WHERE employee_id = p_emp_id;

 INSERT INTO audit_log (employee_id, old_salary, new_salary, update_date)
 VALUES (p_emp_id, old_salary, p_new_salary, SYSDATE);

 COMMIT;
 EXCEPTION
 WHEN OTHERS THEN
 ROLLBACK;
 RAISE;
 END update_salary;

END emp_mgmt;
/

주요 구성 요소 설명:

  • emp_mgmt: 패키지 이름
  • emp_rec: 직원 정보를 저장하는 레코드 유형
  • get_employee: 직원 ID를 받아 직원 정보를 반환하는 함수
  • update_salary: 직원 급여를 업데이트하고 감사 로그를 기록하는 프로시저

코드 실행 및 결과


-- 직원 정보 조회
DECLARE
 emp_info emp_mgmt.emp_rec;
BEGIN
 emp_info := emp_mgmt.get_employee(100);
 IF emp_info IS NOT NULL THEN
 DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_info.employee_id || ', Salary: ' || emp_info.salary);
 END IF;
END;
/

-- 급여 업데이트 및 로그 기록
BEGIN
 emp_mgmt.update_salary(100, 12000);
END;
/

위 코드를 실행하면 다음과 같은 결과가 나타날 수 있습니다.

직원 정보 조회 결과:

Employee ID: 100, Salary: 24000

감사 로그 테이블 확인 결과:

SELECT employee_id, old_salary, new_salary FROM audit_log;

EMPLOYEE_ID OLD_SALARY NEW_SALARY
----------- ---------- ----------
 100 24000 12000

쿼리 튜닝을 위한 하위 프로그램 및 패키지 활용

쿼리 튜닝은 데이터베이스 성능을 최적화하는 중요한 과정입니다. PL/SQL 하위 프로그램과 패키지를 이용하여 쿼리 성능을 효과적으로 향상시킬 수 있습니다.

  • 인덱스 활용: 쿼리 성능을 향상시키기 위해 테이블에 적절한 인덱스를 생성합니다.
  • 쿼리 재작성: 불필요한 연산을 제거하고 쿼리 구조를 최적화합니다.
  • 힌트 사용: 옵티마이저에게 특정 실행 계획을 유도하는 힌트를 사용합니다.

결론

PL/SQL 하위 프로그램과 패키지는 오라클 데이터베이스 개발에 필수적인 요소입니다. 코드를 모듈화하고 재사용성을 높이며, 성능을 향상시키고 보안을 강화하는 데 기여합니다. 예제 코드를 통해 PL/SQL 하위 프로그램과 패키지를 이해하고 활용함으로써 데이터베이스 애플리케이션의 효율성을 극대화할 수 있습니다.