mysql 1329,如何摆脱“错误1329：无数据-提取，选择或处理零行”

I have a stored procedure which does not need to return any values. It runs smoothly and without any problem. However, it outputs an error message after finishing its run:

Error: No data - zero rows fetched, selected, or processed

How can I get rid of this error message?

CREATE PROCEDURE `testing_proc`()

READS SQL DATA

BEGIN

DECLARE done INT DEFAULT 0;

DECLARE l_name VARCHAR(20);

DECLARE my_cur CURSOR FOR

SELECT name FROM customer_tbl;

OPEN my_cur;

my_cur_loop:

LOOP FETCH my_cur INTO l_name;

IF done = 1 THEN

LEAVE my_cur_loop;

END IF;

INSERT INTO names_tbl VALUES(l_name);

END LOOP my_cur_loop;

CLOSE my_cur;

END

解决方案

I guess you just forgot to include the following line in your post:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

Your code is correct, but bug/strange behaviour of mysql causes the warning to appear even if it was handled. You can avoid that if you add a "dummy" statement to the end of your procedure that invovles a table and is successful, this will clear the warning. (See http://dev.mysql.com/doc/refman/5.5/en/show-warnings.html)

In your case:

SELECT name INTO l_name FROM customer_tbl LIMIT 1;

after the end of the loop.

On MySQL 5.5.13 the warning disappears, on Linux and Windows.

I commented on MySQL Bug 60840 and I hope they will fix it some time in the future...