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

参考网站:http://www.cnblogs.com/liuzhengdao/archive/2011/03/17/1987201.html

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

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，并不会退出循环，可能是死循环。