16 апреля 2026 г.

Stored procedures в SQL: полный гайд

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

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

На практике это выглядит так: вместо того чтобы каждый раз писать «удали старые записи, пересчитай метрики, залей в витрину» тремя отдельными SQL-запросами, вы оформляете всё это одной процедурой refresh_daily_mart() и вызываете её одной строкой CALL refresh_daily_mart();. Логика живёт в БД, клиент только дергает её.

Для аналитика процедуры важны по двум причинам. Первая — часто именно аналитик пишет или читает код процедур, особенно на позициях DA+DE или в банках. Вторая — понимание того, как работают stored procedures, помогает общаться с дата-инженерами и не переизобретать велосипед.

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

Это часто путают. Разница на уровне SQL-стандарта:

Функция возвращает значение (скаляр или таблицу). Её можно вызвать внутри SELECT: SELECT calculate_retention(user_id) FROM users;. Функции обычно без side-effects — не меняют данные.

Процедура ничего не возвращает (или возвращает через OUT-параметры). Её нельзя вызвать внутри SELECT — только через CALL procedure_name() или EXEC. Процедуры могут менять данные: делать UPDATE, DELETE, INSERT, управлять транзакциями.

Простое правило: нужно вычислить значение — функция. Нужно выполнить последовательность действий — процедура.

В PostgreSQL до 11-й версии процедур в полном смысле не было, всё оформлялось функциями. С версии 11 появился CREATE PROCEDURE с возможностью управлять транзакциями. В MySQL и SQL Server процедуры есть давно и в полноценном виде.

Синтаксис в PostgreSQL

Базовый пример:

CREATE OR REPLACE PROCEDURE archive_old_orders(cutoff_date DATE)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Переносим старые заказы в архив
    INSERT INTO orders_archive
    SELECT * FROM orders WHERE created_at < cutoff_date;

    -- Удаляем из основной таблицы
    DELETE FROM orders WHERE created_at < cutoff_date;

    -- Коммитим промежуточно, если нужно
    COMMIT;

    RAISE NOTICE 'Archived orders before %', cutoff_date;
END;
$$;

-- Вызов
CALL archive_old_orders('2024-01-01');

Язык процедур PostgreSQL — это PL/pgSQL, расширение SQL с переменными, условиями, циклами. Выглядит как урезанный Python внутри Oracle-подобного синтаксиса.

Переменные, условия, циклы

Стандартные элементы процедурного программирования:

CREATE OR REPLACE PROCEDURE update_user_segments()
LANGUAGE plpgsql AS $$
DECLARE
    user_rec RECORD;
    segment_name TEXT;
BEGIN
    FOR user_rec IN SELECT user_id, total_spent FROM users LOOP

        IF user_rec.total_spent > 100000 THEN
            segment_name := 'vip';
        ELSIF user_rec.total_spent > 10000 THEN
            segment_name := 'regular';
        ELSE
            segment_name := 'casual';
        END IF;

        UPDATE users SET segment = segment_name WHERE user_id = user_rec.user_id;
    END LOOP;
END;
$$;

Это цикл по таблице с условной логикой. На практике такой код пишется редко — обычно то же самое делается одним UPDATE с CASE WHEN, что быстрее в 10-100 раз. Но бывают случаи, где построчная обработка оправдана: сложная логика с вызовами других процедур, внешние API, транзакционные границы.

Когда процедуры уместны

Не каждая задача просит процедуру. Критерии, когда они оправданы:

Повторяющаяся логика из нескольких шагов. Если вы регулярно делаете цепочку INSERT → UPDATE → DELETE → INSERT, это просится в процедуру. Поддерживать один блок проще, чем четыре разных скрипта в Airflow.

Инкапсуляция транзакций. Нужно, чтобы несколько действий прошли атомарно. Процедура с BEGIN/COMMIT даёт гарантии, что либо всё выполнилось, либо ничего.

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

Предварительные вычисления. Сложная агрегация, которую дорого делать на каждом запросе. Процедура считает один раз за ночь, кладёт в материализованную таблицу.

Разобраться с такими штуками на уровне собеседования удобно через практику — в тренажёре Карьерник есть задачи на SQL разного уровня с разборами, в том числе по DDL и процедурным расширениям.

Когда процедуры — плохая идея

Обратная сторона — когда процедуры усложняют жизнь:

Бизнес-логика приложения. Если правила «как считать выручку» живут в процедурах, это значит, что логика распылена между приложением и БД. Отладка становится кошмаром. Обычно лучше держать бизнес-логику в коде на Python/Java, а БД использовать только как хранилище.

Сложные вычисления. То, что в Python делается за 10 строк pandas, в PL/pgSQL займёт 50 строк и будет медленнее. БД хороша для SET-операций, не для процедурного программирования.

Переносимость. Процедуры в PostgreSQL написаны на PL/pgSQL, в MySQL на MySQL-specific синтаксисе, в SQL Server на T-SQL. При миграции между СУБД всю логику придётся переписывать. Функции и VIEW переносятся проще.

Процедуры vs приложение

Вечный спор: логика в БД или в приложении? Короткий ответ — зависит.

В банках и enterprise процедуры используются массово. Исторически БД была главным местом логики, приложения были тонкими клиентами. Сейчас это меняется, но legacy-системы на миллионы строк PL/SQL никуда не денутся.

В современных IT-продуктах процедур обычно мало. Вся логика в микросервисах, БД — хранилище. ORM (SQLAlchemy, Hibernate) генерирует простые запросы, сложная обработка идёт в коде.

В аналитических пайплайнах используют сочетание: простые трансформации в dbt-моделях (которые по сути — VIEW/TABLE с SQL), периодические задачи — процедуры или функции, запускаемые из Airflow.

Пример из жизни: ежедневная витрина

Типовая задача — ежедневно обновлять агрегированную таблицу с метриками:

CREATE OR REPLACE PROCEDURE refresh_daily_metrics(target_date DATE)
LANGUAGE plpgsql AS $$
BEGIN
    -- Удаляем данные за эту дату, если они уже есть
    DELETE FROM mart_daily_metrics WHERE day = target_date;

    -- Вставляем свежий расчёт
    INSERT INTO mart_daily_metrics (day, dau, revenue, new_users)
    SELECT
        target_date AS day,
        COUNT(DISTINCT user_id) FILTER (WHERE event_name = 'app_open') AS dau,
        SUM(amount) FILTER (WHERE event_name = 'purchase') AS revenue,
        COUNT(DISTINCT user_id) FILTER (WHERE event_name = 'signup') AS new_users
    FROM events
    WHERE event_time::DATE = target_date;

    -- Логируем
    INSERT INTO refresh_log (table_name, target_date, refreshed_at)
    VALUES ('mart_daily_metrics', target_date, NOW());
END;
$$;

-- Вызов из Airflow DAG
CALL refresh_daily_metrics(CURRENT_DATE - 1);

Процедура атомарно обновляет витрину и пишет лог. Если что-то сломалось — всё откатится, старые данные останутся.

Ошибки новичков

Первая — писать всю аналитическую логику в процедурах. Так делали в 2000-х, сейчас это антипаттерн. Простые SELECT с CTE и оконными функциями обычно понятнее и быстрее.

Вторая — забыть про транзакции. Процедура делает DELETE, потом INSERT, потом падает на строчке UPDATE. Без явной транзакции часть данных удалилась, часть вставилась — витрина сломана. Всегда оборачивайте в BEGIN/COMMIT или используйте SAVEPOINT.

Третья — не логировать ошибки. Когда процедура падает ночью, без логов непонятно, в какой строке. Используйте RAISE NOTICE для информации и RAISE EXCEPTION для критических ошибок.

Четвёртая — создавать процедуры, дублирующие функциональность. Если у вас три разные процедуры для обновления трёх похожих витрин, вынесите общий код в отдельную процедуру, которая вызывается из трёх.

На собеседовании

Junior-аналитика про процедуры обычно не спрашивают. Middle+ — могут, особенно в банках и enterprise. Типичные вопросы:

Чем процедура отличается от функции? Функция возвращает значение, процедура — нет. Функцию можно вызвать в SELECT, процедуру — только через CALL.

Зачем процедуры вообще нужны? Инкапсуляция сложной логики, атомарные транзакции, контроль доступа.

Почему бизнес-логика в процедурах — плохая идея? Распыление логики между приложением и БД, сложность отладки и тестирования, vendor lock-in.

Какой язык используется? В PostgreSQL — PL/pgSQL. В SQL Server — T-SQL. В Oracle — PL/SQL. В MySQL — свой процедурный диалект. Все похожи, но не совместимы.

FAQ

Процедура быстрее обычного SQL?

Не обязательно. SQL внутри процедуры выполняется тем же оптимизатором. Выигрыш в том, что клиенту не нужно слать несколько запросов по сети — всё в одном вызове. На низкой latency-связи это экономит время.

Можно ли тестировать процедуры?

Можно, но неудобно. Обычно через pgTAP для PostgreSQL или tSQLt для SQL Server. Это ещё одна причина, почему сложную логику лучше держать в приложении — там нормальные unit-тесты.

Что такое триггер и как он связан с процедурой?

Триггер — это процедура, которая вызывается автоматически при INSERT/UPDATE/DELETE на таблице. Удобно для аудита (логировать изменения) или денормализации (поддерживать счётчики). На практике используйте осторожно: неявные вызовы сложно отлаживать.

dbt заменяет процедуры?

Частично. dbt хорош для трансформаций в формате SELECT → INSERT. Процедурная логика (циклы, условия, изменение данных по строкам) остаётся за процедурами или Python-скриптами в Airflow.

Тренироваться в Telegram