[데이터베이스/DB] Stored Procedure vs Trigger?

두유노 프로시져 ?

 

📘 Stored Procedure(저장 프로시저)

자주 사용하는 SQL문을 하나의 이름으로 묶어서 저장하는 방법으로, 필요할 때 마다 호출하여 실행할 수 있는 프로그램 객체를 의미한다. 주로 반복되는 로직을 '재사용' 하거나, 복잡한 트랜잭션 처리를 위해 사용된다.

 

이렇게 말하면 어려운데 나름 매개변수의 개념도 있고, 재사용성을 위해 활용된다는 점에서 일종의 method/함수와 유사한 구조로 작동한다.

 

특징으로는 입력(IN), 출력(OUT), 입출력(INOUT)의 개념이 존재하고, IF/WHILE/LOOP/CASE 등의 제어문 사용도 가능하다.

 

프로시저는 구현 및 디버깅이 어렵다는 한계점도 있으나, 로직 캡슐화, 중복 제거, 성능 향상 등 유지보수 면에서 명확한 장점이 존재한다. 

 

코드 예시

상황: 쇼핑몰 관리 DB를 만들었다고 가정할 때, 상품 주문 시 재고가 없을 경우 재고가 없다는 메시지를 출력하는 프로시저를 작성해보았다.

 

DELIMITER //

CREATE PROCEDURE PlaceOrder(
    IN p_ProductID INT,
    IN p_Quantity INT
)

 

이와 같이 PlaceOrder 라는 이름의 프로시저를 정의하고, 입력(IN) 매개변수로 받을 상품의 ID(p_ProductID), 구매 희망 수량(p_Quantity)을 먼저 정의한다.

 

참고로 DELIMITER // 라는 것은.. ..

원래 SQL에서 실행 단위를 세미콜론 ; 으로 구분하지만, 프로시저에서는 다양한 SQL문들이 하나의 블록으로 구성되기 때문에, 쿼리문이 중간에 실행되다 잘리는 일이 없도록 하나의 실행 단위를 DELIMITER 뒤에 선언하는 기호로 구분하겠다는 의미이다.

 

DELIMITER // 면 다음 //이 나올때까지가 한 블록이라는 의미!

 

BEGIN
    DECLARE v_Stock INT;
    DECLARE v_OrderID INT;

    -- 현재 재고 확인
    SELECT Stock INTO v_Stock
    FROM Product_SP
    WHERE ProductID = p_ProductID;

    -- 재고가 없거나 부족한 경우
    IF v_Stock IS NULL OR v_Stock < p_Quantity THEN
        INSERT INTO Order_Log (OrderID, LogMessage)
        VALUES (NULL, CONCAT('ProductID ', p_ProductID, ' - 재고 부족으로 주문 실패'));
    ELSE
        -- 주문 생성
        INSERT INTO `Order` (ProductID, Quantity)
        VALUES (p_ProductID, p_Quantity);

        -- 마지막 주문 번호 가져오기
        SET v_OrderID = LAST_INSERT_ID();

        -- 재고 차감
        UPDATE Product_SP
        SET Stock = Stock - p_Quantity
        WHERE ProductID = p_ProductID;

        -- 로그 기록
        INSERT INTO Order_Log (OrderID, LogMessage)
        VALUES (v_OrderID, CONCAT('ProductID ', p_ProductID, ' 주문 성공, 수량: ', p_Quantity));
    END IF;
END;
//

DELIMITER ;

 

다음 BEGIN ~ END 부분에서 실행 로직을 정의한다.

 

DECLARE를 통해 본문 안에서 사용할 로컬 변수를 선언하고(명령어 진짜 직관적임), 원하는 로직대로 쿼리문을 구성한다.

우선 주문하려는 상품의 현재 재고를 갖고오기 위해 Product_SP 테이블에서 v_Stock이라는 변수에 Stock을 할당해주고,

 

미리 매개변수로 입력받은 p_Quantity보다 v_Stock이 적거나 정보가 없으면 log 테이블에 주문 실패했다는 기록을, 주문 가능한 경우 주문 내역과 수량 정보를 입력한다.

 

이후 주문하려는 수량과 주문하려는 개수만큼 숫자나 값을 담아

CALL PlaceOrder(1, 2);
CALL PlaceOrder(2, 1);
CALL PlaceOrder(5, 15);
-- ....

 

와 같이 사용할 경우, 다음과 같이 로그가 남는 것을 확인할 수 있다.

실행 결과

 

📘 Trigger(트리거)

트리거는 특정 이벤트(INSERT, UPDATE, DELETE 등)가 테이블에서 발생할 때 자동으로 실행되는 SQL 블록이다.

일종의 onClick과 같은 이벤트 리스너 역할을 수행하기 때문에, 감사 로그(audit log)를 자동 기록하거나, 데이터 무결성을 보조하거나 알림 기능을 하는 등 간단한 작업을 자동으로 수행할 때 정의하면 좋다.

 

코드 예시

사실 위에서 프로시저로 보여준 예시처럼 주문 발생 시 주문 정보에 대한 로그를 남기는 정도의 액션은 트리거로 작성하는 것이 일반적이다.

 

DELIMITER //

CREATE TRIGGER trg_after_order_insert
AFTER INSERT ON `Order`
FOR EACH ROW
BEGIN
    INSERT INTO Order_Log (OrderID, LogMessage)
    VALUES (NEW.OrderID, CONCAT('주문 번호 ', NEW.OrderID, '이 생성되었습니다.'));
END;
//

DELIMITER ;

 

AFTER INSERT ON `Order` 를 통해 'INSERT' action이 발생할 때 각 행마다(For Each Row) BEGIN ~ END 사이에 기록된 쿼리문이 실행되는 트리거를 작성해준다.

 

그러면,

INSERT INTO `Order` (ProductID, Quantity)
VALUES (2, 1);

이처럼 trigger 이름을 굳이 명시하지 않아도 `Order` 테이블에 INSERT문이 실행되면 자동으로 Order_Log에 기록이 남게 된다.

 

 

LogID 1 ~ 5까지가 프로시저를 통해 기록된 로그, LogID 6번이 insert를 수행했을 때 트리거를 통해 기록된 로그이다.

 

정말 간단하죠?

 

그러나!!!!!!!

SQL은 어디까지나 데이터를 가져오고 처리하는 데 최적화된 언어일 뿐, 잦은 연산이나 흐름 제어 등 '절차형 제어'에는 한계가 있다.

따라서 무턱대고 모든 단순 작업을 프로시저나 트리거를 정의하는 것은 지양해야 할 것이다.

 

📚 정리

비교표