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

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

Описание задачи процедуры

Процедура для обработки заказов должна выполнять следующие этапы:

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 'Order with ID % does not exist.', $1;
    END IF;

    -- Шаг 2: Проверяем наличие товара на складе
    SELECT stock INTO v_stock
    FROM inventory
    WHERE product_id = v_product_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Product with ID % does not exist in inventory.', v_product_id;
    END IF;

    IF v_stock < v_quantity THEN
        RAISE EXCEPTION 'Not enough stock for product ID %. Requested: %, Available: %.',
            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, 'Order successfully processed.');

EXCEPTION
    WHEN OTHERS THEN
        -- Логируем ошибку при любой неудаче
        INSERT INTO order_logs (order_id, log_message)
        VALUES ($1, 'Error processing order: ' || 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
    ('Alice', 1, 2),
    ('Bob', 2, 1),
    ('Charlie', 1, 20); -- Этот заказ должен вызвать ошибку

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

-- Обрабатываем заказ Alice
SELECT process_order(1);

-- Обрабатываем заказ Bob
SELECT process_order(2);

-- Пытаемся обработать заказ Charlie (ошибка)
SELECT process_order(3);

Результаты:

• Заказы Alice и Bob будут успешно обработаны, запишутся в лог, а остатки на складе уменьшатся.
• Заказ Charlie вызовет ошибку из-за недостаточного количества товара на складе, и запись об ошибке появится в логе.

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

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

Типичные ошибки и советы

1. Ошибка: забыли проверить NOT FOUND после SELECT INTO.

   Всегда обрабатывайте случаи, когда запрос возвращает пустой результат, иначе это приведёт к неожиданным исключениям.

2. Ошибка: не добавили EXCEPTION-блок.

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

3. Совет: защищайтесь от SQL-инъекций.

   Используйте строго типизированные параметры и избегайте динамического SQL, если это не требуется.

Расширение процедуры

В реальной жизни можно добавить больше проверок, например:

• Учитывать скидки или акции для клиентов.
• Проверять кредитный лимит клиента перед обработкой заказа.
• Логировать не только успешные операции, но и откаты.

2

Задача

SQL SELF, 54 уровень, 0 лекция

Недоступна

Процедура обработки заказа

Процедура обработки заказа

Открыть