Какая цель хранимых процедур?

Какая цель хранимых процедур

Хранимые процедуры (Stored Procedures) — это предкомпилированные SQL программы, хранящиеся в базе данных. Они решают конкретные задачи и имеют несколько целей.

Основная цель хранимых процедур

1. Инкапсуляция и переиспользование бизнес-логики в БД
2. Повышение производительности за счёт уменьшения сетевого трафика
3. Безопасность — контроль доступа на уровне БД
4. Сложные операции с несколькими таблицами
5. Транзакции и откаты

1. Простая хранимая процедура

CREATE OR REPLACE FUNCTION get_user_by_id(p_user_id BIGINT)
RETURNS TABLE(id BIGINT, email VARCHAR, name VARCHAR) AS $$
BEGIN
    RETURN QUERY
    SELECT u.id, u.email, u.name
    FROM users u
    WHERE u.id = p_user_id;
END;
$$ LANGUAGE plpgsql;

Использование из Java:

public class UserDAO {
    private final NamedParameterJdbcTemplate jdbcTemplate;
    
    public User getUserById(Long userId) {
        String sql = "SELECT * FROM get_user_by_id(:user_id)";
        
        Map<String, Object> params = new HashMap<>();
        params.put("user_id", userId);
        
        return jdbcTemplate.queryForObject(sql, params, new UserRowMapper());
    }
}

2. Хранимая процедура с INSERT/UPDATE

CREATE PROCEDURE register_user(
    IN p_email VARCHAR(255),
    IN p_password VARCHAR(255),
    IN p_name VARCHAR(100),
    OUT p_user_id BIGINT
)
BEGIN
    DECLARE v_email_exists INT;
    
    SELECT COUNT(*) INTO v_email_exists
    FROM users
    WHERE email = p_email;
    
    IF v_email_exists > 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Email already exists';
    END IF;
    
    INSERT INTO users (email, password_hash, name, created_at)
    VALUES (p_email, p_password, p_name, NOW());
    
    SET p_user_id = LAST_INSERT_ID();
    COMMIT;
END;

3. Сложная хранимая процедура с трансакциями

CREATE OR REPLACE FUNCTION transfer_money(
    p_from_account_id BIGINT,
    p_to_account_id BIGINT,
    p_amount DECIMAL(10, 2)
)
RETURNS BOOLEAN AS $$
DECLARE
    v_from_balance DECIMAL(10, 2);
BEGIN
    SELECT balance INTO v_from_balance
    FROM accounts
    WHERE id = p_from_account_id
    FOR UPDATE;
    
    IF v_from_balance < p_amount THEN
        RAISE EXCEPTION 'Insufficient funds';
    END IF;
    
    UPDATE accounts
    SET balance = balance - p_amount
    WHERE id = p_from_account_id;
    
    UPDATE accounts
    SET balance = balance + p_amount
    WHERE id = p_to_account_id;
    
    INSERT INTO transactions (from_account, to_account, amount, created_at)
    VALUES (p_from_account_id, p_to_account_id, p_amount, NOW());
    
    RETURN TRUE;
    
EXCEPTION WHEN OTHERS THEN
    RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

Использование из Java:

@Service
@Transactional
public class MoneyTransferService {
    
    private final NamedParameterJdbcTemplate jdbcTemplate;
    
    public boolean transferMoney(Long fromAccountId, Long toAccountId, BigDecimal amount) {
        String sql = "SELECT transfer_money(:from_id, :to_id, :amount)";
        
        Map<String, Object> params = new HashMap<>();
        params.put("from_id", fromAccountId);
        params.put("to_id", toAccountId);
        params.put("amount", amount);
        
        Boolean result = jdbcTemplate.queryForObject(sql, params, Boolean.class);
        return result != null && result;
    }
}

4. Курсоры для обработки наборов данных

CREATE OR REPLACE FUNCTION process_all_users()
RETURNS TABLE(user_id BIGINT, email VARCHAR, status VARCHAR) AS $$
DECLARE
    v_user_record RECORD;
    v_cursor CURSOR FOR 
        SELECT id, email, created_at FROM users
        ORDER BY created_at DESC;
BEGIN
    OPEN v_cursor;
    
    LOOP
        FETCH v_cursor INTO v_user_record;
        EXIT WHEN NOT FOUND;
        
        RETURN QUERY
        SELECT 
            v_user_record.id,
            v_user_record.email,
            CASE 
                WHEN v_user_record.created_at > NOW() - INTERVAL '7 days' THEN 'NEW'
                ELSE 'OLD'
            END as status;
    END LOOP;
    
    CLOSE v_cursor;
END;
$$ LANGUAGE plpgsql;

5. Условия и циклы

CREATE PROCEDURE calculate_user_scores()
BEGIN
    DECLARE v_user_id BIGINT;
    DECLARE v_done INT DEFAULT FALSE;
    DECLARE v_cursor CURSOR FOR SELECT id FROM users;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
    
    OPEN v_cursor;
    
    read_loop: LOOP
        FETCH v_cursor INTO v_user_id;
        IF v_done THEN
            LEAVE read_loop;
        END IF;
        
        UPDATE user_scores
        SET score = (
            SELECT COUNT(*) * 10 FROM posts WHERE user_id = v_user_id
        ) + (
            SELECT COUNT(*) * 5 FROM comments WHERE user_id = v_user_id
        )
        WHERE user_id = v_user_id;
    END LOOP;
    
    CLOSE v_cursor;
END;

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

• Инкапсуляция: бизнес-логика в одном месте
• Производительность: меньше сетевых запросов
• Безопасность: контроль доступа на уровне БД
• Сложные операции: транзакции, курсоры в БД
• Переиспользование: одна процедура для разных приложений
• Атомарность: гарантированная целостность данных

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

• Отладка: нужны специальные инструменты
• Версионирование: сложнее управлять кодом
• Зависимость от БД: код привязан к конкретной системе
• Для простых операций: может быть медленнее ORM
• Масштабируемость: узкое место в одном месте (БД)

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

✅ Используй когда:

• Сложные мультитабличные операции
• Требуется гарантировать атомарность
• Высокая производительность критична
• Логику используют несколько приложений
• Критична безопасность данных

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

• Простые CRUD операции (используй ORM)
• Часто меняется логика
• Нужна мобильность между БД
• Разработка прототипа

Альтернативы в современной Java разработке

@Service
@Transactional
public class UserService {
    
    private final UserRepository userRepository;
    private final TransactionTemplate transactionTemplate;
    
    public void registerUser(String email, String password) {
        transactionTemplate.execute(status -> {
            if (userRepository.findByEmail(email).isPresent()) {
                throw new EmailAlreadyExistsException(email);
            }
            
            User user = new User();
            user.setEmail(email);
            user.setPassword(passwordEncoder.encode(password));
            userRepository.save(user);
            
            return null;
        });
    }
}