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

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

5.1.2.Пример 37: контроль операций с данными с использованием хранимых функций

Задача 5.1.2.a{370}: создать хранимую функцию, автоматизирующую проверку условий задачи 4.2.1.a{315}, т.е. возвращающую значение 1 (все условия выполнены) или -1, -2, -3 (если хотя бы одно условие нарушено, модуль числа соответствует номеру условия) в зависимости от того, выполняются ли следующие условия:

•дата выдачи книги не может находиться в будущем;

•дата возврата книги не может находиться в прошлом (только в случае вставки данных);

•дата возврата книги не может быть меньше даты выдачи книги.

Задача 5.1.2.b{373}: создать хранимую функцию, автоматизирующую проверку условий задачи 4.2.2.a{338}, т.е. возвращающую 1, если имя читателя содержит хотя бы два слова и одну точку, и 0, если это условие нарушено.

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

Функция возвращает 1, если все условия задачи выполнены, и 0, если хотя бы одно условие нарушено.

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

Функция возвращает 1, если условие задачи выполнено, и 0, если оно нару-

шено.

Решение 5.1.2.a{370}.

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

1DELIMITER $$

2CREATE FUNCTION CHECK_SUBSCRIPTION_DATES(sb_start DATE,

5RETURNS INT

6DETERMINISTIC

7BEGIN

8DECLARE result INT DEFAULT 1;

9

10-- Блокировка выдач книг с датой выдачи в будущем

11IF (sb_start > CURDATE())

12THEN

13SET result = -1;

14END IF;

15

16-- Блокировка выдач книг с датой возврата в прошлом.

17IF ((sb_finish < CURDATE()) AND (is_insert = 1))

18THEN

19SET result = -2;

20END IF;

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

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

21-- Блокировка выдач книг с датой возврата меньшей, чем дата выдачи.

22IF (sb_finish < sb_start)

23THEN

24SET result = -3;

25END IF;

26

27RETURN result;

28END;

29$$

30DELIMITER ;

1SELECT CHECK_SUBSCRIPTION_DATES('2025-01-01', '2026-01-01', 1);

2SELECT CHECK_SUBSCRIPTION_DATES('2025-01-01', '2026-01-01', 0);

3

4SELECT CHECK_SUBSCRIPTION_DATES('2005-01-01', '2006-01-01', 1);

5SELECT CHECK_SUBSCRIPTION_DATES('2005-01-01', '2006-01-01', 0);

6

7SELECT CHECK_SUBSCRIPTION_DATES('2005-01-01', '2004-01-01', 1);

8SELECT CHECK_SUBSCRIPTION_DATES('2005-01-01', '2004-01-01', 0);

MS SQL Решение 5.1.2.a (код функции)

4RETURNS INT

5WITH SCHEMABINDING

6AS

7BEGIN

8DECLARE @result INT = 1;

9

10-- Блокировка выдач книг с датой выдачи в будущем

11IF (@sb_start > CONVERT(date, GETDATE()))

12BEGIN

13SET @result = -1;

14END;

15

16-- Блокировка выдач книг с датой возврата в прошлом.

17IF ((@sb_finish < CONVERT(date, GETDATE())) AND (@is_insert = 1))

18BEGIN

19SET @result = -2;

20END;

21

22-- Блокировка выдач книг с датой возврата меньшей, чем дата выдачи.

23IF (@sb_finish < @sb_start)

24BEGIN

25SET @result = -3;

26END;

27

28RETURN @result;

29END;

1SELECT dbo.CHECK_SUBSCRIPTION_DATES('2025-01-01', '2026-01-01', 1);

2SELECT dbo.CHECK_SUBSCRIPTION_DATES('2025-01-01', '2026-01-01', 0);

3

4SELECT dbo.CHECK_SUBSCRIPTION_DATES('2005-01-01', '2006-01-01', 1);

5SELECT dbo.CHECK_SUBSCRIPTION_DATES('2005-01-01', '2006-01-01', 0);

6

7SELECT dbo.CHECK_SUBSCRIPTION_DATES('2005-01-01', '2004-01-01', 1);

8SELECT dbo.CHECK_SUBSCRIPTION_DATES('2005-01-01', '2004-01-01', 0);

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

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

4RETURN NUMBER DETERMINISTIC IS

5result_value NUMBER := 1;

6BEGIN

7-- Блокировка выдач книг с датой выдачи в будущем

8IF (sb_start > TRUNC(SYSDATE))

9THEN

10result_value := -1;

11END IF;

12

13-- Блокировка выдач книг с датой возврата в прошлом.

14IF ((sb_finish < TRUNC(SYSDATE)) AND (is_insert = 1))

15THEN

16result_value := -2;

17END IF;

18

19-- Блокировка выдач книг с датой возврата меньшей, чем дата выдачи.

20IF (sb_finish < sb_start)

21THEN

22result_value := -3;

23END IF;

24

25RETURN result_value;

26END;

1SELECT CHECK_SUBSCRIPTION_DATES(TO_DATE('2025-01-01', 'YYYY-MM-DD'),

2TO_DATE('2026-01-01', 'YYYY-MM-DD'), 1) FROM dual;

3SELECT CHECK_SUBSCRIPTION_DATES(TO_DATE('2025-01-01', 'YYYY-MM-DD'),

4TO_DATE('2026-01-01', 'YYYY-MM-DD'), 0) FROM dual;

5

6SELECT CHECK_SUBSCRIPTION_DATES(TO_DATE('2005-01-01', 'YYYY-MM-DD'),

7TO_DATE('2006-01-01', 'YYYY-MM-DD'), 1) FROM dual;

8SELECT CHECK_SUBSCRIPTION_DATES(TO_DATE('2005-01-01', 'YYYY-MM-DD'),

9TO_DATE('2006-01-01', 'YYYY-MM-DD'), 0) FROM dual;

10

11SELECT CHECK_SUBSCRIPTION_DATES(TO_DATE('2005-01-01', 'YYYY-MM-DD'),

12TO_DATE('2004-01-01', 'YYYY-MM-DD'), 1) FROM dual;

13SELECT CHECK_SUBSCRIPTION_DATES(TO_DATE('2005-01-01', 'YYYY-MM-DD'),

14TO_DATE('2004-01-01', 'YYYY-MM-DD'), 0) FROM dual;

Сам код функций примитивен и не нуждается в пояснениях, но стоит отметить отличие логики представленного здесь решения от логики решения{315} задачи

4.2.1.a{315}.

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

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

Если такое поведение окажется неудобным, всегда можно переписать код, возвращая результат по факту провала первой же проверки, обнаружившей нарушение условия задачи.

На этом решение данной задачи завершено.

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

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

Oracle Решение 5.1.2.b (код функции)

1CREATE OR REPLACE

2FUNCTION CHECK_SUBSCRIBER_NAME (subscriber_name NVARCHAR2)

3RETURN NUMBER DETERMINISTIC IS

4BEGIN

5IF ((NOT REGEXP_LIKE(subscriber_name, '^[a-zA-Zа-яА-ЯёЁ''-]+([^a-zA-Zа-яА-

6ЯёЁ''-]+[a-zA-Zа-яА-ЯёЁ''.-]+){1,}$'))

7OR (INSTRC(subscriber_name, '.', 1, 1) = 0))

8THEN

9RETURN 0;

10ELSE

11RETURN 1;

12END IF;

13END;

1SELECT CHECK_SUBSCRIBER_NAME(N'Иванов') FROM dual;

2SELECT CHECK_SUBSCRIBER_NAME(N'Иванов И') FROM dual;

3SELECT CHECK_SUBSCRIBER_NAME(N'Иванов И.') FROM dual;

На этом решение данной задачи завершено.

Задание 5.1.2.TSK.A: переписать решения{315}, {338} задач 4.2.1.a{315} и 4.2.2.a{338} с использованием хранимых функций, созданных в решениях{370}, {373} задач 5.1.2.a{370} и 5.1.2.b{370} соответственно.

Задание 5.1.2.TSK.B: создать хранимую функцию, автоматизирующую проверку условий задачи 4.2.1.b{315}, т.е. возвращающую 1, если у читателя на руках сейчас менее десяти книг, и 0 в противном случае.

Задание 5.1.2.TSK.C: создать хранимую функцию, автоматизирующую проверку условий задачи 4.2.2.b{338}, т.е. возвращающую 1, если книга издана менее ста лет назад, и 0 в противном случае.

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