MySQL 저장 프로시저, 함수, 트리거

저장 프로시저(Stored Procedure)

• 정의: 이름이 부여된 일련의 SQL 작업 묶음
• 특징
  • 파라미터를 받아 로직을 처리할 수 있고, IF문이나 LOOP문 같은 제어문도 사용할 수 있음
  • 여러 개의 INSERT, UPDATE, DELETE 작업을 포함하는 복잡한 비즈니스 로직을 하나의 단위로 처리하는데 사용
• 호출 방식: CALL 프로시저이름(p1, p2);

저장 프로시저 생성

-- 구분자를 // 로 변경
DELIMITER
//
CREATE PROCEDURE sp_change_user_address(
  IN user_id_param INT,
  IN new_address_param VARCHAR (255)
)
BEGIN
-- 1. users 테이블의 주소 업데이트
UPDATE users
SET address = new_address_param
WHERE user_id =
      user_id_param;
-- 2. logs 테이블에 변경 이력 기록
INSERT INTO logs (description)
VALUES (CONCAT('User ID ', user_id_param, ' 주소 변경 ', new_address_param));
END
//
-- 구분자를 다시 ; 로 원상 복구
DELIMITER ;

저장 프로시저 호출

CALL sp_change_user_address(2, '부산 서구');

저장 함수(Stored Function)

• 정의: 특정 계산을 수행하고 반드시 하나의 값을 반환하는 프로그램
• 특징
  • 프로시저와 달리, 반드시 하나의 값을 반환 해야 한다는 점이 다름
  • 이 특징 때문에 SUM()이나 COUNT() 같은 내장 함수처럼 일반적인 SELECT 쿼리문 안에서 값의 일부로 사용 될 수 있음
• 사용 방식: SELECT name, 나의함수(c1) FROM table; 처럼 쿼리의 일부로 사용

저장 함수 생성

DELIMITER
//
CREATE FUNCTION fn_get_final_price(
  price_param INT,
  discount_rate_param DECIMAL (5, 2)
)
  RETURNS DECIMAL(10, 2)
  DETERMINISTIC
BEGIN
-- 최종 가격을 계산 (가격 * (1 - 할인율/100))
RETURN price_param * (1 - discount_rate_param / 100);
END
//
DELIMITER ;

트리거(Trigger)

• 정의: 특정 테이블에 특정 이벤트가 발생했을 때, 자동으로 실행되도록 약속된 프로그램
• 특징: 개발자가 직접 호출하는 것이 아니라, 특정 조건이 만족되면 DB에 의해 자동으로 실행됨

트리거 생성

DELIMITER
//
CREATE TRIGGER trg_backup_user
  BEFORE DELETE
  ON users
  FOR EACH ROW
BEGIN
END //
INSERT INTO retired_users (id, name, email, retired_date)
VALUES (OLD.user_id, OLD.name, OLD.email, CURDATE());
DELIMITER;

• BEFORE DELETE ON users: users 테이블에서 레코드가 삭제되기 직전에 트리거를 실행하라는 의미
  • BEFORE와 AFTER 키워드를 통해 이벤트 발생 시점을 지정할 수 있음
• FOR EACH ROW: DELETE 되는 각 행에 대해 트리거의 본문(BEGIN ... END)을 한 번씩 실행하라는 의미
• OLD.user_id, OLD.name, OLD.email: 트리거 내에서는 OLD 키워드를 사용하여 이벤트 발생 전의 컬럼 값에 접근 할 수 있음
  • NEW 키워드는 INSERT나 UPDATE 이벤트 시 이벤트 발생 후의 컬럼 값에 접근할 때 사용
• CURDATE(): MySQL 내장 함수로, 현재 날짜를 반환

DB 로직의 함정

• 유지보수의 어려움: 비즈니스 로직이 애플리케이션과 DB에 분산되어 추적이 어렵고, Git과 같은 버전 관리가 까다로움
• 성능 및 확장성 문제: 로직이 DB에 지중되면 병목 현상이 발생하며, 수평 확장이 쉬운 애플리케이션 서버와 달리 DB는 확장이 어렵고 비용이 많이 듦
• DB 종속성: 프로시저 등의 문법은 DB 제조사 마다 달라, 다른 DB로 이전하는 것을 매우 어렵게 만듦

현대의 대안: 명확한 역할 분리

• 애플리케이션 계층: 비즈니스 로직, 데이터 가공, 조건 처리, 절차 제어 등 모든 지능적인 처리 담당
• DB 계층: 오직 데이터의 저장, 조회, 무결성 보장(제약 조건), 트랜잭션 관리라는 데이터 본연의 역할에만 충실

참고

• https://www.inflearn.com/course/김영한-실전-데이터베이스-기본편