MySQL: 触发器+游标应用
1. 问题背景:
有一个订单表,一个订单详细表,一个商品表,设置一个触发器,实现当订单成功支付后,订单中的多个商品(一个订单下有一个或多个商品)在商品表中商品库存减少相应数量。
(1) 商品表设计
(2) 订单表设计
(3) 订单详细表设计
2. 触发器设计
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
TRIGGER `store`.`inventory_update` AFTER UPDATE
ON `store`.`orderlist`
FOR EACH ROW BEGIN
DECLARE conId INT; -- 定义变量,接收商品编号
DECLARE conNum INT;-- 定义变量,接收商品购买数量
DECLARE cur_id INT;-- 定义变量,接收订单详细表的编号
DECLARE COUNT INT DEFAULT 1;-- 定义变量,接收订单的商品编号
-- 声明一个游标变量
DECLARE cursor_1 CURSOR FOR SELECT id FROM `orderdetail` WHERE orderId = New.orderId;
-- 当游标没有内容时,count为0
DECLARE EXIT HANDLER FOR NOT FOUND SET COUNT = 0;
-- 当订单支付(1:支付成功)后,商品库存减少相应的数量
IF (new.payWay = 1)
THEN
-- 打开游标
OPEN cursor_1;
-- 使用repeat循环读取游标的内容
REPEAT
FETCH cursor_1 INTO cur_id; -- 每次把游标指向的那行的id赋值给cur_id
-- 查询商品编号赋值给变量
SELECT productId INTO conId FROM `orderdetail` WHERE id=cur_id;
-- 查询商品数量赋值给变量
SELECT productNum INTO conNum FROM `orderdetail` WHERE id=cur_id;
-- 对商品表的库存进行更新操作
UPDATE `products` SET inventory = (inventory-conNum) WHERE id=conId;
UNTIL COUNT = 0
END REPEAT;
CLOSE cursor_1; -- 关闭游标
END IF;
END$$
DELIMITER ;
3. 测试触发器
(1) test1:测试一个订单有一个商品的情况
- 将订单表的payWay属性的值变为1(0:未支付,1:已支付),对订单100001进行支付,该订单购买了商品1(华为电脑)2台。
- 查看相应的商品表中的商品库存是否改变, 发现商品1的库存发生相应改变,测试成功。
(2) test2:测试一个订单有多个商品的情况
- 将订单表的payWay属性的值变为1(0:未支付,1:已支付),对订单100003进行支付,该订单购买了商品1(华为电脑)1台,商品2(华为平板)1台。
- 查看相应的商品表中的商品库存是否改变, 发现商品1和商品2的库存均发生相应改变,测试成功。
注:本人MySQL环境为mysql-8.0.28、SQLyog。