Хранимые процедуры в базе данных: зачем нужны и как писать

Хранимая процедура - это именованный блок SQL-кода, который хранится прямо внутри базы данных и вызывается по имени, как обычная подпрограмма. Вместо того чтобы гонять десяток отдельных запросов с приложения на сервер и обратно, вы один раз описываете логику на стороне СУБД и потом дёргаете её одной командой. Это снижает сетевой трафик, централизует бизнес-логику и закрывает таблицы от прямого доступа. Разберём, что такое хранимые процедуры, как они устроены в PostgreSQL и других СУБД, чем отличаются от функций и триггеров, и когда их стоит применять. Ниже - интерактивный разбор синтаксиса под вашу СУБД.

Что такое хранимая процедура

Хранимая процедура (stored procedure) - это заранее скомпилированный набор SQL-инструкций, сохранённый под именем в словаре данных СУБД. С точки зрения приложения процедура выглядит как чёрный ящик: вы передаёте параметры и вызываете её командой CALL, а вся логика - выборки, вставки, проверки, циклы - исполняется на сервере базы данных.

Ключевая идея - перенести часть логики из приложения ближе к данным. Если операция затрагивает много строк или требует нескольких связанных запросов, выгоднее выполнить её там, где данные физически лежат, а не пересылать каждый промежуточный результат по сети.

В разных СУБД процедуры пишут на своих процедурных языках: в PostgreSQL это PL/pgSQL, в Oracle - PL/SQL, в MS SQL Server - Transact-SQL, в MySQL - собственный диалект SQL/PSM. Синтаксис похож, но детали (объявление переменных, обработка ошибок, возврат данных) отличаются.

Рассчитать для своей задачи

Синтаксис CREATE PROCEDURE

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

CREATE PROCEDURE add_order(IN customer INT, IN amount NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO orders(customer_id, total)
    VALUES (customer, amount);
END;
$$;

Разберём по частям:

• CREATE PROCEDURE add_order(...) - объявляем процедуру и её имя.
• Список параметров с указанием режима (IN, OUT, INOUT) и типа.
• LANGUAGE plpgsql - на каком языке написано тело.
• AS $$ ... $$ - само тело между долларовыми кавычками (dollar-quoting), чтобы внутри можно было свободно использовать одинарные кавычки.
• Блок BEGIN ... END - исполняемая часть.

Вызывается процедура командой CALL:

CALL add_order(42, 1500.00);

В отличие от функции, процедура не возвращает значение через RETURN и не может стоять внутри запроса SELECT. Её задача - выполнить действие, а не вычислить результат.

Параметры: IN, OUT, INOUT

У параметров есть три режима, и они определяют направление передачи данных:

• IN - входной параметр (режим по умолчанию). Процедура получает значение, но изменения внутри наружу не возвращаются.
• OUT - выходной параметр. Внутри процедура присваивает ему значение, и оно возвращается вызывающей стороне.
• INOUT - двунаправленный: значение и принимается, и возвращается.

Пример процедуры, которая считает остаток на счёте и возвращает его через OUT:

CREATE PROCEDURE get_balance(IN acc INT, OUT balance NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT sum(amount) INTO balance
    FROM transactions
    WHERE account_id = acc;
END;
$$;

Поскольку у процедуры нет привычного возврата, именно OUT-параметры - основной способ отдать данные обратно. При вызове через CALL для OUT-параметра в PostgreSQL передаётся плейсхолдер (например NULL), а драйвер получает заполненное значение.

Рассчитать для своей задачи

Чем процедура отличается от функции

Это самый частый вопрос на собеседованиях и экзаменах. Процедура и функция похожи, но решают разные задачи:

• Возврат значения. Функция обязана вернуть результат (RETURNS), процедура - нет. Функцию используют в выражениях: SELECT calc_tax(price). Процедуру так вызвать нельзя - только CALL.
• Управление транзакциями. Процедура может содержать COMMIT и ROLLBACK внутри тела (это её главное преимущество, появившееся в PostgreSQL 11). Функция исполняется в рамках уже открытой транзакции и собственным COMMIT управлять не вправе.
• Контекст вызова. Функция встраивается в SQL-запрос, процедура вызывается отдельной командой как самостоятельное действие.

Грубое правило: если нужно вычислить и вернуть значение для использования в запросе - пишите функцию; если нужно выполнить последовательность действий с управлением транзакцией - процедуру. Связанная тема - ACID-свойства транзакций, которые как раз и обеспечивают надёжность операций внутри процедуры.

Процедуры и триггеры

Триггер - это не то же самое, что процедура, хотя они тесно связаны. Триггер - это правило, которое автоматически срабатывает на событие (INSERT, UPDATE, DELETE), а исполняет он специальную триггерную функцию. То есть триггер запускает код, но сам кодом не является.

Ключевые отличия:

• Процедура вызывается явно командой CALL из приложения или другого кода. Триггер срабатывает неявно, как реакция на изменение данных.
• Процедура принимает произвольные параметры. Триггерная функция работает с предопределёнными записями NEW и OLD, описывающими строку до и после изменения.
• Триггеры удобны для аудита, поддержания инвариантов и каскадных обновлений; процедуры - для бизнес-операций, запускаемых по требованию.

Тесная связь данных проявляется и в схеме базы: процедура часто обходит несколько таблиц, связанных, например, отношением один ко многим, и держит их согласованными в одной транзакции.

Плюсы хранимых процедур

Почему процедуры до сих пор широко применяются:

• Производительность и трафик. Логика исполняется на сервере, по сети передаётся только вызов и результат, а не десятки промежуточных запросов. Для пакетных операций выигрыш заметный.
• Безопасность. Можно выдать приложению право только на EXECUTE процедуры, не открывая прямой доступ к таблицам. Это снижает риск SQL-инъекций и ограничивает поверхность атаки.
• Централизация логики. Одно правило подсчёта скидки или начисления бонусов лежит в одном месте, а не дублируется в трёх микросервисах на разных языках.
• Повторное использование. Одну процедуру вызывают и из веб-приложения, и из отчётного скрипта, и из джоба по расписанию.

Минусы и когда не стоит

У подхода есть и обратная сторона, о которой часто забывают:

• Сложность сопровождения. Логика в базе хуже ложится в систему контроля версий, тестируется сложнее и менее наглядна, чем код приложения. Большой массив процедур превращается в «теневой бэкенд».
• Привязка к СУБД. PL/pgSQL не перенесётся на Oracle без переписывания. Миграция между СУБД становится дороже.
• Масштабирование. Сервер базы данных масштабировать горизонтально сложнее, чем сервера приложений. Перенося тяжёлую логику в процедуры, вы нагружаете самое узкое звено.

Поэтому современный подход - держать в процедурах операции, которые действительно выигрывают от близости к данным (пакетные обновления, агрегации), а основную бизнес-логику оставлять в приложении.

Пример с обработкой ошибок

Реальная процедура почти всегда содержит проверки и откат. Вот перевод денег между счетами с контролем остатка:

CREATE PROCEDURE transfer(IN src INT, IN dst INT, IN sum NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts SET balance = balance - sum WHERE id = src;
    IF (SELECT balance FROM accounts WHERE id = src) < 0 THEN
        RAISE EXCEPTION 'Недостаточно средств на счёте %', src;
    END IF;
    UPDATE accounts SET balance = balance + sum WHERE id = dst;
    COMMIT;
END;
$$;

Здесь видно сразу несколько важных вещей: проверка условия через IF, генерация исключения через RAISE EXCEPTION (которое автоматически откатит изменения) и явный COMMIT - то, что процедуре доступно, а функции нет.

Частые ошибки

• Путают процедуру и функцию. Пытаются вызвать процедуру в SELECT или ждут от неё RETURN. Процедура вызывается только через CALL и данные отдаёт через OUT-параметры.
• Забывают про LANGUAGE. Без указания языка тела (LANGUAGE plpgsql) PostgreSQL не поймёт, как исполнять блок BEGIN ... END.
• Используют COMMIT в функции. Управление транзакциями доступно процедуре, но не функции - попытка вызвать там COMMIT приведёт к ошибке.
• Складывают всю бизнес-логику в базу. Сотни процедур без тестов и версионирования превращаются в неподдерживаемый «теневой бэкенд».
• Игнорируют обработку ошибок. Без RAISE и блока EXCEPTION процедура молча оставит данные в несогласованном состоянии при сбое.

FAQ

Можно ли вызвать процедуру из другой процедуры? Да. Внутри тела одной процедуры можно использовать CALL other_proc(...). Это нормальный способ декомпозировать сложную логику на переиспользуемые блоки. Главное - следить за транзакционным контекстом: вложенный COMMIT завершит общую транзакцию.

Чем хранимая процедура отличается от обычного SQL-запроса? Запрос отправляется с приложения каждый раз заново и парсится на сервере. Процедура хранится в базе в уже разобранном виде, вызывается по имени и может содержать управляющие конструкции (циклы, условия, обработку ошибок), которых в одиночном SELECT нет.

Поддерживает ли MySQL хранимые процедуры? Да, начиная с MySQL 5.0. Синтаксис похож на стандарт: CREATE PROCEDURE, параметры IN/OUT/INOUT, тело между BEGIN и END. Отличается оформление (разделитель DELIMITER, отсутствие dollar-quoting) и набор встроенных возможностей.

Коротко

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