MySQL 스토어드 프로시저와 함수 — 서버 사이드 로직 구현

지난 글에서 파생 테이블 병합 최적화를 살펴봤습니다. 이번 글에서는 MySQL에서 복잡한 비즈니스 로직을 서버에 저장해 재사용하는 **스토어드 루틴(프로시저·함수)**을 다룹니다.

스토어드 루틴이란

스토어드 루틴은 SQL과 절차적 로직을 묶어 데이터베이스 서버에 저장해 두고 이름으로 호출하는 코드 단위입니다. 애플리케이션이 매번 SQL을 전송하는 대신 CALL proc_name(args) 하나로 복잡한 처리를 서버에서 완결할 수 있습니다.

-- 루틴 목록 확인
SHOW PROCEDURE STATUS WHERE Db = 'mydb';
SHOW FUNCTION STATUS WHERE Db = 'mydb';

-- 루틴 정의 확인
SHOW CREATE PROCEDURE transfer_points\G
SHOW CREATE FUNCTION tier_label\G

DELIMITER 설정

MySQL 클라이언트는 ;을 명령 구분자로 사용합니다. 루틴 본문에도 ;이 있으므로 구분자를 임시 변경해야 합니다.

DELIMITER //

CREATE PROCEDURE my_proc()
BEGIN
  SELECT 1;  -- 이 ; 는 명령 끝이 아님
END //

DELIMITER ;

// 외에 $$를 쓰기도 합니다. 루틴 정의가 끝나면 반드시 원래 ;로 복원합니다.

프로시저 vs 함수

두 가지의 결정적 차이는 반환 방식과 DML 허용 여부입니다. 프로시저는 INSERT, UPDATE, DELETE, 트랜잭션 제어까지 모두 가능하고, CALL로 호출합니다. 함수는 SELECT 식 어디서나 사용할 수 있는 단일 스칼라값을 반환하지만 DML은 기본적으로 금지됩니다.

파라미터 모드 (프로시저)

DELIMITER //
CREATE PROCEDURE calc_discount(
  IN  p_price    DECIMAL(10,2),   -- 호출자→루틴 (읽기 전용)
  IN  p_rate     DECIMAL(5,2),
  OUT p_discount DECIMAL(10,2),   -- 루틴→호출자 (반환)
  INOUT p_total  DECIMAL(10,2)    -- 양방향
)
BEGIN
  SET p_discount = p_price * p_rate / 100;
  SET p_total    = p_total - p_discount;
END //
DELIMITER ;

-- 호출
SET @total = 50000;
CALL calc_discount(50000, 10.0, @disc, @total);
SELECT @disc, @total;

OUT/INOUT 인수는 반드시 사용자 변수(@이름)로 전달합니다.

스토어드 함수 — DETERMINISTIC 선언

DELIMITER //
CREATE FUNCTION vat_amount(price DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
  RETURN price * 0.10;
END //
DELIMITER ;

-- WHERE 절에서도 직접 사용 가능
SELECT id, price, vat_amount(price) vat
FROM products
WHERE vat_amount(price) > 5000;

DETERMINISTIC은 같은 입력에 항상 같은 결과를 반환함을 선언합니다. 바이너리 로그 복제 환경에서 함수가 DETERMINISTIC이 아니면 MySQL이 복제 안전성 문제로 에러를 냅니다(log_bin_trust_function_creators 설정으로 우회 가능하지만 권장하지 않음).

제어 흐름

-- IF / ELSEIF / ELSE
IF p_score >= 90 THEN
  SET v_grade = 'A';
ELSEIF p_score >= 80 THEN
  SET v_grade = 'B';
ELSE
  SET v_grade = 'F';
END IF;

-- WHILE 루프
SET i = 1;
WHILE i <= 10 DO
  INSERT INTO log_table VALUES (i, NOW());
  SET i = i + 1;
END WHILE;

-- LOOP + LEAVE (break)
my_loop: LOOP
  FETCH cur INTO v_id;
  IF done THEN LEAVE my_loop; END IF;
END LOOP my_loop;

커서 — 결과셋 행 단위 처리

DELIMITER //
CREATE PROCEDURE process_overdue()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE v_id INT;
  DECLARE v_amount DECIMAL(10,2);

  DECLARE cur CURSOR FOR
    SELECT id, amount FROM orders WHERE due_date < CURDATE();
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur;
  row_loop: LOOP
    FETCH cur INTO v_id, v_amount;
    IF done THEN LEAVE row_loop; END IF;
    UPDATE orders SET penalty = v_amount * 0.05 WHERE id = v_id;
  END LOOP row_loop;
  CLOSE cur;
END //
DELIMITER ;

커서 루프는 행 단위 처리라 대량 데이터에는 느립니다. 가능하면 UPDATE ... JOIN 등 집합 기반 쿼리로 대체합니다.

예외 처리 — HANDLER

DELIMITER //
CREATE PROCEDURE safe_insert(IN p_id INT, IN p_name VARCHAR(50))
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '삽입 실패';
  END;

  START TRANSACTION;
  INSERT INTO members (id, name) VALUES (p_id, p_name);
  COMMIT;
END //
DELIMITER ;

EXIT HANDLER는 예외 발생 시 루틴을 종료합니다. CONTINUE HANDLER는 예외 발생 후 다음 문장을 계속 실행합니다.

루틴 관리

-- 수정: DROP 후 재생성 (ALTER는 속성만 변경)
DROP PROCEDURE IF EXISTS transfer_points;

-- 권한 부여 (특정 루틴만 실행 허용)
GRANT EXECUTE ON PROCEDURE mydb.transfer_points TO 'appuser'@'%';
GRANT EXECUTE ON FUNCTION  mydb.tier_label TO 'appuser'@'%';

스토어드 루틴은 비즈니스 로직 중 변경 빈도가 낮고, 여러 애플리케이션이 공유하며, 트랜잭션이 포함된 작업에 적합합니다. 로직이 자주 바뀌거나 애플리케이션 단 테스트가 필요하다면 애플리케이션 레이어에 두는 것이 더 유연합니다.

---

지난 글: MySQL Derived Table Merge — 파생 테이블 병합 최적화

다음 글: MySQL 트리거 — 자동 감사와 무결성 보호

읽어주셔서 감사합니다. 😊