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

# Хранимые процедуры в базе данных

Определение

Хранимая процедура (Stored Procedure) — это набор SQL-команд, скомпилированный и сохранённый в БД. Процедура выполняется на сервере БД, а не на клиенте, и может принимать параметры и возвращать результаты.

Основные характеристики

-- Создание простой процедуры
CREATE PROCEDURE get_user_info(IN user_id INT)
BEGIN
    SELECT id, email, created_at FROM users WHERE id = user_id;
END;

-- Вызов процедуры
CALL get_user_info(5);

Типы процедур по назначению

1. Процедура без параметров

CREATE PROCEDURE get_active_users()
BEGIN
    SELECT * FROM users WHERE active = true;
END;

CALL get_active_users();

2. Процедура с параметрами

CREATE PROCEDURE transfer_money(
    IN from_account_id INT,
    IN to_account_id INT,
    IN amount DECIMAL(10,2),
    OUT success BOOLEAN
)
BEGIN
    START TRANSACTION;
    
    UPDATE accounts SET balance = balance - amount WHERE id = from_account_id;
    UPDATE accounts SET balance = balance + amount WHERE id = to_account_id;
    
    IF ROW_COUNT() > 0 THEN
        COMMIT;
        SET success = true;
    ELSE
        ROLLBACK;
        SET success = false;
    END IF;
END;

// Вызов из Java (JPA)
StoredProcedureQuery query = em.createStoredProcedureQuery("transfer_money");
query.registerStoredProcedureParameter("from_account_id", Integer.class, ParameterMode.IN);
query.registerStoredProcedureParameter("to_account_id", Integer.class, ParameterMode.IN);
query.registerStoredProcedureParameter("amount", BigDecimal.class, ParameterMode.IN);
query.registerStoredProcedureParameter("success", Boolean.class, ParameterMode.OUT);

query.setParameter("from_account_id", 1);
query.setParameter("to_account_id", 2);
query.setParameter("amount", new BigDecimal("100.00"));
query.execute();

Boolean result = (Boolean) query.getOutputParameterValue("success");

3. Процедура с курсорами (для выборки множества записей)

CREATE PROCEDURE get_orders_by_status(
    IN status_filter VARCHAR(50),
    OUT total_orders INT,
    OUT result_cursor CURSOR
)
BEGIN
    SELECT COUNT(*) INTO total_orders FROM orders WHERE status = status_filter;
    
    DECLARE result_cursor CURSOR FOR
    SELECT id, user_id, total_amount FROM orders WHERE status = status_filter;
    
    OPEN result_cursor;
END;

Плюсы хранимых процедур

✓ Производительность: Выполняется на сервере БД, меньше сетевого трафика

✓ Безопасность: Логика защищена на уровне БД, сложнее к SQL-инъекциям

✓ Переиспользуемость: Несколько приложений могут вызывать одну процедуру

✓ Сложные транзакции: Атомарные операции в одной процедуре

✓ Предварительная компиляция: БД оптимизирует план выполнения при создании

// Пример: Сложная бизнес-логика в одной атомарной операции
StoredProcedureQuery query = em.createStoredProcedureQuery("complete_order");
query.registerStoredProcedureParameter("order_id", Long.class, ParameterMode.IN);
query.setParameter("order_id", orderId);
query.execute();
// БД гарантирует, что всё выполнится как одна транзакция

Минусы хранимых процедур

✗ Сложность в версионировании: Процедуры хранятся в БД, их сложнее отслеживать в git

✗ Зависимость от БД: Каждая СУБД имеет свой синтаксис (T-SQL, PL/pgSQL, MySQL)

✗ Тестирование сложнее: Нет интеграции с обычными unit-test фреймворками

✗ Отладка затруднена: Сложнее отлаживать код в БД

✗ Требует DBA: Нужна поддержка специалистов по БД

✗ Нарушает архитектуру: Бизнес-логика размазывается между приложением и БД

Современный подход - ORM и Repository

В Java-приложениях рекомендуется использовать ORM (Hibernate/JPA) вместо хранимых процедур:

// ❌ Старый подход со stored procedure
public boolean transferMoney(long fromId, long toId, BigDecimal amount) {
    StoredProcedureQuery query = em.createStoredProcedureQuery("transfer_money");
    // ... регистрация параметров и вызов
}

// ✓ Современный подход - репозиторий
@Repository
public class AccountTransferService {
    @Transactional
    public void transferMoney(long fromId, long toId, BigDecimal amount) {
        Account from = accountRepository.findById(fromId);
        Account to = accountRepository.findById(toId);
        
        from.setBalance(from.getBalance().subtract(amount));
        to.setBalance(to.getBalance().add(amount));
        
        accountRepository.save(from);
        accountRepository.save(to);
        // @Transactional гарантирует атомарность
    }
}

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

Используй процедуры в сложных случаях:

1. Очень сложные аналитические запросы (много JOIN, подзапросов, вычислений)

CREATE PROCEDURE generate_monthly_report(...)

1. Высоконагруженные операции где каждая миллисекунда важна

CREATE PROCEDURE process_high_load_batch(...)

1. Интеграция с Legacy системами которые требуют работы с процедурами

2. Операции, требующие максимальной безопасности (платежи, финансы)

Альтернативы в современной архитектуре

// 1. Criteria API для сложных запросов
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<User> query = cb.createQuery(User.class);
Root<User> user = query.from(User.class);
query.select(user).where(cb.and(...));

// 2. Spring Data JPA @Query с JPQL
@Query("SELECT u FROM User u WHERE u.status = :status ORDER BY u.createdAt DESC")
List<User> findByStatus(@Param("status") String status);

// 3. Native SQL запросы когда это необходимо
@Query(value = "SELECT * FROM users WHERE active = true", nativeQuery = true)
List<User> findActive();

// 4. QueryDSL для type-safe запросов
QUser user = QUser.user;
List<User> result = queryFactory
    .selectFrom(user)
    .where(user.status.eq("active"))
    .fetch();

Заключение

Хранимые процедуры — это мощный инструмент для сложных операций на уровне БД. Однако в современной архитектуре Java-приложений рекомендуется минимизировать их использование в пользу ORM и репозиториев. Процедуры имеют смысл для специфичных случаев: аналитические отчёты, высокая нагрузка, legacy-интеграция.