Запропонувати правку

Покращити цю статтю

Допрацюйте відповідь до «Яка різниця між functions і procedures в SQL?». Ваші зміни проходять модерацію перед публікацією.

Потрібне підтвердження

Контент

Що ви змінюєте

Заголовок (UA)

Коротка відповідь (UA)

Показується над повною відповіддю для швидкого нагадування.

Відповідь (UA)

**SQL function** (функція) повертає значення і може використовуватись прямо в запитах. **SQL procedure** (процедура) виконує дії і нічого не повертає (або повертає через output-параметри).

## Теорія

### TL;DR

- Функція - як калькулятор: даєш число, отримуєш відповідь. Процедура - як скрипт: запускаєш, щось відбувається.
- Функція зобов'язана повернути рівно одне значення. Процедура за замовчуванням нічого не повертає.
- Функцію можна ставити в SELECT, WHERE, JOIN. Процедуру викликають через CALL або EXEC.
- Потрібне значення всередині запиту? Функція. Потрібно оновити рядки, записати лог і зробити commit? Процедура.

### Швидкий приклад

```sql
-- FUNCTION: повертає одне значення, можна використовувати в SELECT
CREATE FUNCTION CalculateBonus(salary DECIMAL)
RETURNS DECIMAL AS
BEGIN
  RETURN salary * 0.1;  -- 10% бонус
END;

-- Використовується прямо в запиті
SELECT employee_name, CalculateBonus(salary) AS bonus
FROM employees;
-- Виведе: Alice | 5000, Bob | 7000

-- PROCEDURE: виконує операції, нічого не повертає
CREATE PROCEDURE UpdateSalary(emp_id INT, new_salary DECIMAL)
AS
BEGIN
  UPDATE employees SET salary = new_salary WHERE id = emp_id;
  INSERT INTO audit_log VALUES (emp_id, new_salary, GETDATE());
END;

-- Викликається окремо, не всередині запиту
EXEC UpdateSalary(1, 50000);
```

Функції інтегруються у вирази. Процедури запускаються як окрема одиниця і керують побічними ефектами.

### Ключова різниця

Функція - це блок, який можна вставити в запит. Вона дає значення, яке query engine підставляє прямо в SELECT або WHERE. Процедура - це окремий виконавець дій: вона може оновлювати рядки, додавати записи в лог і керувати транзакціями, але не може стояти всередині виразу. Просто запитай себе: "Мені потрібне значення в запиті чи потрібно щось зробити?"

### Коли що використовувати

- **Function**: розрахунок податку, знижки або віку з дати народження; трансформація даних для відображення; будь-яка логіка, результат якої потрібен у запиті
- **Procedure**: пакетні операції на кшталт щомісячного нарахування зарплат; багатокрокові процеси, які зачіпають кілька таблиць; міграція даних; все, де потрібен явний COMMIT або ROLLBACK

### Таблиця порівняння

| Аспект | Function | Procedure |
|--------|----------|-----------|
| Повернення значення | Зобов'язана повернути одне значення | Нічого не повертає (або output-параметри) |
| Використання в запитах | SELECT, WHERE, JOIN, ORDER BY | Не можна вставляти у SQL-вирази |
| Виклик | `SELECT FunctionName()` | `CALL ProcedureName()` або `EXEC ProcedureName` |
| Побічні ефекти | Уникай | Очікувані і типові |
| Параметри | Зазвичай тільки вхідні | Вхідні і вихідні |
| Контроль транзакцій | Обмежений | Повний (COMMIT, ROLLBACK) |
| Коли використовувати | Розрахунки, трансформації | Batch-завдання, багатокрокові операції |

### Як це обробляє рушій

Коли функція стоїть всередині запиту, рушій обчислює її inline: підставляє результат замість виклику і тоді вже виконує основний запит. Якщо функція стоїть у WHERE-умові по 100 000 рядках - вона виконається 100 000 разів. Це реальна ціна. Процедура компілюється один раз і запускається як блок: рушій передає управління процедурі, виконує всі операції і повертає управління назад. Функції не мають стану. Процедури можуть підтримувати стан між операціями і керувати транзакціями.

### Типові помилки

**Помилка 1: Побічні ефекти всередині функції**

```sql
-- НЕПРАВИЛЬНО: функція змінює дані
CREATE FUNCTION ProcessRefund(order_id INT) RETURNS DECIMAL AS
BEGIN
  UPDATE orders SET status = 'REFUNDED' WHERE id = order_id;
  RETURN 100.00;
END;
-- Оптимізатор може викликати функцію 0, 1 або N разів залежно від плану запиту.
-- UPDATE спрацює непередбачувано. Використовуй процедуру.
```

**Помилка 2: Виклик процедури в WHERE**

```sql
-- НЕПРАВИЛЬНО: процедура нічого не повертає
SELECT * FROM orders WHERE ProcessOrder(order_id) = 1;
-- Це не скомпілюється. ProcessOrder не повертає значення.

-- Правильно: викликай окремо
EXEC ProcessOrder(1);
SELECT * FROM orders WHERE status = 'PROCESSED';
```

**Помилка 3: Думати, що скалярні функції завжди швидкі**

```sql
-- ПОВІЛЬНО: GetCustomerTier() виконується для кожного рядка
SELECT o.order_id, c.name, GetCustomerTier(c.customer_id) AS tier
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

-- Краще: зберігати tier у таблиці або рахувати через CASE
SELECT o.order_id, c.name,
  CASE WHEN c.total_spend > 10000 THEN 'Gold' ELSE 'Standard' END AS tier
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;
```

Цей патерн неодноразово давав 10-кратне уповільнення в звітах, які нормально працювали на тестових даних і падали на продакшені. Завжди перевіряй execution plan, коли використовуєш скалярні функції на великих таблицях.

### Де зустрічається

- PostgreSQL: функції для обчислюваних колонок у запитах; процедури для ETL-завдань
- SQL Server: скалярні функції в SELECT для розрахункових полів; stored procedures для нічних batch-завдань
- MySQL: функції для трансформації даних у запитах; процедури для багатокрокових операцій з транзакціями
- Oracle: функції у виразах і function-based indexes; процедури в PL/SQL-пакетах

### Питання на співбесіді

**Q:** Чи може функція викликати процедуру?
**A:** Ні, не в стандартному SQL. Функція має бути без побічних ефектів, а виклик процедури порушує цю умову. Процедура може викликати функцію і використовувати її результат.

**Q:** Яка різниця в продуктивності між скалярною функцією і inline-SQL?
**A:** Функція додає накладні витрати на кожен виклик і виконується порядково для великих результатів. Inline SQL дозволяє оптимізатору працювати з усією вибіркою одразу. На великих даних set-based логіка в чистому SQL зазвичай швидша.

**Q:** Як обробляти помилки у функціях і процедурах?
**A:** Процедури підтримують повний контроль транзакцій: COMMIT, ROLLBACK, обробку помилок з повторними спробами. Функції зазвичай кидають виняток або повертають NULL при помилці. Потрібен rollback при помилці? Тільки процедура.

**Q:** Чи можна індексувати результат функції?
**A:** Так. SQL Server і Oracle підтримують обчислювані колонки на основі функцій, і такі колонки можна індексувати. У PostgreSQL функція має бути позначена як IMMUTABLE. Корисно для продуктивності, але збільшує обсяг сховища.

## Приклади

### Базовий: Розрахунок бонусу

```sql
CREATE FUNCTION CalculateBonus(salary DECIMAL)
RETURNS DECIMAL AS
BEGIN
  RETURN salary * 0.1;
END;

SELECT name, salary, CalculateBonus(salary) AS bonus
FROM employees;
-- name  | salary | bonus
-- Alice | 50000  | 5000.0
-- Bob   | 70000  | 7000.0
```

Функція виконується для кожного рядка і підставляє результат прямо в колонку. Ніякого окремого виклику не потрібно.

### Середній рівень: Загальна сума замовлення з урахуванням податку

```sql
-- Враховує ставку податку залежно від штату і поріг безкоштовної доставки
CREATE FUNCTION CalculateOrderTotal(
  subtotal DECIMAL,
  state VARCHAR(2)
) RETURNS DECIMAL AS
BEGIN
  DECLARE @tax_rate DECIMAL = CASE
    WHEN state = 'CA' THEN 0.0725
    WHEN state = 'TX' THEN 0.0625
    ELSE 0.05
  END;
  DECLARE @shipping DECIMAL = CASE
    WHEN subtotal > 100 THEN 0
    ELSE 10
  END;
  RETURN (subtotal * (1 + @tax_rate)) + @shipping;
END;

SELECT
  order_id,
  subtotal,
  CalculateOrderTotal(subtotal, customer_state) AS total
FROM orders
WHERE order_date > '2025-01-01';
-- order_id | subtotal | total
-- 1001     | 150.00   | 159.38
-- 1002     |  75.00   |  89.38
```

Це функція, а не процедура, саме тому що результат потрібен прямо в SELECT. Процедура тут не підійде.

Markdown · перетягуйте зображення · ⌘B / ⌘I шорткати1063 слів

Для рев’юера

Примітка для модератора (необов’язково)

Бачить лише модератор. Прискорює рев’ю.