오라클 프로시저의 작성법과 다양한 유형의 예제

오라클 데이터베이스에서 프로시저(Procedure)는 SQL과 PL/SQL을 활용하여 작성된, 이름을 가진 프로그램 단위입니다. 프로시저는 특정 작업을 수행하기 위해 설계되며, 반복적인 작업을 자동화하고 코드 재사용성을 높이는 데 매우 유용합니다.

이번 글에서는 오라클 프로시저의 작성법, 주요 구성 요소, 다양한 유형의 예제, 활용 시 주의사항 등을 자세히 설명합니다.

---

1. 오라클 프로시저란?

오라클 프로시저는 하나 이상의 SQL 문과 PL/SQL 블록을 포함하는 서브 프로그램으로, 특정 작업이나 프로세스를 수행하기 위해 설계되었습니다. 프로시저는 여러 번 호출 가능하며, 매개변수(Parameter)를 사용해 데이터를 입력하거나 출력할 수 있습니다.

1.1. 프로시저의 특징

• 반환값이 없어도 됨: 프로시저는 반환값 없이도 작업을 수행할 수 있습니다.
• 매개변수 지원: 입력값(IN), 출력값(OUT), 또는 둘 다를 지원(IN OUT)합니다.
• 독립적 실행: 독립적으로 호출되며, 다른 프로그램이나 트리거에서도 사용할 수 있습니다.
• 트랜잭션 관리: DML(INSERT, UPDATE, DELETE) 작업 및 COMMIT/ROLLBACK과 같은 트랜잭션 제어가 가능합니다.

---

2. 프로시저의 작성법

오라클 프로시저는 다음의 기본 구조로 작성됩니다.

2.1. 프로시저 기본 구조

CREATE [OR REPLACE] PROCEDURE 프로시저명
   (매개변수1 IN 데이터타입, 매개변수2 OUT 데이터타입, ...)
IS
   -- 지역 변수 선언
BEGIN
   -- 프로시저의 로직
EXCEPTION
   -- 예외 처리 블록
END 프로시저명;

2.2. 주요 구성 요소

1. CREATE [OR REPLACE] PROCEDURE: 새로 프로시저를 생성하거나 기존 프로시저를 대체합니다.
2. 매개변수: IN, OUT, IN OUT 키워드를 사용하여 입력 및 출력값을 정의합니다.
   • IN: 호출 시 값을 전달받아 사용.
   • OUT: 호출 후 값을 반환.
   • IN OUT: 호출 시 값을 전달받고, 변경된 값을 반환.
3. 지역 변수 선언: 프로시저 내부에서만 사용하는 변수를 정의.
4. BEGIN ... END: 프로시저의 실행 블록.
5. 예외 처리 블록 (선택적): 오류를 처리하는 로직을 작성.

---

3. 기본 예제: 간단한 프로시저 작성

3.1. 입력 매개변수만 사용하는 프로시저

CREATE OR REPLACE PROCEDURE display_employee_name(emp_id IN NUMBER) IS
   emp_name VARCHAR2(100);
BEGIN
   SELECT first_name || ' ' || last_name INTO emp_name
   FROM employees
   WHERE employee_id = emp_id;

   DBMS_OUTPUT.PUT_LINE('직원 이름: ' || emp_name);
END display_employee_name;
/

호출 예제:

BEGIN
   display_employee_name(101);
END;
/

---

4. 다양한 유형의 프로시저 예제

4.1. 출력 매개변수를 사용하는 프로시저

CREATE OR REPLACE PROCEDURE get_employee_salary(emp_id IN NUMBER, emp_salary OUT NUMBER) IS
BEGIN
   SELECT salary INTO emp_salary
   FROM employees
   WHERE employee_id = emp_id;
END get_employee_salary;
/

호출 예제:

DECLARE
   salary NUMBER;
BEGIN
   get_employee_salary(101, salary);
   DBMS_OUTPUT.PUT_LINE('직원의 급여: ' || salary);
END;
/

---

4.2. 입력과 출력 매개변수를 사용하는 프로시저

CREATE OR REPLACE PROCEDURE calculate_bonus(emp_id IN NUMBER, bonus_rate IN NUMBER, bonus OUT NUMBER) IS
   salary NUMBER;
BEGIN
   SELECT salary INTO salary
   FROM employees
   WHERE employee_id = emp_id;

   bonus := salary * bonus_rate;
END calculate_bonus;
/

호출 예제:

DECLARE
   bonus_amount NUMBER;
BEGIN
   calculate_bonus(101, 0.1, bonus_amount);
   DBMS_OUTPUT.PUT_LINE('보너스 금액: ' || bonus_amount);
END;
/

---

4.3. 지역 변수를 사용하는 프로시저

CREATE OR REPLACE PROCEDURE calculate_total_sales(dept_id IN NUMBER) IS
   total_sales NUMBER;
BEGIN
   SELECT SUM(salary) INTO total_sales
   FROM employees
   WHERE department_id = dept_id;

   DBMS_OUTPUT.PUT_LINE('총 매출: ' || total_sales);
END calculate_total_sales;
/

호출 예제:

BEGIN
   calculate_total_sales(50);
END;
/

---

4.4. 트랜잭션 관리 프로시저

CREATE OR REPLACE PROCEDURE transfer_salary(from_emp_id IN NUMBER, to_emp_id IN NUMBER, amount IN NUMBER) IS
BEGIN
   UPDATE employees
   SET salary = salary - amount
   WHERE employee_id = from_emp_id;

   UPDATE employees
   SET salary = salary + amount
   WHERE employee_id = to_emp_id;

   COMMIT;
   DBMS_OUTPUT.PUT_LINE('급여 이체 완료');
EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE('에러 발생: 트랜잭션 롤백');
END transfer_salary;
/

호출 예제:

BEGIN
   transfer_salary(101, 102, 500);
END;
/

---

4.5. 반복문을 사용하는 프로시저

CREATE OR REPLACE PROCEDURE list_all_employees IS
   CURSOR emp_cursor IS
      SELECT first_name, last_name FROM employees;
   emp_record emp_cursor%ROWTYPE;
BEGIN
   OPEN emp_cursor;
   LOOP
      FETCH emp_cursor INTO emp_record;
      EXIT WHEN emp_cursor%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(emp_record.first_name || ' ' || emp_record.last_name);
   END LOOP;
   CLOSE emp_cursor;
END list_all_employees;
/

호출 예제:

BEGIN
   list_all_employees;
END;
/

---

5. 예외 처리

5.1. 특정 예외 처리

CREATE OR REPLACE PROCEDURE find_employee(emp_id IN NUMBER) IS
   emp_name VARCHAR2(100);
BEGIN
   SELECT first_name || ' ' || last_name INTO emp_name
   FROM employees
   WHERE employee_id = emp_id;

   DBMS_OUTPUT.PUT_LINE('직원 이름: ' || emp_name);
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('해당 직원 ID가 없습니다.');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('알 수 없는 오류 발생');
END find_employee;
/

호출 예제:

BEGIN
   find_employee(999); -- 존재하지 않는 직원 ID
END;
/

---

6. 프로시저 사용 시 주의사항

1. 성능 고려
   • 프로시저 내부에서 무거운 SQL 문을 과도하게 사용하면 성능 저하가 발생할 수 있습니다.
2. 매개변수 사용
   • 입력값 검증을 통해 불필요한 오류를 방지해야 합니다.
   • OUT 매개변수 사용 시 NULL 값 반환 여부를 주의해야 합니다.
3. 트랜잭션 처리
   • 프로시저 내부에서 트랜잭션을 명확히 정의하여 데이터 일관성을 유지해야 합니다.
4. 재사용성
   • 프로시저는 일반적인 작업을 묶어서 재사용 가능하도록 설계해야 합니다.

---

7. 결론

오라클 프로시저는 데이터베이스 내에서 반복적인 작업을 자동화하고 코드의 재사용성을 높이는 데 매우 유용합니다. 이 글에서는 프로시저의 기본 구조와 다양한 유형의 예제를 살펴보았습니다.
프로시저 설계 시 매개변수 사용, 트랜잭션 처리, 예외 처리 등에 주의를 기울여야 하며, 성능 최적화를 위한 설계도 중요합니다.

프로시저를 적절히 활용하여 데이터베이스 작업을 효율적으로 관리해보세요!