mysql存储过程报错_MySQL存储过程错误No data - zero rows fetched, selected, or processed

刚好一个案例需要在写存储过程，但是总是报错

No data - zero rows fetched, selected, or processed

存错过程代码如下

DELIMITER $$

DROP PROCEDURE IF EXISTS `jbpm`.`refreshRoster` $$

CREATE DEFINER=`root`@`%` PROCEDURE `refreshRoster`(in hostname varchar(30))

BEGIN

DECLARE uname varchar(30);

DECLARE cur1 CURSOR FOR SELECT username FROM ofuser;

delete from ofroster;

OPEN cur1;

read_loop: LOOP

FETCH cur1 INTO uname;

update temp set TEXT_='1';

......

END LOOP;

CLOSE cur1;

END $$

DELIMITER ;

修改后

DELIMITER $$

DROP PROCEDURE IF EXISTS `jbpm`.`refreshRoster` $$

CREATE DEFINER=`root`@`%` PROCEDURE `refreshRoster`(in hostname varchar(30))

BEGIN

DECLARE i int;

DECLARE tc int;

DECLARE flag int;

DECLARE uname varchar(30);

DECLARE cur1 CURSOR FOR SELECT username FROM ofuser;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET flag = 3;

SELECT count(*) into tc FROM ofuser;

delete from ofroster;

SET i=0;

OPEN cur1;

read_loop: LOOP

FETCH cur1 INTO uname;

update temp set TEXT_='1';

IF i > tc THEN

LEAVE read_loop;

ELSE

.......

END IF;

SET i=i+1;

END LOOP;

CLOSE cur1;

END $$

DELIMITER ;

增加了红色语句部分，我创建一个表temp，仅一个字段，然后每次循环时更新一下，这样就不会报错。其中使用了i和tc，因为我发现单纯使用FETCH，并不会退出循环，可能是死循环。