声明游标时候有这么一条语句:
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配合来设置默认值。就可以解决了。