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

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

¶История

Концепция хранимых процедур возникла в 1970-х годах вместе с развитием реляционных баз данных. Первые реализации появились в коммерческих СУБД, таких как Oracle (с языком PL/SQL) и Sybase (с языком Transact-SQL). В 1980-х годах Microsoft внедрила поддержку хранимых процедур в SQL Server, также используя Transact-SQL. В 1990-х годах стандарт SQL/PSM (Persistent Stored Modules) был принят ISO для унификации языков хранимых процедур. В настоящее время практически все современные СУБД (MySQL, PostgreSQL, IBM Db2, MariaDB) поддерживают хранимые процедуры, хотя синтаксис и возможности могут различаться.

¶Классификация

Хранимые процедуры классифицируются по нескольким признакам:

¶По типу возвращаемых данных

Процедуры без возврата значений — выполняют действия (например, вставку или удаление записей) без явного возврата результата.
Процедуры с возвратом значений — могут возвращать одно или несколько значений через выходные параметры или результирующие наборы (result sets).

¶По способу вызова

Пользовательские — создаются разработчиками для конкретных задач.
Системные — встроенные процедуры СУБД для администрирования (например, sp_help в SQL Server).

¶По месту хранения

Локальные — хранятся в текущей базе данных.
Глобальные — временные процедуры, доступные всем сессиям (в некоторых СУБД, например, в SQL Server — с префиксом ##).

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

Хранимая процедура состоит из заголовка и тела. Заголовок определяет имя, список параметров и атрибуты (например, язык, права доступа). Тело содержит SQL-инструкции и управляющие конструкции (условные операторы, циклы, обработка ошибок). Пример на языке Transact-SQL (Microsoft SQL Server):

```sql CREATE PROCEDURE GetEmployeeInfo @EmployeeID INT, @Name NVARCHAR(100) OUTPUT AS BEGIN SET NOCOUNT ON; SELECT @Name = FirstName + ' ' + LastName FROM Employees WHERE EmployeeID = @EmployeeID;

IF @@ROWCOUNT = 0 RAISERROR('Сотрудник не найден', 16, 1); END; ```

В PostgreSQL используется язык PL/pgSQL:

```sql CREATE OR REPLACE FUNCTION get_employee_info(emp_id INT, OUT emp_name TEXT) AS $$ BEGIN SELECT first_name || ' ' || last_name INTO emp_name FROM employees WHERE employee_id = emp_id;

IF NOT FOUND THEN RAISE EXCEPTION 'Сотрудник с ID % не найден', emp_id; END IF; END; $$ LANGUAGE plpgsql; ```

¶Параметры

Входные (IN) — передают данные в процедуру.
Выходные (OUT) — возвращают данные из процедуры.
Входные/выходные (INOUT) — могут изменяться внутри процедуры и возвращать новое значение.

¶Управляющие конструкции

Условные операторы: IF ... THEN ... ELSE, CASE.
Циклы: WHILE, FOR, LOOP (зависит от СУБД).
Обработка ошибок: блоки TRY ... CATCH (SQL Server), EXCEPTION (PostgreSQL), DECLARE ... HANDLER (MySQL).

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

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

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

¶Недостатки

Зависимость от СУБД: код хранимых процедур часто не переносим между разными СУБД из-за различий в синтаксисе и возможностях.
Сложность отладки: отладка процедур менее удобна, чем отладка кода на стороне приложения.
Нагрузка на сервер: выполнение сложной логики на сервере может увеличить нагрузку на СУБД.
Версионирование: управление версиями хранимых процедур сложнее, чем управление кодом приложения.

¶Применение

Хранимые процедуры широко используются в различных сферах:

¶Бизнес-логика

Обработка заказов: проверка наличия товара, расчёт скидок, обновление остатков.
Расчёт заработной платы: вычисление налогов, надбавок, формирование ведомостей.
Генерация отчётов: агрегация данных за период, фильтрация по параметрам.

¶Администрирование баз данных

Очистка устаревших записей.
Создание резервных копий.
Мониторинг производительности (системные процедуры).

¶Интеграция с приложениями

Веб-приложения (например, на PHP, Java, C#) вызывают хранимые процедуры через API СУБД.
Мобильные приложения используют процедуры для выполнения операций с данными на сервере.

¶Примеры

¶Процедура для вставки данных с проверкой

```sql CREATE PROCEDURE AddProduct @ProductName NVARCHAR(100), @Price DECIMAL(10,2), @CategoryID INT AS BEGIN IF NOT EXISTS (SELECT 1 FROM Categories WHERE CategoryID = @CategoryID) BEGIN RAISERROR('Категория не существует', 16, 1); RETURN; END

INSERT INTO Products (ProductName, Price, CategoryID) VALUES (@ProductName, @Price, @CategoryID); END; ```

¶Процедура для получения иерархии сотрудников (рекурсивная)

``sql CREATE PROCEDURE GetSubordinates @ManagerID INT AS BEGIN WITH EmployeeHierarchy AS ( SELECT EmployeeID, FirstName, LastName, ManagerID, 0 AS Level FROM Employees WHERE EmployeeID = @ManagerID UNION ALL SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID, eh.Level + 1 FROM Employees e INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID ) SELECT * FROM EmployeeHierarchy; END; ``

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

В некоторых СУБД (например, Oracle) хранимые процедуры могут быть написаны не только на SQL, но и на Java или C.
Хранимые процедуры могут быть рекурсивными, но глубина рекурсии ограничена настройками сервера (обычно до 32–100 уровней).
В MySQL хранимые процедуры появились только в версии 5.0 (2005 год), что позже, чем в других популярных СУБД.
Системные хранимые процедуры в SQL Server (с префиксом sp_) хранятся в базе данных master и могут вызываться из любой базы данных.

¶Критика

Некоторые разработчики и архитекторы программного обеспечения критикуют хранимые процедуры за то, что они привязывают бизнес-логику к конкретной СУБД, усложняя миграцию на другую платформу. Альтернативой является использование объектно-реляционного отображения (ORM), например, Entity Framework или Hibernate, которое позволяет переносить логику на уровень приложения. Однако сторонники хранимых процедур указывают на их производительность и безопасность, особенно в системах с высокой нагрузкой и строгими требованиями к целостности данных.

¶Источники

Документация Microsoft SQL Server: «Хранимые процедуры (ядро СУБД)».
Документация PostgreSQL: «PL/pgSQL — SQL Procedural Language».
Документация MySQL: «CREATE PROCEDURE and CREATE FUNCTION Statements».
Стандарт ISO/IEC 9075-4:2016 «SQL/PSM».
Книга: «SQL и реляционная теория» К. Дж. Дейта.

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

На главную BFOmetr →