Учебные задачи по PL/SQL

Ссылки сюда (1) →

Логическая схема

База данных эмулирует географически распределенную торговую компанию, торгующую с центрального склада. Каждое низовое подразделение отвечает за определенный регион (город), где набирает клиентов и организует продажи. Ассортимент товаров, цены и закупки осуществляются централизованно. Часть схемы, относящаяся к закупкам (и остаткам) пока не наполнена данными.

Особенности отдельных сущностей.

• В интервалах цен Дата конца — всегда не входит.
• Подразделения образуют иерархию. Для отчетов (запросов) будет использоваться выборка по иерархии. Однако название однозначно идентифицирует подразделение и его достаточно при показе информации.
• Для именования клиентов и поставщиков применяется два атрибута: Название (moniker), которое должно быть уникальным и Наименование (name), которое соотвествует официальным документам и используется в печатных формах.
• Каталог товаров также иерархический. В отличие от подразделений, на каждом уровне ветки именуются коротким кодом, а для отражения в плоских отчетах и интерфейсах применяется иерархический код.
• На строку продажи действуют две скидки: указанная в продаже (t_sale.discount) и дополнительная, назначенная индивидуально для строки (t_sale_str.discount).
• Стоимость товара в каждой строке, естественно, получается умножением цены на количество, а для продаж — дополнительно с применением скидки. Теоретически его можно не хранить, либо можно не хранить цену, однако и то и другое часто нужно в разных отчетах, и поэтому оба атрибута обычно хранимые. Кроме того, необходима воспроизводимость при печати документов, не смотря на возможные проблемы окрушгления, что тоже является аргументом за хранение всех атрибутов, а также НДС.
• Общая сумма продажи и НДС продажи, естественно, равна сумме по всем позициям. Однако из тех же соображений удобства отчетов она обычно является хранимой.
• Поставки и Продажи имеют два состояния: Черновик (new) и Исполнена (done). При исполнении документа изменяются остатки на складе. Пока документ — черновик, его можно свободно изменять. А исполненные документы править нельзя. Естественно, в реальных системах состояний больше, но для модельных целей этого достаточно.
• Текущий остаток (t_rest) и История остатков (t_rest_hist) содержат текущий остаток на складе. Таблицы заполнены с учетом всех накладных и должны изменяться при их исполнении. В задания входит разработка соовтетствующих процедур, а заполненность таблиц используется в заданиях на запросы.
• Продажи месяца (t_sale_rep) — таблица месячных отчетов по продажам. Естественно, в реальных системах отчетность устроена сложнее, однако отдельные таблицы для месячной или другой регулярной отчетности — достаточно типичная вещь. Отчет содержит данные за каждый месяц по каждому товару. Отчетные данные представлены как в штуках, так и в денежном выражении, и при построении итоговых отчетов, как правило, используются именно данные в деньгах. Товарный запас на складе оценивается в ценах поставки. Оценка на конец месяца получается следующим образом: к стоимости на начало месяца добавляют сумму закупок за месяц, а затем вычитают стоимость доли товара, которая была продана за месяц: out_sum = (inp_sum+supply_sum) * (1-sale_qty/(inp_qty+supply_qty))

Задачи для запроса данных

QUERY-01

Найти товары, цены на которые отличаются от цены на модель.

QUERY-02

Найти продаже, цены без скидки в которых отличаются от цены на товар.

QUERY-03

Вывод каталога с отступами в соответствии с уровнями каталога. Для каждого узла — число нижележащих узлов.

QUERY-04

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

QUERY-05

Вывод каталога с отступами, для листа с товарами — число моделей и товаров в ним.

QUERY-06

Вывод каталога с отступами, для каждого узла — число моделей и товаров в нем и нижележащих узлах

QUERY-07

Вычислить число продаж, их общую сумму, средневзвешенную, максимальную и минимальную скидки по каждому клиенту, у которых были продажи со скидкой более, чем 25%. Все — без учета скидок на строки

QUERY-08

Вычислить число строк продаж, количество, сумму средневзвешенную, максимальную и минимальную скидку по торговой марке (t_model.label).

QUERY-09

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

QUERY-10

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

QUERY-11

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

QUERY-12

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

QUERY-13

Стандратная оборотка по товару в штуках за период: остаток на начало, приход, уход, остаток на конец. Различные группировки. Остатки выбираются из таблицы остатков, а приход и уход — из документов. С помощью отчета проверить таблицу остатков.

QUERY-14

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

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

Задачи для программирования

Задачи на программирование правильно начинать с реализации теста, который бы проверял правильность вашей программы. Тест должен вставлять некоторые модельные данные, а потом контролировать, что получен ожидаемый результат. Для тестирования создаются временные записи и для простоты можно использовать фиксированные значения ключей. По соглашению, для специальных целей резервируются малые значения ключей, от 100 до 1000 (до 100 обычно используется для специальных объектов).

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

CREATE OR REPLACE PROCEDURE test_supply_summa AS
    l_ware  t_ware.id_ware%TYPE := 4289542; /* Товар T203Z2-0050 L Ветровка JACKET COCTEAU темно-синий р.L */
    l_summa t_supply.summa%TYPE;
    l_nds   t_supply.nds%TYPE;
BEGIN
    INSERT INTO t_supply (id_supply, code, dt, id_supplier)
        VALUES(101, 'Test-1', DATE'2007-09-01', 1001 /*COLUMBIA*/);
    dbms_output.put_line('Test insert line');
        /* В начальном состоянии следующий insert не пройдет: нужен триггер, вычисляющий summa и nds строки */
    INSERT INTO t_supply_str(id_supply_str, id_supply, num, id_ware, qty, price)
        VALUES(101, 101, 1, l_ware, 2, 20);
    SELECT summa, nds INTO l_summa, l_nds
        FROM t_supply_str
        WHERE id_supply_str = 101;
    IF NVL(l_summa, 0) <> 40 OR NVL(l_nds, 0) <> ROUND(40*18/118, 2) THEN
        raise_application_error(-20001, 'На шаге 1 в строке получено summa='||l_summa||' nds='||l_nds
                ||', а ожидалось summa=40 nds='||ROUND(40*18/118, 2));
    END IF;
    SELECT summa, nds INTO l_summa, l_nds
        FROM t_supply
        WHERE id_supply = 101;
    IF NVL(l_summa, 0) <> 40 OR NVL(l_nds, 0) <> ROUND(40*18/118, 2) THEN
        raise_application_error(-20001, 'На шаге 1 в заголовке получено summa='||l_summa||' nds='||l_nds
                ||', а ожидалось summa=40 nds='||ROUND(40*18/118, 2));
    END IF;
    dbms_output.put_line('Test update line');
    UPDATE t_supply_str
        SET qty = 5
        WHERE id_supply_str = 101;
    /* Проверки ... */
    dbms_output.put_line('Test insert second line');
    /* ... */
    dbms_output.put_line('Test delete line');
    /* ... */
    dbms_output.put_line('Test completed');
END;

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

Далее приведен набор задач для решения. Задачи разной сложности, и их не следует делать все подряд, правильно выбрать какую-нибудь.

TASK-01

Триггер для строки поставки, автоматически вычисляющий сумму и НДС строки. Следующий шаг — вычисление в триггере суммы и НДС заголовка поставки по изменению в строках.

TASK-02

Триггер, блокирующий установку скидки более 20% для «неVIP»-клиентов.

TASK-03

Триггер, автоматически устанавливающий цену продажи товара по значению цены на дату продажи с учетом скидки.

TASK-04

Триггер для строки продажи и для самой продажи, автоматически вычисляющий сумму по строкам при изменении цены или количества, а также скидки в заголовке, и изменяющие общую сумму продажи в заголовке. Задача сложная из-за mutating по простой схеме, когда изменение скидки меняет строки, а изменение суммы строки — меняет сумму в заголовке. Как альтернатива: для изменения скидки используется процедура, но тогда нужно придумать механизм блокировки прямого изменения этого поля в таблице.

TASK-05

Процедура назначения цены товару на заданном интервале, соответственно изменяющая таблицу цен. Должна изменять также цены в продажах-черновиках, естественно, с учетом скидки.

TASK-06

Процедура или триггер исполнения поставки и продажи, изменяющая текущее количество товара на складе. Естественно, должна обрабатыватьcя отмена исполнения. Для проверки, помимо теста, можно отменить исполнение всех накладных — остаток должен стать нулевым. После этого — снова исполнить все накладные и проверить, что результат совпал — используя для сравнения таблицу истории остатков.

TASK-07

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

TASK-08

Аналогично, триггера или процедуры, но для ведения таблицы истории остатков.

TASK-09

Аналогично, триггера или процедуры, но для ведения таблицы отчетов по продажам.

TASK-10

Набор процедур для элементарных операций над узлами каталога, обеспечивающих вычисление иерархического кода: вставка, перемещение в иерархии, переименование.

TASK-11

Альтернатива предыдущему: триггера, обеспечивающие вычисление иерархического кода для узлов каталога. Это сложнее, чем набор процедур.

TASK-12

Набор процедур элементарных действий или триггера, контролирующие отсутствие циклов в иерархии подразделений.