Pastein: Отчёт по практической работе Тема: «Переменные и подпрограммы в MySQL: пользовательские и локальные переменн

Загрузка данных
Отчёт по практической работе

Тема: «Переменные и подпрограммы в MySQL: пользовательские и локальные переменные, хранимые процедуры и функции»

Дисциплина: МДК 11 «Технология разработки защиты баз данных»

Специальность: 09.02.07 Информационные системы и программирование

Выполнил(а): студент(ка) группы ______

ФИО: _________________________________

Проверил(а): преподаватель _____________

Оценка: _________ Дата: __________

г. Арзамас, 2026 г.

---

Цель работы

Сформировать навыки создания и использования хранимых процедур и функций в MySQL, работы с пользовательскими и локальными переменными, применения управляющих конструкций.

Среда выполнения: MySQL Workbench.

---

Подготовка к работе

Создана база данных lab_procedures и таблица products (id, name, price, discount_percent, quantity). В таблицу добавлены пять тестовых записей.

Скриншот 1 — в дереве объектов слева видна созданная база lab_procedures и таблица products; в Result Grid — пять строк с товарами.

[Вставить скриншот 1]

---

Задание 1. Пользовательские переменные

Объявлены переменные @base_price, @discount, @final_price. Через SELECT ... INTO записаны цена и скидка товара с id = 1. Итоговая цена рассчитана по формуле «цена × (1 − скидка/100)».

Скриншот 2 — одна строка с результатом: base_price = 85000.00, discount = 5.00, final_price = 80750.00. Расчёт корректен: 85000 × 0,95 = 80750.

[Вставить скриншот 2]

Ответ на контрольный вопрос. DECLARE работает только внутри блока BEGIN ... END хранимых процедур, функций и триггеров, поскольку объявляет локальные переменные, существующие лишь в рамках программы. Вне программы у MySQL нет такого контекста, поэтому сервер выдаёт синтаксическую ошибку. На уровне сессии используются пользовательские переменные — они начинаются с @ и задаются через SET.

---

Задание 2. Функция calc_final_price

Создана функция с параметрами p_price и p_discount_percent, возвращающая DECIMAL(10,2) и помеченная DETERMINISTIC. Внутри объявлены локальные переменные v_discount и v_final: сначала вычисляется сумма скидки, затем она вычитается из цены.

Скриншот 3 — в разделе Schemas → Functions видна созданная функция; в Result Grid — таблица из пяти строк с колонкой final_price (80750.00, 1200.00, 4050.00, 20350.00, 3200.00).

[Вставить скриншот 3]

Ответ на контрольный вопрос. Функция инкапсулирует формулу в одном месте: при изменении логики правка делается один раз, а не во всех запросах. Это исключает ошибки копирования и делает SQL-запросы короче. Функцию можно переиспользовать в SELECT, WHERE, ORDER BY и внутри других процедур.

---

Задание 3. Процедура get_product_info

Создана процедура с входным параметром p_id и тремя выходными: out_name, out_final_price, out_status. Данные выбираются через SELECT ... INTO. Если товар не найден — выходные параметры заполняются значениями «Товар не найден», 0, «Не применимо». Иначе итоговая цена считается через calc_final_price, а статус определяется по количеству через IF ... ELSEIF ... ELSE.

Скриншот 4 — три результата вызовов:

· id = 1 → «Ноутбук», 80750.00, «Мало» (quantity = 10, не больше 10);
· id = 3 → «Клавиатура механическая», 4050.00, «В наличии» (quantity = 30);
· id = 99 → «Товар не найден», 0, «Не применимо».

[Вставить скриншот 4]

Ответ на контрольный вопрос. Параметры OUT позволяют вернуть сразу несколько значений, тогда как SELECT внутри процедуры даёт только результирующий набор. Значения из OUT можно сохранить в переменные и использовать дальше в вызывающем коде. Это делает интерфейс процедуры явным: видно, что она принимает и что возвращает.

---

Задание 4. Процедура report_total_value

Создана процедура без параметров с циклом WHILE. Счётчик v_id идёт от 1 до MAX(id) таблицы. Для каждого id выбираются цена и скидка; если товар существует, к накопителю v_total прибавляется результат calc_final_price. После цикла выводится итоговая сумма.

Скриншот 5 — CALL report_total_value() вернул 109550.00, и проверочный SELECT SUM(calc_final_price(...)) дал такое же значение. Совпадение подтверждает корректность цикла. Расчёт: 80750 + 1200 + 4050 + 20350 + 3200 = 109550.

[Вставить скриншот 5]

Ответ на контрольный вопрос. Обычный SQL-запрос проще и быстрее, когда расчёт сводится к одной агрегатной функции — сервер сам его оптимизирует. Процедура с циклом оправдана при итеративной логике, зависящей от предыдущих шагов: накопление с проверками, вызовы других процедур, запись промежуточных результатов. В нашем случае цикл — учебный пример; на практике сумму быстрее получить одним SELECT SUM(...).

---

Самостоятельная работа

Вариант A

Функция calc_vat(p_amount) возвращает p_amount * 0.20 и помечена DETERMINISTIC. Процедура apply_vat_report() выводит для каждого товара цену со скидкой и НДС от неё.

Скриншот 6 — таблица с колонками id, name, price, discount_percent, final_price, vat_amount. Для ноутбука final_price = 80750.00, vat_amount = 16150.00; для мыши — 1200.00 и 240.00; для клавиатуры — 4050.00 и 810.00; для монитора — 20350.00 и 4070.00; для веб-камеры — 3200.00 и 640.00.

[Вставить скриншот 6]

Вариант B

Процедура reserve_stock принимает p_product_id и p_qty_to_reserve, возвращает out_success и out_message. Если товар не найден — ошибка с сообщением. Если на складе достаточно — UPDATE уменьшает quantity и возвращается успех. Если недостаточно — данные не меняются, возвращается сообщение с остатком и запросом.

Скриншот 7 — три результата:

· id = 1, резерв 3 → success = 1, «Зарезервировано 3 ед. товара "Ноутбук"»;
· id = 1, резерв 100 → success = 0, «Недостаточно товара "Ноутбук": на складе 7, запрошено 100»;
· id = 999 → success = 0, «Товар с id=999 не найден».

Дополнительный SELECT показывает, что после теста 1 quantity уменьшилось с 10 до 7; тесты 2 и 3 данные не изменили.

[Вставить скриншот 7]

---

Код созданных объектов

```sql
-- Функция расчёта итоговой цены со скидкой
DELIMITER //
CREATE FUNCTION calc_final_price(
    p_price DECIMAL(10,2),
    p_discount_percent DECIMAL(5,2)
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE v_discount DECIMAL(10,2);
    DECLARE v_final DECIMAL(10,2);
    SET v_discount = p_price * (p_discount_percent / 100.0);
    SET v_final = p_price - v_discount;
    RETURN v_final;
END //
DELIMITER ;

-- Процедура получения информации о товаре
DELIMITER //
CREATE PROCEDURE get_product_info(
    IN p_id INT,
    OUT out_name VARCHAR(100),
    OUT out_final_price DECIMAL(10,2),
    OUT out_status VARCHAR(50)
)
BEGIN
    DECLARE v_price DECIMAL(10,2);
    DECLARE v_discount DECIMAL(5,2);
    DECLARE v_qty INT;

    SELECT name, price, discount_percent, quantity
    INTO out_name, v_price, v_discount, v_qty
    FROM products WHERE id = p_id;

    IF out_name IS NULL THEN
        SET out_name = 'Товар не найден';
        SET out_final_price = 0;
        SET out_status = 'Не применимо';
    ELSE
        SET out_final_price = calc_final_price(v_price, v_discount);
        IF v_qty > 10 THEN SET out_status = 'В наличии';
        ELSEIF v_qty >= 1 THEN SET out_status = 'Мало';
        ELSE SET out_status = 'Нет в наличии';
        END IF;
    END IF;
END //
DELIMITER ;

-- Процедура подсчёта общей стоимости с циклом
DELIMITER //
CREATE PROCEDURE report_total_value()
BEGIN
    DECLARE v_id INT DEFAULT 1;
    DECLARE v_max_id INT;
    DECLARE v_current_price DECIMAL(10,2);
    DECLARE v_current_discount DECIMAL(5,2);
    DECLARE v_total DECIMAL(15,2) DEFAULT 0;

    SELECT MAX(id) INTO v_max_id FROM products;

    WHILE v_id <= v_max_id DO
        SELECT price, discount_percent
        INTO v_current_price, v_current_discount
        FROM products WHERE id = v_id;

        IF v_current_price IS NOT NULL THEN
            SET v_total = v_total + calc_final_price(v_current_price, v_current_discount);
        END IF;
        SET v_id = v_id + 1;
    END WHILE;

    SELECT v_total AS total_value_with_discount;
END //
DELIMITER ;

-- Функция расчёта НДС
DELIMITER //
CREATE FUNCTION calc_vat(p_amount DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN p_amount * 0.20;
END //
DELIMITER ;

-- Процедура отчёта с НДС
DELIMITER //
CREATE PROCEDURE apply_vat_report()
BEGIN
    SELECT id, name, price, discount_percent,
           calc_final_price(price, discount_percent) AS final_price,
           calc_vat(calc_final_price(price, discount_percent)) AS vat_amount
    FROM products;
END //
DELIMITER ;

-- Процедура резервирования товара
DELIMITER //
CREATE PROCEDURE reserve_stock(
    IN p_product_id INT,
    IN p_qty_to_reserve INT,
    OUT out_success BOOLEAN,
    OUT out_message VARCHAR(255)
)
BEGIN
    DECLARE v_qty INT;
    DECLARE v_name VARCHAR(100);

    SELECT name, quantity INTO v_name, v_qty
    FROM products WHERE id = p_product_id;

    IF v_name IS NULL THEN
        SET out_success = FALSE;
        SET out_message = CONCAT('Товар с id=', p_product_id, ' не найден');
    ELSEIF v_qty >= p_qty_to_reserve THEN
        UPDATE products SET quantity = quantity - p_qty_to_reserve
        WHERE id = p_product_id;
        SET out_success = TRUE;
        SET out_message = CONCAT('Зарезервировано ', p_qty_to_reserve,
                                 ' ед. товара "', v_name, '"');
    ELSE
        SET out_success = FALSE;
        SET out_message = CONCAT('Недостаточно товара "', v_name,
                                 '": на складе ', v_qty,
                                 ', запрошено ', p_qty_to_reserve);
    END IF;
END //
DELIMITER ;
```

---

Вывод

Освоил объявление пользовательских переменных через SET и @, а также разницу между ними и локальными переменными, объявляемыми через DECLARE внутри программ. Научился создавать хранимые функции с DETERMINISTIC и применять их в SELECT, создавать процедуры с параметрами IN и OUT, использовать IF ... ELSEIF ... ELSE и цикл WHILE, а также менять данные внутри процедуры через UPDATE с проверкой условий.

Основная сложность — директива DELIMITER: без её смены MySQL воспринимает тело процедуры как несколько отдельных запросов и выдаёт ошибки. Также потребовалось время на понимание того, что SELECT ... INTO при отсутствии строк оставляет переменные в NULL, и это нужно обрабатывать явно. В варианте B важным оказалось разместить UPDATE только в успешной ветке IF, чтобы при неудаче данные не менялись.

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