剛好一個案例需要在寫存儲過程，但是總是報錯

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

總結

以上是生活随笔為你收集整理的mysql存储过程报错_MySQL存储过程错误No data - zero rows fetched, selected, or processed的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。