NO_DATA_FOUND, который не падает 🫥

Коллеги, всем привет! 👋
На связи Денис.

Продолжаем тему тихих ошибок после when others then null. Снова простенькое, для Junior и Middle 😄

Итак, функция-справочник. Таких в любом проекте до фига:

create or replace function get_dept_name(p_dept_id number) return varchar2 is
  l_name departments.department_name%type;
begin
  select department_name into l_name
    from departments
   where department_id = p_dept_id;
  return l_name;
end;

Все знают: select into без строк = ORA-01403: no data found. Проверяем в PL/SQL:

begin
  dbms_output.put_line(get_dept_name(999));
end;

ORA-01403: no data found
ORA-06512: at "HR.GET_DEPT_NAME", line 4

Упало, всё честно ✅

А теперь та же функция в обычном запросе:

select get_dept_name(10)  d10,
       get_dept_name(999) d999
  from dual;

D10              D999
---------------  ------
Administration   (null)

Никакой ошибки. Отдела 999 нет, а запрос спокойно вернул NULL 🤷🏻‍♂️

Почему так?
Для SQL-движка ORA-01403 - это не ошибка, а сигнал "строк больше нет". Он долетает из функции и SQL воспринимает его как "ну нет так нет", подставляя NULL. В PL/SQL-блоке такой магии нет, там исключение летит наверх как положено.

Причём это только про NO_DATA_FOUND. Если в функции select into вернёт несколько строк, запрос упадёт по-честному:

ORA-01422: exact fetch returns more than requested number of rows

Чем это грозит?
🔹 Отчёт с get_dept_name(...) в select-листе. Кривой id - и в отчёте пустая ячейка вместо ошибки. Никто не заметит неделями.
🔹 insert into ... select ..., get_xxx(id) from ... - в таблицу молча едут NULL-ы, если колонка не NOT NULL.
🔹 Одна и та же функция ведёт себя по-разному в PL/SQL и в SQL. Тесты на PL/SQL зелёные, а в отчёте пусто.

Как надо
✅ Решить, что для вас значит "нет данных", и написать это явно. Если это ошибка - перевыбросить своим исключением, его SQL уже не проглотит:

exception
  when no_data_found then
    raise_application_error(-20001, 'Нет отдела ' || p_dept_id);

ORA-20001: Нет отдела 999
ORA-06512: at "HR.GET_DEPT_NAME", line 10

✅ Если NULL - нормальный ответ, тоже напишите это явно: when no_data_found then return null;. Тогда следующий разработчик хотя бы увидит, что это задумано, а не случайно.
✅ А вообще для отчётов лучше left join departments, чем функция на каждую строку. Но это уже другая история 😊

Хозяйке на заметку: прогнал на 11g, 18c и 23ai, поведение везде одинаковое. Разве что в 23ai у ORA-01422 текст немного другой и ссылка на доку.

Документация: предопределённые исключения и SELECT INTO.

Итог
select into в функции, которую вызывают из SQL, - это потенциальный тихий NULL. Пусть функция сама говорит, ошибка это или нормальный ответ, а не SQL решает за вас.

А у вас в команде есть правило, что делать с NO_DATA_FOUND в функциях-справочниках? Или каждый как хочет? Пишите в чатике 💬

С вами был Денис. Всем хорошего дня 🤝

#oracle #plsql #sql #oracledeveloper #базы_данных #Denis_Kivilev

Канал Oracle Developer | Чатик 💬
Мини-курс Оптимизация: Быстрый старт 🚀

📱 YouTube 📱 ВКонтакте 📱 LinkedIn 📱Threads
RUTUBE