MySQL触发器+游标应用

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：测试一个订单有一个商品的情况

1. 将订单表的payWay属性的值变为1（0：未支付，1：已支付），对订单100001进行支付，该订单购买了商品1（华为电脑）2台。

2. 查看相应的商品表中的商品库存是否改变， 发现商品1的库存发生相应改变，测试成功。

(2) test2：测试一个订单有多个商品的情况

1. 将订单表的payWay属性的值变为1（0：未支付，1：已支付），对订单100003进行支付，该订单购买了商品1（华为电脑）1台，商品2（华为平板）1台。

2. 查看相应的商品表中的商品库存是否改变， 发现商品1和商品2的库存均发生相应改变，测试成功。
   

注：本人MySQL环境为mysql-8.0.28、SQLyog。