Что такое хранимая процедура?

Что такое хранимая процедура?

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

Определение

-- Простая хранимая процедура на SQL Server
CREATE PROCEDURE sp_GetUserById
    @UserId INT
AS
BEGIN
    SELECT * FROM Users WHERE id = @UserId;
END;

-- Процедура с логикой
CREATE PROCEDURE sp_TransferMoney
    @FromAccountId INT,
    @ToAccountId INT,
    @Amount DECIMAL
AS
BEGIN
    BEGIN TRANSACTION;
    
    UPDATE Accounts SET balance = balance - @Amount 
    WHERE id = @FromAccountId;
    
    UPDATE Accounts SET balance = balance + @Amount 
    WHERE id = @ToAccountId;
    
    COMMIT TRANSACTION;
END;

Вызов из Java

1. Через JDBC

// Подготовка вызова
String query = "{ call sp_GetUserById(?) }";
CallableStatement stmt = connection.prepareCall(query);
stmt.setInt(1, userId);

// Выполнение
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
    String name = rs.getString("name");
    String email = rs.getString("email");
}

2. С out параметром

-- Процедура с результатом
CREATE PROCEDURE sp_GetUserCount
    @Count INT OUTPUT
AS
BEGIN
    SELECT @Count = COUNT(*) FROM Users;
END;

CallableStatement stmt = connection.prepareCall("{ call sp_GetUserCount(?) }");
stmt.registerOutParameter(1, Types.INTEGER);
stmt.execute();

int count = stmt.getInt(1);
System.out.println("Total users: " + count);

3. Через ORM (JPA/Hibernate)

@Repository
public class UserRepository {
    @PersistenceContext
    private EntityManager em;
    
    public List<User> getUsersByProcedure(int userId) {
        StoredProcedureQuery query = em.createStoredProcedureQuery(
            "sp_GetUserById",
            User.class
        );
        query.registerStoredProcedureParameter(
            1, Integer.class, ParameterMode.IN
        );
        query.setParameter(1, userId);
        
        return query.getResultList();
    }
}

ПЛЮСЫ хранимых процедур

1. Производительность

// Хранимая процедура
// - Откомпилирована один раз
// - Нет overhead передачи SQL через сеть
// - Выполняется прямо на сервере БД

CallableStatement stmt = connection.prepareCall("{ call sp_ComplexCalculation() }");
ResultSet rs = stmt.executeQuery();

// vs обычный SQL из Java
// - Compilation overhead
// - Передача запроса по сети
// - Передача результатов по сети

2. Безопасность (защита от SQL injection)

-- Хранимая процедура не уязвима для injection
CREATE PROCEDURE sp_Login
    @Username VARCHAR(100),
    @Password VARCHAR(100)
AS
BEGIN
    SELECT * FROM Users 
    WHERE username = @Username AND password = @Password;
END;

// Параметры используются как данные, не как SQL
CallableStatement stmt = connection.prepareCall("{ call sp_Login(?, ?) }");
stmt.setString(1, username); // Безопасно
stmt.setString(2, password); // Безопасно

3. Сложная бизнес-логика в БД

-- Процедура с условиями, циклами, переменными
CREATE PROCEDURE sp_MonthlyPayroll
AS
BEGIN
    DECLARE @EmployeeId INT;
    DECLARE @Salary DECIMAL;
    DECLARE @Tax DECIMAL;
    
    DECLARE emp_cursor CURSOR FOR 
        SELECT id, salary FROM Employees WHERE status = 'active';
    
    OPEN emp_cursor;
    
    FETCH NEXT FROM emp_cursor INTO @EmployeeId, @Salary;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @Tax = @Salary * 0.13;
        
        INSERT INTO Payroll (employee_id, gross, tax, net)
        VALUES (@EmployeeId, @Salary, @Tax, @Salary - @Tax);
        
        FETCH NEXT FROM emp_cursor INTO @EmployeeId, @Salary;
    END;
    
    CLOSE emp_cursor;
    DEALLOCATE emp_cursor;
END;

4. Повторное использование

CREATE PROCEDURE sp_UpdateUserProfile
    @UserId INT,
    @FirstName VARCHAR(100),
    @LastName VARCHAR(100),
    @Email VARCHAR(100)
AS
BEGIN
    UPDATE Users 
    SET first_name = @FirstName, 
        last_name = @LastName, 
        email = @Email
    WHERE id = @UserId;
END;

// Может вызваться из разных мест кода
public class UserService {
    public void updateProfile(UserDTO dto) {
        executeStoredProcedure("sp_UpdateUserProfile", dto);
    }
}

public class AdminService {
    public void updateUserAsAdmin(UserDTO dto) {
        executeStoredProcedure("sp_UpdateUserProfile", dto);
    }
}

МИНУСЫ хранимых процедур

1. Сложность версионирования

// Как контролировать версию процедуры?
// Нет встроенного версионирования
// Нужны миграции БД

// Migration.sql
ALTER PROCEDURE sp_GetUser
    @UserId INT,
    @Status VARCHAR(50) OUTPUT  -- Новый параметр!
AS
BEGIN
    SELECT @Status = status FROM Users WHERE id = @UserId;
END;

// Все Java коды, вызывающие эту процедуру, могут сломаться

2. Тесты сложнее писать

// Нельзя unit тестировать без БД
public class UserRepositoryTest {
    @Test
    public void testGetUserByStoredProcedure() {
        // Нужна реальная БД или мок
        // vs обычный SQL, который легко мокировать
        
        UserRepository repo = new UserRepository(mockDb);
        User user = repo.getUsersByProcedure(1);
        
        // Очень сложно мокировать CallableStatement
    }
}

3. Зависимость от БД

-- PostgreSQL
CREATE PROCEDURE sp_GetUsers() AS $$
BEGIN
    RETURN QUERY SELECT * FROM users;
END;
$$ LANGUAGE plpgsql;

-- MySQL (совсем другой синтаксис)
DELIMITER //
CREATE PROCEDURE sp_GetUsers()
BEGIN
    SELECT * FROM users;
END //
DELIMITER ;

-- Если мигрировать БД — переписывать все процедуры!

4. Сложность отладки

// Ошибка в процедуре - как её найти?
// SQL Server Management Studio vs IDE
// Нет breakpoints как в Java
// Нет stack trace

CallableStatement stmt = connection.prepareCall("{ call sp_Complex() }");
stmt.execute(); // Ошибка где-то внутри процедуры, но где?

5. Бизнес-логика разделена

// Java код
public void createOrder(Order order) {
    // Часть логики здесь
    orderRepository.save(order);
    
    // Часть логики в БД (в процедуре)
    callStoredProcedure("sp_ValidateOrder", order);
}

// SQL код
CREATE PROCEDURE sp_ValidateOrder
AS
BEGIN
    -- Часть логики здесь
END;

// Логика размазана по двум местам - сложно понять и поддерживать

6. Контроль версий

// SQL код в отдельных файлах
// Сложнее отслеживать изменения
// Сложнее делать code review
// Сложнее обновлять версии

Когда использовать

Используй хранимые процедуры когда:

• Критична производительность (например, reporting)
• Очень сложная бизнес-логика в БД
• Много повторного использования SQL кода
• Интеграция с legacy системами
• Требуется контроль доступа на уровне БД

НЕ используй когда:

• Обычное CRUD приложение
• Микросервисная архитектура
• Нужна гибкость и быстрые изменения
• Важна тестируемость
• Нужна миграция между БД

Современный подход

В современной Java разработке тренд — минимизировать процедуры. Вместо них:

// Вся логика в Java коде
public class OrderService {
    public void createOrder(Order order) {
        // Валидация
        validateOrder(order);
        
        // Расчёты
        BigDecimal total = calculateTotal(order);
        
        // Сохранение
        orderRepository.save(order);
    }
    
    private void validateOrder(Order order) { }
    private BigDecimal calculateTotal(Order order) { }
}

Этот подход:

• Проще тестировать
• Легче версионировать
• Проще отлаживать
• Не зависит от БД
• Легче миграция

Хранимые процедуры — инструмент из 90-х, в современной архитектуре используется редко.