Что такое хранимые процедуры SQL: определение, синтаксис и применение

Хранимая процедура в SQL: что это такое и как работает

Хранимая процедура хранится непосредственно на сервере базы данных — в отличие от обычного SQL-запроса, который приложение отправляет по сети при каждом обращении. При выполнении команды CREATE PROCEDURE сервер разбирает код, строит оптимальный план выполнения и сохраняет его в кэше. При следующем вызове повторная компиляция не нужна: СУБД использует уже готовый план, что ускоряет работу при многократных обращениях.

Структура хранимой процедуры состоит из четырёх частей:

• Заголовок — имя процедуры и список параметров.
• Блок объявлений — переменные с типами (DECLARE).
• Тело — основная логика между BEGIN и END.
• Блок обработки ошибок — EXCEPTION / CATCH / HANDLER.

Хранимую процедуру легко спутать с функцией или триггером, но ключевое отличие уже в вызове: процедуру запускают явно командой CALL или EXEC, функция встраивается в тело запроса SELECT, а триггер срабатывает автоматически при изменении данных в таблице.

Зачем нужны хранимые процедуры: преимущества и ограничения

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

Кэш плана — главный источник прироста производительности: СУБД не разбирает и не оптимизирует запрос заново при каждом вызове. GRANT EXECUTE позволяет предоставить право на запуск без прямого доступа к самим таблицам — это снижает риск случайного или намеренного повреждения данных.

Синтаксис хранимых процедур: PostgreSQL, MySQL, SQL Server, Oracle

Синтаксис хранимых процедур существенно различается в зависимости от системы управления базами данных. Общая логика одна: заголовок с именем и параметрами, тело между BEGIN и END, механизм обработки ошибок. Но команды создания, разделители, префиксы параметров и способ изменения готовой процедуры у каждой СУБД свои.

Если работа с базами данных — направление, в котором вы хотите развиваться, в рамках федерального проекта «Активные меры содействия занятости» нацпроекта «Кадры» можно пройти программу «Специалист по аналитике и базам данных в информационных системах» — бесплатно, онлайн, с нуля. Подробности — в каталоге программ обучения.

PostgreSQL (PL/pgSQL)

PostgreSQL использует процедурный язык PL/pgSQL — расширение SQL с переменными, условиями, циклами и обработкой исключений. Тело процедуры заключается в долларовое квотирование: $$ … $$. Это избавляет от необходимости экранировать одинарные кавычки внутри кода.

CREATE OR REPLACE PROCEDURE transfer_funds(

p_from INT, p_to INT, p_amount NUMERIC

)

LANGUAGE plpgsql AS $$

DECLARE

v_balance NUMERIC;

BEGIN

SELECT balance INTO v_balance FROM accounts WHERE id = p_from;

IF v_balance < p_amount THEN

RAISE EXCEPTION ‘Недостаточно средств’;

END IF;

UPDATE accounts SET balance = balance — p_amount WHERE id = p_from;

UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;

COMMIT;

END;

$$;

Вызов: CALL transfer_funds(1, 2, 500.00). PostgreSQL поддерживает перегрузку процедур — несколько процедур с одним именем, но разными параметрами. DEFAULT-значения и именованный вызов: CALL search_products(p_category => ‘books’) — аргумент передаётся по имени, остальные принимают значения по умолчанию.

MySQL

В MySQL перед созданием процедуры обязательно менять разделитель командой DELIMITER. Без этого сервер завершит команду на первой точке с запятой внутри тела — процедура не создастся.

DELIMITER //

CREATE PROCEDURE get_order(IN p_id INT)

BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN

ROLLBACK;

RESIGNAL;

END;

START TRANSACTION;

SELECT * FROM orders WHERE id = p_id;

COMMIT;

END//

DELIMITER ;

Вызов: CALL get_order(42). Изменить тело существующей процедуры в MySQL нельзя — только удалить и создать заново: ALTER PROCEDURE изменяет лишь метаданные (комментарий, характеристики безопасности), но не тело.

SQL Server — хранимые процедуры в MS SQL (T-SQL)

В SQL Server хранимые процедуры пишутся на языке Transact-SQL (T-SQL). Все параметры и локальные переменные начинаются с символа @. Это обязательное правило MS SQL — в PostgreSQL и MySQL такой префикс не используется.

CREATE PROCEDURE usp_get_customer

@customer_id INT,

@include_orders BIT = 0

AS

BEGIN

SET NOCOUNT ON;

BEGIN TRY

SELECT * FROM customers WHERE id = @customer_id;

IF @include_orders = 1

SELECT * FROM orders WHERE customer_id = @customer_id;

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION;

THROW;

END CATCH

END;

Вызов с именованными параметрами: EXEC usp_get_customer @customer_id = 5, @include_orders = 1. Для изменения используется ALTER PROCEDURE без удаления — это уникальная возможность среди рассмотренных СУБД. Безопасный динамический SQL реализуется через sp_executesql с параметрами вместо конкатенации строк.

Oracle (PL/SQL)

Oracle использует процедурный язык PL/SQL. Ссылочный тип table.column%TYPE позволяет объявить переменную с типом конкретного столбца — процедура не сломается при изменении схемы таблицы.

CREATE OR REPLACE PROCEDURE get_employee(p_id IN employees.id%TYPE)

AS

v_name employees.name%TYPE;

BEGIN

SELECT name INTO v_name FROM employees WHERE id = p_id;

DBMS_OUTPUT.PUT_LINE(v_name);

EXCEPTION

WHEN NO_DATA_FOUND THEN

RAISE_APPLICATION_ERROR(-20001, ‘Сотрудник не найден’);

END get_employee;

/

Oracle компилирует процедуры в байт-код — это ускоряет повторное выполнение. Пакеты (Packages) группируют связанные процедуры, функции и типы в один объект схемы. PRAGMA AUTONOMOUS_TRANSACTION позволяет выполнять процедуру в независимой транзакции, не затрагивающей вызывающую.

Типы параметров хранимых процедур: IN, OUT и INOUT

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

Применение IN, OUT и INOUT на практике

IN-параметр — самый распространённый: передаёт значение (идентификатор, категорию, сумму), процедура читает его, но значение в вызывающем контексте не меняется.

OUT-параметр возвращает результат через аргумент. В PostgreSQL: CALL get_student_name(1, NULL) — второй аргумент при вызове равен NULL, после выполнения содержит имя студента.

INOUT передаёт значение и возвращает изменённое: процедура расчёта скидки получает цену, уменьшает её на нужный процент и записывает результат обратно в тот же параметр.

В PostgreSQL DEFAULT-значения позволяют не передавать все аргументы: CALL search_products(p_category => ‘books’) — аргумент указывается по имени, остальные принимают значения по умолчанию.

Управляющие конструкции в хранимых процедурах

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

Переменные. В PostgreSQL: DECLARE v_counter INT := 0;. В SQL Server: DECLARE @counter INT = 0;. В Oracle можно использовать ссылочный тип: v_price products.price%TYPE — переменная автоматически получит тип столбца.

Условия. PostgreSQL и Oracle: IF … ELSIF … ELSE … END IF. SQL Server: IF … BEGIN … END ELSE IF … BEGIN … END. MySQL: IF … ELSEIF … END IF.

Циклы. PostgreSQL: LOOP … EXIT WHEN condition; END LOOP или FOR i IN 1..10 LOOP … END LOOP. SQL Server: WHILE condition BEGIN … END. MySQL: WHILE condition DO … END WHILE.

Обработка ошибок. PostgreSQL: EXCEPTION WHEN division_by_zero THEN …. SQL Server: BEGIN TRY … END TRY BEGIN CATCH ROLLBACK; THROW; END CATCH. MySQL: DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END. Oracle: EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE.

Распространённый антипаттерн — COMMIT внутри цикла. Если цикл рассчитан на 10 итераций и третья завершится ошибкой, первые две уже зафиксированы — ROLLBACK их не откатит. Правило: управление транзакцией выносится за пределы цикла, один COMMIT ставится после завершения всей логики.

Хранимые процедуры, функции и триггеры SQL — ключевые отличия

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

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

Безопасность хранимых процедур

Параметризованные хранимые процедуры по умолчанию защищены от SQL-инъекций: значение параметра передаётся как данные, а не как часть SQL-команды. Риск появляется при использовании динамического SQL — когда строка запроса собирается через конкатенацию входных данных.

Опасный паттерн:

EXECUTE ‘SELECT * FROM users WHERE name = »’ || p_name || »»;

Если p_name содержит ‘ OR ‘1’=’1, запрос вернёт все строки таблицы. Безопасная замена: EXECUTE … USING p_name в PostgreSQL или sp_executesql @sql, @params, @p_name = @p_name в SQL Server. В PostgreSQL для экранирования идентификаторов используют quote_ident(), для значений — quote_literal().

Права доступа выдаются на уровне процедуры: GRANT EXECUTE ON PROCEDURE get_report TO analyst_role. Пользователь с этим правом запускает процедуру, но не имеет прямого доступа к таблицам.

Два режима выполнения в PostgreSQL:

• SECURITY DEFINER — процедура выполняется с правами владельца (создателя). Используется, когда вызывающий пользователь не должен видеть структуру таблиц напрямую.
• SECURITY INVOKER — с правами того, кто вызвал. Поведение по умолчанию в большинстве СУБД.

Когда использовать хранимые процедуры, а когда избегать

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

Два архитектурных подхода: «толстая» процедура (Fat) — вся бизнес-логика в базе данных; «тонкая» (Thin) — только критичные атомарные операции. Разумный компромисс: атомарность и целостность данных — в процедурах, валидация ввода и UI-логика — в приложении.

Часто задаваемые вопросы

Что такое хранимые процедуры в SQL?

Хранимая процедура — именованный набор SQL-команд и управляющей логики (условия, циклы, обработка ошибок), сохранённый на сервере базы данных. Создаётся один раз, вызывается командой CALL или EXEC с разными параметрами. Ключевые атрибуты: кэш плана выполнения, поддержка транзакций, инкапсуляция бизнес-логики.

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

Процедура не обязана возвращать значение, может изменять данные (INSERT/UPDATE/DELETE) и управляет транзакциями (COMMIT/ROLLBACK). Функция всегда возвращает одно значение, встраивается в SELECT/WHERE/JOIN, но COMMIT внутри запрещён. Используйте процедуру для операций над данными, функцию — для вычислений в теле запроса.

Чем хранимая процедура отличается от триггера?

Процедура вызывается явно командой CALL/EXEC; триггер срабатывает автоматически при INSERT, UPDATE или DELETE на таблице. Процедура принимает параметры, триггер — нет. Оба объекта могут управлять транзакциями и изменять данные.

Как создать хранимую процедуру в MySQL?

Используйте DELIMITER //, затем CREATE PROCEDURE имя(params) BEGIN … END//, верните разделитель: DELIMITER ;. Смена DELIMITER обязательна — без неё MySQL завершает команду на первой ; внутри тела процедуры.

Как создать хранимую процедуру в PostgreSQL?

Синтаксис: CREATE OR REPLACE PROCEDURE name(params) LANGUAGE plpgsql AS $$ BEGIN … END; $$. Двойной доллар ($$ … $$) — долларовое квотирование, избавляет от экранирования символов. Вызов: CALL name(params). Процедуры поддерживаются с PostgreSQL 11.

Как создать хранимую процедуру в SQL Server (MS SQL)?

Синтаксис: CREATE PROCEDURE name @param TYPE AS BEGIN … END. Все параметры и переменные с префиксом @. Вызов: EXEC name или EXECUTE name @param = value. Для изменения — ALTER PROCEDURE без удаления объекта.

Какие типы параметров поддерживают хранимые процедуры?

Три типа: IN — передаёт значение в процедуру (по умолчанию); OUT — процедура записывает результат, вызывающий код считывает; INOUT — передаёт и возвращает изменённое значение. В PostgreSQL поддерживаются DEFAULT-значения с именованными аргументами при вызове.

Как защитить хранимую процедуру от SQL-инъекций?

Параметризованные процедуры безопасны — значение параметра не интерпретируется как SQL. Риск возникает при динамическом SQL через конкатенацию строк. Защита: EXECUTE … USING или quote_ident() в PostgreSQL; sp_executesql в SQL Server.

Можно ли использовать COMMIT внутри хранимой процедуры?

Да, в процедурах COMMIT разрешён (в отличие от функций). Антипаттерн — COMMIT внутри цикла: если следующая итерация упадёт, предыдущие изменения уже зафиксированы и ROLLBACK их не откатит. Правило: одна транзакция на весь блок операций, COMMIT — только после полного завершения логики.