Oracle函数中对于NO_DATA_FOUND异常处理的研究

时间:2021-07-26 20:11:02

一直以来有一个困惑,一直没解决,昨天一哥们问我这个问题,决心弄清楚,终于得到了答案。
先看下面这个函数:

Oracle函数中对于NO_DATA_FOUND异常处理的研究
Oracle函数中对于NO_DATA_FOUND异常处理的研究
create or replace function fn_test(c_xm varchar) return varchar2 as
V_P1 varchar(10);
begin
select name into V_p1 from t1 where 1 = 2;--将name查出赋值给v_p1
return 'test' || c_xm;
end;
Oracle函数中对于NO_DATA_FOUND异常处理的研究
Oracle函数中对于NO_DATA_FOUND异常处理的研究

这个函数很简单,是我写的一个测试函数,没什么意义,“select name into V_p1 from t1 where 1 = 2;”这句话有经验的人一看就知道它会报错,因为这个查询返回的结果集是空,会报一个错,将其赋值时,pl/sql引擎会认为它没有数据,是一个null,这很类似于java中的空指针异常。当我们调试该函数的时候,到这一句,立刻会报ORA-1403错误:没有数据。
但是如果在sql中调用该函数呢?执行以下查询:
select fn_test('1') from dual;
结果是返回一个空记录,没有任何报错。这是为什么呢?难道遇到了bug?如果是存储过程呢?无论如何调试还是直接调用,此处都会报错,有兴趣的可以验证一下,我就不验证了,因为我之前碰到过许多次了,所以一般在select into时,如果没有把握这个结果集一定有,都会select count一下然后再into。
这究竟是怎么回事呢?
下面是我的猜测:
对于查不到结果集来说,这不是什么很严重的错误,没有就没有了,不用报错吧?如我们执行一条sql,select * from t1 where 1=2;如果这个sql没有查到数据,难道就非得报个错?基于这个考虑,在sql调用函数时,如果这种NO__DATA_FOUND的异常,可能sql解析器就直接处理了,不用再报错了.因为它并不像诸如找不到表、找不到字段、没有权限等等的错误严重。
这仅仅是猜测,但是究竟为什么呢?
最后我在asktom的网站上找到了答案:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:10321465390114

从1楼这哥们的提问来看,他也是很郁闷,他甚至和我提出了一样的疑问:“为什么过程就没有这种问题?”
我看完了后,总结了一下,原因大致为:
当select a into b 时,如果没有命中结果集,则当前的查询为空,在sql语句的查询中,解析器仅仅是认为“没有数据(no data found)”而已,而不是将它作为一个错误,然后返回一个null,函数就此停止不再往下执行,tom的解释也比较诙谐:

Under the covers, SQL is raising back to the client application "hey buddy -- no_data_found".  The
client in this case says "ah hah, no data found means 'end of data'" and stops.

但是在pl/sql中却不是,pl/sql的处理方式却是将它认为是一个错误,

Under the covers, PLSQL is raising back to the client application "hey -- no_data_found.  The
client in this case says "uh-oh, wasn't expecting that from PLSQL -- sql sure, but not PLSQL. Lets
print out the text that goes with this exceptional condition and continue on"

NO_DATA_FOUND并不是一个错误,而且一个意外的情况,这类似于空指针异常,而这个意外情况只是没找到数据而已,当调用者不同时,对其的处理也不同,当sql查询调用时,遇到这个异常就认为是没有数据,然后返回一个Null,但是当PL/sql调用时,会认为这是一个不好的情况,转由异常处理块处理。
归根结底一句话,NO_DATA_FOUND都会由调用者捕获,只是调用者对这个异常的处理方式不一样而已。如果想在sql调用时报错怎么办?其实很简单,捕获这个NO_DATA_FOUND异常,然后raise即可:

Oracle函数中对于NO_DATA_FOUND异常处理的研究
Oracle函数中对于NO_DATA_FOUND异常处理的研究
create or replace function fn_test(c_xm varchar) return varchar2 as
V_P1 varchar(10);
begin
select name into V_p1 from t1 where 1 = 2;
return 'test' || c_xm;
exception
when no_data_found then
/*RAISE_APPLICATION_ERROR(-20000, 'no data found');*/--抛出自定义的异常也行
raise program_error;
end;
Oracle函数中对于NO_DATA_FOUND异常处理的研究
Oracle函数中对于NO_DATA_FOUND异常处理的研究

这样,当再执行到该句时,立刻转到异常处理块,抛出一个非NO_DATA_FOUND异常,调用者不认识,认为是一个错误或者很严重的异常,只能报错给客户端了。

对于【异常总是会抛出,只是客户端(调用者)对其处理方式不一样】,可以这样理解:
当用pl/sql调试时,运行到1403异常处,pl/sql调试器的处理方式就是立刻弹出一个错误信息;而sql调用时,这地方异常也会抛出,但是sql查询器会认为,哦,没有数据,查询器选择了用一个null值应对这个异常,而作为执行sql的我们,所看到的就是一个空值,而没有报错。
这类似于我们写java程序对异常的处理,有的异常我们会直接抛给用户,让用户知道出错了,而有的异常被我们吃掉,然后选择了别的处理方法,用户看到的是另外一个情形,他根本不知道后台有异常发生。
这也就是对于【异常存在,只是怎么应对】的解释。

有个结论:如果在function中,如果某行报了NO_DATA_FOUND,也没有处理块,那么不好意思,pl/sql语句就此就不在执行,这和普通的java程序是一样的,什么地方抛出异常,程序在此就停止运行,要么转到异常处理部分,要么就此stop,如果在sql查询语句中调用这个fn_test函数:
select fn_test('1') from dual;
执行函数调用的过程用伪代码表示如下:
Oracle函数中对于NO_DATA_FOUND异常处理的研究
Oracle函数中对于NO_DATA_FOUND异常处理的研究
begin:
select fn_test('1') from dual;--开始解析sql查询语句
call fn_test;--发现值来自于函数,开始调用fn_test
var result;--定义临时变量接收结果
try{
result=fn_test('1');
}catch(NO_DATA_FOUND){--如果是NO_DATA_FOUND异常则null处理
result=null;
}catch(OTHERS){--如果其他异常则抛出
throw others;
}
select result from dual;
end;
Oracle函数中对于NO_DATA_FOUND异常处理的研究
Oracle函数中对于NO_DATA_FOUND异常处理的研究

以上过程模拟了select语句调用函数的过程,如果出现了异常,在报异常的地方函数就此停止运行,不再往下执行。
验证:

Oracle函数中对于NO_DATA_FOUND异常处理的研究
Oracle函数中对于NO_DATA_FOUND异常处理的研究
create or replace function fn_test(c_xm varchar) return varchar2 as
V_P1 varchar(10);
begin
select name into V_p1 from t1 where 1 = 2;--NO_DATA_FOUND,也会报错,但是sql解析器会以null返回
select 1/0 into v_p1 from dual;--除数为0,会报错
return 'test' || c_xm;
end;
Oracle函数中对于NO_DATA_FOUND异常处理的研究
Oracle函数中对于NO_DATA_FOUND异常处理的研究

Oracle函数中对于NO_DATA_FOUND异常处理的研究

当再次执行selectu语句的时候,并没有报除数为0的错误,因为查询在第一条语句就停止了,不再往下执行,如果去掉第一条语句:

Oracle函数中对于NO_DATA_FOUND异常处理的研究
Oracle函数中对于NO_DATA_FOUND异常处理的研究
create or replace function fn_test(c_xm varchar) return varchar2 as
V_P1 varchar(10);
begin
--select name into V_p1 from t1 where 1 = 2;--NO_DATA_FOUND,注释掉该行
select 1/0 into v_p1 from dual;--除数为0,会报错
return 'test' || c_xm;
end;
Oracle函数中对于NO_DATA_FOUND异常处理的研究
Oracle函数中对于NO_DATA_FOUND异常处理的研究

执行查询,立刻报错:ORA-01476:除数为0。如下图:
Oracle函数中对于NO_DATA_FOUND异常处理的研究

当执行了异常处理时,若发生了异常,则会立即跳转到异常块中,这和java是一样的,可以选择捕获NO_DATA_FOUND异常然后外抛。

Oracle函数中对于NO_DATA_FOUND异常处理的研究
Oracle函数中对于NO_DATA_FOUND异常处理的研究
create or replace function fn_test(c_xm varchar) return varchar2 as
V_P1 varchar(10);
begin
select name into V_p1 from t1 where 1 = 2;--NO_DATA_FOUND,会立即跳转到exception块,不再继续执行
select 1 / 0 into v_p1 from dual; --除数为0,会报错,但是这句没有机会执行了
return 'test' || c_xm;
exception
when NO_DATA_FOUND then
raise_application_error('-20000', '没找到数据');--异常外抛给调用者,直接报错
end;
Oracle函数中对于NO_DATA_FOUND异常处理的研究
Oracle函数中对于NO_DATA_FOUND异常处理的研究

如下图:
Oracle函数中对于NO_DATA_FOUND异常处理的研究

Oracle函数中对于NO_DATA_FOUND异常处理的研究

也可以在异常中返回一个有意义的提示,告诉调用者一个有意义的信息,如:

Oracle函数中对于NO_DATA_FOUND异常处理的研究
Oracle函数中对于NO_DATA_FOUND异常处理的研究
create or replace function fn_test(c_xm varchar) return varchar2 as
V_P1 varchar(10);
begin
select name into V_p1 from t1 where 1 = 2;--NO_DATA_FOUND,会立即跳转到exception块,不再继续执行
select 1 / 0 into v_p1 from dual; --除数为0,会报错,但是这句没有机会执行了
return 'test' || c_xm;
exception
when NO_DATA_FOUND then
return '没有找到数据!';
end;
Oracle函数中对于NO_DATA_FOUND异常处理的研究
Oracle函数中对于NO_DATA_FOUND异常处理的研究

结果如下图:

Oracle函数中对于NO_DATA_FOUND异常处理的研究

这个结论适用于其他情况,无论是在loop中,还是单一查询,只要报了NO_DATA_FOUND异常,都会立即stop,要么跳转到exception,要么返回null,不再继续执行,其实原理很简单,和java是一样的,很好理解。