| 【原创】Oracle函数中对于NO | 您所在的位置：网站首页 › oracle存储过程异常处理问题怎么解决 › 【原创】Oracle函数中对于NO |
【原创】Oracle函数中对于NO
|
一直以来有一个困惑，一直没解决，昨天一哥们问我这个问题，决心弄清楚，终于得到了答案。先看下面这个函数： 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;这个函数很简单，是我写的一个测试函数，没什么意义，“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即可： 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;这样，当再执行到该句时，立刻转到异常处理块，抛出一个非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;执行函数调用的过程用伪代码表示如下： 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;以上过程模拟了select语句调用函数的过程，如果出现了异常，在报异常的地方函数就此停止运行，不再往下执行。验证： 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;当再次执行selectu语句的时候，并没有报除数为0的错误，因为查询在第一条语句就停止了，不再往下执行，如果去掉第一条语句： 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;执行查询，立刻报错：ORA-01476:除数为0。如下图： 当执行了异常处理时，若发生了异常，则会立即跳转到异常块中，这和java是一样的，可以选择捕获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;如下图：
也可以在异常中返回一个有意义的提示，告诉调用者一个有意义的信息，如： 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;结果如下图： 这个结论适用于其他情况，无论是在loop中，还是单一查询，只要报了NO_DATA_FOUND异常，都会立即stop，要么跳转到exception，要么返回null，不再继续执行，其实原理很简单，和java是一样的，很好理解。 |
【本文地址】