Что такое stored procedures?

Question

claude-haiku-4.5 · Accepted Answer

## Stored Procedures (Хранимые процедуры)

**Stored Procedure** (хранимая процедура) — это **подпрограмма, написанная на SQL**, которая **хранится и выполняется внутри базы данных** на сервере. Это альтернатива выполнению SQL запросов из приложения.

### Что такое stored procedure?

Хранимая процедура — это набор SQL команд, которые:
- **Хранятся в БД** (а не в приложении)
- **Выполняются на сервере БД** (экономит трафик сети)
- **Могут принимать параметры** и возвращать результаты
- **Содержат логику** (условия, циклы, транзакции)

```sql
-- Пример простой stored procedure в PostgreSQL
CREATE FUNCTION get_user_by_id(user_id INT)
RETURNS TABLE(id INT, name VARCHAR, email VARCHAR) AS $$
BEGIN
    RETURN QUERY
    SELECT id, name, email FROM users WHERE id = user_id;
END;
$$ LANGUAGE plpgsql;
```

### Примеры хранимых процедур

**Пример 1: Простая функция в PostgreSQL**

```sql
-- Функция, возвращающая количество пользователей
CREATE FUNCTION count_users()
RETURNS INT AS $$
DECLARE
    count INT;
BEGIN
    SELECT COUNT(*) INTO count FROM users;
    RETURN count;
END;
$$ LANGUAGE plpgsql;

-- Вызов из Python
import psycopg2

conn = psycopg2.connect("dbname=mydb user=postgres")
cur = conn.cursor()
cur.execute("SELECT count_users();")
result = cur.fetchone()[0]
print(f"Total users: {result}")
```

**Пример 2: Процедура с параметрами и логикой**

```sql
-- Процедура для создания заказа
CREATE FUNCTION create_order(user_id INT, total_amount DECIMAL)
RETURNS INT AS $$
DECLARE
    order_id INT;
BEGIN
    -- Вставляем заказ
    INSERT INTO orders (user_id, total_amount, created_at)
    VALUES (user_id, total_amount, NOW())
    RETURNING id INTO order_id;
    
    -- Обновляем статистику пользователя
    UPDATE users SET order_count = order_count + 1
    WHERE id = user_id;
    
    -- Возвращаем ID заказа
    RETURN order_id;
END;
$$ LANGUAGE plpgsql;

-- Вызов
cur.execute("SELECT create_order(%s, %s);", (user_id, 99.99))
order_id = cur.fetchone()[0]
```

**Пример 3: Процедура с условиями**

```sql
-- Процедура для применения скидки
CREATE FUNCTION apply_discount(user_id INT, discount_percent DECIMAL)
RETURNS VARCHAR AS $$
DECLARE
    user_age INT;
    new_discount DECIMAL;
BEGIN
    -- Получаем возраст пользователя
    SELECT age INTO user_age FROM users WHERE id = user_id;
    
    -- Логика: пенсионеры получают доп. скидку
    IF user_age > 65 THEN
        new_discount := discount_percent + 10;
    ELSE
        new_discount := discount_percent;
    END IF;
    
    -- Обновляем скидку
    UPDATE users SET discount = new_discount WHERE id = user_id;
    
    RETURN 'Discount applied: ' || new_discount || '%';
END;
$$ LANGUAGE plpgsql;
```

**Пример 4: Процедура с курсором (цикл)**

```sql
-- Процедура для расчета общей суммы заказов пользователя
CREATE FUNCTION get_user_orders_total(user_id INT)
RETURNS DECIMAL AS $$
DECLARE
    total DECIMAL := 0;
    order_row orders%ROWTYPE;
BEGIN
    -- Курсор проходит по всем заказам
    FOR order_row IN SELECT * FROM orders WHERE user_id = $1 LOOP
        total := total + order_row.total_amount;
    END LOOP;
    
    RETURN total;
END;
$$ LANGUAGE plpgsql;
```

### Stored Procedures в MySQL

```sql
-- Синтаксис MySQL
DELIMITER $$

CREATE PROCEDURE get_user_info(IN user_id INT)
BEGIN
    SELECT id, name, email FROM users WHERE id = user_id;
END$$

DELIMITER ;

-- Вызов из Python
import mysql.connector

conn = mysql.connector.connect(host="localhost", user="root", database="mydb")
cur = conn.cursor()
cur.callproc('get_user_info', [123])
result = cur.fetchall()
for row in result:
    print(row)
```

### Преимущества stored procedures

**1. Производительность**
```python
# БЕЗ stored procedure — множество запросов
cur.execute("SELECT * FROM users WHERE id = %s", (user_id,))
user = cur.fetchone()

cur.execute("SELECT * FROM orders WHERE user_id = %s", (user_id,))
orders = cur.fetchall()

cur.execute("SELECT * FROM payments WHERE user_id = %s", (user_id,))
payments = cur.fetchall()

# Три сетевых запроса!

# С stored procedure — один запрос
cur.execute("SELECT get_user_full_info(%s)", (user_id,))
result = cur.fetchone()
```

**2. Безопасность (защита от SQL injection)**
```python
# Параметры автоматически экранируются
cur.execute("SELECT get_user_by_id(%s)", (user_id,))
# SQL injection невозможен, т.к. логика в БД
```

**3. Переиспользование кода**
```python
# Один раз написали процедуру в БД
# Используют все приложения (Python, Node.js, Java)
cur.execute("CALL create_order(%s, %s, %s)", (user_id, items, total))
```

**4. Сложные транзакции**
```sql
CREATE PROCEDURE transfer_money(from_user INT, to_user INT, amount DECIMAL)
BEGIN
    START TRANSACTION;
    
    UPDATE accounts SET balance = balance - amount WHERE user_id = from_user;
    UPDATE accounts SET balance = balance + amount WHERE user_id = to_user;
    
    IF @@error != 0 THEN
        ROLLBACK;
    ELSE
        COMMIT;
    END IF;
END;
```

### Недостатки stored procedures

**1. Сложность тестирования**
```python
# Сложно писать unit-тесты для SQL кода
# Нужны специальные инструменты
```

**2. Сложность версионирования**
```bash
# Миграции БД более сложные
# Как обновить stored procedure в продакшене?
# Как откатить изменения?
```

**3. Зависимость от БД**
```python
# Хранимые процедуры — специфичны для каждой БД
# PostgreSQL процедура не сработает в MySQL
# Сложнее менять БД в будущем
```

**4. Отладка**
```sql
-- Отладка SQL кода сложнее, чем Python
-- Нужны специальные инструменты
-- Нет хороших IDE как для Python
```

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

**✅ Используй stored procedures:**
- Сложная бизнес-логика, зависимая от состояния БД
- Высокопроизводительные системы (финтех, trading)
- Когда нужна транзакционная целостность
- Системы, где множество приложений обращаются к БД

```python
# Хороший пример: банковский перевод
cur.execute("CALL transfer_money(%s, %s, %s)", (from_id, to_id, amount))
```

**❌ Избегай stored procedures:**
- Простые CRUD операции (SELECT, INSERT, UPDATE)
- Когда нужна быстрая разработка
- Когда логика часто меняется
- Системы с простой БД структурой

```python
# Плохой пример: простой SELECT
# Используй ORM вместо stored procedure
```

### Современный подход: ORM vs Stored Procedures

```python
# Старый подход: stored procedures
cur.execute("CALL get_user_with_orders(%s)", (user_id,))

# Современный подход: ORM (SQLAlchemy, Django ORM)
user = User.objects.prefetch_related('orders').get(id=user_id)
orders = user.orders.all()

# ORM преимущества:
# - Код на Python (легче тестировать)
# - Кроссплатформенность
# - Встроенная защита от SQL injection
# - Легче менять БД
```

### Пример: GraphQL vs Stored Procedures

```python
# Stored procedure для получения всех данных пользователя
cur.execute("SELECT get_full_user_info(%s)", (user_id,))

# Проблема: получаем ВСЕ данные (избыточность)
# GraphQL решение: запрашиваем только нужные поля
query_str = """
    query GetUser($id: ID!) {
        user(id: $id) {
            name
            email
            orders(limit: 10) {
                id
                total
            }
        }
    }
"""
```

### Резюме

**Stored Procedures** — это SQL подпрограммы, хранящиеся в БД:

1. **Что это:** SQL код, выполняющийся на сервере БД
2. **Преимущества:** производительность, безопасность, переиспользование
3. **Недостатки:** сложность, зависимость от БД, сложная отладка
4. **Когда использовать:** сложная логика, высокая нагрузка, транзакции
5. **Современная тенденция:** ORM + API (GraphQL/REST) вместо stored procedures

Для большинства приложений Python — используй ORM и храни логику в коде. Stored procedures — для специальных случаев.

Что такое stored procedures?

Комментарии (1)

Stored Procedures (Хранимые процедуры)

Что такое stored procedure?

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

Stored Procedures в MySQL

Преимущества stored procedures

Недостатки stored procedures

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

Современный подход: ORM vs Stored Procedures

Пример: GraphQL vs Stored Procedures

Резюме