mysql存储过程游标结果集时，数据没有遍历完整

Question

mysql存储过程游标结果集时，里面调用其他存储过程，如果调用的存储过程查询为空，游标循环就结束了，怎么解决

Answer 1

CREATE DEFINER=`root`@`%` PROCEDURE `insertPresale`()BEGIN&nbsp; &nbsp; #Routine body goes here...&nbsp; &nbsp; DECLARE done INT DEFAULT 0; &nbsp;/*用于判断是否结束循环*/&nbsp; &nbsp; DECLARE goodsId VARCHAR(255);#标记商品id&nbsp; &nbsp; DECLARE flag INT DEFAULT 0;#标记数据库是否包含此条商品记录&nbsp; &nbsp; /*用于存储结果集的记录*/&nbsp; &nbsp; /*定义游标*/&nbsp; &nbsp; DECLARE idCur CURSOR FOR SELECT goods_id FROM `sys_goods_publish` WHERE presale= 1 AND presale_time <= NOW();&nbsp; &nbsp; /*定义 设置循环结束标识done值怎么改变 的逻辑*/&nbsp; &nbsp; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; /*done = true;亦可*/&nbsp; &nbsp; OPEN idCur; &nbsp;/*打开游标*/&nbsp; &nbsp; /* 循环开始 */&nbsp; &nbsp; REPEAT&nbsp; &nbsp; #/* 如果要fetch多列应该这样写，fetch cur/*对应下面的idCur*/&nbsp; &nbsp; FETCH idCur INTO goodsId; &nbsp;/*还可以fetch多列（假设结果集的记录不是单列的话）*/&nbsp; &nbsp; IF NOT done THEN &nbsp;/*数值为非0，MySQL认为是true*/&nbsp; &nbsp; &nbsp; &nbsp; SELECT COUNT(*) INTO flag FROM `itemsinfonew` WHERE TaoBaoitemId = goodsId;&nbsp; &nbsp; &nbsp; &nbsp; IF (flag>0) THEN #如果数据库中有爬取此条记录则删除已用户发布的为准&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DELETE FROM `itemsinfonew` WHERE TaoBaoitemId = goodsId;&nbsp; &nbsp; &nbsp; &nbsp; END IF;&nbsp; &nbsp; &nbsp; &nbsp; INSERT INTO `itemsinfonew` (TaoBaoitemId,CouponID,CreateSourceName)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECT goods_id,coupon_id,SUBSTRING("customer_fd",0.5)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FROM `sys_goods_publish`&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHERE &nbsp;presale_time <= NOW() AND goods_id = goodsId;&nbsp; &nbsp; &nbsp; &nbsp; UPDATE `sys_goods_publish` SET presale = 0 WHERE presale= 1 AND presale_time <= NOW() AND goods_id = goodsId;&nbsp; &nbsp; END IF;&nbsp; &nbsp; UNTIL done END REPEAT;&nbsp; &nbsp; CLOSE idCur; &nbsp;/*关闭游标*/