저장 프로시저 (Stored Procedure)

저장 프로시저(Stored Procedure)는 자주 사용하는 SQL 문들을 하나의 이름 아래 묶어 데이터베이스 서버에 미리 저장해 두고, 필요할 때 호출해서 실행하는 프로그램 단위다. 변수 선언, 조건문, 반복문 같은 절차적 로직을 담을 수 있으며, 네트워크 왕복을 줄이고 로직을 데이터 가까이에서 실행한다는 장점과 유지보수, 확장성 측면의 단점을 함께 가진다.

개념과 동작 방식

일반적인 애플리케이션은 SQL 문을 매번 데이터베이스로 전송하고, 데이터베이스는 그때마다 구문을 분석하고 실행 계획을 세운다. 저장 프로시저는 이 SQL 묶음을 데이터베이스 안에 객체로 저장해 두는 방식이다. 최초 생성 시 구문 검사가 이루어지고, 호출 시점에는 컴파일된 형태나 캐시된 실행 계획을 재사용할 수 있어 반복 호출에 유리하다.

호출하는 쪽에서는 CALL 프로시저명(인자) 형태로 이름과 매개변수만 전달하면 되므로, 여러 SQL 문이 필요한 작업도 네트워크 왕복 한 번으로 끝난다. 입력 매개변수(IN), 출력 매개변수(OUT), 양방향 매개변수(INOUT)를 통해 값을 주고받으며, 내부에서 트랜잭션 제어와 예외 처리도 할 수 있다.

작성 예시

MySQL에서 주문을 생성하면서 재고를 차감하는 프로시저 예시다.

DELIMITER //

CREATE PROCEDURE create_order (
    IN  p_user_id    BIGINT,
    IN  p_product_id BIGINT,
    IN  p_qty        INT,
    OUT p_order_id   BIGINT
)
BEGIN
    DECLARE v_stock INT;

    START TRANSACTION;

    SELECT stock INTO v_stock
    FROM product
    WHERE id = p_product_id
    FOR UPDATE;

    IF v_stock < p_qty THEN
        ROLLBACK;
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '재고가 부족합니다';
    ELSE
        UPDATE product SET stock = stock - p_qty
        WHERE id = p_product_id;

        INSERT INTO orders (user_id, product_id, qty)
        VALUES (p_user_id, p_product_id, p_qty);

        SET p_order_id = LAST_INSERT_ID();
        COMMIT;
    END IF;
END //

DELIMITER ;

-- 호출
CALL create_order(42, 7, 3, @order_id);
SELECT @order_id;

장점과 단점

구분	내용
장점	여러 SQL을 한 번의 호출로 실행해 네트워크 왕복이 줄어든다. 실행 계획 재사용으로 반복 실행 성능이 좋다. 테이블 접근 권한 대신 프로시저 실행 권한만 부여해 보안을 강화할 수 있다. 매개변수 바인딩을 쓰면 SQL 인젝션(SQL Injection) 방어에도 도움이 된다. 여러 애플리케이션이 같은 로직을 공유할 수 있다.
단점	비즈니스 로직이 애플리케이션과 데이터베이스 양쪽에 흩어져 유지보수가 어려워진다. 버전 관리, 테스트, 디버깅 도구가 애플리케이션 코드에 비해 빈약하다. 데이터베이스 제품마다 문법이 달라 이식성이 낮다. 로직이 데이터베이스에서 실행되므로 수평 확장이 어려운 데이터베이스에 CPU 부하가 집중된다.