目前仅知道select into 会触发no_data_found异常,原因是将空值赋值给变量,有点像java中空指针异常。
解决方法:
1、将查询语句结果赋予游标,通过sql%notfound检验
2、先select count(*) into v_count 通过判断v_count来检查是否查找到结果(update、delete 可以通过sql%rowcount(隐式游标)判断是否有更新)
例:group by ---->no_data_found
declare
v_count int;
begin
select employee_id into v_count from employees where employee_id=11 group by employee_id;
exception
when no_data_found then
dbms_output.put_line('no_data_found');
end;
此例将会产生no_data_found异常,原因为group by的执行顺序即oracle中先统计count,然后在group by ,如果count=0,再group by 将返回空指针,导致no_data_found异常
使用下例进行规避:
declare
v_count int;
begin
select count(*) into v_count from (select employee_id from employees where employee_id=11 group by employee_id);
dbms_output.put_line(v_count);
exception
when no_data_found then
dbms_output.put_line('no_data_found');
end;
总结:
1.一定要注意在使用group by时,SELECT COUNT(*) INTO语句也会产生NO_DATA_FOUND 异常。
2.一定要理解好group by执行的顺序。