Обработка исключений в Oracle

Я пытаюсь разобрать годы, введенные в виде строк (пожалуйста, не заставляйте меня начинать — это то, что есть). Однако есть введенные годы, которые не могут быть проанализированы 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.


exception sql oracle exception-handling parseint
person Patrick Gregorio    schedule 06.06.2018    source источник
comment
Какая у вас версия? Возможно, ПО УМОЛЧАНИЮ... ПРИ ОШИБКЕ ПРЕОБРАЗОВАНИЯ или VALIDATE_CONVERSION   -  person Lukasz Szozda    schedule 06.06.2018
comment
Кажется, вам не хватает некоторых точек с запятой - это не простой SQL-запрос, это PL/SQL   -  person David Faber    schedule 06.06.2018
comment
@ lad2025 Это кажется хорошим решением, но мы на 11g, поэтому похоже, что я не могу его использовать.   -  person Patrick Gregorio    schedule 06.06.2018
comment
Однако следует отметить, что всегда лучше анализировать эти даты (год в вашем случае) в вашем веб-приложении и хранить и работать только с типами дат внутри базы данных и связанного с ней кода. Важно отметить, что никогда не храните даты и временные метки в виде строк в базах данных, отлично, если вы этого еще не сделали или не планируете делать.   -  person Kaushik Nayak    schedule 06.06.2018
comment
@KaushikNayak Мне нужно проанализировать эти годы в запросе, потому что я создаю отчеты с параметрами даты. И да, я всегда использую правильные типы данных, это просто очень старая система, которую мы все еще поддерживаем. Разработан не лучшим образом, но это то, что есть.   -  person Patrick Gregorio    schedule 06.06.2018

Ответы (2)


arrow_upward
2
arrow_downward

В SQL нет обработки исключений; вам нужно будет создать блок PL/SQL для обработки исключения (обратите внимание, что я изменил ваши UNIONs на 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;

Надеюсь это поможет.

person David Faber    schedule 06.06.2018
comment
Я просто хочу добавить, что одним из обходных путей с Oracle 12c может быть WITH FUNCTION, и тогда я смогу обработать его во встроенной функции. - person Lukasz Szozda; 06.06.2018
comment
Я запустил это из PL/SQL и получил ошибку ORA-06550: line 2, column 3: PLS-00428: an INTO clause is expected in this SELECT statement. - person Patrick Gregorio; 06.06.2018
comment
@ uom-pgregorio, да, извините. Вам придется SELECT ... INTO а затем распечатать результаты или выполнить обновление и т. д. Но для этого вам не нужен блок PL/SQL; см. мою правку. - person David Faber; 06.06.2018
comment
Спасибо. Решение регулярного выражения, похоже, работает хорошо. - person Patrick Gregorio; 06.06.2018

arrow_upward
4
arrow_downward

В 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.
person Lukasz Szozda    schedule 06.06.2018