MySQL周内训参照5、存储过程创建

DELIMITER //
CREATE PROCEDURE create_order_infos(
    IN user_id_param INT,
    IN product_id_param INT,
    IN quantity_param INT,
    OUT order_id_out INT
)
BEGIN
    -- 声明变量
    DECLARE product_price DECIMAL(10, 2);
    DECLARE total_cost DECIMAL(10, 2);
    DECLARE user_balance DECIMAL(10, 2);
    DECLARE wallet_log_id INT;
 
    -- 1. 获取商品的价格
    SELECT price INTO product_price FROM product WHERE product_id = product_id_param;
 
    -- 2. 计算总价格
    SET total_cost = product_price * quantity_param;
 
    -- 3. 获取用户钱包余额
    SELECT balance INTO user_balance FROM user_wallet WHERE user_id = user_id_param;
 
    -- 4. 检查用户余额是否足够
    IF user_balance < total_cost THEN
        -- 余额不足，抛出异常
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '用户余额不足，无法完成订单。';
    ELSE
        -- 5. 开始事务，确保数据一致性
        START TRANSACTION;
 
        -- 5.1 插入用户钱包交易日志
        INSERT INTO user_wallet_log (user_id, transaction_type, amount, transaction_time)
        VALUES (user_id_param, '支出', total_cost, NOW());
        SET wallet_log_id = LAST_INSERT_ID();
 
        -- 5.2 更新用户钱包余额
        UPDATE user_wallet SET balance = balance - total_cost WHERE user_id = user_id_param;
 
        -- 5.3 插入订单信息
        INSERT INTO `order` (user_id, order_status, total_price)
        VALUES (user_id_param, '待支付', total_cost);
        SET order_id_out = LAST_INSERT_ID();
 
        -- 5.4 插入订单详情
        INSERT INTO order_info (order_id, product_id, quantity, unit_price)
        VALUES (order_id_out, product_id_param, quantity_param, product_price);
 
        -- 5.5 更新商品库存（如果需要的话）
        -- UPDATE product SET stock = stock - quantity_param WHERE product_id = product_id_param;
 
        -- 提交事务
        COMMIT;
    END IF;
END //
DELIMITER ;

 
-- 假设我们有一个用户和一个商品，它们的ID分别为1和1，我们要购买数量为2的商品
SET @user_id = 1;
SET @product_id = 1;
SET @quantity = 2;
SET @order_id = NULL;
 
 
-- 调用存储过程
CALL create_order_infos(@user_id, @product_id, @quantity, @order_id);
 
 
-- 查询返回的订单ID
SELECT @order_id;
 
 
-- 可以根据需要查询订单、订单详情、用户钱包、用户钱包交易日志等表来验证数据是否已正确插入。

SET @user_id = 1;
SET @product_id = 7;
SET @quantity = 2;
SET @order_id = NULL;
 
 
-- 调用存储过程
CALL create_order_infos(@user_id, @product_id, @quantity, @order_id);
 
 
-- 查询返回的订单ID
SELECT @order_id;