mysql存储过程游标嵌套示例

CREATE PROCEDURE update_order_categories()
BEGIN
DECLARE $orderId varchar(10);
DECLARE $categoryId INT;
DECLARE STOP INT DEFAULT 0; 
#定义第一个游标 
DECLARE ca_orderId CURSOR FOR SELECT id FROM `order` WHERE categories is null and date(created_time)>=DATE_SUB(date(now()),INTERVAL 3 DAY) ORDER BY created_time;
#定义第二个游标
DECLARE ca_categoryId CURSOR FOR SELECT DISTINCT(g.category_id) FROM `order_item` oi LEFT JOIN goods g ON oi.goods_id=g.id WHERE oi.order_id=$orderId order by g.category_id;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET STOP=1; 

#开始循环第一个游标
    OPEN  ca_orderId;
    FETCH ca_orderId INTO  $orderId;
WHILE STOP <> 1 DO
#订单包含所有商品类型
SET @categoryIds="";

#嵌套游标 循环第二个游标
        OPEN  ca_categoryId;
        FETCH ca_categoryId INTO  $categoryId;
WHILE STOP <> 1 DO
#SELECT CONCAT($orderId,"|",$categoryId);
#获取商品类型
IF (@categoryIds="") THEN
SET @categoryIds=$categoryId;
ELSE
SET @categoryIds=CONCAT(@categoryIds,",",$categoryId);
END IF;

        FETCH ca_categoryId INTO  $categoryId;  
END WHILE; 
        CLOSE ca_categoryId;
SET STOP=0;#这个地方设置为0是关键

#修改字段内容
        update `order` SET categories=@categoryIds WHERE id=$orderId;

    FETCH ca_orderId INTO  $orderId;  
END WHILE; 
    CLOSE ca_orderId;
END;