Хранимые процедуры и функции: серверная логика в БД

Введение

Хранимая процедура/функция — блок процедурной логики, сохранённый и выполняемый непосредственно внутри СУБД, а не в коде клиентского приложения — статья 410 уже использовала функции для реализации триггеров; в этой статье разберём их самостоятельное использование, не привязанное к триггерам, и компромиссы такого подхода.

Концепция

Хранимая функция возвращает значение (или набор строк) и может вызываться внутри обычных SQL-запросов (SELECT my_function(...)), тогда как хранимая процедура (отдельная концепция в некоторых СУБД, хотя терминология и точное разделение варьируется между ними) обычно вызывается отдельной командой (CALL my_procedure(...)) и может выполнять управление транзакциями самостоятельно (COMMIT/ROLLBACK внутри себя), что функциям обычно не разрешено. Перенос части бизнес-логики в хранимые процедуры/функции может уменьшить количество отдельных обращений к БД от приложения (выполнив несколько шагов логики за один вызов) и централизовать логику, используемую несколькими разными клиентскими приложениями, обращающимися к одной БД.

Пример кода: Хранимые процедуры и функции

-- Хранимая функция — вычисление и возврат значения
CREATE OR REPLACE FUNCTION calculate_customer_discount(customer_id_param INTEGER)
RETURNS DECIMAL(5, 2) AS $$
DECLARE
    total_spent DECIMAL(12, 2);
    discount_rate DECIMAL(5, 2);
BEGIN
    SELECT COALESCE(SUM(total_amount), 0) INTO total_spent
    FROM orders WHERE customer_id = customer_id_param;

    IF total_spent > 50000 THEN
        discount_rate := 0.15;
    ELSIF total_spent > 10000 THEN
        discount_rate := 0.10;
    ELSE
        discount_rate := 0.0;
    END IF;

    RETURN discount_rate;
END;
$$ LANGUAGE plpgsql;

-- Использование функции непосредственно в обычном запросе
SELECT name, calculate_customer_discount(id) AS discount_rate
FROM customers;

-- Хранимая процедура — может управлять транзакциями самостоятельно (синтаксис PostgreSQL 11+)
CREATE OR REPLACE PROCEDURE transfer_funds(from_account INTEGER, to_account INTEGER, amount DECIMAL(12, 2))
LANGUAGE plpgsql AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;

    IF (SELECT balance FROM accounts WHERE id = from_account) < 0 THEN
        RAISE EXCEPTION 'Недостаточно средств на счёте %', from_account;
    END IF;

    UPDATE accounts SET balance = balance + amount WHERE id = to_account;
    COMMIT; -- процедура может явно управлять транзакцией, в отличие от обычной функции
END;
$$;

CALL transfer_funds(1, 2, 1000);

Пояснения к коду

calculate_customer_discount() инкапсулирует логику расчёта скидки в одном, централизованном месте внутри самой БД — если эту логику использует несколько разных клиентских приложений (веб-сайт, мобильное приложение, внутренняя административная панель), каждое из них может просто вызвать одну и ту же функцию через обычный SQL, не дублируя саму логику расчёта на стороне каждого отдельного клиента, что устраняет риск рассогласования логики между разными клиентами при необходимости её изменить в будущем. transfer_funds() как процедура (а не функция) явно вызывает COMMIT внутри себя — это демонстрирует ключевое архитектурное отличие процедур от функций в современных версиях PostgreSQL: процедуры могут управлять собственными транзакциями, что функциям не разрешено, делая процедуры более подходящими для логики, явно включающей в себя управление транзакционными границами.

Подводные камни

Перенос значительной части бизнес-логики приложения в хранимые процедуры без учёта снижения переносимости между разными СУБД — синтаксис процедурных языков (PL/pgSQL для PostgreSQL, T-SQL для Microsoft SQL Server, PL/SQL для Oracle) существенно различается между разными СУБД, и логика, реализованная как хранимая процедура для одной конкретной СУБД, как правило, не может быть прямо перенесена на другую без существенного переписывания, что создаёт значительно более тесную привязку (vendor lock-in) к конкретной СУБД по сравнению с логикой, реализованной на уровне приложения на универсальном языке программирования.
Усложнение тестирования бизнес-логики, перенесённой в хранимые процедуры, по сравнению с логикой на уровне приложения — тестирование хранимых процедур требует либо специализированных инструментов тестирования именно для конкретной СУБД, либо запуска реальной тестовой БД (статья 439) для каждого теста, тогда как логика на уровне приложения часто проще покрыть быстрыми unit-тестами без необходимости реального обращения к БД для каждого теста.
Распределение бизнес-логики между несколькими местами без явного, продуманного и документированного разделения ответственности (часть логики — в коде приложения, часть — в триггерах, статья 410, часть — в хранимых процедурах) — без чёткого, осознанного и зафиксированного в документации проекта понимания, какая логика находится где и почему, со временем становится трудно поддерживать целостную картину того, как реально работает система, особенно для новых участников команды, не знакомых с историей принятия этих архитектурных решений.
Использование хранимых процедур исключительно ради потенциального выигрыша в производительности от уменьшения количества обращений к БД, без реального измерения, что эта оптимизация действительно нужна для конкретного приложения — аналогично общему принципу из статьи 403 про денормализацию, перенос логики в хранимые процедуры имеет реальную стоимость (сложность поддержки, переносимости, тестирования), которая должна быть оправдана подтверждённой, измеренной выгодой производительности, а не применяться «по умолчанию» в качестве предполагаемой, но не проверенной оптимизации.