Файл: Практикум направление подготовки 09. 03. 03 Прикладная информатика Уровень высшего образования Бакалавриат.docx

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. 
   Разобрать учебные примеры по написанию и запуску хранимых процедур, функций и триггеров к ранее созданной базе данных «Библиотека» (library).
   
2. 
   В соответствии с заданием написать и протестировать работу процедур, функций и триггеров к ранее созданной базе данных «Библиотека» (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).
  

---