Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 31: обновление кэширующих таблиц и полей
MySQL I |
Решение 4.1.1.b (триггеры для таблицы subscriptions) (продолжение) |
| |
59-- Создание триггера, реагирующего на удаление выдачи книги:
60CREATE TRIGGER 'upd_avgs_on_subscriptions_del'
61AFTER DELETE
62ON 'subscriptions'
63FOR EACH ROW
64BEGIN
65UPDATE 'averages',
66 |
|
(SELECT COUNT('s id') AS 'subscribers count' |
|
67 |
|
FROM |
'subscribers') AS 'tmp subscribers count', |
68 |
|
(SELECT COUNT('sb id') AS 'active count' |
|
69 |
|
FROM |
'subscriptions' |
70 |
|
WHERE |
'sb is active' = 'Y') AS 'tmp active count', |
71 |
|
(SELECT COUNT('sb id') AS 'inactive count' |
|
72 |
|
FROM |
'subscriptions' |
73 |
|
WHERE |
'sb is active' = 'N') AS 'tmp inactive count', |
74 |
|
(SELECT SUM(DATEDIFF('sb finish', 'sb start')) AS 'days sum' |
|
75 |
|
FROM |
'subscriptions' |
76 |
|
WHERE |
'sb is active' = 'N') AS 'tmp days sum' |
77 |
SET |
'books taken' = 'active count' / 'subscribers count', |
|
78 |
|
'days to read' = 'days sum' / 'inactive count', |
|
79 |
|
'books returned' = 'inactive count' / 'subscribers count'; |
|
80END;
81$$
82
83-- Восстановление разделителя завершения запросов:
84DELIMITER ;
Проверим работоспособность полученного решения. Будем изменять данные в таблицах subscribers и subscriptions и отслеживать изменения данных в таблице averages.
Исходное состояние таблицы averages таково:
books_taken |
days_to_read |
books_returned |
||||
1.25 |
|
|
|
46 |
1.5 |
|
|
Добавим читателя: |
|
|
|||
|
|
|
|
|||
1 |
INSERT INTO 'subscribers' |
|
|
|||
2 |
|
|
|
('s_id', |
|
|
3 |
|
|
|
's name') |
|
|
4 |
VALUES |
(500, |
|
|
||
5 |
|
|
|
'Читателев |
|
|
|
|
|||||
books_taken |
days_to_read |
books_returned |
||||
1 |
|
|
|
46 |
1.2 |
|
|
Теперь удалим его: |
|
|
|||
MySQL |
Решение 4.1.1.b (проверка работоспособности) |
|||||
|
1 |
|
DELETE FROM 'subscribers' |
|||
2 |
WHERE 's id' = 500 |
|
|
|||
books_taken days_to_read books_returned |
||
1.25 |
46 |
1.5 |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 300/545
Пример 31: обновление кэширующих таблиц и полей
Добавим две выдачи книги:
MySQL I Решение 4.1.1.b (проверка работоспособности)
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 |
200, |
9 |
|
1 |
10 |
|
1 |
11 |
|
'2019-01-12', |
12 |
|
'2019-02-12', |
13 |
|
'N'), |
14 |
|
201, |
15 |
|
2 |
16 |
|
1 |
17 |
|
'2020-01-12', |
18 |
|
'2020-02-12', |
19 |
|
'N' ) |
|
|
|
books_taken days_to_read books_returned |
||
1.25 |
42.25 |
2 |
Изменим состояние добавленных выдач с «книга возвращена» на «книга не возвращена»:
Решение 4.1.1.b
1 UPDATE 'subscriptions'
2 SET 'sb_is_active' = 'Y
3 WHERE 'sb id' >= 200
books_taken |
days_to_read |
books_returned |
||||
1.75 |
|
|
46 |
|
1.5 |
|
|
Удалим эти две выдачи книг: |
|||||
|
|
|
|
|||
books_taken |
days_to_read |
books_returned |
|
|||
1.25 |
|
46 |
1.5 |
|
||
Итак, триггеры для MySQL работают корректно. Переходим к решению для
MS SQL Server.
MS SQL Решение 4.1.1 .b (создание агрегирующей
1таблицы)
2CREATE TABLE. [aVerages] ..............
3[books_taken] DOUBLE PRECISION NOT NULL,
4 |
[days_to_read] DOUBLE PRECISION NOT NULL, |
5 |
[books returned] DOUBLE PRECISION NOT NULL |
6 |
|
Проинициализируем данные в созданной таблице. Обратите внимание: здесь снова актуальна проблема преобразования типов данных, т.к. результат деления окажется целочисленным (с потерей части данных), если предварительно не преобразовать полученные значения COUNT и SUM к дроби.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 301/545
Пример 31: обновление кэширующих таблиц и полей
MS SQL Решение 4.1.1 |
и |
|
||
1 |
.b |
|
|
|
-- Очистка таблицы: |
|
|||
2 |
TRUNCATE TABLE [averages]; |
|
||
3 |
|
|
|
|
4 |
-- Инициализация данных: |
|
||
5 |
INSERT INTO [averages] |
|
||
6 |
|
([books taken] |
|
|
7 |
|
|
[days to read], |
|
8 |
|
|
[books returned] |
|
9 |
SELECT ( [active count] / [subscribers count] ) |
AS [books taken], |
||
10 |
|
( [days sum] / [inactive count] ) |
AS [days to read], |
|
11 |
|
( [inactive count] / [subscribers count] ) AS [books returned] |
||
12 |
FROM |
(SELECT CAST(COUNT>[s id]) AS DOUBLE PRECISION) |
||
13 |
|
AS [subscribers count] |
|
|
14 |
|
FROM |
[subscribers]) AS [tmp subscribers count], |
|
15 |
|
(SELECT CAST(COUNT( [sb id] AS DOUBLE PRECISION) |
||
16 |
|
AS [active count] |
|
|
17 |
|
FROM |
[subscriptions] |
|
18 |
|
WHERE |
[sb is active] = 'Y') AS [tmp active count], |
|
19 |
|
(SELECT CAST(COUNT( [sb id] AS DOUBLE PRECISION) |
||
20 |
|
AS [inactive count] |
|
|
21 |
|
FROM |
[subscriptions] |
|
22 |
|
WHERE |
[sb is active] = 'N') AS [tmp inactive count], |
|
23 |
|
(SELECT CAST(SUM(DATEDIFF(day, [sb start], [sb finish] ) |
||
24 |
|
AS DOUBLE PRECISION) AS [days sum] |
|
|
25 |
|
FROM |
[subscriptions] |
|
26 |
|
WHERE |
[sb is active] = 'N') AS [tmp days sum]; |
|
|
|
|
|
|
Создадим на таблицах subscribers и subscriptions триггеры, модифицирующие данные в агрегирующей таблице averages. Напомним, что MS SQL Server позволяет указывать в триггере сразу несколько активирующих событий, что позволяет нам немного сократить количество написанного кода.
MS SQL Решение 4.1.1 .b (триггеры для таблицы subscribers) |
1CREATE TRIGGER [upd avgs on subscribers ins del]
2ON [subscribers]
3AFTER INSERT, DELETE
4AS
5UPDATE [averages]
6 |
SET |
[books taken] = [active count] / [subscribers count] |
|
7 |
|
[days to read] = [days sum] / [inactive count], |
|
8 |
|
[books returned] = [inactive count] / [subscribers count] |
|
9 |
FROM |
(SELECT CAST(COUNT([s id] AS DOUBLE PRECISION) |
|
10 |
|
AS [subscribers count] |
|
11 |
|
FROM |
[subscribers] AS [tmp subscribers count], |
12 |
|
(SELECT CAST(COUNT([sb id]) AS DOUBLE PRECISION) |
|
13 |
|
AS [active count] |
|
14 |
|
FROM |
[subscriptions] |
15 |
|
WHERE |
[sb is active] = 'Y') AS [tmp active count], |
16 |
|
(SELECT CAST(COUNT([sb id]) AS DOUBLE PRECISION) |
|
17 |
|
AS [inactive count] |
|
18 |
|
FROM |
[subscriptions] |
19 |
|
WHERE |
[sb is active] = 'N') AS [tmp inactive count], |
20 |
|
(SELECT CAST(SUM(DATEDIFF(day, [sb start], [sb finish])) |
|
21 |
|
AS DOUBLE PRECISION) AS [days sum] |
|
22 |
|
FROM |
[subscriptions] |
23 |
|
WHERE |
[sb is active] = 'N') AS [tmp days sum]; |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 302/545
Пример 31: обновление кэширующих таблиц и полей
MS SQL Решение 4.1.1.b (триггеры для таблицы subscriptions)
1CREATE TRIGGER [upd_avgs_on_subscriptions_ins_upd_del]
2ON [subscriptions]
3AFTER INSERT, UPDATE, DELETE
4AS
5UPDATE [averages]
6 |
SET |
[books_taken] = [active_count] / [subscribers_count] |
|||
|
|
[days_to_read] = |
[days_sum] / [inactive_count], |
||
8 |
|
[books_returned] = |
[inactive_count] / [subscribers_count] |
||
9 |
FROM |
(SELECT CAST(COUNT([s_id] |
AS DOUBLE PRECISION) |
||
10 |
|
AS [subscribers_count] |
|
||
11 |
|
FROM |
[subscribers] AS [tmp_subscribers_count], |
||
12 |
|
(SELECT CAST(COUNT([sb_id]) AS DOUBLE PRECISION) |
|||
13 |
|
AS [active_count] |
|
|
|
14 |
|
FROM |
[subscriptions] |
|
|
15 |
|
WHERE |
[sb_is_active] = |
'Y') AS [tmp_active_count], |
|
16 |
|
(SELECT CAST(COUNT([sb_id]) AS DOUBLE PRECISION) |
|||
17 |
|
AS [inactive_count] |
|
||
18 |
|
FROM |
[subscriptions] |
|
|
19 |
|
WHERE |
[sb_is_active] = |
'N') AS [tmp_inactive_count], |
|
20 |
|
(SELECT CAST(SUM(DATEDIFF(day, [sb_start], [sb_finish])) |
|||
21 |
|
AS DOUBLE PRECISION) AS |
[days_sum] |
||
22 |
|
FROM |
[subscriptions] |
|
|
23 |
|
WHERE |
[sb is_ active] |
= 'N') AS [tmp days sum]; |
|
В корректности работы полученного решения вы можете убедиться, выполнив следующие запросы (они выполнены пошагово с пояснениями и показом результатов в решении для MySQL):
MS SQL I Решение 4.1.1.b (запросы для проверки работоспособности) |
1 SET IDENTITY INSERT [subscribers] ON;
2
3-- Добавление читателя:
4INSERT INTO [subscribers]
5 |
|
([s id], |
|
6 |
|
[s |
name]) |
7 |
VALUES |
500, |
|
8 |
|
N' |
Читателев Ч.Ч.'); |
9 |
|
|
|
10-- Удаление только что добавленного читателя:
11DELETE FROM [subscribers]
12WHERE [s id] = 500
13
14SET IDENTITY INSERT [subscribers] OFF;
15SET IDENTITY INSERT [subscriptions] ON;
17-- Добавление двух выдач книг:
18 |
INSERT INTO [subscriptions] |
|||
19 |
|
([sb id] , |
||
20 |
|
[sb |
subscriber], |
|
21 |
|
[sb |
book] |
|
22 |
|
[sb |
start], |
|
23 |
|
[sb |
finish], |
|
24 |
|
[sb |
is active]) |
|
25 |
VALUES |
200, |
||
26 |
|
1 |
■ |
|
27 |
|
1 |
■ |
|
28 |
|
'2019-01-12', |
||
29 |
|
'2019-02-12', |
||
30 |
|
'N'), |
||
31 |
|
201, |
|
|
32 |
|
2 |
, |
|
33 |
|
1 |
■ |
|
34 |
|
'2020-01-12', |
||
35 |
|
'2020-02-12', |
||
36 |
|
'N'); |
||
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 303/545
Пример 31: обновление кэширующих таблиц и полей
MS SQL I Решение 4.1.1.b (запросы для проверки работоспособности) (продолжение) |
37-- Изменение состояния добавленных выдач с «книга возвращена»
38-- на «книга не возвращена»:
39UPDATE [subscriptions]
40 |
SET |
[sb is active] = 'Y' |
41 |
WHERE |
[sb id] >= 200; |
42 |
|
|
43-- Удаление только что добавленных выдач книг:
44DELETE FROM [subscriptions]
45WHERE [sb id] >= 200;
46
47 SET IDENTITY_INSERT [subscriptions] OFF;
Переходим к решению для Oracle, которое отличается от решения для MS SQL Server только синтаксическими особенностями реализации той же логики:
Oracle і Решение 4.1.1.b (создание агрегирующей таблицы)
1CREATE TABLE "averages"
2(
3"books_taken" DOUBLE PRECISION NOT NULL,
4"days_to_read" DOUBLE PRECISION NOT NULL,
5"books_returned" DOUBLE PRECISION NOT NULL
6)
Oracl |
і Решение 4.1.1.b (очистка таблицы и инициализация данных) |
| |
|
||
e |
|
||||
|
|
|
|
|
|
1 |
-- Очистка таблицы: |
|
|
|
|
2 |
TRUNCATE TABLE "averages" ; |
|
|
|
|
3 |
|
|
|
|
|
4 |
-- Инициализация данных: |
|
|
|
|
5 |
INSERT INTO "averages" |
|
|
|
|
6 |
|
"books taken" |
|
|
|
7 |
|
"days to read" |
|
|
|
8 |
|
"books returned") |
|
|
|
9 |
SELECT ( "active count" / "subscribers count" ) |
AS "books taken" |
|||
10 |
( "days sum" / "inactive count" ) |
|
AS "days to read" |
||
11 |
( "inactive count" / "subscribers count" ) AS "books returned" |
||||
12 |
FROM |
COUNT("s id" |
AS "subscribers count" |
||
13 |
FROM |
"subscribers" |
"tmp subscribers count", |
||
14 |
(SELECT COUNT("sb id" |
AS "active count" |
|
||
15 |
FROM |
"subscriptions" |
|
|
|
16 |
WHERE |
"sb is active" = 'Y') "tmp active count" |
|||
17 |
(SELECT COUNT("sb id" |
AS "inactive count" |
|
||
18 |
FROM |
"subscriptions" |
|
|
|
19 |
WHERE |
"sb is active" = 'N') "tmp inactive count", |
|||
20 |
(SELECT SUM "sb finish" - "sb start" |
AS "days sum" |
|||
21 |
FROM |
"subscriptions" |
|
|
|
22 |
WHERE |
"sb is active" = 'N') "tmp days sum" |
|||
Несмотря на то, что логика работы триггеров в Oracle полностью идентична подходам, использованным в MySQL и MS SQL Server, в силу синтаксических особенностей данной СУБД сам запрос на обновление данных выглядит несколько необычно. Здесь мы используем оператор MERGE, указав как условие объединения 1=1, т.е. заведомо выполняющееся равенство.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 304/545