刚好一个案例需要在写存储过程,但是总是报错
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,并不会退出循环,可能是死循环。