Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 31: обновление кэширующих таблиц и полей
Добавим две выдачи книги:
MySQL Решение 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 |
Изменим состояние добавленных выдач с «книга возвращена» на «книга не возвращена»:
MySQL |
|
Решение 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 |
Удалим эти две выдачи книг:
MySQL Решение 4.1.1.b (проверка работоспособности)
1DELETE FROM `subscriptions`
2WHERE `sb_id` >= 200
books_taken days_to_read books_returned |
||
1.25 |
46 |
1.5 |
Итак, триггеры для MySQL работают корректно. Переходим к решению для
MS SQL Server.
MS SQL Решение 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)
Проинициализируем данные в созданной таблице. Обратите внимание: здесь снова актуальна проблема преобразования типов данных, т.к. результат деления окажется целочисленным (с потерей части данных), если предварительно не преобразовать полученные значения COUNT и SUM к дроби.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 285/545
Пример 31: обновление кэширующих таблиц и полей
MS SQL Решение 4.1.1.b (очистка таблицы и инициализация данных)
1-- Очистка таблицы:
2TRUNCATE TABLE [averages];
3
4-- Инициализация данных:
5INSERT 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)
16AS [active_count]
17 FROM [subscriptions]
18WHERE [sb_is_active] = 'Y') AS [tmp_active_count],
19(SELECT CAST(COUNT([sb_id]) AS DOUBLE PRECISION)
20AS [inactive_count]
21 FROM [subscriptions]
22WHERE [sb_is_active] = 'N') AS [tmp_inactive_count],
23(SELECT CAST(SUM(DATEDIFF(day, [sb_start], [sb_finish]))
24AS 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 Стр: 286/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], |
|
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 
Решение 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;
16
17-- Добавление двух выдач книг:
18INSERT 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 Стр: 287/545
Пример 31: обновление кэширующих таблиц и полей
MS SQL 
Решение 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)
Oracle Решение 4.1.1.b (очистка таблицы и инициализация данных)
1-- Очистка таблицы:
2TRUNCATE TABLE "averages";
3
4-- Инициализация данных:
5INSERT 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 |
COUNT("s_id") AS "subscribers_count" |
|
|
13 |
|
|
FROM |
"subscribers") "tmp_subscribers_count", |
||
14 |
|
|
(SELECT |
COUNT("sb_id") AS "active_count" |
|
|
15 |
|
|
FROM |
"subscriptions" |
|
|
|
|
|
|
|
|
|
16WHERE "sb_is_active" = 'Y') "tmp_active_count",
17(SELECT COUNT("sb_id") AS "inactive_count"
18 FROM "subscriptions"
19WHERE "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 Стр: 288/545
Пример 31: обновление кэширующих таблиц и полей
Oracle Решение 4.1.1.b (триггеры для таблицы subscribers)
1CREATE TRIGGER "upd_avgs_on_sbrs_ins_del"
2AFTER INSERT OR DELETE
3ON "subscribers"
4BEGIN
5MERGE INTO "averages"
6USING
7(
8 |
|
SELECT |
( "active_count" / "subscribers_count" ) |
AS |
"books_taken", |
||
9 |
|
|
( "days_sum" / "inactive_count" ) |
AS |
"days_to_read", |
||
10 |
|
|
( "inactive_count" / "subscribers_count" ) AS |
"books_returned" |
|||
11 |
|
FROM |
(SELECT |
COUNT("s_id") |
AS "subscribers_count" |
|
|
12 |
|
|
FROM |
"subscribers") |
"tmp_subscribers_count", |
||
13 |
|
|
(SELECT |
COUNT("sb_id") |
AS "active_count" |
|
|
14 |
|
|
FROM |
"subscriptions" |
|
|
|
|
|
|
|
|
|
|
|
15WHERE "sb_is_active" = 'Y') "tmp_active_count",
16(SELECT COUNT("sb_id") AS "inactive_count"
17 FROM "subscriptions"
18WHERE "sb_is_active" = 'N') "tmp_inactive_count",
19(SELECT SUM("sb_finish" - "sb_start") AS "days_sum"
20 FROM "subscriptions"
21WHERE "sb_is_active" = 'N') "tmp_days_sum"
22) "tmp" ON (1=1)
23WHEN MATCHED THEN UPDATE
24 |
SET |
"averages"."books_taken" = "tmp"."books_taken", |
25"averages"."days_to_read" = "tmp"."days_to_read",
26"averages"."books_returned" = "tmp"."books_returned";
27END;
Oracle Решение 4.1.1.b (триггеры для таблицы subscriptions)
1CREATE TRIGGER "upd_avgs_on_sbps_ins_upd_del"
2AFTER INSERT OR UPDATE OR DELETE
3ON "subscriptions"
4BEGIN
5MERGE INTO "averages"
6USING
7(
8 |
|
SELECT |
( "active_count" / "subscribers_count" ) |
AS |
"books_taken", |
||
9 |
|
|
( "days_sum" / "inactive_count" ) |
AS |
"days_to_read", |
||
10 |
|
|
( "inactive_count" / "subscribers_count" ) AS |
"books_returned" |
|||
11 |
|
FROM |
(SELECT |
COUNT("s_id") |
AS "subscribers_count" |
|
|
12 |
|
|
FROM |
"subscribers") |
"tmp_subscribers_count", |
||
13 |
|
|
(SELECT |
COUNT("sb_id") |
AS "active_count" |
|
|
14 |
|
|
FROM |
"subscriptions" |
|
|
|
15WHERE "sb_is_active" = 'Y') "tmp_active_count",
16(SELECT COUNT("sb_id") AS "inactive_count"
17 FROM "subscriptions"
18WHERE "sb_is_active" = 'N') "tmp_inactive_count",
19(SELECT SUM("sb_finish" - "sb_start") AS "days_sum"
20 FROM "subscriptions"
21WHERE "sb_is_active" = 'N') "tmp_days_sum"
22) "tmp" ON (1=1)
23WHEN MATCHED THEN UPDATE
24 |
|
SET |
"averages"."books_taken" = "tmp"."books_taken", |
25"averages"."days_to_read" = "tmp"."days_to_read",
26"averages"."books_returned" = "tmp"."books_returned";
27END;
Как и в решении{272} задачи 4.1.1.a{272}, здесь мы использовали возможность Oracle создавать т.н. «триггеры уровня выражения» (statement level triggers): такой триггер активируется после выполнения всей операции один раз, а не для каждого модифицируемого ряда отдельно, как это происходит, например, в MySQL, где поддерживаются только «триггеры уровня записи» (row level triggers), активирующиеся отдельно для каждого модифицируемого ряда.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 289/545