Хранимые процедуры, триггеры и представления

Зачем нужна логика на стороне СУБД, создание процедур и функций, курсоры, триггеры и их применение, представления и материализованные представления, отладка.

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

Зачем логика в базе

Хранимая процедура

CREATE OR REPLACE PROCEDURE transfer_stock(
    p_part_id   INTEGER,
    p_from      INTEGER,
    p_to        INTEGER,
    p_quantity  INTEGER
)
LANGUAGE plpgsql AS $$
DECLARE
    v_available INTEGER;
BEGIN
    IF p_quantity <= 0 THEN
        RAISE EXCEPTION 'Количество должно быть положительным, получено %', p_quantity;
    END IF;

    SELECT quantity INTO v_available
      FROM stock WHERE part_id = p_part_id AND warehouse_id = p_from
      FOR UPDATE;                       -- блокируем строку до конца транзакции

    IF v_available IS NULL THEN
        RAISE EXCEPTION 'Запчасть % на складе % не найдена', p_part_id, p_from;
    END IF;

    IF v_available < p_quantity THEN
        RAISE EXCEPTION 'Недостаточно: есть %, требуется %', v_available, p_quantity;
    END IF;

    UPDATE stock SET quantity = quantity - p_quantity
     WHERE part_id = p_part_id AND warehouse_id = p_from;

    INSERT INTO stock (part_id, warehouse_id, quantity)
    VALUES (p_part_id, p_to, p_quantity)
    ON CONFLICT (part_id, warehouse_id)
    DO UPDATE SET quantity = stock.quantity + p_quantity;

    INSERT INTO stock_log (part_id, from_wh, to_wh, qty, moved_at)
    VALUES (p_part_id, p_from, p_to, p_quantity, now());
END;
$$;

CALL transfer_stock(42, 1, 2, 10);

Функция

-- Функция возвращает значение и может использоваться в запросах
CREATE OR REPLACE FUNCTION order_total(p_order_id INTEGER)
RETURNS NUMERIC(12,2)
LANGUAGE sql STABLE AS $$
    SELECT COALESCE(SUM(quantity * unit_price), 0)
      FROM order_parts WHERE order_id = p_order_id;
$$;

SELECT id, order_total(id) AS сумма FROM orders WHERE status = 'new';

-- Функция, возвращающая таблицу
CREATE OR REPLACE FUNCTION orders_by_period(p_from DATE, p_to DATE)
RETURNS TABLE (id INTEGER, client TEXT, total NUMERIC)
LANGUAGE sql AS $$
    SELECT o.id, c.full_name, order_total(o.id)
      FROM orders o JOIN clients c ON c.id = o.client_id
     WHERE o.accepted_at BETWEEN p_from AND p_to;
$$;

SELECT * FROM orders_by_period('2026-01-01', '2026-03-31');

Триггеры

-- Автоматическое ведение истории изменений цен
CREATE TABLE price_history (
    id         SERIAL PRIMARY KEY,
    part_id    INTEGER NOT NULL,
    old_price  NUMERIC(10,2),
    new_price  NUMERIC(10,2),
    changed_at TIMESTAMP NOT NULL DEFAULT now(),
    changed_by TEXT NOT NULL DEFAULT current_user
);

CREATE OR REPLACE FUNCTION log_price_change() RETURNS TRIGGER
LANGUAGE plpgsql AS $$
BEGIN
    IF NEW.price <> OLD.price THEN         -- пишем только реальные изменения
        INSERT INTO price_history (part_id, old_price, new_price)
        VALUES (OLD.id, OLD.price, NEW.price);
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_price_history
AFTER UPDATE OF price ON parts
FOR EACH ROW EXECUTE FUNCTION log_price_change();

-- Запрет удаления клиента с активными заказами
CREATE OR REPLACE FUNCTION check_client_orders() RETURNS TRIGGER
LANGUAGE plpgsql AS $$
BEGIN
    IF EXISTS (SELECT 1 FROM orders
                WHERE client_id = OLD.id AND status <> 'done') THEN
        RAISE EXCEPTION 'У клиента есть незавершённые заказы';
    END IF;
    RETURN OLD;
END;
$$;

CREATE TRIGGER trg_client_delete
BEFORE DELETE ON clients
FOR EACH ROW EXECUTE FUNCTION check_client_orders();

Представления

-- Обычное представление: сохранённый запрос, данные всегда актуальны
CREATE VIEW v_active_orders AS
SELECT o.id, o.accepted_at, c.full_name AS client, c.phone,
       o.status, order_total(o.id) AS total
  FROM orders o
  JOIN clients c ON c.id = o.client_id
 WHERE o.status IN ('new', 'in_work');

SELECT * FROM v_active_orders WHERE total > 5000;

-- Материализованное: результат хранится физически
CREATE MATERIALIZED VIEW mv_monthly_stats AS
SELECT date_trunc('month', accepted_at) AS month,
       COUNT(*) AS orders_count,
       SUM(order_total(id)) AS revenue,
       AVG(order_total(id)) AS avg_order
  FROM orders
 GROUP BY 1
 WITH DATA;

CREATE UNIQUE INDEX ON mv_monthly_stats (month);

-- Требует обновления вручную или по расписанию
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_stats;

Отладка

-- Вывод сообщений в лог
RAISE NOTICE 'Обработано строк: %, сумма: %', v_count, v_total;

-- Перехват ошибок
BEGIN
    UPDATE stock SET quantity = quantity - 1 WHERE id = p_id;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'Деление на ноль, пропускаем';
    WHEN OTHERS THEN
        RAISE NOTICE 'Ошибка %: %', SQLSTATE, SQLERRM;
        RAISE;                          -- пробрасываем дальше
END;

-- Посмотреть текст существующей процедуры
SELECT prosrc FROM pg_proc WHERE proname = 'transfer_stock';

-- План выполнения запроса внутри процедуры
EXPLAIN ANALYZE SELECT * FROM v_active_orders;

Что показать в курсовой

1. Процедуру, выполняющую многошаговую операцию с проверками и транзакционностью.
2. Функцию, вычисляющую значение и применяемую в запросах.
3. Триггер аудита: журнал изменений важной таблицы.
4. Триггер контроля целостности, который нельзя выразить ограничением CHECK.
5. Представление, упрощающее отчётный запрос.
6. Демонстрацию срабатывания: снимки данных до и после, вывод ошибок при нарушении правил.

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