mysql 存储过程 游标不完整遍历的问题

声明游标时候有这么一条语句：

         DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  它是对全局的select有效的，只要有一条select语句返回空，那么就是触发该语句，从而导致游标提前退出。

完整例子：

DROP PROCEDURE IF EXISTS `pro_update_data`;
CREATE PROCEDURE `pro_update_data` ()
BEGIN
DECLARE done BOOLEAN DEFAULT 0;
DECLARE pid VARCHAR (50) DEFAULT NULL;
DECLARE pweight DECIMAL (14, 5) DEFAULT NULL;
DECLARE
cur CURSOR FOR 
SELECT productId FROM product_xxx WHERE value IN ('xxxx');
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

OPEN cur;
REPEAT
FETCH cur INTO pid;
SELECT IFNULL(max(weight),0) INTO pweight FROM p_records WHERE id = pid;
UPDATE product_xxx SET value = pweight WHERE productId = pid ;
UNTIL done
END REPEAT;
CLOSE cur;
END

重点是这条  SELECT IFNULL(max(weight),0) INTO pweight FROM p_records WHERE id = pid;

红字部分，当查询不到数据时，可用聚合函数max和函数ifnull配合来设置默认值。就可以解决了。