/*根据状态修改订单信息*/
DELIMITER $$
DROP PROCEDURE IF EXISTS PRODUCT_SUCCESS $$
CREATE PROCEDURE PRODUCT_SUCCESS(IN i_productId INT)
BEGIN
DECLARE o_userId, o_orderId, o_type, o_status DECIMAL DEFAULT 0;
/*首先这里对游标进行定义*/
DECLARE cur_order CURSOR FOR
SELECT zpo.userId,zpo.id,zpo.type
FROM zc_product_order zpo
WHERE zpo.status = 2002 AND zpo.productId = i_productId;
/*这个是个条件处理,针对NOT FOUND的条件*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET o_userId = -1;
/*接着使用OPEN打开游标*/
OPEN cur_order;
/*把第一行数据写入变量中,游标也随之指向了记录的第一行*/
FETCH cur_order
INTO o_userId, o_orderId, o_type;
/* 遍历数据表 */
REPEAT
CASE o_type
WHEN 1
THEN SET o_status = 2005;
WHEN 2
THEN SET o_status = 2005;
WHEN 3
THEN SET o_status = 2003;
WHEN 4
THEN SET o_status = 2003;
END CASE;
/*1.修改订单状态*/
UPDATE zc_product_order
SET status = o_status
WHERE id = o_orderId;
/*2.新增订单日志记录*/
INSERT INTO zc_product_order_log (orderId, userId, status, createTime)
VALUES (o_orderId, o_userId, o_status, now());
/*退出*/
FETCH cur_order
INTO o_userId, o_orderId, o_type;
UNTIL o_userId = -1
END REPEAT;
/*Finally: cursor need be closed 用完后记得用CLOSE把资源释放掉*/
CLOSE cur_order;
END $$
|
