스토어드 프로그램

스토어드 프로그램(stored program)은 SQL 여러 문장에 변수·조건문·반복문을 붙여 만든 코드를 DB 서버 안에 저장해 두고 실행하는 기능이다. 애플리케이션이 SQL을 하나씩 보내는 대신 서버가 저장된 코드를 직접 돌린다. 네트워크 왕복은 줄지만 로직이 DB 안으로 숨어들기 때문에, 요즘 서비스 개발에서는 꼭 필요한 곳에만 제한적으로 쓴다.

네 가지 종류

스토어드 프로그램은 스토어드 프로시저(stored procedure)·스토어드 함수(stored function)·트리거(trigger)·MySQL 이벤트(event) 네 가지다.

종류	실행 계기	대표 용도
스토어드 프로시저	`CALL`로 직접 호출	여러 SQL을 묶은 작업
스토어드 함수	SQL 식 안에서 호출	값 하나를 계산해 돌려줌
트리거	테이블의 INSERT·UPDATE·DELETE	변경 이력 남기기
MySQL 이벤트	정해 둔 시각·주기	주기적 정리 작업

네 종류 모두 DB 안에 저장되는 객체라, 만들고 실행하려면 CREATE ROUTINE·EXECUTE·TRIGGER·EVENT 같은 권한(privilege)이 필요하다.

장점과 단점: 실무에서 잘 안 쓰는 이유

장점은 분명하다. 여러 SQL을 서버 안에서 이어 실행하므로 애플리케이션과 DB 사이의 네트워크 왕복이 줄어든다. 사용자에게 테이블 권한 대신 프로시저 실행 권한만 주면 정해진 작업만 하게 막을 수도 있다. 여러 애플리케이션이 같은 DB를 쓸 때 공통 로직을 한곳에 둘 수 있다는 점도 장점이다.

단점은 운영 규모가 커질수록 커진다.

확장이 어렵다. 애플리케이션 서버는 대수를 늘리기 쉽지만, 쓰기를 받는 DB 서버는 서버끼리 데이터를 맞추기 어려워 보통 한 대다. 로직을 DB에 넣으면 가장 늘리기 어려운 서버의 CPU를 계산에 쓰게 된다.
디버깅과 테스트가 어렵다. 중단점(breakpoint)을 걸거나 단위 테스트(unit test)를 돌리는 도구가 애플리케이션 언어보다 빈약하다.
버전 관리와 배포가 따로 논다. 코드가 DB 안에 저장되므로 저장소의 코드와 실제 서버의 코드가 어긋나기 쉽다. 애플리케이션 배포와 순서를 맞춰야 하고, 되돌리기도 번거롭다.
로직이 흩어진다. 같은 업무 규칙이 애플리케이션과 DB 양쪽에 나뉘면, 코드를 읽는 사람이 동작을 한눈에 파악하지 못한다. 특히 트리거는 SQL 한 줄 뒤에서 몰래 실행되어 놓치기 쉽다.

그래서 일반적인 서비스는 업무 로직을 애플리케이션에 두고, 스토어드 프로그램은 DB 안에서 끝내는 편이 나은 작업(대량 데이터 정리, 운영용 스크립트)에만 쓴다.

스토어드 프로시저와 스토어드 함수

스토어드 프로시저는 CALL로 호출하는 작업 단위다. 매개변수에 IN(입력)·OUT(출력)·INOUT(입출력)을 지정해 값을 주고받고, 결과 집합(result set)을 돌려줄 수도 있으며, 안에서 COMMIT·ROLLBACK으로 트랜잭션(transaction)을 다룰 수 있다.

sql

DELIMITER //                         -- 본문 안의 ;와 문장 끝을 구분하려고 구분자를 바꾼다
CREATE PROCEDURE archive_orders(IN before_date DATE, OUT moved INT)
BEGIN
  INSERT INTO orders_archive SELECT * FROM orders WHERE created_at < before_date;
  SET moved = ROW_COUNT();
  DELETE FROM orders WHERE created_at < before_date;
END//
DELIMITER ;
CALL archive_orders('2026-01-01', @moved);

스토어드 함수는 RETURNS로 반환 타입을 정하고 값 하나를 돌려주며, SELECT f(col) FROM t처럼 SQL 식 안에서 쓴다. 식 안에서 불리므로 제약이 더 많다. 매개변수는 모두 입력용이고, 결과 집합을 돌려줄 수 없으며, 커밋(commit)·롤백(rollback)을 일으키는 문장을 쓸 수 없고, 자기 자신을 재귀 호출할 수 없다.

트리거

트리거는 테이블에 INSERT·UPDATE·DELETE가 일어날 때 자동으로 실행되도록 걸어 둔 코드다. BEFORE(변경 전)나 AFTER(변경 후)를 고르고, 본문에서 NEW.col(새 값)·OLD.col(옛 값)으로 행을 참조한다. BEFORE 트리거에서는 SET NEW.col = ...로 들어갈 값을 바꿀 수도 있다.

MySQL 트리거는 행 단위로만 실행된다(FOR EACH ROW). 100만 행을 바꾸는 UPDATE는 트리거도 100만 번 실행하므로 대량 변경이 눈에 띄게 느려진다.
InnoDB에서는 트리거가 실패하면 그 문장 전체가 실패하고 문장의 변경이 롤백된다.
부모 행을 지우거나 고칠 때 자식 행도 자동으로 바꾸는 외래 키(foreign key)의 연쇄 동작(ON DELETE CASCADE 등)으로 바뀐 행에는 트리거가 실행되지 않는다. 이력을 트리거로 남긴다면 이 구멍을 알고 있어야 한다.

MySQL 이벤트

MySQL 이벤트는 정해 둔 시각이나 주기에 서버가 알아서 실행하는 SQL이다. 운영체제의 예약 실행 도구인 cron처럼 동작한다. 실제 실행은 서버 안의 이벤트 스케줄러(event scheduler) 스레드가 맡고, MySQL 8.4에서 event_scheduler의 기본값은 ON이다.

sql

CREATE EVENT purge_sessions
  ON SCHEDULE EVERY 1 HOUR           -- 한 시간마다. 한 번만이면 AT '시각'
  DO DELETE FROM sessions WHERE expires_at < NOW();

실행을 마친 1회용 AT 이벤트나 ENDS 시각이 지난 반복 이벤트는 기본값(ON COMPLETION NOT PRESERVE)이면 곧바로 삭제된다. 남겨 두려면 ON COMPLETION PRESERVE를 쓴다.
이전 실행이 주기 안에 안 끝나면 같은 이벤트가 겹쳐 실행될 수 있다. 매뉴얼은 네임드 락(named lock)을 잡는 GET_LOCK() 같은 함수로 겹침을 막으라고 안내한다(MySQL 엔진의 잠금 참고).
복제(replication)로 원본의 변경을 받아 적용하는 레플리카(replica)에서는 이벤트가 REPLICA_SIDE_DISABLED 상태가 되어 실행되지 않는다. 원본에서 이벤트가 만든 변경이 복제로 넘어오므로 두 번 실행하지 않기 위해서다.

면접 포인트

"스토어드 프로시저를 왜 잘 안 쓰나요?" → DB 서버는 확장이 어렵고, 디버깅·테스트·버전 관리가 불편하며, 로직이 흩어진다. 장점은 네트워크 왕복 감소와 권한 캡슐화.
프로시저 vs 함수: 프로시저는 CALL·OUT 매개변수·결과 집합·트랜잭션 제어 가능. 함수는 식 안에서 값 하나, 커밋·결과 집합·재귀 불가.
트리거는 행 단위로만 돌고 외래 키 연쇄 동작에는 실행되지 않는다. 이벤트는 겹쳐 실행될 수 있어 GET_LOCK()으로 막는다.

스토어드 프로그램 ​

네 가지 종류 ​

장점과 단점: 실무에서 잘 안 쓰는 이유 ​

스토어드 프로시저와 스토어드 함수 ​

트리거 ​

MySQL 이벤트 ​

면접 포인트 ​

관련 문서