MySQL周内训参照5、存储过程创建_业界新闻

发布时间:2024-07-13 20:12

阅读量:0

编号	人员	题目	总分数	题干	提交内容	得分标准
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;