Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 27: выборка данных с использованием кэширующих представлений и таблиц
вершенно тривиальны, и единственная их непривычность заключается в использовании ключевых слов OLD и NEW, которые мы только что рассмотрели.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 230/545
Пример 27: выборка данных с использованием кэширующих представлений и таблиц
MySQL . і Решение 3.1.2.a (триггеры для таблицы subscriptions)
1 |
-- Удаление старых версий триггеров |
|
|||
2 |
|
-- (удобно в процессе разработки и отладки): |
|||
DROP TRIGGER |
'upd_bks_sts_on_subscriptions_ins'; |
||||
3 |
|||||
DROP TRIGGER |
'upd_bks_sts_on_subscriptions_del'; |
||||
4 |
|||||
DROP TRIGGER |
'upd_bks_sts_on_subscriptions_upd'; |
||||
5 |
|||||
|
|
|
|
||
6 |
|
|
|
|
|
7 |
— Переключение разделителя завершения запроса, |
||||
8 |
|||||
-- |
т.к. сейчас запросом будет создание триггера, |
||||
9 |
|||||
-- |
|
внутри которого есть |
свои, классические |
||
10 |
|
||||
запросы: |
|
|
|
||
11 |
DELIMITER $$ |
|
|
||
12 |
|
|
|||
|
|
|
|
||
13 |
-- Создание триггера, реагирующего на добавление выдачи книг: CREATE TRIGGER |
||||
14 |
|||||
|
'upd_bks_sts_on_subscriptions_ins' |
||||
15 |
|
||||
BEFORE INSERT |
|
|
|||
16 |
|
|
|||
ON 'subscriptions' FOR EACH ROW |
|
||||
17 |
|
||||
BEGIN |
|
|
|||
18 |
|
|
|||
|
|
|
|
||
19 |
SET @delta = 0; |
|
|
||
20 |
|
|
|||
|
|
|
|
||
21 |
IF |
(NEW.'sb_is_active' = 'Y') THEN |
|
||
|
|
||||
22 |
SET @delta = 1; |
|
|||
23 |
END IF; |
|
24 |
||
|
25UPDATE 'books_statistics' SET
26'rest' = 'rest' - @delta, 'given' = 'given' + @delta;
27END;
28$$
29 |
|
30 |
-- Создание триггера, реагирующего на удаление выдачи книг: CREATE TRIGGER |
31 |
'upd_bks_sts_on_subscriptions_del' |
32BEFORE DELETE
33ON 'subscriptions' FOR EACH ROW
34BEGIN
35
36
37
38IF (OLD.'sb_is_active' = 'Y') THEN
39SET @delta = 1;
40END IF;
41
42UPDATE 'books_statistics' SET
43'rest' = 'rest' + @delta, 'given' = 'given' - @delta;
44END;
45$$
46
47
48
49
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 231/545
Пример 27: выборка данных с использованием кэширующих представлений и таблиц
MySQL I |
Решение 3.1.2.a (триггеры для таблицы subscriptions) (продолжение) |
| |
50-- Создание триггера, реагирующего на обновление выдачи книг:
51CREATE TRIGGER 'upd_bks_sts_on_subscriptions_upd'
52BEFORE UPDATE
53ON 'subscriptions'
54FOR EACH ROW
55BEGIN
56SET @delta = 0;
57
58 IF ((NEW.'sb is active' = 'Y') AND (OLD.'sb is active' =THEN
59SET @delta = - 1
60END IF;
61
62 IF ((NEW.'sb is active' = 'N') AND (OLD.'sb is active' =THEN
63SET @delta = 1
64END IF;
65 |
|
|
|
66 |
UPDATE 'books |
statistics' SET |
|
67 |
'rest' |
= 'rest' + |
@delta |
68 |
'given' = 'given' |
- @delta; |
|
69END;
70$$
71
72-- Восстановление разделителя завершения запросов:
73DELIMITER ;
Триггеры на таблице subscriptions оказываются чуть более сложными, тем триггеры на таблице books: здесь приходится анализировать происходящее и предпринимать действия в зависимости от ситуации.
В триггере, реагирующем на добавление выдачи книг (строки 13-30) мы должны изменить значения 'rest' и 'given' только в том случае, если книга в добавляемой выдаче отмечена как находящаяся на руках у читателя. Изначально мы предполагаем, что это не так, и инициализируем в строке 20 переменную @delta значением 0. Если далее оказывается, что книга всё же выдана, мы изменяем значение этой переменной на 1 (строки 22-24). Таким образом, в запросе в строках 26-28 значения полей агрегирующей таблицы будут меняться на 0 (т.е. оставаться неизменными) или на 1 в зависимости от того, выдана ли книга читателю.
Абсолютно аналогичной логикой мы руководствуемся в триггере, реагирующем на удаление выдачи книги (строки 32-49).
В триггере, реагирующем на обновление выдачи книги, нам нужно рассмотреть четыре случая (из которых нас на самом деле интересуют только два последних):
•книга была на руках у читателя и там же осталась (значение '
sb_is_active' было равно Y и таким же осталось);
•книга не была на руках у читателя и там же осталась (значение '
sb_is_active' было равно N и таким же осталось);
•книга была на руках у читателя, и он её вернул (значение 'sb_is_active'
было равно Y, но поменялось на N — строки 58-60 запроса);
• книга не была на руках у читателя, но он её забрал (значение 'sb_is_active' было равно N, но поменялось на Y — строки 62-64 запроса).
Очевидно, что количество выданных и оставшихся в библиотеке книг изменяется только в двух последних случаях, которые и учтены в условиях, представленных в строках 58-64. Запрос в строках 66-68 использует значение переменной @delta, изменённое этими условиями, для модификации агрегированных данных.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 232/545
Пример 27: выборка данных с использованием кэширующих представлений и таблиц
Проверим, как работает то, что мы создали. Будем модифицировать данные в таблицах books и subscriptions и выбирать данные из таблицы books_statistics.
Добавим две книги с количеством экземпляров 5 и 10:
MySQL Решение 3.1.2.a (проверка реакции на добавление книг)
|
1 |
INSERT INTO 'books' |
||||
|
2 |
|
|
('b id', |
||
|
3 |
|
|
'b name', |
||
|
4 |
|
|
'b_quantity', |
||
|
5 |
|
|
'b_year') |
||
|
6 |
VALUES |
(NULL, |
|||
|
7 |
|
|
'Новая книга 1', |
||
|
8 |
|
|
5, |
|
|
|
9 |
|
|
2001), |
|
|
|
10 |
|
|
(NULL, |
||
|
11 |
|
|
'Новая книга 2', |
||
|
12 |
|
|
10 |
|
|
|
13 |
|
|
2002) |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
total |
given |
rest |
|
Было |
|
33 |
5 |
28 |
|
|
Стало |
|
48 |
5 |
43 |
|
|
Увеличим на пять единиц количество экземпляров книги, которой сейчас в библиотеке зарегистрировано 10 экземпляров (такая книга у нас одна):
|
|
|
Решение 3.1.2.a |
|
|
на изменение количества |
||
|
1 |
UPDATE 'books' |
|
|
|
|||
|
2 |
SET |
'b_quanti |
= 'b_quantity' + 5 |
||||
|
3 |
WHERE |
'b quantity' = 10 |
|||||
|
|
|
|
|
|
|
|
|
|
|
|
total |
|
given |
rest |
|
|
Было |
|
48 |
|
5 |
43 |
|
|
|
Стало |
|
53 |
|
5 |
48 |
|
|
|
Удалим книгу, оба экземпляра которой сейчас находится на руках у читателей (книга с идентификатором 1).
|
MySQL |
|
|
|
Решение 3.1.2.a (проверка реакции на удаление книги) |
||
|
|
1 |
DELETE FROM 'books' |
||||
2 |
WHERE 'b id' = 1 |
|
|
||||
|
|
|
|
|
|
|
|
|
|
|
total |
given |
rest |
|
|
Было |
|
53 |
|
5 |
48 |
|
|
Стало |
|
51 |
|
3 |
48 |
|
|
Отметим, что по выдаче с идентификатором 3 книга возвращена:
Решение 3.1.2.a |
на |
1 UPDATE 'subscriptions'
2 SET 'sb_is_active' = 'N
3 WHERE 'sb id' = 3
|
total |
given |
rest |
Было |
51 |
3 |
48 |
Стало |
51 |
2 |
49 |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 233/545
Пример 27: выборка данных с использованием кэширующих представлений и таблиц
Отменим эту операцию (снова отметим книгу как невозвращённую):
|
MySQL |
|
|
Решение 3.1.2.a (проверка реакции на отмену возврата книги) |
|||
|
1 |
UPDATE 'subscriptions' |
|||||
|
2 |
SET |
'sb is active' = 'Y' |
||||
|
3 |
WHERE |
'sb id' = 3 |
||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
total |
|
given |
rest |
|
Было |
|
51 |
|
2 |
49 |
|
|
Стало |
|
51 |
|
3 |
48 |
|
|
Добавим в базу данных информацию о том, что читатель с идентификатором 2 взял в библиотеке книги с идентификаторами 5 и 6:
|
MySQL | |
|
|
Решение 3.1.2.a (проверка реакции на выдачу книг) |
| |
||||
|
1 |
INSERT |
INTO 'subscriptions' |
|
|||||
|
2 |
|
|
|
('sb id', |
|
|||
|
3 |
|
|
|
'sb |
subscriber', |
|
||
|
4 |
|
|
|
'sb |
book', |
|
||
|
5 |
|
|
|
'sb |
start', |
|
||
|
6 |
|
|
|
'sb |
finish' |
|
||
|
7 |
|
|
|
'sb |
is active') |
|
||
|
8 |
VALUES |
|
(NULL, |
|
||||
|
9 |
|
|
2 , |
|
|
|
||
|
10 |
|
|
5 , |
|
|
|
||
|
11 |
|
|
'2016-01-10', |
|
||||
|
12 |
|
|
'2016-02-10', |
|
||||
|
13 |
|
|
|
'Y'), |
|
|||
|
14 |
|
|
|
(NULL, |
|
|||
|
15 |
|
|
2 , |
|
|
|
||
|
16 |
|
|
6 , |
|
|
|
||
|
17 |
|
|
'2016-01-10', |
|
||||
|
18 |
|
|
'2016-02-10', |
|
||||
|
19 |
|
|
|
'Y' |
) |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
total |
|
given |
rest |
|
|
|
|
Было |
51 |
|
3 |
48 |
|
|
||
|
Стало |
51 |
|
5 |
46 |
|
|
||
Удалим информацию о выдаче с идентификатором 42 (книга по этой выдаче уже возвращена):
|
MySQL |
|
Решение 3.1.2.a (проверка реакции на удаление выдачи с возвращённой книгой) |
||||
|
|
1 |
DELETE FROM 'subscriptions' |
||||
2 |
WHERE 'sb id' = 42 |
||||||
|
|
|
|
|
|
|
|
|
|
|
total |
given |
rest |
|
|
Было |
|
51 |
|
5 |
46 |
|
|
Стало |
|
51 |
|
5 |
46 |
|
|
Удалим информацию о выдаче с идентификатором 62 (книга по этой выдаче ещё не возвращена):
|
MySQL |
Решение 3.1.2.a (проверка реакции на удаление выдачи с не возвращённой книгой) |
|||||
|
|
1 |
DELETE FROM 'subscriptions' |
||||
2 |
WHERE 'sb id' = 62 |
||||||
|
|
|
|
|
|
|
|
|
|
|
total |
given |
rest |
|
|
Было |
|
51 |
|
5 |
46 |
|
|
Стало |
|
51 |
|
4 |
47 |
|
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 234/545