Перейти к основному содержимому

Хранимые процедуры и пользовательские функции в БД

Хранимые процедуры предназначены для выполнения действий, а пользовательские функции - для вычислений и возврата значений.

Хранимые процедуры​

Хранимые процедуры — это набор инструкций, которые выполняют любые операции с данными, сложную логику или задачи на стороне сервера БД.

  • компилируются один раз и хранятся на сервере
  • могут содержать циклы, условные операторы
  • как в обычном коде в языках программирования, можно реализовать логику работы с данными
  • могут принимать параметры и возвращать результаты, но могут и не возвращать

Как используются?​

  • выполнение сложных операций (сортировка, фильтрация и агрегация), сложной бизнес-логики
  • могут включать несколько операций, которые выполняются как одна транзакция
  • последовательное выполнение нескольких SQL-команд
  • регулярно выполняющихся операций, требующих быстрого повторного исполнения
  • обеспечивают защиту, выполняясь на стороне сервера БД, а также контролируют доступ и выполнение операций. С помощью процедур можно реализовать логику доступа, проверки прав и аутентификации пользователей

Примеры​

  • Процедура принимает ID сотрудника в качестве входного параметра и возвращает его имя из таблицы Employees.
  • Процедура принимает данные о новом заказе (например, клиент, товары, количество) и добавляет соответствующую запись в базу данных.
  • Входной параметр — ID заказа, процедура извлекает информацию о товарах в заказе и вычисляет стоимость всех товаров.

Минусы​

  • Когда сложны, тяжело поддерживаемы, возникают проблемы с рефакторингом.
  • Могут создавать зависимость от конкретной СУБД — затрудняет масштабирование.
  • Хранение бизнес-логики в PLSQL ведет к созданию монолита. Его дальнейшее разбиение затрудняется.
  • Отладка не всегда удобна, логирование и обработка ошибок выполняется вручную.

Пользовательские функции​

Пользовательские функции — это фрагменты кода, предназначенные для выполнения конкретных операций или вычислений над данными.

  • принимают входные параметры, выполняют определенные вычисления и всегда возвращают результат
  • не предназначены для изменения данных или выполнения транзакций

Как используются?​

  • Для сложных логических или арифметических операций.
  • Возвращаемые значения используются в других запросах / выражениях.
  • Для использования в операторах SQL (SELECT, WHERE и т.д.) для вычисления значений.
  • Фильтрации данных, поиска определенных значений или обработки данных по заданным условиям.
  • Для работы с текстовыми данными (разбиение строк, поиск подстрок, замена символов и т.д.)

Примеры​

  • Преобразование даты в определенный формат или вычисление дополнительных параметров на основе входных данных.
  • Входные параметры — ID пользователя и ID ресурса, в ответе булевое значение, указывающее, имеет ли пользователь доступ к этому ресурсу.

Минусы​

  • Не эффективны в сложных запросах.
  • На больших объемах данных могут привести к низкой производительности.
  • Отладка может быть трудной из-за изоляции от основного кода.
  • Могут зависеть от контекста сессии или окружения, что может привести к неожиданным результатам.

Основные различия​

Назначение​

  • Процедуры — для выполнения последовательности операций и изменения данных в БД.
  • Функции — для выполнения вычислений и возвращения результата.

Возвращаемые значения​

  • Процедуры могут возвращать 0 или более значений или изменять состояние БД.
  • Функции возвращают только одно значение.

Типы вызовов​

  • Процедуры — как отдельными операторами SQL, так и из других процедур и функций.
  • Функции — вызываются обычно внутри запросов SQL или в выражениях, где нужно вычислить значения.

Хранимые процедуры и пользовательские функции могут использоваться не только в БД, а еще в:​

  • приложениях на стороне сервера: могут быть частью серверных приложений, написанных на Java, C#, Python и др. Используются для обработки данных на сервере перед отправкой их клиенту.
  • интеграциях в веб.
  • интеграциях с внешними системами, такими как API, сервисы и внешние БД, чтобы обрабатывать данные и для взаимодействия между приложениями и платформами.

Подборка материалов по хранимым процедурам и пользовательским функциям​

  1. Пользовательские функции
  2. Функции с примерами
  3. Как создавать функции
  4. Oracle процедуры и функции PL/SQL с примерами
  5. Вред хранимых процедур
  6. Хранимые функции. За и против
  7. Функции и процедуры в Postgeres
  8. Процедура vs Функция MySQL: когда и зачем использовать?
  9. Хранимые процедуры для повышения производительности системы
  10. Транзакции хранимых процедур
  11. Применение Tarantool: хранимые процедуры
  12. «Хранимые процедуры» в Redis

Видео​

  1. Процедуры и функции в MySQL
  2. Процедуры и функции в PostgreSQL
  3. Плюсы, минусы и ограничения хранимых процедур
  4. Хранимые процедуры. Пользовательские функции