Приклад комплексної процедури для обробки замовлень: валідація даних, оновлення статусу, логування

Сьогодні будемо розбирати побудову реальної процедури для обробки замовлень. Вона включає кілька етапів: валідацію даних, оновлення статусу замовлення, а також логування. Уяви собі ресторан, де шеф-кухар, офіціант і касир мають діяти злагоджено. У нашій процедурі ми реалізуємо схожу логіку взаємодії між етапами.

Опис завдання процедури

Процедура для обробки замовлень повинна виконувати такі етапи:

1. Перевірити, чи доступний потрібний товар на складі.
2. Якщо товару достатньо, списати його кількість зі складу.
3. Оновити статус замовлення, щоб він став "Оброблено".
4. Записати інформацію про успішну операцію в журнал (лог).
5. При виникненні будь-якої помилки відкотити зміни до початку.

Реалізація процедури

Крок 1. Створюємо схему і таблиці для роботи

Перш ніж писати процедуру, створимо таблиці, з якими вона буде працювати.

Таблиця orders — замовлення

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_name TEXT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    status TEXT DEFAULT 'Pending'
);

Ця таблиця зберігає замовлення. У кожного замовлення є клієнт, ідентифікатор продукту, кількість і статус (за замовчуванням "Очікує обробки").

Таблиця inventory — склад

CREATE TABLE inventory (
    product_id SERIAL PRIMARY KEY,
    product_name TEXT NOT NULL UNIQUE,
    stock INT NOT NULL CHECK (stock >= 0)
);

Таблиця зі списком товарів на складі. Кожен продукт має поточний запас (stock).

Таблиця order_logs — журнал операцій

CREATE TABLE order_logs (
    log_id SERIAL PRIMARY KEY,
    order_id INT NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
    log_message TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Журнал буде використовуватись для запису інформації про статус виконання замовлень.

Крок 2. Структура процедури

Ось структура багатокрокової процедури:

1. Перевірити, чи є запитуваний товар на складі і чи достатньо його.
2. Якщо товару достатньо, зменшити його кількість у таблиці inventory.
3. Змінити статус замовлення на "Оброблено".
4. Записати успішний результат у таблицю order_logs.
5. Обробити можливі помилки з відкотом змін.

Крок 3. Написання процедури

Давай напишемо процедуру process_order для виконання описаних вище кроків.

CREATE OR REPLACE FUNCTION process_order(order_id INT)
RETURNS VOID AS $$
DECLARE
    v_product_id INT;
    v_quantity INT;
    v_stock INT;
BEGIN
    -- Крок 1: Отримуємо інформацію про замовлення
    SELECT product_id, quantity
    INTO v_product_id, v_quantity
    FROM orders
    WHERE order_id = $1;

    -- Перевіряємо, чи існує замовлення
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Замовлення з ID % не існує.', $1;
    END IF;

    -- Крок 2: Перевіряємо наявність товару на складі
    SELECT stock INTO v_stock
    FROM inventory
    WHERE product_id = v_product_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Товар з ID % не існує на складі.', v_product_id;
    END IF;

    IF v_stock < v_quantity THEN
        RAISE EXCEPTION 'Недостатньо товару для продукту ID %. Запрошено: %, Доступно: %.',
            v_product_id, v_quantity, v_stock;
    END IF;

    -- Крок 3: Зменшуємо кількість товару на складі
    UPDATE inventory
    SET stock = stock - v_quantity
    WHERE product_id = v_product_id;

    -- Крок 4: Оновлюємо статус замовлення на 'Processed'
    UPDATE orders
    SET status = 'Processed'
    WHERE order_id = $1;

    -- Крок 5: Записуємо успішне виконання в журнал
    INSERT INTO order_logs (order_id, log_message)
    VALUES ($1, 'Замовлення успішно оброблено.');

EXCEPTION
    WHEN OTHERS THEN
        -- Логуємо помилку при будь-якій невдачі
        INSERT INTO order_logs (order_id, log_message)
        VALUES ($1, 'Помилка при обробці замовлення: ' || SQLERRM);

        -- Відкатуємо всі зміни
        RAISE;
END;
$$ LANGUAGE plpgsql;

Давай розберемо цю процедуру.

1. Етап перевірки:

   ми перевіряємо, чи існує вказане замовлення у таблиці orders. Якщо замовлення не знайдено, викликається виключення з детальним повідомленням. Аналогічно, перевіряємо наявність і кількість товару на складі.

2. Етап роботи зі складом:

   якщо товару достатньо, зменшуємо його кількість на складі. Це робиться через UPDATE.

3. Етап зміни статусу замовлення:

   змінюємо статус на "Processed" (Оброблено), щоб показати, що замовлення успішно завершене.

4. Етап логування:

   після успішної обробки замовлення додаємо повідомлення у таблицю order_logs, щоб зберегти інформацію про операцію.

5. Обробка виключень:

   якщо щось йде не так, ми перехоплюємо помилку у блоці EXCEPTION, записуємо у лог детальне повідомлення про помилку і відкатуємо всі зміни.

Приклади використання

Створимо тестові дані для перевірки роботи нашої процедури.

-- Додаємо товар на склад
INSERT INTO inventory (product_name, stock)
VALUES ('Laptop', 10), ('Monitor', 5);

-- Додаємо замовлення
INSERT INTO orders (customer_name, product_id, quantity)
VALUES
    ('Аліса', 1, 2),
    ('Боб', 2, 1),
    ('Чарлі', 1, 20); -- Це замовлення має викликати помилку

Тепер протестуємо процедуру:

-- Обробляємо замовлення Аліси
SELECT process_order(1);

-- Обробляємо замовлення Боба
SELECT process_order(2);

-- Пробуємо обробити замовлення Чарлі (помилка)
SELECT process_order(3);

Результати:

• Замовлення Аліси і Боба будуть успішно оброблені, запишуться у лог, а залишки на складі зменшаться.
• Замовлення Чарлі викличе помилку через недостатню кількість товару на складі, і запис про помилку з'явиться у журналі.

Перевіримо таблиці після виконання запитів:

SELECT * FROM inventory; -- Зміни у запасах
SELECT * FROM orders; -- Зміни у статусах замовлень
SELECT * FROM order_logs; -- Записи у журналі

Типові помилки і поради

1. Помилка: забули перевірити NOT FOUND після SELECT INTO.

   Завжди обробляй випадки, коли запит повертає порожній результат, інакше це призведе до неочікуваних виключень.

2. Помилка: не додали EXCEPTION-блок.

   Якщо у процедурі не передбачено обробник помилок, при виключенні транзакція може зависнути або порушити логіку виконання.

3. Порада: захищайся від SQL-ін'єкцій.

   Використовуй строго типізовані параметри і уникай динамічного SQL, якщо це не потрібно.

Розширення процедури

У реальному житті можна додати більше перевірок, наприклад:

• Враховувати знижки або акції для клієнтів.
• Перевіряти кредитний ліміт клієнта перед обробкою замовлення.
• Логувати не тільки успішні операції, а й відкати.