🧱 22편. 프로시저, 함수, 트리거 만들기

🧱 22편. 프로시저, 함수, 트리거 만들기

PL/SQL의 강력한 기능 중 하나는 복잡한 로직을 객체로 저장하여 반복해서 호출할 수 있다는 점입니다.
이번 글에서는 프로시저(Procedure), 함수(Function), 트리거(Trigger)를 작성하는 기본 문법과 실무 예제를 소개합니다.

---

🔧 1. 프로시저 (Procedure)

프로시저는 반환값 없이 작업만 수행하는 저장형 PL/SQL 블록입니다.

하지만 다음을 통해 값을 돌려줄 수 있습니다:

• OUT 파라미터
• IN OUT 파라미터
• REF CURSOR
• (내부에서 값을 처리만 하고 반환은 하지 않을 수도 있음)

즉, **“결과를 명시적으로 반환하는 구조가 필요한 경우 함수가 더 적합”**합니다. DML 수행 후 OUT으로 결과 넘기거나 부수 작업이 많다면 → 프로시저가 적절

✅ 프로시저 생성 예제 (리턴이 없는 경우)

CREATE OR REPLACE PROCEDURE raise_salary (
  p_empno IN EMP.EMPNO%TYPE,
  p_amount IN NUMBER
)
IS
BEGIN
  UPDATE EMP
  SET SAL = SAL + p_amount
  WHERE EMPNO = p_empno;
  
  DBMS_OUTPUT.PUT_LINE('급여 인상 완료');
END;

📞 실행 방법

BEGIN
  raise_salary(7369, 500);
END;

 

✅ 1. OUT 파라미터 + BULK COLLECT INTO (컬렉션(배열)을 리턴하는 경우)

-- 컬렉션 타입 선언 (패키지 또는 SQL 레벨에서)
CREATE OR REPLACE TYPE emp_id_table IS TABLE OF NUMBER;

-- 프로시저
CREATE OR REPLACE PROCEDURE get_emp_ids(p_emp_ids OUT emp_id_table) AS
BEGIN
  SELECT employee_id BULK COLLECT INTO p_emp_ids
  FROM employees
  WHERE department_id = 10;
END;

📞 실행 방법 사용 시점에 TABLE()을 통해 SQL에서도 다룰 수 있음

SELECT * FROM TABLE(CAST(:bind_variable AS emp_id_table));

 

✅ 다중 행 프로시저 예시

CREATE OR REPLACE PROCEDURE get_emp_list (
  p_deptno IN NUMBER,
  p_cursor OUT SYS_REFCURSOR
)
IS
BEGIN
  OPEN p_cursor FOR
    SELECT empno, ename, sal
    FROM emp
    WHERE deptno = p_deptno;
END;

📞 실행 예:

VARIABLE rc REFCURSOR;
EXEC get_emp_list(10, :rc);
PRINT rc;

---

✅ 2. 커서 (REF CURSOR) 리턴

(함수 RETURN값으로 REF CURSOR 가능하지만 일반적으로프로시저의 OUT 파라미터로 REF CURSOR를 리턴함) 

• SQL과 가장 비슷하게 사용됨
• 조회 결과를 커서로 넘겨주는 방식

-- 패키지 헤더
CREATE OR REPLACE PACKAGE emp_pkg AS
  TYPE emp_cursor IS REF CURSOR;
  PROCEDURE get_emp_list(p_deptno IN NUMBER, p_result OUT emp_cursor);
END;

-- 패키지 바디
CREATE OR REPLACE PACKAGE BODY emp_pkg AS
  PROCEDURE get_emp_list(p_deptno IN NUMBER, p_result OUT emp_cursor) IS
  BEGIN
    OPEN p_result FOR
      SELECT employee_id, first_name
      FROM employees
      WHERE department_id = p_deptno;
  END;
END;

📌 JDBC, Python, PHP 등 외부 프로그램에서도 다중행을 쉽게 처리 가능한 형태

---

🔢 2. 함수 (Function)

함수는 리턴값을 반환하며, SQL문 안에서도 호출 가능합니다.

✅ 함수 생성 예제

CREATE OR REPLACE FUNCTION get_annual_salary (
  p_empno IN EMP.EMPNO%TYPE
) RETURN NUMBER
IS
  v_salary EMP.SAL%TYPE;
BEGIN
  SELECT SAL INTO v_salary FROM EMP WHERE EMPNO = p_empno;
  RETURN v_salary * 12;
END;

📞 실행 방법

SELECT get_annual_salary(7369) AS 연봉 FROM DUAL;

 

---

✅ 3. 파이프라인 테이블 함수 (PIPELINED FUNCTION) (조회 전용. 즉 내부 실행은 없음)

• 함수 호출로 테이블처럼 사용할 수 있음
• 복잡한 로직 후 행 단위로 return

-- 리턴할 ROW 타입 정의
CREATE OR REPLACE TYPE emp_row_type AS OBJECT (
  employee_id NUMBER,
  first_name VARCHAR2(50)
);

-- ROW 타입의 TABLE 정의
CREATE OR REPLACE TYPE emp_table_type AS TABLE OF emp_row_type;

-- PIPELINED FUNCTION 함수 정의
CREATE OR REPLACE FUNCTION get_emp_table(p_deptno IN NUMBER)
RETURN emp_table_type PIPELINED 
IS
BEGIN
  FOR rec IN (SELECT employee_id, first_name FROM employees WHERE department_id = p_deptno) LOOP
    PIPE ROW(emp_row_type(rec.employee_id, rec.first_name));
  END LOOP;
  RETURN;
END;

📌 호출 예시:

SELECT * FROM TABLE(get_emp_table(10));

 

---

🧲 3. 트리거 (Trigger)

트리거는 특정 이벤트 발생 시 자동으로 실행되는 PL/SQL 블록입니다.
(예: INSERT, UPDATE, DELETE 수행 시 자동 동작)

✅ 트리거 생성 예제

CREATE OR REPLACE TRIGGER trg_emp_audit
AFTER INSERT OR UPDATE OR DELETE ON EMP
FOR EACH ROW
BEGIN
  INSERT INTO EMP_LOG (
    ACTION_DATE, ACTION_TYPE, EMPNO
  ) VALUES (
    SYSDATE,
    CASE
      WHEN INSERTING THEN 'INSERT'
      WHEN UPDATING THEN 'UPDATE'
      WHEN DELETING THEN 'DELETE'
    END,
    :OLD.EMPNO
  );
END;

📌 포인트

• :OLD – 이전 값, :NEW – 변경 후 값
• BEFORE / AFTER로 트리거 실행 시점 제어
• FOR EACH ROW는 행 단위 트리거

---

 

🔄 다중 행 처리 시 프로시저와 함수 선택 기준

다중 행(예: 여러 사원의 급여 인상 또는 조회)이 필요할 때는 다음처럼 접근합니다:

목적	권장 방식	이유
데이터를 여러 건 수정	프로시저 + BULK 처리	OUT 파라미터로 결과 전달 가능, INSERT/UPDATE에 적합
여러 건 조회하여 SQL에서 사용	함수 (PIPELINED FUNCTION)	SELECT 문 안에서 사용 가능
외부 앱에 조회 결과 전달	프로시저 + REF CURSOR	JDBC, Python 등에서 사용하기 용이

✅ 추천 기준

상황추천 방식

SQL 내에서 직접 결과를 조회하고 싶다	함수 (특히 PIPELINED FUNCTION)
외부 프로그램에서 프로시저 호출 후 다중 결과를 받고 싶다	프로시저 + REF CURSOR
다수의 OUT 값 또는 복합 로직을 처리하고 싶다	프로시저
간단한 값 또는 리스트 반환	함수 + BULK COLLECT

 

 

---

✅ 다중 행 프로시저 예시

 

CREATE OR REPLACE PROCEDURE get_emp_list (
  p_deptno IN NUMBER,
  p_cursor OUT SYS_REFCURSOR
)
IS
BEGIN
  OPEN p_cursor FOR
    SELECT empno, ename, sal
    FROM emp
    WHERE deptno = p_deptno;
END;

📞 실행 예:

VARIABLE rc REFCURSOR;
EXEC get_emp_list(10, :rc);
PRINT rc;

---

✅ PIPELINED FUNCTION 예시 (조회 전용)

-- 리턴 타입 정의
CREATE OR REPLACE TYPE emp_obj AS OBJECT (
  empno NUMBER,
  ename VARCHAR2(50)
);

CREATE OR REPLACE TYPE emp_tbl AS TABLE OF emp_obj;

-- 함수 정의
CREATE OR REPLACE FUNCTION get_emp_func(p_deptno NUMBER)
RETURN emp_tbl PIPELINED
IS
BEGIN
  FOR rec IN (SELECT empno, ename FROM emp WHERE deptno = p_deptno) LOOP
    PIPE ROW(emp_obj(rec.empno, rec.ename));
  END LOOP;
  RETURN;
END;

📞 사용 예:

SELECT * FROM TABLE(get_emp_func(10));

---

---

⚠ 실무 주의사항

• 트리거는 비즈니스 로직 복잡성 증가에 주의
• 무한 루프나 순환 호출이 발생하지 않도록 설계
• 프로시저/함수는 예외 처리(WHEN OTHERS)와 로그 로직 함께 구성 권장

---

📝 마무리

이번 글에서는 Oracle에서 프로시저, 함수, 트리거를 생성하는 방법과 활용 예제를 다뤘습니다.
다음 글에서는 PL/SQL에서 사용하는 변수, 레코드 타입, 예외 처리 고급 기능을 소개하겠습니다.