4주차 작업 기록

장바구니

장바구니 총 결제 금액 계산 (함수)

DROP FUNCTION IF EXISTS get_cart_price;

DELIMITER $$

CREATE FUNCTION get_cart_price (
	p_member_id BIGINT
) 
RETURNS INT
DETERMINISTIC
BEGIN
	DECLARE total_price INT;
    
    SELECT IFNULL(SUM((p.price + pd.surcharge) * ci.quantity), 0) INTO total_price
    FROM cart_item ci
    JOIN product_detail pd ON ci.product_detail_id = pd.product_detail_id
    JOIN product p ON pd.product_id = p.product_id
    WHERE ci.member_id = p_member_id;
    
    RETURN total_price;
    
END $$
DELIMITER ;

price랑 surcharge은 product, product_detail 테이블에 따로 분리해 둬서 조인하도록 함수를 약간 수정하였다.

장바구니 아이템 추가 (프로시저)

사용자가 장바구니에 상품을 담을 때 호출되는 프로시저로, 상품이 이미 담겨있는지 여부에 따라 수량 증가/신규 추가를 자동으로 판단하여 처리하였다.

DROP PROCEDURE IF EXISTS add_cart;

DELIMITER $$

CREATE PROCEDURE add_cart (
	  IN p_member_id BIGINT,
    IN p_product_id BIGINT,
    IN p_quantity INT
) 
BEGIN
	DECLARE exist INT;
    
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
		ROLLBACK;
        RESIGNAL;
	  END;
    
	START TRANSACTION;
    
    SELECT COUNT(*) INTO exist
    FROM cart_item
    WHERE member_id = p_member_id AND product_detail_id = p_product_id;
    
    IF exist > 0 THEN
		UPDATE cart_item
        SET quantity = quantity + p_quantity
        WHERE member_id = p_member_id AND product_detail_id = p_product_id;
	ELSE
		INSERT INTO cart_item (member_id, product_detail_id, quantity)
        VALUES (p_member_id, p_product_id, p_quantity);
	END IF;
    
    COMMIT;
END $$
DELIMITER ;

장바구니 재고 확인 (트리거)

장바구니에 상품을 새로 담거나 수량을 변경할 때, 요청 수량이 실제 남은 재고보다 많을 경우 에러를 발생한다.

DELIMITER $$

CREATE TRIGGER check_stock_insert
BEFORE INSERT ON cart_item
FOR EACH ROW
BEGIN
	DECLARE stock INT;
    
    SELECT stock_quantity INTO stock
    FROM product_detail
    WHERE product_detail_id = NEW.product_detail_id;
    
    IF NEW.quantity > stock
    THEN SIGNAL SQLSTATE '45000'
		 SET MESSAGE_TEXT = '상품 재고가 부족합니다.';
	END IF;
END $$

CREATE TRIGGER check_stock_update
BEFORE UPDATE ON cart_item
FOR EACH ROW
BEGIN
	DECLARE stock INT;
    
    SELECT stock_quantity INTO stock
    FROM product_detail
    WHERE product_detail_id = NEW.product_detail_id;
    
    IF NEW.quantity > stock
    THEN SIGNAL SQLSTATE '45000'
		 SET MESSAGE_TEXT = '상품 재고가 부족합니다.';
	END IF;
END $$
DELIMITER ;

장바구니 상세 조회

장바구니 재고 부족 테스트

DB

postman

ROLLBACK되었기 때문에 DB에는 반영되지 않았다.

장바구니 상품 수량 수정

cart_item 테이블

장바구니 상품 삭제

cart_item 테이블

주문

주문 생성 프로시저 수정

기존 프로시저는 회원의 장바구니에 있는 모든 아이템을 무조건 다 결제하도록 되어있어서, 선택한 아이템만 부분 결제 가능하도록 프로시저를 수정했습니다.

-- 기존에 만들어진 CheckoutPartialCart가 있다면 삭제
DROP PROCEDURE IF EXISTS CheckoutPartialCart;

-- 새로운 프로시저를 생성
DELIMITER //

CREATE PROCEDURE CheckoutPartialCart(
    IN p_member_id BIGINT,
    IN p_delivery_address_id BIGINT,
    IN p_selected_item_ids TEXT
)
BEGIN
    -- 변수 선언
    DECLARE v_item_total BIGINT DEFAULT 0;
    DECLARE v_shipping_fee INT DEFAULT 0;
    DECLARE v_final_price BIGINT DEFAULT 0;
    DECLARE v_shipping_address VARCHAR(255);

    -- 에러 발생 시 롤백
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT 'FAIL: 데이터베이스 오류로 주문이 취소되었습니다.' AS result;
END;

START TRANSACTION;

-- 0. 배송지 ID로 실제 주소 텍스트를 찾아 변수에 조합해 넣기
SELECT CONCAT(city, ' ', district, ' ', detail_address) INTO v_shipping_address
FROM delivery_address
WHERE member_id = p_member_id AND address_id = p_delivery_address_id;

-- 1. 결제하는 회원의 등급별 배송비 조회
SELECT mg.shipping_fee INTO v_shipping_fee
FROM member m
         JOIN member_grade mg ON m.grade_id = mg.grade_id
WHERE m.member_id = p_member_id;

-- 2. 장바구니에 담긴 상품 중 "선택한 상품들"의 순수 결제 금액 계산
SELECT SUM((p.price + pd.surcharge) * ci.quantity) INTO v_item_total
FROM cart_item ci
         JOIN product_detail pd ON ci.product_detail_id = pd.product_detail_id
         JOIN product p ON pd.product_id = p.product_id
WHERE ci.member_id = p_member_id
  AND FIND_IN_SET(ci.product_detail_id, p_selected_item_ids) > 0;

-- 유효성 검사 분기 처리
IF v_shipping_address IS NULL THEN
        ROLLBACK;
SELECT 'FAIL: 유효하지 않은 배송지입니다.' AS result;
ELSEIF v_item_total IS NULL THEN
        ROLLBACK;
SELECT 'FAIL: 선택한 상품을 장바구니에서 찾을 수 없습니다.' AS result;
ELSE
        -- 3. 최종 금액 및 누적 금액 업데이트
        SET v_final_price = v_item_total + v_shipping_fee;

UPDATE member
SET total_purchase_amount = total_purchase_amount + v_item_total
WHERE member_id = p_member_id;

-- 4. 선택한 상품만 재고 차감
UPDATE product_detail pd
    JOIN cart_item ci ON pd.product_detail_id = ci.product_detail_id
    SET pd.stock_quantity = pd.stock_quantity - ci.quantity
WHERE ci.member_id = p_member_id
  AND FIND_IN_SET(ci.product_detail_id, p_selected_item_ids) > 0
  AND pd.stock_quantity >= ci.quantity;

-- 5. 주문 생성 (조합해둔 문자열 주소인 v_shipping_address 사용)
INSERT INTO orders (member_id, order_date, shipping_address, total_price)
VALUES (p_member_id, NOW(), v_shipping_address, v_final_price);

-- 6. 주문 상세 내역에 선택한 상품들만 다중 삽입 (status_id=2 결제완료)
INSERT INTO order_item (order_id, product_detail_id, status_id, quantity, order_price)
SELECT
    LAST_INSERT_ID(),
    pd.product_detail_id,
    2,
    ci.quantity,
    (p.price + pd.surcharge)
FROM cart_item ci
         JOIN product_detail pd ON ci.product_detail_id = pd.product_detail_id
         JOIN product p ON pd.product_id = p.product_id
WHERE ci.member_id = p_member_id
  AND FIND_IN_SET(ci.product_detail_id, p_selected_item_ids) > 0;

-- 7. 장바구니에서 결제 완료된 상품들만 삭제
DELETE FROM cart_item
WHERE member_id = p_member_id
  AND FIND_IN_SET(product_detail_id, p_selected_item_ids) > 0;

COMMIT;
SELECT CONCAT('SUCCESS: 주문 완료. 총 ', v_final_price, '원 결제됨') AS result;
END IF;

END //
DELIMITER ;

주문 생성

order 테이블

order_item 테이블

주문 단건 조회

주문 목록 조회

배송상태 변경

USE ecommerce;

-- 이벤트 스케줄러 활성화
SET GLOBAL event_scheduler = ON;

-- 켜졌는지 확인하는 쿼리 -> ON으로 나와야 함
SHOW VARIABLES LIKE 'event_scheduler';

DELIMITER //

CREATE PROCEDURE AutoUpdateOrderStatus()
BEGIN
    -- 주문접수(1) -> 결제완료(2): 주문한 지 1분 경과
    UPDATE order_item oi
    JOIN orders o ON oi.order_id = o.order_id
    SET oi.status_id = 2
    WHERE oi.status_id = 1 AND o.order_date <= DATE_SUB(NOW(), INTERVAL 1 MINUTE);

    -- 결제완료(2) -> 배송중(3): 주문한 지 24시간 후
    UPDATE order_item oi
    JOIN orders o ON oi.order_id = o.order_id
    SET oi.status_id = 3
    WHERE oi.status_id = 2 AND o.order_date <= DATE_SUB(NOW(), INTERVAL 24 HOUR);

    -- 배송중(3) -> 배송완료(4): 주문한 지 48시간 후
    UPDATE order_item oi
    JOIN orders o ON oi.order_id = o.order_id
    SET oi.status_id = 4
    WHERE oi.status_id = 3 AND o.order_date <= DATE_SUB(NOW(), INTERVAL 48 HOUR);
END //

DELIMITER ;

CREATE EVENT auto_update_order_status_event
ON SCHEDULE EVERY 1 MINUTE
DO CALL AutoUpdateOrderStatus();

주문최소

소감

4주간의 데이터베이스 팀 프로젝트가 드디어 마무리되었다. 데이터베이스의 뼈대인 ERD 논리적/물리적 설계부터 시작해, 주요 기능과 통계, 프로시저, 트랜잭션까지 직접 구현해 보니 하나의 서비스를 기초부터 탄탄하게 쌓아올리는 기분을 느낄 수 있었다.

가장 힘들었던 부분은 주문 생성 프로시저 작성이었다. 단순한 데이터 삽입을 넘어, 재고 확인, 결제 금액 계산, 주문 내역 생성, 장바구니 비우기 등 수많은 과정을 하나의 트랜잭션 단위로 묶어내야 했다. 이 과정에서 롤백과 예외 처리를 꼼꼼하게 체크해 데이터 무결성을 지키는 것이 실제 구현에서 굉장한 힘이 든다는걸 몸소 체험할 수 있었다...

또한, 이번 프로젝트에서 이벤트 스케줄러를 사용해보았는데, 시간에 따른 배송 상태 변경을 백엔드 서버가 아닌 DB 단에서 스스로 처리하도록 자동화해 보면서 데이터베이스가 단순한 저장소를 넘어 능동적으로 동작할 수 있다는 점을 깨달아 무척 편리하고 흥미로웠다.