存储过程创建

1、添加一个用户下订单的存储过程

-- 存储过程
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;
 
    SELECT price INTO product_price FROM product WHERE product_id = product_id_param;
 
    SET total_cost = product_price * quantity_param;
 
    SELECT balance INTO user_balance FROM user_wallet WHERE user_id = user_id_param;
 
    IF user_balance < total_cost THEN
      
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '用户余额不足，无法完成订单。';
    ELSE
        
        START TRANSACTION;
 
        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();
 
        UPDATE user_wallet SET balance = balance - total_cost WHERE user_id = user_id_param;
 
        INSERT INTO `order` (user_id, order_status, total_price)
        VALUES (user_id_param, '待支付', total_cost);
        SET order_id_out = LAST_INSERT_ID();
 
        INSERT INTO order_info (order_id, product_id, quantity, unit_price)
        VALUES (order_id_out, product_id_param, quantity_param, product_price);
 
        COMMIT;
    END IF;
END $$
DELIMITER ;

2、测试存储过程1：买不起效果

-- 测试存储过程1
 
-- 假设我们有一个用户和一个商品，它们的ID分别为6和6，我们要购买数量为2的商品
SET @user_id = 6;
SET @product_id = 6;
SET @quantity = 2;
SET @order_id = NULL;
 
 
-- 调用存储过程
CALL create_order_infos(@user_id, @product_id, @quantity, @order_id);
 
 
-- 查询返回的订单ID
SELECT @order_id;

 

3、测试存储过程2：买得起的效果 

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