代码之家 › 专栏 › 技术社区 › dokgu

在Oracle中处理异常

parseint exception-handling exception oracle sql

dokgu · 技术社区 · 8 年前

我试图将输入的年份作为字符串进行分析( 请不要让我开始-就是这样 )但是,有些年份是无法通过 TO_NUMBER .

WITH src AS (
        SELECT '2000' AS y FROM DUAL
  UNION SELECT '1991' AS y FROM DUAL
  UNION SELECT '20--' AS y FROM DUAL
  UNION SELECT '09' AS y FROM DUAL
  UNION SELECT '11' AS y FROM DUAL
  UNION SELECT '95' AS y FROM DUAL
)

BEGIN
  SELECT
    s.y,
    TO_NUMBER(s.y) AS p
  FROM src s
EXCEPTION
  WHEN INVALID_NUMBER THEN NULL
END

我从来没有在Oracle中做过异常处理,所以如果这是一个很基本的问题,我很抱歉。

当运行上面的查询时,我得到 ORA-00928: missing SELECT keyword 然后它突出了 BEGIN 关键字。从四处搜寻我所看到的人们所做的就是 BEGIN SELECT 这也是我在做的。我猜我把别的地方搞砸了?

基本上,我想做的是解析字符串,如果抛出异常,我将把它设置为 NULL .

编辑

我尝试了一种不同的方法,并添加了一些分号,@davidfaber在下面评论道。

BEGIN
  SELECT
    s.y,
    TO_NUMBER(s.y) AS p
  FROM (
          SELECT '2000' AS y FROM DUAL
    UNION SELECT '1991' AS y FROM DUAL
    UNION SELECT '20--' AS y FROM DUAL
    UNION SELECT '09' AS y FROM DUAL
    UNION SELECT '11' AS y FROM DUAL
    UNION SELECT '95' AS y FROM DUAL
  ) s;
EXCEPTION
  WHEN INVALID_NUMBER THEN NULL;
END;

我现在有一个不同的错误 ORA-06550: line 2, column 3: PLS-00428: an INTO clause is expected in this SELECT statement .

2 回复 | 直到 8 年前

David Faber 8 年前

SQL中没有异常处理;您需要创建一个PL/SQL块来处理异常(注意,我更改了 UNION S to UNION ALL ):

BEGIN
  WITH src AS (
    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
  )
  SELECT s.y, TO_NUMBER(s.y) AS p
    FROM src s;
EXCEPTION
  WHEN INVALID_NUMBER THEN NULL;
END;
/

但是,您可以使用正则表达式执行“安全”数字转换,而不是使用pl/sql块:

  WITH src AS (
    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
  )
  SELECT s.y, TO_NUMBER(REGEXP_SUBSTR(s.y, '^\d+'))
    FROM src s;

以上将转换值 20-- 到 20 这可能不是你想要的-在这种情况下,试试这个模式 ^\d+$ 而是:

  WITH src AS (
    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
  )
  SELECT s.y, TO_NUMBER(REGEXP_SUBSTR(s.y, '^\d+$'))
    FROM src s;

希望这有帮助。

Lukasz Szozda 8 年前

There's no exception-handling in SQL;

是的,这是真的,但是有一些解决方法,比如内联函数(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.