주문 처리용 복합 프로시저 예시: 데이터 검증, 상태 업데이트, 로그 기록

오늘은 실제 주문 처리를 위한 프로시저를 만들어볼 거야. 이 프로시저는 여러 단계를 포함해: 데이터 검증, 주문 상태 업데이트, 그리고 로그 기록까지. 레스토랑에서 셰프, 웨이터, 캐셔가 서로 맞춰서 일하는 걸 상상해봐. 우리 프로시저도 단계별로 이런 협업 로직을 구현할 거야.

프로시저 과제 설명

주문 처리 프로시저는 다음 단계를 수행해야 해:

1. 필요한 상품이 창고에 있는지 확인한다.
2. 상품이 충분하면, 창고에서 수량을 차감한다.
3. 주문 상태를 "처리됨"으로 업데이트한다.
4. 성공적으로 처리된 정보를 로그(저널)에 기록한다.
5. 어떤 오류라도 발생하면 모든 변경을 롤백한다.

프로시저 구현

1단계. 사용할 스키마와 테이블 만들기

프로시저를 작성하기 전에, 먼저 사용할 테이블들을 만들어보자.

orders 테이블 — 주문

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_name TEXT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    status TEXT DEFAULT 'Pending'
);

이 테이블은 주문을 저장해. 각 주문에는 고객, 상품 ID, 수량, 그리고 상태(기본값은 "처리 대기중")가 있어.

inventory 테이블 — 창고

CREATE TABLE inventory (
    product_id SERIAL PRIMARY KEY,
    product_name TEXT NOT NULL UNIQUE,
    stock INT NOT NULL CHECK (stock >= 0)
);

창고에 있는 상품 목록을 저장하는 테이블이야. 각 상품은 현재 재고(stock)를 가지고 있어.

order_logs 테이블 — 작업 로그

CREATE TABLE order_logs (
    log_id SERIAL PRIMARY KEY,
    order_id INT NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
    log_message TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

이 로그는 주문 처리 상태 정보를 기록하는 데 사용할 거야.

2단계. 프로시저 구조

여기 여러 단계로 이루어진 프로시저 구조가 있어:

1. 요청한 상품이 창고에 있고, 수량이 충분한지 확인한다.
2. 상품이 충분하면 inventory 테이블에서 수량을 줄인다.
3. 주문 상태를 "처리됨"으로 변경한다.
4. 성공 결과를 order_logs 테이블에 기록한다.
5. 오류가 발생하면 모든 변경을 롤백한다.

3단계. 프로시저 작성하기

이제 위에서 설명한 단계를 수행하는 process_order 프로시저를 작성해보자.

CREATE OR REPLACE FUNCTION process_order(order_id INT)
RETURNS VOID AS $$
DECLARE
    v_product_id INT;
    v_quantity INT;
    v_stock INT;
BEGIN
    -- 1단계: 주문 정보 가져오기
    SELECT product_id, quantity
    INTO v_product_id, v_quantity
    FROM orders
    WHERE order_id = $1;

    -- 주문이 존재하는지 확인
    IF NOT FOUND THEN
        RAISE EXCEPTION 'ID %인 주문이 존재하지 않아.', $1;
    END IF;

    -- 2단계: 창고에 상품이 있는지 확인
    SELECT stock INTO v_stock
    FROM inventory
    WHERE product_id = v_product_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'ID %인 상품이 창고에 없어.', v_product_id;
    END IF;

    IF v_stock < v_quantity THEN
        RAISE EXCEPTION 'ID %인 상품의 재고가 부족해. 요청: %, 남은 재고: %.',
            v_product_id, v_quantity, v_stock;
    END IF;

    -- 3단계: 창고에서 상품 수량 차감
    UPDATE inventory
    SET stock = stock - v_quantity
    WHERE product_id = v_product_id;

    -- 4단계: 주문 상태를 'Processed'로 업데이트
    UPDATE orders
    SET status = 'Processed'
    WHERE order_id = $1;

    -- 5단계: 성공적으로 처리된 주문을 로그에 기록
    INSERT INTO order_logs (order_id, log_message)
    VALUES ($1, '주문이 성공적으로 처리됨.');

EXCEPTION
    WHEN OTHERS THEN
        -- 실패 시 오류 로그 기록
        INSERT INTO order_logs (order_id, log_message)
        VALUES ($1, '주문 처리 중 오류 발생: ' || SQLERRM);

        -- 모든 변경 롤백
        RAISE;
END;
$$ LANGUAGE plpgsql;

이 프로시저를 하나씩 살펴보자.

1. 검증 단계:

   orders 테이블에서 해당 주문이 존재하는지 확인해. 주문이 없으면, 상세 메시지와 함께 예외를 발생시켜. 마찬가지로, 창고에 상품이 있는지와 수량도 체크해.

2. 창고 처리 단계:

   상품이 충분하면, UPDATE로 창고 재고를 줄여.

3. 주문 상태 변경 단계:

   상태를 "Processed"(처리됨)로 바꿔서 주문이 성공적으로 끝났음을 표시해.

4. 로그 기록 단계:

   주문이 성공적으로 처리된 후, order_logs 테이블에 메시지를 추가해서 작업 내역을 남겨.

5. 예외 처리:

   뭔가 잘못되면 EXCEPTION 블록에서 오류 메시지를 로그에 남기고, 모든 변경을 롤백해.

사용 예시

프로시저가 잘 동작하는지 테스트할 데이터를 만들어보자.

-- 창고에 상품 추가
INSERT INTO inventory (product_name, stock)
VALUES ('Laptop', 10), ('Monitor', 5);

-- 주문 추가
INSERT INTO orders (customer_name, product_id, quantity)
VALUES
    ('Alice', 1, 2),
    ('Bob', 2, 1),
    ('Charlie', 1, 20); -- 이 주문은 오류를 발생시켜야 함

이제 프로시저를 테스트해보자:

-- Alice의 주문 처리
SELECT process_order(1);

-- Bob의 주문 처리
SELECT process_order(2);

-- Charlie의 주문 처리 시도(오류 발생)
SELECT process_order(3);

결과:

• Alice와 Bob의 주문은 성공적으로 처리되고, 로그에 기록되며, 창고 재고가 줄어들 거야.
• Charlie의 주문은 창고 재고 부족으로 오류가 발생하고, 오류 로그가 남을 거야.

아래 쿼리로 처리 결과를 확인해보자:

SELECT * FROM inventory; -- 재고 변화 확인
SELECT * FROM orders; -- 주문 상태 변화 확인
SELECT * FROM order_logs; -- 로그 기록 확인

흔한 실수와 팁

1. 실수: SELECT INTO 후 NOT FOUND 체크를 빼먹음.

   쿼리 결과가 없을 때 항상 처리해줘야 해. 안 그러면 예기치 않은 예외가 발생할 수 있어.

2. 실수: EXCEPTION 블록을 안 넣음.

   프로시저에 오류 처리기가 없으면, 예외 발생 시 트랜잭션이 꼬이거나 로직이 깨질 수 있어.

3. 팁: SQL 인젝션 방지하기.

   타입이 명확한 파라미터를 사용하고, 동적 SQL은 꼭 필요할 때만 써.

프로시저 확장하기

실제 서비스에서는 더 많은 검증을 추가할 수 있어. 예를 들면:

• 고객 할인이나 프로모션 적용하기.
• 주문 처리 전에 고객의 신용 한도 체크하기.
• 성공뿐 아니라 롤백도 로그로 남기기.