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

ОБЯЗАТЕЛЬНОДЛЯ НОВИЧКОВ

Разработчику Аналитику Тестировщику
Архитектору Инженеру

---

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

Общая база: именованный вызов с параметрами в коде — функции в коде. Ниже — процедуры и триггеры как объекты внутри СУБД.

---

О процедурах

В контексте SQL и реляционных баз данных термин "процедура" почти всегда означает хранимую процедуру (stored procedure) — программный объект, хранящийся в каталоге СУБД и выполняемый на сервере. Понятия "нехранимой процедуры" в классическом смысле не существует, поскольку ключевая характеристика процедуры в СУБД — её сохранение в базе данных для повторного использования. Временные или динамические блоки кода, не сохранённые в виде объекта БД, не считаются процедурами. Хранимые процедуры (Stored Procedures) – мощный инструмент SQL для для создания предопределённых операций, которые выполняются непосредственно на сервере базы данных.

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

1. По типу реализации
   • SQL-процедуры — написаны на диалекте SQL (PL/pgSQL, T-SQL, PL/SQL и т.д.), используют стандартные SQL-операторы и расширения (циклы, условия).
   • Процедуры на языках общего назначения — в некоторых СУБД (например, SQL Server с CLR, PostgreSQL с функциями на C/Python) допускается реализация на внешних языках.
2. По способу вызова
   • Именованные процедуры — создаются с помощью CREATE PROCEDURE и вызываются по имени.
   • Анонимные блоки — в PL/SQL (Oracle) поддерживаются блоки без имени, но они не являются "процедурами" в строгом смысле.
3. По уровню доступа
   • Системные процедуры — предоставляемые СУБД для администрирования (например, sp_help, sp_rename в SQL Server).
   • Пользовательские процедуры — созданные разработчиком под конкретную логику.
4. По параметрам. Процедуры могут принимать:
   • Входные параметры (IN)
   • Выходные (OUT)
   • Входные и выходные (INOUT / IN OUT)

Пример (PostgreSQL):

Код ITЗагрузка примера кода…

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

Хранимые процедуры нужны для следующих целей:

• повторное использование кода – один раз написал, много раз вызвал;
• безопасность – можно давать права на выполнение процедуры без доступа к таблицам;
• производительность – выполняются на сервере, минимизация сетевого трафика;
• централизованная логика – изменение в одном месте влияет на все приложения;
• сокращение SQL-инъекций – параметры передаются безопасно.

Общая структура процедур:

CREATE [OR REPLACE] PROCEDURE procedure_name ([параметры])
[характеристики]
BEGIN
    -- SQL-операции
END;

Пример:

DELIMITER //
CREATE PROCEDURE GetEmployee(IN emp_id INT)
BEGIN
    SELECT * FROM employees WHERE id = emp_id;
END //
DELIMITER ;

Параметры – значения, которые принимаются процедурами:

Тип параметра	Описание	Пример
IN	Входной параметр (по умолчанию)	IN emp_name VARCHAR(100)
OUT	Выходной параметр (возвращает значение)	OUT total_employees INT
INOUT	Входной и выходной одновременно	INOUT counter INT

Пример с параметрами:

CREATE PROCEDURE UpdateSalary(
    IN emp_id INT,
    IN increase_amount DECIMAL(10,2),
    OUT new_salary DECIMAL(10,2)
BEGIN
    UPDATE employees 
    SET salary = salary + increase_amount 
    WHERE id = emp_id;
    
    SELECT salary INTO new_salary FROM employees WHERE id = emp_id;
END;

Вызов процедур – обращение к уже существующей процедуре, с целью получить результат. Синтаксис вызова зависит от СУБД:

MySQL:

CALL GetEmployee(123);

SQL Server:

EXEC GetEmployee @emp_id = 123;

Oracle:

BEGIN
    GetEmployee(123);
END;

Вызов с выходными параметрами:

-- MySQL
CALL UpdateSalary(123, 5000, @new_salary);
SELECT @new_salary;

-- SQL Server
DECLARE @result DECIMAL(10,2);
EXEC UpdateSalary @emp_id=123, @increase_amount=5000, @new_salary=@result OUTPUT;
SELECT @result;

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

• упрощение кода;
• безопасность;
• производительность.

Для выполнения хранимой процедуры используют EXEC / EXECUTE (примеры выше в блоках T-SQL).

---

Функция и процедура в SQL

В SQL слово «функция» встречается в двух смыслах. Встроенные функции (COUNT, SUM, LOWER и т.д.) — часть языка запросов; о них — в статье Встроенные и пользовательские функции в SQL. Хранимая функция (CREATE FUNCTION) — отдельный объект каталога СУБД, как и хранимая процедура (CREATE PROCEDURE). Ниже — именно про эту пару.

Оба объекта — именованные подпрограммы (routines): код хранится в базе, компилируется сервером и вызывается по имени с параметрами. Различие не в «сложности» кода, а в контракте вызова — что объект обязан вернуть и где его можно использовать.

Определение: хранимая функция в SQL

★ Хранимая функция (stored function, SQL function) — именованная подпрограмма, созданная командой CREATE FUNCTION, у которой обязан быть объявленный тип возвращаемого значения (RETURNS / RETURN). Результат возвращается через RETURN <выражение> (скаляр) или через RETURNS TABLE / табличный тип (набор строк). Функцию вызывают как выражение — в списке SELECT, в WHERE, JOIN, вложенном подзапросе: её имя подставляется туда, где допустимо значение или табличный источник.

Сигнатура (обобщённо):

CREATE [OR REPLACE] FUNCTION function_name (параметры)
RETURNS <тип_или_таблица>
[характеристики: LANGUAGE, DETERMINISTIC, ...]
AS $$
BEGIN
    -- вычисления
    RETURN <результат>;
END;
$$;

Вызов — часть SQL-запроса, без CALL / EXEC:

SELECT function_name(аргументы);
SELECT * FROM function_name(аргументы);  -- табличная функция

Определение: хранимая процедура в SQL

★ Хранимая процедура (stored procedure, SQL procedure) — именованная подпрограмма, созданная командой CREATE PROCEDURE, которая не обязана иметь скалярный RETURNS. Она выполняет последовательность операторов (DML, циклы, ветвления); результат передаётся через наборы строк (SELECT внутри тела), параметры OUT / INOUT или (в отдельных СУБД) несколько result set'ов. Процедуру вызывают отдельной командой — CALL, EXEC / EXECUTE, BEGIN ... END — а не вставляют в выражение SELECT.

Сигнатура (обобщённо):

CREATE [OR REPLACE] PROCEDURE procedure_name (параметры)
[характеристики]
BEGIN
    -- SQL-операции, транзакционная логика
END;

Вызов — отдельный оператор, не выражение:

CALL procedure_name(аргументы);           -- PostgreSQL, MySQL
EXEC procedure_name @param = value;       -- SQL Server

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

Критерий	Хранимая процедура	Хранимая функция
Создание	CREATE PROCEDURE	CREATE FUNCTION
Возврат значения	Не обязана; часто OUT-параметры или наборы строк	Обязан RETURNS + RETURN (или RETURNS TABLE)
Вызов	CALL / EXEC — отдельный оператор	Внутри SELECT, WHERE, FROM — как выражение
Роль	Действие: обновить, удалить, провести транзакцию, пакетная обработка	Вычисление: вернуть значение или виртуальную таблицу для запроса
Изменение данных	Обычно разрешено	В большинстве СУБД ограничено (в PostgreSQL функция в SELECT не должна менять состояние БД)
Транзакции	В ряде СУБД допускает COMMIT / ROLLBACK внутри тела	Обычно не управляет транзакцией снаружи вызывающего запроса

Коротко: функция отвечает на вопрос «какое значение?» и встраивается в запрос; процедура отвечает на вопрос «что сделать?» и запускается как самостоятельная операция.

Пример функции:

CREATE FUNCTION get_employee_count(dept_id INT)
RETURNS INT
AS $$
DECLARE cnt INT;
BEGIN
    SELECT COUNT(*) INTO cnt FROM employees WHERE department_id = dept_id;
    RETURN cnt;
END;
$$ LANGUAGE plpgsql;

-- использование в запросе:
SELECT get_employee_count(5);

Пример процедуры (для сравнения — тот же смысл, но через OUT):

CREATE PROCEDURE get_employee_count_proc(dept_id INT, OUT cnt INT)
BEGIN
    SELECT COUNT(*) INTO cnt FROM employees WHERE department_id = dept_id;
END;

CALL get_employee_count_proc(5, @cnt);
SELECT @cnt;

Функции — для вычислений и преобразований внутри SQL. Процедуры — для сценариев с несколькими шагами, явными транзакциями, курсорами и побочными эффектами. Синтаксис по СУБД: Справочник по SQL.

---

Триггеры

★ Триггер – специальный вид хранимой процедуры, который автоматически выполняется при наступлении определённого события в базе данных. То есть, когда выполняется INSERT, UPDATE, DELETE в какой-то таблице, триггер может автоматически сделать что-то ещё.

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

Структура триггера включает:

• Событие – когда запускается триггер (INSERT, UPDATE, DELETE);
• Таблица – к какой таблице относится;
• Функция / тело – какие действия выполняются;
• Момент – до или после события (BEFORE / AFTER).

Пример триггера в PostgreSQL:

CREATE OR REPLACE FUNCTION log_user_update()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO user_log(user_id, action, change_time)
    VALUES (OLD.id, 'update', NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_user_update
AFTER UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION log_user_update();

Play ITЗагрузка интерактивного демо…

★ Виды триггеров:

Вид	Когда вызывается	На что реагирует
BEFORE INSERT	До вставки новой строки	Может изменить данные перед вставкой
AFTER INSERT	После вставки	Для обновления другой таблицы
BEFORE UPDATE	Перед обновлением	Можно изменить новые значения
AFTER UPDATE	После обновления	Логирование, синхронизация
BEFORE DELETE	Перед удалением	Может предотвратить данные
AFTER DELETE	После удаления	Чистка, каскадное удаление

Триггеры весьма непростой инструмент, и часто может замедлить операции.

---

Операторы управления потоком в SQL-процедурах

Оператор	Синтаксис	Описание	СУБД
DECLARE	DECLARE var_name TYPE [DEFAULT value]	Объявляет переменную	MySQL, SQL Server, PostgreSQL, Oracle
SET	SET var_name = value	Присваивает значение переменной	Все основные СУБД
IF-THEN-ELSE	IF condition THEN statements [ELSEIF condition THEN statements] [ELSE statements] END IF;	Условное выполнение кода	Все основные СУБД
CASE	CASE WHEN condition THEN statements [WHEN condition THEN statements] [ELSE statements] END CASE;	Множественное ветвление	Все основные СУБД
WHILE	WHILE condition DO statements END WHILE;	Цикл с предусловием	MySQL, SQL Server, PostgreSQL
REPEAT	REPEAT statements UNTIL condition END REPEAT;	Цикл с постусловием	MySQL, PostgreSQL
LOOP	LOOP statements [LEAVE label;] [ITERATE label;] END LOOP;	Бесконечный цикл с ручным управлением	MySQL, PostgreSQL
LEAVE	LEAVE label;	Выход из цикла или блока (аналог break)	MySQL, PostgreSQL
ITERATE	ITERATE label;	Переход к следующей итерации цикла (аналог continue)	MySQL, PostgreSQL
FOR	FOR var_name IN [REVERSE] start..end [BY step] LOOP statements END LOOP;	Цикл с счётчиком	PostgreSQL, Oracle
TRY-CATCH	BEGIN TRY statements END TRY BEGIN CATCH statements END CATCH	Обработка ошибок	SQL Server
DECLARE HANDLER	DECLARE handler_type HANDLER FOR condition handler_statements	Обработка исключений	MySQL
RETURN	RETURN value;	Возврат значения из функции	Все основные СУБД
GOTO	GOTO label;	Безусловный переход	SQL Server, Oracle

---