oracle 出错的行数 异常_在Oracle中处理异常

是的,这是真的,但是有一些解决方法,比如内联函数(oracle 12c):

WITH FUNCTION safe_to_NUMBER(input IN VARCHAR2)

RETURN NUMBER IS

i NUMBER;

BEGIN

i:= TO_NUMBER(input);

RETURN i;

EXCEPTION

WHEN OTHERS THEN

RETURN NULL;

END;

SELECT sub.y, safe_to_NUMBER(sub.y)

FROM (

SELECT '2000' AS y FROM DUAL UNION ALL

SELECT '1991' AS y FROM DUAL UNION ALL

SELECT '20--' AS y FROM DUAL UNION ALL

SELECT '09' AS y FROM DUAL UNION ALL

SELECT '11' AS y FROM DUAL UNION ALL

SELECT '95' AS y FROM DUAL

) sub;

结果:

Y SAFE_TO_NUMBER(SUB.Y)

---- ---------------------

2000 2000

1991 1991

20--

09 9

11 11

95 95

6 rows selected.

当然我不会写这样的生产代码:)

正确的方法(

DEFAULT NULL ON CONVERSION ERROR

-从Oracle12Cr2开始提供:

SELECT sub.y, TO_NUMBER(sub.y DEFAULT NULL ON CONVERSION ERROR) AS y

FROM (

SELECT '2000' AS y FROM DUAL UNION ALL

SELECT '1991' AS y FROM DUAL UNION ALL

SELECT '20--' AS y FROM DUAL UNION ALL

SELECT '09' AS y FROM DUAL UNION ALL

SELECT '11' AS y FROM DUAL UNION ALL

SELECT '95' AS y FROM DUAL

) sub;

输出:

Y Y

---- ----------

2000 2000

1991 1991

20--

09 9

11 11

95 95

6 rows selected.