mysql 游标取值为空的问题

DELIMITER $$ 
DROP PROCEDURE IF EXISTS updatePic $$  
CREATE PROCEDURE updatePic()
BEGIN
	DECLARE cover_pic VARCHAR(500);
	DECLARE collect_id BIGINT(20);
	DECLARE cur1 CURSOR FOR SELECT collect_id FROM collect WHERE IFNULL(cover_pic,'')='';
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET cover_pic = '';
	OPEN cur1;
	FETCH cur1 INTO collect_id;
	WHILE(collect_id IS NOT NULL) DO
		SELECT GROUP_CONCAT(cust_pic_url) INTO cover_pic FROM collect_item WHERE collect_id=collect_id and IFNULL(cust_pic_url,'') !='' AND cust_pic_url!=',' LIMIT 6;
		UPDATE collect SET cover_pic=cover_pic WHERE collect_id=collect_id;
		FETCH cur1 INTO collect_id;
	END WHILE;
	CLOSE cur1;
END$$

　　

上面的存储过程一直不能正确运行，每次select collect_id得到的都是null，主要原因是定义的变量名和select语句里取的字段相同，改成下面就OK了

 

DELIMITER $$ 
DROP PROCEDURE IF EXISTS updatePic $$  
CREATE PROCEDURE updatePic()
BEGIN
	DECLARE cover_pic VARCHAR(500);
	DECLARE id BIGINT(20);
	DECLARE cur1 CURSOR FOR SELECT collect_id FROM collect b;
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET id = NULL;
	OPEN cur1;
	FETCH cur1 INTO id;
	SELECT id;
	WHILE(id IS NOT NULL) DO
		SELECT 1;
		SELECT GROUP_CONCAT(cust_pic_url) INTO cover_pic FROM collect_item WHERE collect_id=id AND IFNULL(cust_pic_url,'') !='' AND cust_pic_url!=',' LIMIT 6;
		UPDATE collect SET cover_pic=cover_pic WHERE collect_id=id;
		FETCH cur1 INTO id;
	END WHILE;
	CLOSE cur1;
END$$