Месячный Архив: Апрель 2018

SQL and PL/SQL

Мистический пробел, или неправильный способ обработки исключений

На днях коллеги из соседнего отдела столкнулись с проблемой. Иногда один и тот же вызов процедуры на PL/SQL с одними и теми же параметрами давал различные результаты. Причем, если в проблемном блоке, извлекающем одно значение из выборки в переменную, поставить пробел перед точкой с запятой, ошибки нет. Если убрать пробел, все хорошо. Но чудес не бывает. Выяснилось, что практически во всем пакете запросы, возвращающие одну строку в переменные, были обрамлены в блоки обработки исключений следующим образом:

BEGIN
   SELECT VAL
     INTO v_val
     FROM TBL
    WHERE condition ; -- мистический пробел!
EXCEPTION
   WHEN no_data_found OR too_many_rows
   THEN 
      NULL;
END

И вот когда случалось, что точная выборка возвращает больше одной строки, ошибка гасилась на извлечении второй строки, а в переменную v_val уже успевало попасть значение из первой строки выборки. Установка или удаление мистического пробела меняла план запроса, а может, сам Oracle что-то предпринимал, одно ему, оракулу, ведомое, и в переменную в разных случаях попадало разное значение с последующей неправильной работой всей процедуры.

Посмотрите, как работает пример ниже:

Демо   

Вместо разных планов запроса здесь выступает сортировка по случайному значению. Прогнав данный скрипт, вы увидите, что при ошибке too_many_rows почти каждый раз меняется значение, попадающее в переменную v_1.

Рекомендация в данном случае — в обработке ошибок исключение каждого типа проверять отдельно и в каждом из вариантов инициализировать переменную нужным для дальнейшей корректной работы значением! Может появиться соблазн агрегировать результат при помощи MAX() или MIN(), но это тоже может впоследствии привести — нет, не к ошибке, — к неверной работе реального кода.

Страница 1 из 11