DQL

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
-- 业务逻辑
SELECT in_user_id '用户id',in_product_id '产品id',in_count '购买数量';
end $$
delimiter ;

结果 

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
-- -- 业务逻辑
-- SELECT in_user_id '用户id',in_product_id '产品id',in_count '购买数量';
DECLARE count_money DECIMAL(10,2); -- 用户余额
DECLARE one_price DECIMAL(10,2);-- 产品价格
DECLARE all_price DECIMAL(10,2);-- 总消费
DECLARE result_str VARCHAR(200);-- 用于返回消息
SELECT balance INTO count_money FROM user_wallet WHERE user_id=in_user_id;-- 赋值余额
SELECT price INTO one_price FROM product WHERE product_id=in_product_id;-- 获取产品单价
SET all_price=one_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;
-- 1、修改用户钱包余额
UPDATE user_wallet SET balance=balance-all_price WHERE user_id=in_user_id;
-- 2、修改产品表的产品数量
UPDATE product SET stock = stock-in_count WHERE product_id=in_product_id;
-- 3、添加消费日志
INSERT INTO user_wallet_log(user_id,transaction_type,amount)VALUES(in_user_id,'消费',all_price);
-- 4、添加订单
INSERT INTO `order`(user_id,order_status,payment_time,total_price)VALUES(in_user_id,'已支付','已支付',now(),all_price);
SET new_order_id = LAST_INSERT_ID();
-- 5、添加订单详情
INSERT INTO order_info (order_id,product_id,quantity,unit_price)VALUES(new_order_id,in_product_id,in_count,one_price);
end $$
delimiter ;