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

编号人员题目总分数题干提交内容得分标准8程序员存储过程20procedure基本要求：
1、添加一个用户下订单的存储过程，存储过程名称叫做【create_order_infos()】
2、要求传入创建订单所必须的参数内容，例如：用户编号、商品编号、购买数量等信息。
3、需要根据传入的信息插入【用户钱包交易日志表】、【订单表】、【订单详情表】信息，修改【用户钱包表】、【商品表】。提交完整的存储过程语句与测试语句。1、函数名称匹配(1分)
2、正确传入参数(3分)
3、写明存储过程的详细注释(5分)
4、正确添加所有日志信息(6分)
5、正确添加订单与订单详情(6分)
6、正确修改用户钱包表与商品表数值(6分)
7、给出存储过程测试语句(3分)

根据题目完成存储过程

1、添加一个用户下订单的存储过程，存储过程名称叫做【create_order_infos()】

2、要求传入创建订单所必须的参数内容，例如：用户编号、商品编号、购买数量等信息。

3、需要根据传入的信息插入【用户钱包交易日志表】、【订单表】、【订单详情表】信息，修改【用户钱包表】、【商品表】。

基础示例：

call create_order_infos(7,2,3);
delimiter $$
create procedure create_order_infos(in in_user_id int,in in_product_id int,in in_count int)
begin 
    -- 判断用户是否有消费资格
    declare count_money decimal(10,2); -- 用户余额
    declare out_price decimal(10,2); -- 产品价格
    declare all_price decimal(10,2); -- 总消费金额
    declare result_str varchar(200); -- 用于返回消息
    declare new_order_id int; -- 新插入的订单id
    select balance into count_money from user_wallet where user_id=in_user_id; -- 赋值有多少钱
    select price into out_price from product where product_id=in_product_id; -- 获取产品的单价
    set all_price = out_price * in_count; -- 总消费金额
    -- 判断是否能消费得起
    if all_price>count_money then
        set result_str = concat('金额不足',all_price,'您当前账户余额：',count_money,'请充值。');
        signal sqlstate '45000' set message_text = result_str;
    end if;
    -- 需要修改以及插入具体数据·共计涉及到5张表的操作·2表修改·3个插入
    update user_wallet set balance=balance-all_price where user_id=in_user_id; -- 1、修改用户钱包余额
    update product set stock=stock-in_count where product_id=in_product_id;    -- 2、修改产品表的产品数量
    insert into user_wallet_log (user_id,transaction_type,amount)values(in_user_id,'消费',all_price);    -- 3、添加消费日志
    insert into `order` (user_id,order_status,payment_status,payment_time,total_price)values(
    in_user_id,'已支付','已支付',now(),all_price);    -- 4、添加订单
    set new_order_id = LAST_INSERT_ID(); -- 获取到最后插入的id编号
    insert into order_info (order_id,product_id,quantity,unit_price)values(new_order_id,in_product_id,in_count,out_price);    -- 5、添加订单详情
end $$
delimiter ;

标准示例：

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 ;

详细解析

这个存储过程create_order_infos用于在数据库中创建一个新的订单，并更新相关的用户钱包余额和订单详情。

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
)

创建一个名为create_order_infos的存储过程，它接受三个输入参数（用户ID、商品ID、购买数量）和一个输出参数（订单ID）。

BEGIN -- 开始存储过程的主体部分。

DECLARE product_price DECIMAL(10, 2);
DECLARE total_cost DECIMAL(10, 2);
DECLARE user_balance DECIMAL(10, 2);
DECLARE wallet_log_id INT;

-- 声明四个局部变量，用于存储商品价格、总费用、用户余额和钱包日志ID。

SELECT price INTO product_price FROM product WHERE product_id = product_id_param;

-- 从product表中选择对应商品ID的商品价格，并将其存储到product_price变量中。

SET total_cost = product_price * quantity_param;

-- 计算总费用，即商品价格乘以购买数量，并将结果存储在total_cost变量中。

SELECT balance INTO user_balance FROM user_wallet WHERE user_id = user_id_param;

-- 从user_wallet表中选择对应用户ID的钱包余额，并将其存储到user_balance变量中。

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());

-- 在user_wallet_log表中插入一条新的交易日志记录，记录用户的支出。

SET wallet_log_id = LAST_INSERT_ID(); -- 获取最后插入的日志记录的ID，并存储在wallet_log_id变量中。

UPDATE user_wallet SET balance = balance - total_cost WHERE user_id = user_id_param;

-- 更新user_wallet表中对应用户的钱包余额，减去总费用。

INSERT INTO order (user_id, order_status, total_price)
VALUES (user_id_param, '待支付', total_cost);

-- 在order表中插入一个新的订单记录，订单状态设置为“待支付”。

SET order_id_out = LAST_INSERT_ID();

-- 获取最后插入的订单记录的ID，并存储在输出参数order_id_out中。

INSERT INTO order_info (order_id, product_id, quantity, unit_price)
VALUES (order_id_out, product_id_param, quantity_param, product_price);

-- 在order_info表中插入订单详情，包括订单ID、商品ID、购买数量和单价。

UPDATE product SET stock = stock - quantity_param WHERE product_id = product_id_param;

-- 在product表中更新对应商品的库存数量，减去购买数量。

COMMIT; -- 提交事务，确保前面的所有数据库操作都被保存。

END IF;

END $$
DELIMITER ;

-- 结束IF语句和存储过程主体，并设置回默认的语句分隔符。

测试存储过程的语句

买不起效果：

-- 假设我们有一个用户和一个商品，它们的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;

总结

至此，本次内训过程能掌握当前的内容就已经很强了，数据库是一个学无止境的知识，而且国内的数据库也在蓬勃的发展，后面我们要学习的内容也会越来越多，希望学生们可以持续的成长。