Файл: Практикум направление подготовки 09. 03. 03 Прикладная информатика Уровень высшего образования Бакалавриат.docx
ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 17.03.2024
Просмотров: 69
Скачиваний: 0
ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.
СОДЕРЖАНИЕ
Практическое занятие 10. Хранимые процедуры
Практическое занятие 11. Использование триггеров. Агрегация данных с использованием триггеров
Практическое занятие 12. Наработка навыков создания хранимых процедур, функций и триггеров
Практическое занятие 13. Самостоятельная работа по написанию хранимых процедур, функций и триггеров
k_bill автоматически проверяет все счета на задолженность и добавляет пени, если имеется задолженность.
# Обнулить все пени
Update k_bill SET Bill_Peni = NULL Where Bill_Peni IS NOT NULL;
# Добавить новый счет
INSERT INTO k_bill (Bill_Date, Bill_Sum, Bill_Term, k_contract_Contract_Num) VALUES ('2016-12-10', 5000, '2015-12-10', 2);
7. Создайте и протестируйте триггер на обновление (UPDATE), который после увольнение сотрудника обновляет номер отдела этого сотрудника на NULL (Если человека уволили, то его нет ни в каком отделе) в таблице k_staff.
UPDATE k_staff SET Staff_TermDate = '2008-01-01' Where Staff_Num = 5;
8. Создайте таблицу k_bill_archive, в которой будет храниться информация об изменённых счетах.
Create table k_bill_archive (
Bill_Num INT NOT NULL,
Bill_Date DATE NOT NULL,
Bill_Sum DECIMAL(10,2) NOT NULL,
Bill_Term DATE NULL DEFAULT NULL,
Bill_Peni DECIMAL(8,2) NULL DEFAULT NULL,
k_contract_Contract_Num INT NULL DEFAULT NULL,
Date_Of_Change DATE NOT NULL,
PRIMARY KEY (Bill_Num));
Создайте и протестируйте триггер на обновление (UPDATE), который перед обновлением данных в таблице k_bill переносит значения в таблицу k_bill_archive и добавляет дату добавления записи в архив.
Update k_bill SET Bill_Peni=Bill_Sum/10 WHERE Bill_Term<'2012-01-12';
Практическое занятие 12. Наработка навыков создания
Цель практического занятия: Наработка навыков создания процедур, функций и триггеров.
Задачи:
1. Общие сведения о хранимых функциях
Помимо формы create PROCEDURE, создающей хранимую процедуру, допускается использование формы create FUNCTION, которая создает хранимую функцию. Функция — это хранимая программа, в которую можно передавать параметры и возвращать значение. Функция в отличие от процедуры может вызываться непосредственно, без использования оператора CALL и возвращать одно значение, которое подставляется в место вызова функции, как в случае встроенных функций MySQL.
Синтаксис создания функции в MySQL:
CREATE FUNCTION function_name [ (parameter datatype [, parameter datatype]) ]
RETURNS return_datatype
BEGIN
declaration_section
executable_section
END;
Параметры или аргументы:
function_name — наименование функции в MySQL
parameter — один или несколько параметров передаются в функцию. При создании функции все параметры считаются параметрами IN (не OUT или IN OUT), где параметры могут ссылаться на функцию, но не могут быть перезаписаны функцией.
return_datatype — тип данных возвращаемого значения функции.
declaration_section — место в функции, где вы объявляете локальные переменные.
executable_section — место в функции, в которой вы создаете код функции.
В качестве примера рассмотрим простейшую хранимую функцию, say_hello, которая будет принимать единственный входной параметр с именем name и возвращать фразу "Привет, name!", где вместо подстроки name будет подставлено значение передаваемого в функцию фактического параметра
name:
После объявления параметров функции следует оператор returns, который задает тип возвращаемого функцией значения. Вернуть значение из функции можно при помощи оператора return, который может быть вызван в любой точке функции. Вызов оператора return означает, что функция должна немедленно завершить выполнение и вернуть значение, переданное в качестве аргумента оператора RETURN.
Пример вызова функции с параметром ‘Дмитрий’:
В результате будет получено:
Приведенная в примере функция MySQL объявлена как детерминированная.
Некоторые операции восстановления данных требуют использования двоичного журнала, который содержит информацию об операторах SQL, изменяющих содержимое базы данных. Эта информация хранится в форме «событий», описывающих изменения.
После восстановления файла резервной копии события в двоичном журнале, записанные после создания резервной копии, повторно выполняются. Эти события обновляют базы данных с момента резервного копирования. Однако, если ведение журнала происходит на уровне оператора, возникают определенные проблемы с ведением двоичного журнала в отношении хранимых программ (хранимых процедур и функций, триггеров и событий): если сохраненная программа, изменяющая данные, недетерминирована, она не может быть повторена. Это может привести к разным данным в источнике и реплике или к тому, что восстановленные данные будут отличаться от исходных данных.
Для разрешения этой проблемы функции MySQL могут быть объявлены как детерминированные. Это означает, что каждый раз при вызове с одинаковыми параметрами на одинаковых наборах данных такие функции будут возвращать одинаковые значения. Указание такого свойства хранимой функции позволяет СУБД более эффективно использовать механизмы внутренней оптимизации и повысить производительность.
2. Реализуйте учебные примеры по написанию и запуску функций, хранимых процедур и триггеров к базе данных «Библиотека» (library).
2.1 Создайте хранимую функцию, получающую на вход даты выдачи и возврата книги и возвращающую разницу между этими датами в днях, а также слова «[ХОРОШО]», «[УВЕДОМЛЕНИЕ]», «[ПРЕДУПРЕЖДЕНИЕ]», если разница в днях составляет, соответственно, менее десяти, от десяти до тридцати и более тридцати дней.
Для проверки корректности полученного решения запустите следующий запрос:
В результате будет получено:
2.2 Создайте триггеры для учета статистики выдачи книг в библиотеке.
Сначала необходимо создать агрегирующую таблицу для хранения статистических данных о выданных и оставшихся книгах. Данная таблица будет иметь три столбца: total (всего), given (выдано) и rest (остаток):
Отсутствие ключа в данной таблице объясняется тем, что в ней предполагается хранить ровно одну строку.
Заполним таблицу данными:
Далее напишем триггеры, модифицирующие данные в агрегирующей таблице. Агрегация происходит на основе информации, представленной в таблицах books и subscriptions, потому придётся создавать триггеры для обеих этих таблиц.
Изменения в таблице books влияют на поля total и rest, а изменения в таблице subscriptions – на поля given и rest. Данные могут измениться в результате всех трёх операций модификации данных – вставки, удаления, обновления – потому необходимо создавать триггеры на всех трёх операциях.
Создадим триггер к таблице books, реагирующий на добавление книг:
Создадим триггер к таблице books, реагирующий на удаление книг:
Создадим триггер к таблице books, реагирующий на на изменение количества книг:
Выражение FOR EACH ROW означают, что тело триггера будет выполнено для каждой записи (по-другому триггеры в MySQL и не работают), которую затрагивает операция с таблицей (добавляться, изменяться и удаляться может несколько записей за один раз).
Ключевые слова new и old позволяют обращаться:
Создадим триггер к таблице subscriptions, реагирующий на добавление выдачи книг:
Создадим триггер к таблице subscriptions, реагирующий на удаление выдачи книг:
Создадим триггер к таблице subscriptions, реагирующий на обновление выдачи книг:
Триггеры на таблице subscriptions оказываются чуть более сложными, тем триггеры на таблице books: здесь приходится анализировать происходящее и предпринимать действия в зависимости от ситуации.
В триггере, реагирующем на добавление выдачи книг мы должны изменить значения rest и given только в том случае, если книга в добавляемой выдаче отмечена как находящаяся на руках у читателя. Изначально мы предполагаем, что это не так, и инициализируем переменную @delta значением 0. Если далее оказывается, что книга всё же выдана, мы изменяем значение этой переменной на 1. Таким образом, в запросе значения полей агрегирующей таблицы будут меняться на 0 (т.е. оставаться неизменными) или на 1 в зависимости от того, выдана ли книга читателю.
Абсолютно аналогичной логикой мы руководствуемся в триггере, реагирующем на удаление выдачи книги.
В триггере, реагирующем на обновление выдачи книги, нам нужно рассмотреть четыре случая (из которых нас на самом деле интересуют только два последних):
# Обнулить все пени
Update k_bill SET Bill_Peni = NULL Where Bill_Peni IS NOT NULL;
# Добавить новый счет
INSERT INTO k_bill (Bill_Date, Bill_Sum, Bill_Term, k_contract_Contract_Num) VALUES ('2016-12-10', 5000, '2015-12-10', 2);
7. Создайте и протестируйте триггер на обновление (UPDATE), который после увольнение сотрудника обновляет номер отдела этого сотрудника на NULL (Если человека уволили, то его нет ни в каком отделе) в таблице k_staff.
UPDATE k_staff SET Staff_TermDate = '2008-01-01' Where Staff_Num = 5;
8. Создайте таблицу k_bill_archive, в которой будет храниться информация об изменённых счетах.
Create table k_bill_archive (
Bill_Num INT NOT NULL,
Bill_Date DATE NOT NULL,
Bill_Sum DECIMAL(10,2) NOT NULL,
Bill_Term DATE NULL DEFAULT NULL,
Bill_Peni DECIMAL(8,2) NULL DEFAULT NULL,
k_contract_Contract_Num INT NULL DEFAULT NULL,
Date_Of_Change DATE NOT NULL,
PRIMARY KEY (Bill_Num));
Создайте и протестируйте триггер на обновление (UPDATE), который перед обновлением данных в таблице k_bill переносит значения в таблицу k_bill_archive и добавляет дату добавления записи в архив.
Update k_bill SET Bill_Peni=Bill_Sum/10 WHERE Bill_Term<'2012-01-12';
Практическое занятие 12. Наработка навыков создания
хранимых процедур, функций и триггеров
Цель практического занятия: Наработка навыков создания процедур, функций и триггеров.
Задачи:
-
Разобрать учебные примеры по написанию и запуску хранимых процедур, функций и триггеров к ранее созданной базе данных «Библиотека» (library). -
В соответствии с заданием написать и протестировать работу процедур, функций и триггеров к ранее созданной базе данных «Библиотека» (library).
1. Общие сведения о хранимых функциях
Помимо формы create PROCEDURE, создающей хранимую процедуру, допускается использование формы create FUNCTION, которая создает хранимую функцию. Функция — это хранимая программа, в которую можно передавать параметры и возвращать значение. Функция в отличие от процедуры может вызываться непосредственно, без использования оператора CALL и возвращать одно значение, которое подставляется в место вызова функции, как в случае встроенных функций MySQL.
Синтаксис создания функции в MySQL:
CREATE FUNCTION function_name [ (parameter datatype [, parameter datatype]) ]
RETURNS return_datatype
BEGIN
declaration_section
executable_section
END;
Параметры или аргументы:
function_name — наименование функции в MySQL
parameter — один или несколько параметров передаются в функцию. При создании функции все параметры считаются параметрами IN (не OUT или IN OUT), где параметры могут ссылаться на функцию, но не могут быть перезаписаны функцией.
return_datatype — тип данных возвращаемого значения функции.
declaration_section — место в функции, где вы объявляете локальные переменные.
executable_section — место в функции, в которой вы создаете код функции.
В качестве примера рассмотрим простейшую хранимую функцию, say_hello, которая будет принимать единственный входной параметр с именем name и возвращать фразу "Привет, name!", где вместо подстроки name будет подставлено значение передаваемого в функцию фактического параметра
name:
После объявления параметров функции следует оператор returns, который задает тип возвращаемого функцией значения. Вернуть значение из функции можно при помощи оператора return, который может быть вызван в любой точке функции. Вызов оператора return означает, что функция должна немедленно завершить выполнение и вернуть значение, переданное в качестве аргумента оператора RETURN.
Пример вызова функции с параметром ‘Дмитрий’:
В результате будет получено:
Приведенная в примере функция MySQL объявлена как детерминированная.
Некоторые операции восстановления данных требуют использования двоичного журнала, который содержит информацию об операторах SQL, изменяющих содержимое базы данных. Эта информация хранится в форме «событий», описывающих изменения.
После восстановления файла резервной копии события в двоичном журнале, записанные после создания резервной копии, повторно выполняются. Эти события обновляют базы данных с момента резервного копирования. Однако, если ведение журнала происходит на уровне оператора, возникают определенные проблемы с ведением двоичного журнала в отношении хранимых программ (хранимых процедур и функций, триггеров и событий): если сохраненная программа, изменяющая данные, недетерминирована, она не может быть повторена. Это может привести к разным данным в источнике и реплике или к тому, что восстановленные данные будут отличаться от исходных данных.
Для разрешения этой проблемы функции MySQL могут быть объявлены как детерминированные. Это означает, что каждый раз при вызове с одинаковыми параметрами на одинаковых наборах данных такие функции будут возвращать одинаковые значения. Указание такого свойства хранимой функции позволяет СУБД более эффективно использовать механизмы внутренней оптимизации и повысить производительность.
2. Реализуйте учебные примеры по написанию и запуску функций, хранимых процедур и триггеров к базе данных «Библиотека» (library).
2.1 Создайте хранимую функцию, получающую на вход даты выдачи и возврата книги и возвращающую разницу между этими датами в днях, а также слова «[ХОРОШО]», «[УВЕДОМЛЕНИЕ]», «[ПРЕДУПРЕЖДЕНИЕ]», если разница в днях составляет, соответственно, менее десяти, от десяти до тридцати и более тридцати дней.
Для проверки корректности полученного решения запустите следующий запрос:
В результате будет получено:
2.2 Создайте триггеры для учета статистики выдачи книг в библиотеке.
Сначала необходимо создать агрегирующую таблицу для хранения статистических данных о выданных и оставшихся книгах. Данная таблица будет иметь три столбца: total (всего), given (выдано) и rest (остаток):
Отсутствие ключа в данной таблице объясняется тем, что в ней предполагается хранить ровно одну строку.
Заполним таблицу данными:
Далее напишем триггеры, модифицирующие данные в агрегирующей таблице. Агрегация происходит на основе информации, представленной в таблицах books и subscriptions, потому придётся создавать триггеры для обеих этих таблиц.
Изменения в таблице books влияют на поля total и rest, а изменения в таблице subscriptions – на поля given и rest. Данные могут измениться в результате всех трёх операций модификации данных – вставки, удаления, обновления – потому необходимо создавать триггеры на всех трёх операциях.
Создадим триггер к таблице books, реагирующий на добавление книг:
Создадим триггер к таблице books, реагирующий на удаление книг:
Создадим триггер к таблице books, реагирующий на на изменение количества книг:
Выражение FOR EACH ROW означают, что тело триггера будет выполнено для каждой записи (по-другому триггеры в MySQL и не работают), которую затрагивает операция с таблицей (добавляться, изменяться и удаляться может несколько записей за один раз).
Ключевые слова new и old позволяют обращаться:
-
при операциях вставки через new к новым (добавляемым) данным; -
при операциях обновления через old к старым значениям данных и через new к новым значениям данных; -
при операциях удаления через old к значениям удаляемых данных.
Создадим триггер к таблице subscriptions, реагирующий на добавление выдачи книг:
Создадим триггер к таблице subscriptions, реагирующий на удаление выдачи книг:
Создадим триггер к таблице subscriptions, реагирующий на обновление выдачи книг:
Триггеры на таблице subscriptions оказываются чуть более сложными, тем триггеры на таблице books: здесь приходится анализировать происходящее и предпринимать действия в зависимости от ситуации.
В триггере, реагирующем на добавление выдачи книг мы должны изменить значения rest и given только в том случае, если книга в добавляемой выдаче отмечена как находящаяся на руках у читателя. Изначально мы предполагаем, что это не так, и инициализируем переменную @delta значением 0. Если далее оказывается, что книга всё же выдана, мы изменяем значение этой переменной на 1. Таким образом, в запросе значения полей агрегирующей таблицы будут меняться на 0 (т.е. оставаться неизменными) или на 1 в зависимости от того, выдана ли книга читателю.
Абсолютно аналогичной логикой мы руководствуемся в триггере, реагирующем на удаление выдачи книги.
В триггере, реагирующем на обновление выдачи книги, нам нужно рассмотреть четыре случая (из которых нас на самом деле интересуют только два последних):
-
книга была на руках у читателя и там же осталась (значение sb_is_active было равно Y и таким же осталось); -
книга не была на руках у читателя и там же осталась значение sb_is_active было равно N и таким же осталось); -
книга была на руках у читателя, и он её вернул (значение sb_is_active было равно Y, но поменялось на N); -
книга не была на руках у читателя, но он её забрал (значение sb_is_active было равно N, но поменялось на Y).