Коллеги, всем привет! 👋
На связи Денис.
Можно ли вызвать свою PL/SQL-функцию прямо в
select? Можно. Но за этим «можно» прячется пачка ограничений и пара граблей по производительности. Давайте пройдёмся.Как это выглядит
create or replace function get_vat(p_sum number) return number is
begin
return round(p_sum * 0.2, 2);
end;
/
select id, amount, get_vat(amount) vat
from orders
where get_vat(amount) > 100;
Функция может жить на уровне схемы или в пакете (тогда она должна быть в спецификации). Вызывать можно в select-листе, where, order by, group by, в values у insert и set у update.
Ограничения
🔹 Из запроса (select) функция не может менять данные. Вставили insert внутрь - получите ORA-14551.
🔹 Никаких commit, rollback и DDL внутри - ORA-14552.
🔹 Если функция вызвана из insert/update/delete/merge, она не может читать или менять таблицу, которую этот оператор модифицирует - ORA-04091 (mutating table).
🔹 Параметры только IN. Типы параметров и результата - SQL-типы: никаких PL/SQL-записей и ассоциативных массивов. BOOLEAN в SQL появился только в 23ai.
Да, ORA-14551 обходится через
pragma autonomous_transaction. Но это костыль: отдельная транзакция на каждую строку. Хозяйке на заметку - так лучше не делать 🤷🏻♂️Подводные камни
🔸 Переключение контекста SQL ↔️ PL/SQL на каждый вызов. На миллионе строк это уже заметно.
🔸 Количество вызовов не гарантировано. Oracle может вызвать функцию больше раз, чем строк в выборке. Не завязывайте логику на побочные эффекты.
🔸 Согласованность чтения. Если функция сама делает select, каждый такой запрос видит данные на момент своего вызова, а не на момент старта основного запроса. На долгом запросе можно получить «несогласованный» результат.
Как ускорить
-- кэширование скалярного подзапроса
select id, (select get_vat(amount) from dual) vat
from orders;
🔹 Скалярный подзапрос, как выше: Oracle кэширует результат для одинаковых входных значений, и функция вызывается реже. Как устроен этот кэш и почему на него нельзя слепо полагаться - тема для отдельного поста. Интересно? Ставьте 👍🏻 или пишите в комментах - напишу.
🔹
deterministic - подсказка, что для одних и тех же входных данных результат одинаковый. Нужна и для функционального индекса.🔹 С 12c:
pragma udf в функции или with function прямо в запросе - дешевле переключение контекста.🔹 А лучше всего - если логику можно написать на чистом SQL, пишите на SQL.
Итог
Функции в SQL - нормальный инструмент. Но это не бесплатно и не безгранично. Прежде чем тащить функцию в запрос на миллионы строк, подумайте, нельзя ли обойтись без неё.
А вы используете функции в запросах или бьёте за это по рукам на ревью? Пишите в чатик 💬
С вами был Денис. Всем быстрых запросов и без мутирующих таблиц 🤝
#oracle #sql #plsql #оптимизация #функции #фишки #Denis_Kivilev
Канал Oracle Developer | Чатик 💬
Мини-курс Оптимизация: Быстрый старт 🚀
📱 YouTube 📱 ВКонтакте 📱 LinkedIn 📱Threads
RUTUBE