Хранимые процедуры: CREATE PROCEDURE, EXECUTE

Что такое хранимые процедуры и зачем они нужны? 🛠️

Хранимые процедуры — это готовые SQL-скрипты, которые сохраняются в базе данных. Они похожи на функции в программировании: принимают параметры, выполняют логику и возвращают результат.

Зачем использовать?
✅ Повторное использование кода
✅ Повышение производительности (компилируются один раз)
✅ Безопасность (можно ограничивать доступ)
✅ Упрощение сложных операций

Синтаксис создания процедуры: CREATE PROCEDURE

Создадим первую простую процедуру, которая выводит список всех пользователей:

CREATE PROCEDURE GetAllUsers()
BEGIN
    SELECT * FROM users;
END;

Разберём по частям:
- CREATE PROCEDURE GetAllUsers() — объявляем процедуру с именем GetAllUsers
- BEGIN и END — тело процедуры
- SELECT * FROM users; — сама SQL-команда

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

Процедуры могут принимать параметры трех типов:

1. IN (по умолчанию) — входные параметры
2. OUT — выходные (для возврата значений)
3. INOUT — комбинированные

Пример с IN-параметром:
Процедура для поиска пользователя по email:

CREATE PROCEDURE FindUserByEmail(IN user_email VARCHAR(100))
BEGIN
    SELECT * FROM users WHERE email = user_email;
END;

Вызов:

CALL FindUserByEmail('example@mail.com');

Выполнение процедур: EXECUTE vs CALL

В разных СУБД используются разные команды:

• В MySQL и PostgreSQL — CALL:
  sql CALL GetAllUsers();

• В SQL Server — EXEC или EXECUTE:
  sql EXEC GetAllUsers;

Важно: В некоторых СУБД (EXECUTE в SQL Server) скобки можно опустить, если нет параметров.

Возврат данных через OUT-параметры

Процедуры могут возвращать данные не только через результат запроса, но и через OUT-параметры.

Пример: Подсчёт количества заказов у пользователя:

CREATE PROCEDURE GetUserOrderCount(
    IN user_id INT,
    OUT order_count INT
)
BEGIN
    SELECT COUNT(*) INTO order_count 
    FROM orders 
    WHERE customer_id = user_id;
END;

Вызов и получение результата:

-- MySQL/PostgreSQL:
CALL GetUserOrderCount(5, @count);
SELECT @count;

-- SQL Server:
DECLARE @result INT;
EXEC GetUserOrderCount 5, @result OUTPUT;
SELECT @result;

Практический пример: обновление данных

Создадим процедуру для обновления баланса пользователя с проверкой:

CREATE PROCEDURE UpdateUserBalance(
    IN user_id INT,
    IN amount DECIMAL(10,2)
)
BEGIN
    -- Проверяем, существует ли пользователь
    IF EXISTS (SELECT 1 FROM users WHERE id = user_id) THEN
        UPDATE users 
        SET balance = balance + amount 
        WHERE id = user_id;
        SELECT 'Balance updated successfully!' AS message;
    ELSE
        SELECT 'User not found!' AS message;
    END IF;
END;

Вызов:

CALL UpdateUserBalance(1, 100.50);

Удаление процедуры

Если процедура больше не нужна, её можно удалить:

DROP PROCEDURE IF EXISTS GetAllUsers;

Лучшие практики работы с процедурами

🔹 Именование: Используйте понятные имена в стиле ActionTarget (например, CalculateTotalSales)
🔹 Комментарии: Описывайте назначение и параметры
🔹 Обработка ошибок: Добавляйте проверки (как в примере с UpdateUserBalance)
🔹 Тестирование: Проверяйте работу процедур на разных данных

Где применить хранимые процедуры?

📊 Сложные отчёты — вместо громоздких SQL-запросов
🔒 Безопасность — ограничение прямого доступа к таблицам
⚡ Пакетные операции — массовые обновления данных
🔄 Транзакции — выполнение нескольких операций как одной

Попробуйте создать свою первую процедуру прямо сейчас!