Хранимые процедуры¶

Хранимая процедура — это объект базы данных, представляющий собой набор предварительно скомпилированных инструкций на языке SQL и (часто) процедурных расширений (например, PL/SQL, T-SQL, PL/pgSQL), который хранится на сервере базы данных и выполняется как единое целое. Хранимые процедуры позволяют инкапсулировать бизнес-логику на стороне сервера, уменьшая сетевой трафик, повышая производительность за счёт кеширования плана выполнения и обеспечивая централизованное управление доступом к данным.

¶Определение и сущность

Хранимая процедура — это не просто последовательность SQL-запросов. Она компилируется один раз при первом вызове или при создании, а затем при последующих запусках сервер базы данных использует сохранённый план выполнения, что значительно ускоряет работу. В отличие от обычных SQL-скриптов, выполняемых клиентским приложением, хранимая процедура выполняется непосредственно на сервере СУБД. Это снижает объём данных, передаваемых между клиентом и сервером: вместо нескольких отдельных запросов передаётся только имя процедуры и её параметры.

Основные характеристики хранимой процедуры:

Именованность: каждая процедура имеет уникальное в рамках базы данных имя.
Параметризация: может принимать входные (IN), выходные (OUT) и входно-выходные (INOUT) параметры.
Возвращаемость: может возвращать одно значение (через параметры или RETURN) или набор строк (результат-множество).
Управляющая логика: поддерживает условные операторы (IF, CASE), циклы (WHILE, FOR), обработку исключений.

¶История и развитие

Концепция хранимых процедур возникла в 1970–1980-х годах в связи с развитием реляционных баз данных и потребностью в автоматизации повторяющихся операций. Первые коммерческие реализации появились в СУБД Ingres (1980-е), а затем в Oracle (версия Oracle 7, 1992 год) и Sybase. В 1996 году Microsoft включила поддержку хранимых процедур в SQL Server 6.5. В PostgreSQL процедурный язык PL/pgSQL добавлен в версии 6.0 (1997 год). В MySQL поддержка хранимых процедур появилась относительно поздно — в версии 5.0 (2005 год).

Развитие процедурных расширений шло параллельно: Oracle внедрил PL/SQL (Procedural Language/SQL), Microsoft — Transact-SQL (T-SQL), IBM — SQL PL, а PostgreSQL — PL/pgSQL. Все они, при различиях в синтаксисе, реализуют общий принцип: дополняют стандартный язык DML (Data Manipulation Language) возможностями процедурного программирования.

¶Классификация и разновидности

Существуют различные типы хранимых процедур, различающиеся по способу создания, области действия и возвращаемому результату.

¶По способу создания

Определяемые пользователем (User-defined): создаются разработчиками или администраторами баз данных для конкретных задач.
Системные (System stored procedures): встроенные процедуры СУБД (например, sp_help в Microsoft SQL Server, DBMS_OUTPUT в Oracle), предназначенные для администрирования, мониторинга и диагностики.

¶По возвращаемому значению

Возвращающие одно скалярное значение: через оператор RETURN или выходной параметр.
Возвращающие набор строк (результирующий курсор): как правило, это SELECT без присвоения в переменную.
Без возврата данных: выполняют операции вставки (INSERT), обновления (UPDATE), удаления (DELETE) или DDL-команды.

¶По аспекту выполнения

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

¶Устройство и синтаксис

Общая структура хранимой процедуры в любой СУБД включает три ключевых блока:

Заголовок (Definition): имя процедуры, список параметров с указанием режима (IN, OUT, INOUT) и типа данных.
Тело (Body): объявление локальных переменных, выполнение SQL-команд и управляющей логики.
Обработка исключений (Exception handling): блок для обработки ошибок (например, BEGIN ... EXCEPTION ... END в PL/pgSQL).

Пример синтаксиса на T-SQL (Microsoft SQL Server): ``sql CREATE PROCEDURE GetEmployeeCount @DepartmentID INT, @EmployeeCount INT OUTPUT AS BEGIN SELECT @EmployeeCount = COUNT(*) FROM Employees WHERE DepartmentID = @DepartmentID; END; ` На PL/pgSQL (PostgreSQL): `sql CREATE OR REPLACE FUNCTION add_employee( p_name TEXT, p_salary NUMERIC ) RETURNS INT AS $$ DECLARE new_id INT; BEGIN INSERT INTO employees (name, salary) VALUES (p_name, p_salary) RETURNING id INTO new_id; RETURN new_id; END; $$ LANGUAGE plpgsql; ``

¶Преимущества и недостатки

¶Преимущества

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

¶Недостатки

Зависимость от СУБД: код процедур плохо переносим между различными системами (например, T-SQL в Oracle не работает без адаптации).
Сложность отладки: по сравнению с прикладным языком, отладка на сервере более трудоёмка.
Риск повышения нагрузки на сервер: плохо оптимизированные процедуры могут создавать избыточную нагрузку на процессор и память.
Контроль версий: изменения процедур часто выполняются вне системы контроля версий, что осложняет учёт изменений.

¶Применение

Хранимые процедуры широко применяются в корпоративных информационных системах, финансовом и банковском секторе, веб-приложениях и ETL-процессах.

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

В российских реалиях хранимые процедуры активно используются в системах, работающих на платформах 1С:Предприятие, Oracle Database, PostgreSQL, а также в СУБД, включённых в реестр отечественного ПО (например, Postgres Professional, СУБД Ред База Данных).

¶Интересные факты

В системе управления базами данных PostgreSQL хранимые процедуры реализуются как функции (CREATE FUNCTION) со специальными языками (PL/pgSQL, PL/Python, PL/Perl). Отдельная команда CREATE PROCEDURE появилась только в версии 11 (2018 год).
Многие российские разработчики отдают предпочтение PostgreSQL и Postgres Pro из-за их открытости и соответствия требованиям импортозамещения.
В Microsoft SQL Server можно создавать хранимые процедуры на .NET-языках (CLR-процедуры), что позволяет интегрировать сложные алгоритмы на C#.
В Oracle процедуры часто объединяют в пакеты (PACKAGE), что упрощает именование и управление видимостью.

¶Критика

Основные претензии к хранимым процедурам связаны с проблемами эскалации технологического долга. Со временем в них накапливается «спагетти-код»: сложные вложенные ветвления и недокументированные зависимости. Это затрудняет модернизацию и тестирование. Кроме того, в современных архитектурах (микросервисы) логику стараются выносить из базы данных на уровень приложения, оставляя за СУБД лишь хранение и простейшие триггеры. Тем не менее, для монолитных систем и OLAP-нагрузок хранимые процедуры остаются востребованным инструментом.

¶Источники

Ларри Рокофф. «Изучаем Transact-SQL». 2-е изд., 2019.
Дэвид С. Платт. «PostgreSQL. Основы языка PL/pgSQL», 2021.
Документация Oracle Database 19c: PL/SQL Language Reference.
Документация PostgreSQL: «Создание функций» (раздел 38.4).
Том Кайт, Дарл Кун. «Oracle для профессионалов». 3-е изд., 2020.
ГОСТ Р 51904-2002 «Информационная технология. Процедурные расширения SQL».

BFOmetr — база данных и аналитика по компаниям России.

На главную BFOmetr →