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

Скачать

Заказать новую работу

Внимание! Если размещение файла нарушает Ваши авторские права, то обязательно сообщите нам

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

Решение 5.1.1 .с

1DROP FUNCTION IF EXISTS BOOKS DELTA;

2DELIMITER $$

3CREATE FUNCTION BOOKS DELTA() RETURNS INT

4BEGIN

5DECLARE old books count INT DEFAULT 0

6DECLARE new_books_count INT DEFAULT 0

7

8 SET old_books_count := (SELECT 'total' FROM 'books_statistics'); 9

10UPDATE 'books statistics'

11JOIN

12(SELECT IFNULL('total', 0 AS 'total',

13IFNULL('given', 0 AS 'given',

14IFNULL('total' - 'given', 0) AS 'rest'

15

FROM

(SELECT (SELECT SUM('b quantity')

 

16

 

FROM

'books')

AS 'total',

17

 

(SELECT COUNT('sb book')

 

18

 

FROM

'subscriptions'

 

19

 

WHERE

'sb is active' = 'Y') AS 'given')

20AS 'prepared data') AS 'src'

21SET 'books statistics' 'total' = 'src' 'total',

22'books statistics' 'given' = 'src' 'given',

23'books statistics' 'rest' = 'src' 'rest';

25SET new books count := (SELECT 'total' FROM 'books statistics');

26

27RETURN new books count - old books count ;

28END;

29$$

30DELIMITER ;

Решение для MySQL готово, а т.к. решения для MS SQL Server не существует, сразу переходим к Oracle, где полностью воспроизведём логику решения для

MySQL.

Oracl

і Решение 5.1.1.c (создание таблицы и инициализация данных)

|

e

 

 

 

 

 

1

-- Создание таблицы:

 

 

2

CREATE TABLE "books statistics"

 

 

3

(

 

 

 

 

4

 

"total" NUMBER(10 ,

 

 

5

 

"given" NUMBER(10 ,

 

 

6

 

"rest"

NUMBER(10

 

 

7

);

 

 

 

 

8

 

 

 

 

 

9

-- Инициализация данных:

 

 

10

INSERT INTO "books statistics"

 

 

11

 

("total"

 

 

12

 

"given"

 

 

13

 

"rest"

 

 

14

SELECT "total",

 

 

15

 

"given",

 

 

16

 

("total" - "given") AS "rest"

 

17

FROM

(SELECT SUM("b quantity"

AS "total"

 

18

 

FROM

"books"

 

 

19

JOIN (SELECT COUNT "sb book"

AS "given"

 

20

 

FROM

"subscriptions"

 

 

21

 

WHERE

"sb is active" =

'Y')

 

22

ON 1 = 1;

 

 

 

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

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

Oracl

і

Решение 5.1.1.c (код функции)

I

 

e

 

 

 

 

 

 

 

1

CREATE OR REPLACE FUNCTION BOOKS DELTA RETURN NUMBER IS

2

PRAGMA AUTONOMOUS TRANSACTION;

 

3

old books count NUMBER;

 

 

4

new books count NUMBER;

 

 

5

BEGIN

 

 

 

 

 

6

SELECT "total" INTO old books count FROM "books statistics";

7

COMMIT;

 

 

 

 

8

 

 

 

 

 

 

9

UPDATE "books statistics"

 

 

10

SET

"total", "given", "rest"

=

11

(SELECT "total",

 

 

 

12

 

 

"given",

 

 

 

13

 

 

"total" - "given"

AS "rest"

14

FROM

 

(SELECT SUM "b quantity") AS "total"

15

 

 

FROM

"books")

 

 

16

JOIN (SELECT COUNT("sb book") AS "given"

17

 

 

FROM

"subscriptions"

18

 

 

WHERE

"sb is active" = 'Y')

19

ON 1 = 1 ;

 

 

 

20

COMMIT;

 

 

 

 

21

 

 

 

 

 

 

22

SELECT "total" INTO new books count FROM "books statistics";

23

COMMIT;

 

 

 

 

24

 

 

 

 

 

 

25

RETURN

new books count - old books count ;

26

END;

 

 

 

 

 

Поскольку у Oracle есть ряд ограничений на модификацию данных из кода хранимых функций, нам нужно реализовать две идеи:

•выполнять функцию в автономной транзакции (строка 2);

•подтверждать транзакцию в теле функции после выполнения каждого запроса (строки 7, 20, 23), т.к. в противном случае возникает ситуация взаимной блокировки между запросами на чтение и обновление данных).

Получить результат выполнения функции можно запросом.

Oracle Решение 5.1.1.c (запрос для получения результата работы функции)

1 SELECT BOOKS_DELTA FROM dual

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

Задание 5.1.1.TSK.A: создать хранимую функцию, получающую на вход &идентификатор читателя и возвращающую список идентификаторов книг,

которые он уже прочитал и вернул в библиотеку.

& Задание 5.1.1.TSK.B: создать хранимую функцию, возвращающую список первого диапазона свободных значений автоинкрементируемых первичных ключей в указанной таблице (например, если в таблице есть первичные ключи

1,4, 8, то первый свободный диапазон — это значения 2 и 3).

& Задание 5.1.1.TSK.C: дополнить решение{355} задачи 5.1.1. b{352} для Oracle двумя вариантами реализации хранимой функции, в которых:

•функция возвращает таблицу из одного поля, в котором хранится весь список значений свободных ключей;

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

Задание 5.1.1.TSK.D: создать хранимую функцию, актуализирующую данные в таблице subscriptions_ready (см. задачу 3.1.2. b{215}) и возвращающую число, показывающее изменение количества выдач книг.

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

Пример 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}.

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

MySQL I

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

|

1DELIMITER $$

2CREATE FUNCTION CHECK SUBSCRIPTION DATES sb start DATE,

3

sb

finish

DATE,

4

is

insert

INT)

5RETURNS INT

6DETERMINISTIC

7BEGIN

8DECLARE result INT DEFAULT 1;

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

11IF sb start > CURDATE())

12THEN

13SET result = 1;

14END IF;

15

 

 

 

16

--

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

17

IF

( sb finish <

AND is insert = 1 )

18THEN

19SET result = 2 ;

20END IF;

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

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

MySQL I

Решение 5.1.2.a (код функции) (продолжение)

|

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

22IF sb finish < sb start)

23THEN

24SET result = 3

25END IF;

26

27RETURN result;

28END;

29$$

30DELIMITER ;

MySQL

Решение 5.1.2.a (код для проверки работы функции)

1SELECT CHECK

2SELECT CHECK

3_

CHECK

4SELECT _

5SELECT CHECK

6_

CHECK

7SELECT _

8SELECT CHECK

SUBSCRIPTION DATES('2025-01-01', '2026-01- SUBSCRIPTION DATES('2025-01-01', '2026-01-

01'

'2006-01- SUBSCRIPTION DATES('2005-01-01' 01'

SUBSCRIPTION DATES('2005-01-01', '2006-01-

01'

'2004-01- SUBSCRIPTION DATES('2005-01-01' 01'

SUBSCRIPTION DATES('2005-01-01', '2004-01-

,1);

,0);

,1);

,0);

,1);

,0);

MS SQL I

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

|

1

CREATE FUNCTION CHECK SUBSCRIPTION DATES @sb start DATE,

2

 

 

@sb finish DATE,

3

 

 

@is insert INT)

4RETURNS INT

5WITH SCHEMABINDING

6AS

7BEGIN

8DECLARE @result INT = 1;

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

11IF @sb start > CONVERT(date, GETDATE()))

12BEGIN

13SET @result = - 1

14END;

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

17 IF ( @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;

Oracl

і

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

|

 

e

 

 

 

 

 

1

CREATE OR REPLACE FUNCTION CHECK SUBSCRIPTION DATES

sb start DATE,

2

 

 

 

sb finish DATE,

3

 

 

 

is insert INT)

4

RETURN NUMBER DETERMINISTIC IS

 

 

5

result value NUMBER := 1;

 

 

6

BEGIN

 

 

7

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

 

8

IF (sb start > TRUNC(SYSDATE))

 

9

 

THEN

 

 

10

 

result value := -1;

 

 

11

 

END IF;

 

 

12

 

 

 

 

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

14IF ( sb finish < TRUNC(SYSDATE)) AND (is insert = 1))

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

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

15THEN

16result value := -2;

17END IF;

18

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

20IF (sb finish < sb start)

21THEN

22result value := -3;

23END IF;

24

25RETURN result value

26END;

Сам код функций примитивен и не нуждается в пояснениях, но стоит отметить

отличие логики представленного здесь решения от логики решения{315} задачи

4.2.1. a<315>.

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

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

Если такое поведение окажется неудобным, всегда можно переписать код,

Oracle

1

2

3

4

5

6

7

8

9

10

і

Решение 5.1.2.a (код для проверки работы функции)

|

SELECT CHECK_SUBSCRIPTION_DATES(TO_DATE('2025-01-01' 'YYYY-MM-DD') TO_DATE('2026-01-01', 'YYYY-MM-DD'), 1 FROM dual;

SELECT CHECK_SUBSCRIPTION_DATES(TO_DATE('2025-01-01' 'YYYY-MM-DD') TO DATE('2026-01-01', 'YYYY-MM-DD'), 0 FROM dual;

SELECT CHECK_SUBSCRIPTION_DATES(TO_DATE('2005-01-01' 'YYYY-MM-DD') TO_DATE('2006-01-01', 'YYYY-MM-DD'), 1 FROM dual;

SELECT CHECK_SUBSCRIPTION_DATES(TO_DATE('2005-01-01' 'YYYY-MM-DD' ) TO DATE('2006-01-01', 'YYYY-MM-DD'), 0 FROM dual;

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;

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

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

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

Источник: https://studfile.net/preview/16420333/

Смотрите также:

_-___-~1
_3 тема - Диффузия
_Роджерс Карл, Брак и его альтернативы
'Заповит' Рейнгольда Глиэра: стилистические особенности
'Создание и продвижение интернет - платформы реферальной системы' на примере компании Бартер–кард
[БИЛЕТЫ] IMK №3
[ТЕСТЫ] ИМК №1 по физиологии (база 1 семестр и на экз)
[ТЕСТЫ] Спланхнология
[ТЕСТЫ] ЦНС - ЛУЧШИЕ 2020
+ Проблемы криминологии. Курс лекций