编号 | 人员 | 题目 | 总分数 | 题干 | 提交内容 | 得分标准 |
8 | 程序员 | 存储过程 | 20 | procedure基本要求: 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;
总结
至此,本次内训过程能掌握当前的内容就已经很强了,数据库是一个学无止境的知识,而且国内的数据库也在蓬勃的发展,后面我们要学习的内容也会越来越多,希望学生们可以持续的成长。
还没有评论,来说两句吧...