db_solutions.sql - task12 - MarkZhukov/web

db_solutions.sql
437 строк18 KB
markkentos
add pdf view
04 июн 2026, 01:25
04 июн 2026, 01:25bb001aa
-- Пример 1: Скалярная функция PL/pgSQL
-- Кейс: Расчет общей суммы, потраченной клиентом на завершенные (completed) заказы.
-- Зачем нужно: Необходима для CRM, чтобы определять статус лояльности (VIP и т.д.).
CREATE OR REPLACE FUNCTION get_customer_total_spent(p_customer_id INT)
RETURNS NUMERIC(12,2)
LANGUAGE plpgsql
AS $$
DECLARE
    v_total NUMERIC(12,2);
BEGIN
    SELECT COALESCE(SUM(final_amount), 0.00)
    INTO v_total
    FROM shop_order
    WHERE customer_id = p_customer_id AND status = 'completed';
    
    RETURN v_total;
END;
$$;

-- Пример 2: Функция, возвращающая таблицу
-- Кейс: Вывод товаров, участвующих в конкретной акции, со скидочной ценой.
-- Зачем нужно: Используется для генерации промо-страниц на витрине интернет-магазина.
CREATE OR REPLACE FUNCTION get_products_in_promotion(p_promo_id INT)
RETURNS TABLE (
    product_id INT,
    product_name VARCHAR(255),
    sku VARCHAR(50),
    original_price NUMERIC(10,2),
    discount_type VARCHAR(20),
    discount_value NUMERIC(10,2),
    discounted_price NUMERIC(10,2)
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        p.id,
        p.name,
        p.sku,
        p.price,
        pr.discount_type,
        pr.discount_value,
        CASE 
            WHEN pr.discount_type = 'percent' THEN ROUND(p.price * (1 - pr.discount_value / 100), 2)
            WHEN pr.discount_type = 'fixed' THEN GREATEST(ROUND(p.price - pr.discount_value, 2), 0.00)
            ELSE p.price
        END::NUMERIC(10,2) AS discounted_price
    FROM shop_product p
    JOIN shop_productpromotion pp ON p.id = pp.product_id
    JOIN shop_promotion pr ON pp.promotion_id = pr.id
    WHERE pr.id = p_promo_id AND pr.is_active = TRUE;
END;
$$;

-- Пример 3: Процедура с управлением транзакциями (COMMIT/ROLLBACK)
-- Кейс: Проведение оплаты заказа с автоматическим изменением статуса заказа.
-- Зачем нужно: Гарантирует целостность транзакции при регистрации оплаты в биллинге.
CREATE OR REPLACE PROCEDURE process_order_payment(
    p_order_id INT, 
    p_amount NUMERIC, 
    p_method VARCHAR,
    INOUT p_result VARCHAR(100)
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_final_amount NUMERIC(12,2);
    v_status VARCHAR(50);
BEGIN
    -- Получаем сумму и статус заказа
    SELECT final_amount, status INTO v_final_amount, v_status FROM shop_order WHERE id = p_order_id;
    
    IF v_final_amount IS NULL THEN
        p_result := 'Заказ не найден';
        ROLLBACK;
        RETURN;
    END IF;
    
    IF v_status = 'paid' OR v_status = 'completed' THEN
        p_result := 'Заказ уже оплачен';
        ROLLBACK;
        RETURN;
    END IF;
    
    IF p_amount < v_final_amount THEN
        p_result := 'Недостаточно средств для оплаты';
        INSERT INTO shop_payment (order_id, payment_date, amount, payment_method, payment_status)
        VALUES (p_order_id, NOW(), p_amount, p_method, 'failed');
        COMMIT;
        RETURN;
    END IF;
    
    -- Создаем успешный платеж
    INSERT INTO shop_payment (order_id, payment_date, amount, payment_method, payment_status)
    VALUES (p_order_id, NOW(), p_amount, p_method, 'paid');
    
    -- Обновляем статус заказа
    UPDATE shop_order SET status = 'paid' WHERE id = p_order_id;
    
    p_result := 'Оплата успешно проведена';
    COMMIT;
END;
$$;

-- Пример 4: Функция с перегрузкой и параметрами по умолчанию
-- Кейс: Массовое применение скидки ко всем активным товарам в категории.
-- Зачем нужно: Позволяет менеджеру быстро запускать распродажу целых категорий.
CREATE OR REPLACE FUNCTION apply_mass_category_discount(
    p_category_id INT, 
    p_discount_val NUMERIC, 
    p_discount_type VARCHAR DEFAULT 'percent'
)
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
    v_updated_count INT := 0;
BEGIN
    IF p_discount_type = 'percent' THEN
        UPDATE shop_product
        SET price = ROUND(price * (1 - p_discount_val / 100), 2)
        WHERE category_id = p_category_id AND is_active = TRUE;
        
        GET DIAGNOSTICS v_updated_count = ROW_COUNT;
    ELSIF p_discount_type = 'fixed' THEN
        UPDATE shop_product
        SET price = GREATEST(ROUND(price - p_discount_val, 2), 0.00)
        WHERE category_id = p_category_id AND is_active = TRUE;
        
        GET DIAGNOSTICS v_updated_count = ROW_COUNT;
    ELSE
        RAISE EXCEPTION 'Некорректный тип скидки: %. Допустимы: percent, fixed.', p_discount_type;
    END IF;
    
    RETURN v_updated_count;
END;
$$;


-- =============================================================================
-- БЛОК 2: ЦИКЛЫ (while, for range, for query, foreach)
-- =============================================================================

-- Пример 1: Цикл FOR по диапазону чисел (генерация тестовых данных)
-- Кейс: Массовая вставка тестовых категорий товаров.
CREATE OR REPLACE PROCEDURE generate_test_categories(p_count INT)
LANGUAGE plpgsql
AS $$
BEGIN
    FOR i IN 1..p_count LOOP
        INSERT INTO shop_category (name)
        VALUES ('Тестовая категория #' || i)
        ON CONFLICT (name) DO NOTHING;
    END LOOP;
    RAISE NOTICE 'Успешно проверено/создано % тестовых категорий.', p_count;
END;
$$;

-- Пример 2: Цикл FOR для перебора результирующего набора запроса
-- Кейс: Обход товаров с критически низким запасом на складе и вывод уведомлений.
CREATE OR REPLACE PROCEDURE notify_low_stock_products(p_threshold INT)
LANGUAGE plpgsql
AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN 
        SELECT p.name AS product_name, s.quantity_in_stock, p.sku
        FROM shop_product p
        JOIN shop_stock s ON p.id = s.product_id
        WHERE s.quantity_in_stock < p_threshold
    LOOP
        RAISE NOTICE 'Предупреждение: Товар "%" (SKU: %) имеет низкий остаток на складе: % шт. (Порог: % шт.)', 
            r.product_name, r.sku, r.quantity_in_stock, p_threshold;
    END LOOP;
END;
$$;

-- Пример 3: Цикл WHILE (пополнение запасов в категории до целевого уровня)
-- Кейс: Увеличение запаса на складе товаров категории пачками, пока есть дефицитные товары.
CREATE OR REPLACE PROCEDURE bulk_restock_in_batches(p_category_id INT, p_target_stock INT)
LANGUAGE plpgsql
AS $$
DECLARE
    v_has_understocked BOOLEAN := TRUE;
    v_iterations INT := 0;
BEGIN
    WHILE v_has_understocked LOOP
        -- Пополняем запас на 5 единиц за раз для всех дефицитных товаров категории
        UPDATE shop_stock
        SET quantity_in_stock = LEAST(quantity_in_stock + 5, p_target_stock)
        WHERE product_id IN (
            SELECT id FROM shop_product WHERE category_id = p_category_id
        ) AND quantity_in_stock < p_target_stock;
        
        -- Проверяем, остались ли дефицитные товары
        SELECT EXISTS (
            SELECT 1 
            FROM shop_stock s
            JOIN shop_product p ON s.product_id = p.id
            WHERE p.category_id = p_category_id AND s.quantity_in_stock < p_target_stock
        ) INTO v_has_understocked;
        
        v_iterations := v_iterations + 1;
        
        -- Защита от бесконечного цикла
        IF v_iterations > 100 THEN
            EXIT;
        END IF;
    END LOOP;
    
    RAISE NOTICE 'Пополнение запасов завершено. Выполнено итераций: %', v_iterations;
END;
$$;

-- Пример 4: Цикл FOREACH по массиву элементов
-- Кейс: Вычисление общей стоимости выбранного массива ID товаров.
CREATE OR REPLACE FUNCTION calculate_selected_products_cost(p_product_ids INT[])
RETURNS NUMERIC(12,2)
LANGUAGE plpgsql
AS $$
DECLARE
    v_total NUMERIC(12,2) := 0.00;
    v_id INT;
    v_price NUMERIC(10,2);
END_LOOP_placeholder INT; -- Фиктивная переменная для разметки (в plpgsql используется END LOOP)
BEGIN
    FOREACH v_id IN ARRAY p_product_ids LOOP
        SELECT price INTO v_price FROM shop_product WHERE id = v_id;
        IF v_price IS NOT NULL THEN
            v_total := v_total + v_price;
        END IF;
    END LOOP;
    
    RETURN v_total;
END;
$$;


-- =============================================================================
-- БЛОК 3: ИНДЕКСЫ (Примеры оптимизации запросов)
-- =============================================================================

-- Пример 1: Частичный индекс (Partial Index)
-- Кейс: Индексация только активных дорогостоящих товаров (цена > 10000 руб.).
-- Обоснование: Оптимизирует частые выборки для премиум-витрины без раздувания размера индекса на диске.
CREATE INDEX IF NOT EXISTS idx_active_expensive_products 
ON shop_product (price) 
WHERE is_active = TRUE AND price > 10000.00;

-- Пример 2: Индекс по выражению (Expression Index)
-- Кейс: Регистронезависимый поиск по фамилии клиента.
-- Обоснование: Ускоряет работу поиска клиентов в панели администратора при несовпадении регистра.
CREATE INDEX IF NOT EXISTS idx_customer_last_name_lower 
ON shop_customer (LOWER(last_name));


-- =============================================================================
-- БЛОК 4: ПРАВА ДОСТУПА И ВРЕМЕННЫЕ ПРЕДСТАВЛЕНИЯ
-- =============================================================================

-- 1. Пример временного представления (Temporary View)
-- Кейс: Вывод крупных заказов (более 50 000 руб.) за последнюю неделю. 
-- Описание: Временные представления автоматически удаляются в конце текущей сессии.
CREATE OR REPLACE TEMP VIEW temp_recent_large_orders AS
SELECT 
    o.id AS order_id,
    c.first_name || ' ' || c.last_name AS customer_name,
    o.order_date,
    o.final_amount
FROM shop_order o
JOIN shop_customer c ON o.customer_id = c.id
WHERE o.final_amount > 50000.00
  AND o.order_date >= NOW() - INTERVAL '7 days'
ORDER BY o.final_amount DESC;

-- 2. Пример управления правами доступа (GRANT)
-- Кейс: Создание роли sales_analyst и предоставление доступа к данным о клиентах и заказах.
DO $$
BEGIN
    IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'sales_analyst') THEN
        CREATE ROLE sales_analyst;
    END IF;
END
$$;

-- Предоставление прав на просмотр таблиц (безопасность: только чтение)
GRANT SELECT ON shop_order, shop_orderitem, shop_customer TO sales_analyst;


-- =============================================================================
-- БЛОК 5: ТРИГГЕРЫ (Минимум 3 примера)
-- =============================================================================

-- Создание таблицы для логирования истории изменения цен (для триггера 3)
CREATE TABLE IF NOT EXISTS shop_product_price_history (
    id SERIAL PRIMARY KEY,
    product_id INT NOT NULL,
    old_price NUMERIC(10,2),
    new_price NUMERIC(10,2),
    change_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    changed_by VARCHAR(100)
);

-- Триггер 1: Валидация остатка на складе и автоматический расчет line_total
-- Кейс: Срабатывает BEFORE INSERT OR UPDATE на позиции заказа.
-- Описание: Блокирует покупку при нехватке товара на складе и автоматически пересчитывает сумму строки.
CREATE OR REPLACE FUNCTION fn_before_order_item_insert()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_stock_quantity INT;
    v_product_price NUMERIC(10,2);
BEGIN
    -- Проверяем остаток на складе для товара
    SELECT quantity_in_stock INTO v_stock_quantity 
    FROM shop_stock 
    WHERE product_id = NEW.product_id;
    
    IF v_stock_quantity IS NULL OR v_stock_quantity < NEW.quantity THEN
        RAISE EXCEPTION 'Недостаточно товара (ID: %) на складе. Запрошено: %, В наличии: %', 
            NEW.product_id, NEW.quantity, COALESCE(v_stock_quantity, 0);
    END IF;
    
    -- Получаем актуальную цену товара, если она не была передана
    IF NEW.price IS NULL OR NEW.price = 0.00 THEN
        SELECT price INTO v_product_price FROM shop_product WHERE id = NEW.product_id;
        NEW.price := COALESCE(v_product_price, 0.00);
    END IF;
    
    -- Авторасчет общей стоимости позиции
    NEW.line_total := NEW.quantity * NEW.price;
    
    -- Списываем товар со склада
    UPDATE shop_stock
    SET quantity_in_stock = quantity_in_stock - NEW.quantity
    WHERE product_id = NEW.product_id;
    
    RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER trg_before_order_item_insert
BEFORE INSERT OR UPDATE ON shop_orderitem
FOR EACH ROW
EXECUTE FUNCTION fn_before_order_item_insert();


-- Триггер 2: Синхронизация статуса заказа при полной оплате
-- Кейс: Срабатывает AFTER UPDATE на статус платежа.
-- Описание: Автоматически переводит статус заказа в 'paid', когда сумма успешных оплат покрывает сумму заказа.
CREATE OR REPLACE FUNCTION fn_after_payment_status_update()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_order_total NUMERIC(12,2);
    v_total_paid NUMERIC(12,2);
BEGIN
    IF NEW.payment_status = 'paid' THEN
        -- Получаем итоговую сумму заказа
        SELECT final_amount INTO v_order_total FROM shop_order WHERE id = NEW.order_id;
        
        -- Считаем сумму всех успешных платежей по этому заказу
        SELECT COALESCE(SUM(amount), 0.00) INTO v_total_paid 
        FROM shop_payment 
        WHERE order_id = NEW.order_id AND payment_status = 'paid';
        
        -- Если общая сумма успешных оплат покрывает заказ, обновляем статус заказа
        IF v_total_paid >= v_order_total THEN
            UPDATE shop_order
            SET status = 'paid'
            WHERE id = NEW.order_id;
        END IF;
    END IF;
    
    RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER trg_after_payment_status_update
AFTER INSERT OR UPDATE OF payment_status ON shop_payment
FOR EACH ROW
EXECUTE FUNCTION fn_after_payment_status_update();


-- Триггер 3: Логирование истории изменения цен товаров (Аудит цен)
-- Кейс: Срабатывает AFTER UPDATE цены товара.
-- Описание: Сохраняет лог изменения цены товара для финансового контроля и аудита.
CREATE OR REPLACE FUNCTION fn_track_product_price_changes()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF OLD.price IS DISTINCT FROM NEW.price THEN
        INSERT INTO shop_product_price_history (product_id, old_price, new_price, changed_by)
        VALUES (NEW.id, OLD.price, NEW.price, CURRENT_USER);
    END IF;
    RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER trg_track_product_price_changes
AFTER UPDATE OF price ON shop_product
FOR EACH ROW
EXECUTE FUNCTION fn_track_product_price_changes();


-- =============================================================================
-- ДЕМOНСТРАЦИЯ И ПРОВЕРОЧНЫЕ ЗАПРОСЫ
-- =============================================================================

/*
-- Шаг 1: Проверка функций Блока 1
SELECT get_customer_total_spent(1);
SELECT * FROM get_products_in_promotion(1);

-- Шаг 2: Проверка процедур Блока 1 и Блока 2
CALL generate_test_categories(3);
CALL notify_low_stock_products(10);
CALL bulk_restock_in_batches(1, 50);

-- Шаг 3: Проверка триггеров (Блок 5)
-- Должно вызвать ошибку при нехватке товара (триггер 1):
INSERT INTO shop_orderitem (order_id, product_id, quantity, price, line_total)
VALUES (1, 1, 999999, 100.00, 0.00);

-- Должно успешно добавиться и обновить склад + рассчитать line_total:
INSERT INTO shop_orderitem (order_id, product_id, quantity, price, line_total)
VALUES (1, 1, 1, 1000.00, 0.00);

-- Проверка триггера 3 (История цен):
UPDATE shop_product SET price = price + 500 WHERE id = 1;
SELECT * FROM shop_product_price_history;
*/