[DB] Function / Procedure / Trigger 정리

DB 안에 저장해서 쓰는 코드들은 전부 비슷해 보이지만, 언제 실행되느냐 / 어떻게 호출되느냐 / 무엇을 반환하느냐에 따라 역할이 꽤 다르다.
여기서는 Function, Procedure, Trigger 세 가지를 한 번에 정리한다.

---

1. 공통 개념: Stored Program

• 세 가지 모두 DB에 저장해 두고 재사용하는 코드 블록이다. (Stored Program / Stored Object)
• SQL 문(DML, DDL 일부)과 제어문(IF, LOOP 등)을 섞은 절차형 SQL로 작성한다.
• 애플리케이션에서 같은 로직을 여러 군데 구현하지 않고, DB 쪽으로 끌어올리는 용도에 가깝다.

---

2. Function (펑션)

2.1 개요

Function은 입력 값을 받아서 하나의 값을 반환하는 DB 내부 함수다.
SQL 식(expression) 안에서 일반 함수처럼 호출하는 것을 전제로 한다.

2.2 특징

• 반드시 RETURN 값이 하나 존재한다. (스칼라 값)
• 일반적으로 부작용(INSERT/UPDATE 등 DML)에는 제한이 있거나, 피해서 쓰는 편이다.
• SELECT, WHERE, ORDER BY 등 SQL 식 안에 직접 사용 가능하다.

예)

SELECT
    user_id,
    calc_score(user_id) AS score
FROM users;

2.3 언제 쓰는가

• 점수/가중치 계산
• 문자열/날짜 포맷팅
• 도메인 규칙에 따른 값 변환
  → “입력 → 출력”이 명확한 계산/변환 로직을 DB 쪽으로 올리고 싶을 때 사용한다.

---

3. Procedure (프로시저)

3.1 개요

Procedure는 여러 SQL과 제어문을 묶어서 하나의 작업 단위(트랜잭션)로 실행하는 코드 덩어리다.
애플리케이션이 CALL / EXEC 같은 방식으로 명시적으로 호출한다.

3.2 구성

일반적인 구조는 다음과 같다.

• DECLARE : 프로시저 이름, 변수, 파라미터, 데이터 타입 선언
• BEGIN / END : 본문 시작/종료
• CONTROL : IF, LOOP, CASE 등 제어문
• SQL : DML(DATE 삽입/수정/삭제), 필요 시 DDL/DCL 일부
• EXCEPTION : 예외 처리
• TRANSACTION : COMMIT / ROLLBACK 등 트랜잭션 경계 처리

예시 형식:

CREATE OR REPLACE PROCEDURE close_daily_batch(p_target_date DATE)
IS
BEGIN
    -- 본문: 집계, 로그 적재, 상태 변경 등
    COMMIT;
END;

호출:

EXEC close_daily_batch(SYSDATE - 1);
-- 또는
CALL close_daily_batch(CURRENT_DATE - 1);

3.3 특징과 용도

• 반환값은 필수가 아니다. 필요하면 OUT / INOUT 파라미터로 결과를 돌려준다.
• INSERT / UPDATE / DELETE / COMMIT / ROLLBACK 등 부작용 중심 작업을 포함한다.
• 주 용도
  • 일일 마감, 정산, 배치 작업
  • 여러 테이블을 한 번에 갱신하는 복잡한 비즈니스 로직
  • 여러 애플리케이션에서 공통으로 사용하는 DB 작업 묶음

---

4. Trigger (트리거)

4.1 개요

Trigger는 특정 테이블/뷰에서 이벤트가 발생할 때 자동으로 실행되는 코드다.
애플리케이션이 직접 호출하지 않고, DB가 “조건이 만족되면 알아서” 실행한다.

4.2 구성

일반적인 구조는 다음과 같다.

• DECLARE : 변수, 커서 등 선언
• EVENT : 언제 실행할지 지정
  • BEFORE / AFTER
  • INSERT / UPDATE / DELETE
• BEGIN / END : 본문
• CONTROL : IF, LOOP 등
• SQL : 주로 DML (DCL은 대부분 DB에서 허용되지 않음)
• EXCEPTION : 예외 처리

예시 형식:

CREATE OR REPLACE TRIGGER user_audit_trg
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_audit(user_id, old_status, new_status, changed_at)
    VALUES (:OLD.id, :OLD.status, :NEW.status, CURRENT_TIMESTAMP);
END;

4.3 특징과 용도

• 이벤트 기반
  • 특정 테이블에 INSERT/UPDATE/DELETE가 발생했을 때 동작.
• NEW / OLD 레코드를 참조해서 변경 전/후 값에 접근할 수 있다.
• DCL(권한 변경 등)은 트리거 안에서 사용할 수 없는 경우가 많다.
• 주 용도
  • 감사 로그(Audit log) 기록
  • 파생 데이터/집계 테이블 자동 유지
  • 특정 컬럼 값 검증/강제 (무결성 보조)

---

5. 세 가지 비교 정리

구분	호출 방식	반환값	주요 역할
Function	SELECT/WHERE 등 SQL 식 안에서 호출	반드시 1개의 값 반환	계산/변환/포맷팅, 규칙 기반 값 도출
Procedure	CALL / EXEC 등으로 명시적 호출	없어도 되고 OUT 파라미터 사용	배치/마감/집계 등 “작업 한 덩어리” 실행
Trigger	이벤트 발생 시 DB가 자동 실행	직접 반환 개념 없음	테이블 변경 시 후속 작업, 감사·무결성 처리

한 줄로 요약하면,

• Function: “값을 계산해서 돌려주는 애”
• Procedure: “지금 이 시점에 이 작업 세트를 실행해 주는 애”
• Trigger: “테이블에서 이런 일이 생기면 자동으로 튀어나오는 애”