函数报异常oracle,Oracle数据库疑问：函数出现no_data_found不报异常

今天做一个功能的时候，发现Oracle数据库的一个令人很奇怪的点：在sql里面调用的函数，如果函数体出现no_data_found的报错居然不会显式报错！而是终止执行，不报错，并返回null值。真是太费解了！

不信的可以自己试试。我确实是今天才发现这个“问题”。以前写函数，我一定做异常处理的。不知道这算不算数据库的bug。备注：经过测试得知，别的错误，例如TOO_MANY_ROWS和DUP_VAL_ON_INDEX，还是会显式报错的。就NO_DATA_FOUND不会这样子报错！

实例：

CREATE OR REPLACE FUNCTION GET_IDX_IMG2(P_IDX  NUMBER)

RETURN VARCHAR2

IS

--

L_RETURN VARCHAR2(80);

BEGIN

--raise no_data_found;

SELECT 1 INTO L_RETURN FROM DUAL WHERE 1=2;

RETURN -1;

END;

用语句测试：

SELECT GET_IDX_IMG2(1) FROM DUAL

结果是返回null值，而不是我预期的报错：no_data_found！奇怪！

再测试：

用RAISE_APPLICATION_ERROR报错。

CREATE OR REPLACE FUNCTION GET_IDX_IMG2(P_IDX  NUMBER)

RETURN VARCHAR2

IS

--

L_RETURN VARCHAR2(80);

BEGIN

RAISE_APPLICATION_ERROR(-20007,'出错啦！！！',TRUE);

RETURN -1;

END;

再执行sql：

SELECT GET_IDX_IMG2(1) FROM DUAL

这时候，就如期报错：

ORA-20007: 出错啦！！！

ORA-06512: at "APPS.GET_IDX_IMG2", line 7

不过，如果是封装在plsql里面执行，当执行到这个sql的时候，还是会报错的：

先还原函数：

CREATE OR REPLACE FUNCTION GET_IDX_IMG2(P_IDX  NUMBER)

RETURN VARCHAR2

IS

--

L_RETURN VARCHAR2(80);

BEGIN

--raise no_data_found;

SELECT 1 INTO L_RETURN FROM DUAL WHERE 1=2;

RETURN -1;

END;再执行：

DECLARE

L_RETURN VARCHAR2(100);

BEGIN

L_RETURN:=GET_IDX_IMG2(1);

END;

这时候，还是会报错的：

ORA-01403: no data found

ORA-06512: at "APPS.GET_IDX_IMG2", line 8

ORA-06512: at line 4

结论：

很多时候，我们需要封装一些函数来获取值，给sql使用。

这时候就必须要注意了！必须要有异常处理！

如果没异常处理，那后果是：可能无缘无故你的结果返回是空，而且是隐性的！查找问题真是太困难了！

非常建议的做法：

如果是函数，最好加一个参数：P_RAISE IN NUMBER DEFAULT XYG_PUB_CONST_PKG.C_TRUE

这个参数的作用是，确定如果函数出错的时候，是返回固定值，还是报错。

然后，在函数写异常处理。

例如：

CREATE OR REPLACE FUNCTION GET_IDX_IMG2(P_IDX  NUMBER,P_RAISE IN NUMBER DEFAULT 1)

RETURN VARCHAR2

IS

--

L_RETURN VARCHAR2(80);

BEGIN

--raise no_data_found;

SELECT 1 INTO L_RETURN FROM DUAL WHERE 1=2;

RETURN 1;

EXCEPTION

WHEN OTHERS THEN

IF P_RAISE=1 THEN

RAISE_APPLICATION_ERROR(-20007,'GET_IDX_IMG2出错啦！！！错误信息:'||SQLERRM,TRUE);

ELSE

RETURN -1;

END IF;

END;

这样子写函数，安全又有保障了！

如果希望函数就算出错，也不要影响整体的报表进度，那P_RAISE=0即可。

如果希望这个效果：函数出错之后，必须要报错！那P_RAISE=1就OK了。