Яка різниця між functions і procedures в SQL?

Загальні питання~3 хв читання

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. Процедура тут не підійде.

Коротка відповідь

Для співбесіди

Premium

Коротка відповідь допоможе вам впевнено відповідати на цю тему під час співбесіди.

Дочитали статтю?