Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)

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

MS SQL Решение 4.2.3.b (триггеры для таблицы subscriptions) (продолжение)

84IF (LEN(@bad_records) > 0)

85BEGIN

86SET @msg = CONCAT('Some values were changed: ', @bad_records);

87PRINT @msg;

88RAISERROR (@msg, 16, 0);

89END;

102[subscriptions].[sb_is_active] = [inserted].[sb_is_active]

103FROM [subscriptions]

104JOIN [inserted]

105ON [subscriptions].[sb_id] = [inserted].[sb_id];

106GO

Oracle Решение 4.2.3.b (триггеры для таблицы subscriptions)

1CREATE TRIGGER "sbscs_date_tm_ins_upd"

2BEFORE INSERT OR UPDATE

3ON "subscriptions"

4FOR EACH ROW

5DECLARE

6new_value DATE;

7BEGIN

8IF (:new."sb_finish" < :new."sb_start") OR (:new."sb_finish" < SYSDATE)

9THEN

10new_value := ADD_MONTHS(2, SYSDATE);

11DBMS_OUTPUT.PUT_LINE('Value [' || :new."sb_finish" ||

12'] was automatically changed to [' ||

13TO_CHAR(new_value, 'YYYY-MM-DD') || ']');

14:new."sb_finish" := new_value;

15END IF;

16END;

На этом решение данной задачи завершено. Убедиться в его корректности вы можете самостоятельно, выполнив запросы к таблице 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 Стр: 350/545

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

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

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

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

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

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

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 351/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.b.

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

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

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

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

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

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

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

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

Решение 5.1.1.a{352}.

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

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

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

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

MySQL Решение 5.1.1.a

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.a (проверка работоспособности)

1SELECT `sb_id`,

2`sb_start`,

3`sb_finish`,

4READ_DURATION_AND_STATUS(`sb_start`, `sb_finish`) AS `rdns`

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

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

Решение для MS SQL Server:

3RETURNS NVARCHAR(150)

4WITH SCHEMABINDING

5AS

6BEGIN

7DECLARE @days INT;

8DECLARE @message NVARCHAR(150);

9

10SET @days = DATEDIFF(day, @start_date, @finish_date);

11SET @message =

12CASE

13WHEN (@days<10) THEN ' OK'

14WHEN ((@days>=10) AND (@days<=30)) THEN ' NOTICE'

15WHEN (@days>30) THEN ' WARNING'

16END;

17

18RETURN CONCAT(@days, @message);

19END;

20GO

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

MS SQL Решение 5.1.1.a (проверка работоспособности)

1SELECT [sb_id],

2[sb_start],

3[sb_finish],

4dbo.READ_DURATION_AND_STATUS([sb_start], [sb_finish]) AS [rdns]

3RETURN NVARCHAR2

4DETERMINISTIC

5IS

6days NUMBER(10);

7message NVARCHAR2(150);

8BEGIN

9SELECT (finish_date - start_date) INTO days FROM dual;

10SELECT CASE

11WHEN (days<10) THEN ' OK'

12WHEN ((days>=10) AND (days<=30)) THEN ' NOTICE'

13WHEN (days>30) THEN ' WARNING'

14END

15INTO message FROM dual;

16

17RETURN CONCAT(days, message);

18END;

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