Материал: Using_MySql,_MS_SQL_Server_and_Oracle

Пример 35: прозрачное исправление ошибок в данных

24SET IDENTITY INSERT [subscriptions] ON;

25INSERT INTO [subscriptions]

52SET IDENTITY INSERT [subscriptions] OFF;

53GO

54

55-- Внимание! Чтобы этот триггер можно было создать, необходимо

56-- отключить каскадное обновление на внешних

57-- ключах таблицы [subscriptions].

58-- Правда, тогда придётся доработать триггер так, чтобы с его

59-- помощью обеспечивать ссылочную целостность.

60CREATE TRIGGER [sbscs date tm upd]

61ON [subscriptions]

62INSTEAD OF UPDATE

63AS

64DECLARE @bad records NVARCHAR(max);

65DECLARE @msg NVARCHAR(max);

66

67IF (UPDATE([sb id]))

68BEGIN

69RAISERROR ('Please, do NOT update surrogate PK

71ROLLBACK TRANSACTION;

72RETURN;

73END;

82FOR XML PATH(''), TYPE) value ('.', 'nvarchar(max) '),

831, 2, '');

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 370/545

Пример 35: прозрачное исправление ошибок в данных

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

Задание 4.2.3.TSK.A: доработать решение{347} задачи 4.2.3.b{344} таким образом, чтобы исходные и автоматически полученные скорректированные значения даты в сообщениях, выводимых триггерами, всегда гарантированно представляли дату в одинаковом формате (в текущей реализации формат исходного и полученного значения может различаться).

& Задание 4.2.3.TSK.B: доработать решение{347} задачи 4.2.3.b{344} для MS SQL Server таким образом, чтобы получить возможность создания INSTEAD OF

UPDATE триггера и в то же время не потерять каскадное обновление внешних ключей таблицы subscriptions (иными словами: отключить каскадное обновление на самих внешних ключах и реализовать его «вручную» в INSTEAD

OF UPDATE триггере).

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 371/545

Пример 35: прозрачное исправление ошибок в данных

Задание 4.2.3.TSK.C: создать триггер, корректирующий название книги таким образом, чтобы оно удовлетворяло следующим условиям:

•не допускается наличие пробелов в начале и конце названия;

•не допускается наличие повторяющихся пробелов;

•первая буква в названии всегда должна быть заглавной.

Задание 4.2.3.TSK.D: создать триггер, меняющий дату выдачи книги на текущую, если указанная в SQL-запросе дата выдачи книги меньше текущей на полгода и более.

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 372/545

Пример 36: выборка и модификация данных с использованием хранимых функций

Раздел 5: Использование хранимых функций и процедур

5.1. Использование хранимых функций

5.1.1.Пример 36: выборка и модификация данных с использованием хранимых функций

ОЗадача 5.1.1.a{353}: создать хранимую функцию, получающую на вход даты выдачи и возврата книги и возвращающую разницу между этими датами в

днях, а также слова « [OK]», « [NOTICE]», « [WARNING]», соответственно, если разница в днях составляет менее десяти, от десяти до тридцати и более тридцати дней.

О Задача 5.1.1.b{355}: создать хранимую функцию, возвращающую список свободных значений автоинкрементируемых первичных ключей в указанной

таблице (свободными считаются значения первичного ключа, которые отсутствуют в таблице, и при этом меньше максимального используемого значения; например, если в таблице есть первичные ключи 1, 3, 8, то свободными считаются 2, 4, 5, 6, 7).

О Задача 5.1.1.c{367}: создать хранимую функцию, актуализирующую данные в таблице books_statistics (см. задачу 3.1.2.a{215}) и возвращающую число,

показывающее изменение количества фактически имеющихся в библиотеке книг.

Ожидаемый результат 5.1.1 .a.

Результат выполнения запроса, извлекающего идентификатор, даты выдачи и возврата и результат работы функции для случаев, когда книга не возвращена, должен выглядеть так:

Допускается три формата представления результата:

•таблица из двух колонок, в которых хранятся значения начала и конца диапазонов «свободных ключей»;

•таблица из одной колонки с перечислением всех имеющихся значений «свободных ключей»;

•строка с перечислением всех имеющихся значений «свободных ключей».

Ожидаемый результат 5.1.1.c.

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

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 373/545

Пример 36: выборка и модификация данных с использованием хранимых функций

Чу? Решение 5.1.1.a{352}.

Хранимые процедуры и функции в общем случае могут быть крайне сложными и неочевидными, а их синтаксическим особенностям в документации к каждой СУБД посвящены десятки страниц. Потому в рассматриваемых задачах осознанно сказано создать такие хранимые процедуры, которые можно с минимальными отличиями реализовать во всех трёх СУБД.

Одну «теоретическую особенность» всё же упомянем: во всех трёх представленных ниже решениях данной задачи функции объявлены как детерминированные (в MS SQL Server этот эффект достигается с помощью конструкции WITH

SCHEMABINDING). Это означает, что каждый раз при вызове с одинаковыми пара-

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

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

И это — всё, дальше — только сам код. Решение для MySQL:

MySQL Решение 5.1.1

1DELIMITER.а $$

2CREATE FUNCTION READ DURATION AND STATUS start date DATE, finish date DATE)

3RETURNS VARCHAR(150 DETERMINISTIC

4BEGIN

5DECLARE days INT;

6DECLARE message VARCHAR 150);

7SET days = DATEDIFF finish date, start date);

8CASE

9WHEN (days 10 THEN SET message = ' OK';

10WHEN ( days>=10| AND days<=30)) THEN SET message = ' NOTICE';

11WHEN (days 30 THEN SET message = ' WARNING';

12END CASE;

13RETURN CONCAT(days message);

14END$$

15

16 DELIMITER ;

Для проверки корректности полученного решения нужно использовать следующий запрос:

MySQL і Решение 5.1.1.а (проверка работоспособности)

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 374/545