LR5.Tyutterin_Yakov_Z1411
.pdfМИНИСТЕРСТВО НАУКИ И ВЫСШЕГО ОБРАЗОВАНИЯ РОССИЙСКОЙ ФЕДЕРАЦИИ федеральное государственное автономное образовательное учреждение высшего образования
«САНКТ-ПЕТЕРБУРГСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ АЭРОКОСМИЧЕСКОГО ПРИБОРОСТРОЕНИЯ»
ИНСТИТУТ НЕПРЕРЫВНОГО И ДИСТАНЦИОННОГО ОБРАЗОВАНИЯ
КАФЕДРА ПРИКЛАДНОЙ ИНФОРМАТИКИ
ОЦЕНКА
ПРЕПОДАВАТЕЛЬ
канд. техн. наук, доц. |
|
Е. Л. Турнецкая |
должность, уч. степень, звание |
подпись, дата |
инициалы, фамилия |
ОТЧЕТ О ЛАБОРАТОРНОЙ РАБОТЕ №5
Хранимые процедуры и пользовательские функции
по дисциплине: Базы данных
РАБОТУ ВЫПОЛНИЛ |
|
|
|
|
|
СТУДЕНТ гр. № |
Z1411 |
|
|
Я. Н. Тюттерин |
|
|
|
|
|
|
|
|
номер группы |
подпись, дата |
|
инициалы, фамилия |
|
Студенческий билет № |
2022/4886 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Санкт-Петербург 2024
Лабораторная работа № 5. ПРОГРАММНАЯ РЕАЛИЗАЦИЯ ХРАНИМЫХ ПРОЦЕДУР.
Цели работы: Программная реализация хранимых процедур на сервере.
Вариант 18. Занятость актеров театра. Работников театра можно подразделить на актеров, музыкантов, постановщиков и служащих. Каждая из перечисленных категорий имеет уникальные атрибуты-характеристики и может подразделяться (например, постановщики) на более мелкие категории. Театр возглавляет директор, в функции которого входят контроль за постановками спектаклей, утверждение pепеpтуаpа, принятие на работу новых служащих, приглашение актеров и постановщиков. Актеры, музыканты и постановщики, работающие в театре, могут уезжать на гастроли. Актеры театра могут иметь звания заслуженных и народных артистов, могут быть лауреатами конкурсов. Также актерами театра могут быть и студенты театральных училищ. Каждый актер имеет свои вокальные и внешние данные (пол, возраст, голос, pост и т.п.), которые могут подходить для каких-то pолей, а для каких-то нет (не всегда женщина может сыграть мужчину и наоборот). Для постановки любого спектакля необходимо подобрать актеров на роли и дублеров на каждую главную pоль. Естественно, что один и тот же актер не может играть более одной pоли в спектакле, но может играть несколько pолей в различных спектаклях. У спектакля также имеется pежисеp-постановщик, художник-постановщик, диpижеp- постановщик, автор. Спектакли можно подразделить по жанрам: музыкальная комедия, трагедия, оперетта и пр. С другой стороны, спектакли можно подразделить на детские, молодежные и пр. В pепеpтуаpе театра указывается какие спектакли, в какие дни и в какое время будут проходить, а также даты пpемьеp. В кассах театра можно заранее приобрести билеты или абонемент на любые спектакли. Абонемент обычно включает в себя билеты на спектакли либо конкретного автора, либо конкретного жанра. Цена билетов зависит от места, и спектакля. На премьеры билеты дороже. Администрацией театра фиксируется количество проданных билетов на каждый спектакль.
Рисунок 1 - Схема БД
Результат создания процедуры для создания работника:
CREATE PROCEDURE create_worker(firstName varchar(30), lastName varchar(30), patr varchar(30), gen char(1), sal DECIMAL(10,2), dateOfBirth date, voi integer, he integer)
begin
IF (gen IN ('T', 'F', 'M')) THEN
INSERT INTO workers(first_name, last_name, patronymic, gender, salary, date_of_birth, voice, height) VALUES (firstName, lastName, patr, gen, sal, dateOfBirth, voi, he);
ELSE select ("Incorrect value gender");
END IF; end;
Результат вызова функции:
CALL create_worker('Yakov', 'Tyutterin', 'Nikolaevich', 'M', 100000, '2001-06-08', null, null);
При некорректном параметре гендера запись не происходит:
В результате первого выполнения процедуры - запись была добавлена в таблицу workers
Создана функция, принимающая на вход строку идентификаторов позиций, разделенных запятой, а также идентификатор работника и театра. В результате функция производить связку работника с позициями, которые были переданы функции.
Код функции:
CREATE PROCEDURE add_positions_for_worker(IN positions_ids VARCHAR(1000), workerId integer, theaterId integer)
BEGIN
DECLARE id_array_local VARCHAR(1000);
DECLARE start_pos SMALLINT; DECLARE comma_pos SMALLINT; DECLARE current_id integer; DECLARE end_loop TINYINT;
SET id_array_local = positions_ids;
SET start_pos = 1;
SET comma_pos = locate(',', id_array_local);
REPEAT
IF comma_pos > 0 THEN
SET current_id = CAST(substring(id_array_local, start_pos, comma_pos - start_pos) AS UNSIGNED);
SET end_loop = 0;
ELSE
SET current_id = CAST(substring(id_array_local, start_pos) AS
UNSIGNED);
SET end_loop = 1;
END IF;
INSERT INTO position_worker(position_id, worker_id, theater_id) VALUES (current_id, workerId, theaterId);
IF end_loop = 0 THEN
SET id_array_local = substring(id_array_local, comma_pos + 1); SET comma_pos = locate(',', id_array_local);
END IF;
UNTIL end_loop = 1
END REPEAT;
SELECT w.first_name, w.last_name, p.name AS postion_name from workers w LEFT JOIN position_worker pw ON w.id = pw.worker_id
LEFT JOIN positions p ON p.id = pw.position_id WHERE w.id = workerId;
END
Результат вызова процедуры:
CALL add_positions_for_worker('9,10,11,12,13,14,15', 13, 1);
При попытке передать на вход некорректную строку со списком идентификаторов позиций:
При попытке передать идентификатор несуществующего работника:
При попытке передать идентификатор несуществующего театра:
Представление процедуры create_worker в виде блок-схемы:
Представление процедуры add_positions_for_worker в виде блок-схемы:
Список всех хранимых процедур:
Вывод
В результате проделанной работы был получен практический опыт создания процедур для БД MySql. Процедуры могут быть использованы для ускорения операций вставки и заполнения. Например, вторая позволяет ускорить процесс занесения информации о работнике и его позициях в театре, так как не потребуется писать запрос, а достаточно будет передать параметры в процедуру.
Основная сложность возникла лишь из-за различия синтаксиса, так как до этого использовал и работал лишь с PostgreSQL.
Основной плюс от использования процедур - это снижение сетевого трафика между клиентами и сервером. Команды в процедуре выполняются как один пакет кода, что позволяет существенно сократить сетевой трафик. Это характерно для тех случаев, когда мы хотим выполнить множество запросов к БД. А их можно было бы описать в рамках одной процедуры. Таким образом не будет издержек на соединения с сервером БД для отправки запроса.
Также упрощают обслуживание. Так как, например, если клиентские приложения вызывают процедуры, а операции баз данных остаются на уровне данных, то для внесения изменений в основную базу данных будет достаточно обновить только процедуры.
Улучшение производительности, так как по умолчанию процедура компилируется при первом выполнении и создает план выполнения, который повторно используется для последующих выполнений.
Список использованных источников:
1)https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html
2)https://stackoverflow.com/
3)https://www.mysqltutorial.org/mysql-stored-procedure/getting-started-with-mysql-stored- procedures/
4)https://www.careerride.com/MySQL-Stored-Procedures.aspx