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

Что такое хранимая procedure

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

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

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

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

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

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

Процедура ничего не возвращает (или возвращает через 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 строк и будет медленнее. БД хороша для операций над множествами, не для процедурного программирования.

Переносимость. Процедуры в 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.

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

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

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

Читайте также

• Транзакции и ACID в SQL
• VIEW в SQL: шпаргалка
• CTE в SQL: шпаргалка
• Оптимизация SQL-запросов

FAQ

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

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

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

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

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

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

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

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