mysql错误：关于“ERROR 1329 (02000): No data - zero rows fetched, selected, or processed“错误解决办法

在创建函数时使用游标时遇到的错误，这里提供两种检查方法

第一种：检查数据类型是否匹配，这里不详细说明，去检查数据类型就行了，如果没有解决就来到第二种方法。

第二种方法：是因为FETCH没有跳出循环导致的。下面举个例子：

创建一个存储函数avg_func，通过游标统计指定课程的平均分。

delimiter @@
create function avg_fun(course_id char(4)) returns decimal
deterministic
begin
declare total_score decimal;
declare student_count int;
declare avg_score decimal;
declare cur_score decimal;
declare grade_cursor cursor for
select 分数 from grade
where 课程编号=course_id;
set total_score=0;
set student_count=0;
open grade_cursor;
grade_loop:loop
fetch grade_cursor into cur_score;
if done=1 then
leave grade_loop;
end if;
if cur_score is NULL then
leave grade_loop;
end if;
set total_score=total_score+cur_score;
set student_count=student_count+1;
set done=0;
end loop grade_loop;
close grade_cursor;
set avg_score=total_score/student_count;
return avg_score;
end @@

用上面的代码创建函数后，调用时会是发生错误

 但改成下面这样不会，添加了两个语句，注意第二个语句需要放在declare grade_cursor cursor for后。

delimiter @@
create function avg_fun(course_id char(4)) returns decimal
deterministic
begin
declare total_score decimal;
declare student_count int;
declare avg_score decimal;
declare cur_score decimal;
declare done int default 0;
declare grade_cursor cursor for
select 分数 from grade
where 课程编号=course_id;
declare continue handler for not found set done = 1;
set total_score=0;
set student_count=0;
open grade_cursor;
grade_loop:loop
fetch grade_cursor into cur_score;
if done=1 then
leave grade_loop;
end if;
if cur_score is NULL then
leave grade_loop;
end if;
set total_score=total_score+cur_score;
set student_count=student_count+1;
set done=0;
end loop grade_loop;
close grade_cursor;
set avg_score=total_score/student_count;
return avg_score;
end @@