Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 31: обновление кэширующих таблиц и полей
Oracle Решение 4.1.1 .b (триггеры для
1 CREATE TRIGGER "upd_avgs_on_sbrs_ins_del"
2AFTER INSERT OR DELETE
3ON "subscribers"
4BEGIN
5 |
MERGE INTO "averages" |
6USING
7(
|
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 " |
21 |
|
WHERE |
"sb_is_active" = 'N') " tmp_days_sum" |
22 |
) |
ON 1 |
1) |
23 |
WHEN 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 (триггеры для
1 CREATE TRIGGER "upd_avgs_on_sbps_ins_upd_del"
2AFTER INSERT OR UPDATE OR DELETE
3ON "subscriptions"
4BEGIN
5 MERGE INTO "averages"
6USING
7(
|
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 " |
21 |
|
WHERE |
"sb_is_active" = 'N') " tmp_days_sum" |
22 |
) |
ON 1 |
1) |
23 |
WHEN 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 Стр: 305/545
Пример 31: обновление кэширующих таблиц и полей
В корректности работы полученного решения вы можете убедиться, выполнив следующие запросы (они выполнены пошагово с пояснениями и показом результатов в решении для MySQL):
|
Oracl |
і Решение 4.1.1.b (запросы для проверки работоспособности) | |
e |
|
|
|
|
|
1 |
|
ALTER TRIGGER "TRG subscribers s id" DISABLE; |
2 |
|
ALTER TRIGGER "TRG_subscriptions_sb_id" DISABLE; |
3 |
4 |
|
|
-- Добавление читателя: |
|
5 |
INSERT INTO "subscribers" |
|
6 |
|
("s id" |
7 |
|
"s name") |
8 |
VALUES |
(500 |
9 |
|
N'Читателев Ч.Ч. ' ); |
10 |
|
|
11-- Удаление только что добавленного читателя:
12DELETE FROM "subscribers"
13WHERE "s id" = 500;
14
15-- Добавление двух выдач книг:
16INSERT ALL
17INTO "subscriptions"
18 |
|
("sb id" |
19 |
|
"sb subscriber" |
20 |
|
"sb book", |
21 |
|
"sb start" |
22 |
|
"sb finish" |
23 |
|
"sb is active" |
24 |
VALUES |
(200 |
25 |
|
1, |
26 |
|
1, |
27 |
|
TO DATE '2019-01-12', 'YYYY-MM-DD'), |
28 |
|
TO DATE '2019-02-12', 'YYYY-MM-DD'), |
29 |
|
'N' ) |
30 |
INTO "subscriptions" |
|
31 |
|
("sb id" |
32 |
|
"sb subscriber" |
33 |
|
"sb book", |
34 |
|
"sb start" |
35 |
|
"sb finish" |
36 |
|
"sb is active" |
37 |
VALUES |
(201 |
38 |
|
2, |
39 |
|
1, |
40 |
|
TO DATE '2020-01-12', 'YYYY-MM-DD'), |
41 |
|
TO DATE '2020-02-12', 'YYYY-MM-DD'), |
42 |
|
'N' ) |
43 |
SELECT 1 FROM "DUAL" |
|
44 |
|
|
45-- Изменение состояния добавленных выдач с «книга возвращена»
46-- на «книга не возвращена»:
47UPDATE "subscriptions"
48 |
SET |
"sb is active" = 'Y' |
49 |
WHERE |
"sb id" >= 200; |
50 |
|
|
51-- Удаление только что добавленных выдач книг:
52DELETE FROM "subscriptions"
53WHERE "sb id" >= 200;
54
55ALTER TRIGGER "TRG subscribers s id" ENABLE;
56ALTER TRIGGER "TRG subscriptions sb id" ENABLE;
На этом решение данной задачи завершено.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 306/545
Пример 31: обновление кэширующих таблиц и полей
СЗадание 4.1.1 .TSK.A: модифицировать схему базы данных «Библиотека» таким образом, чтобы таблица authors хранила актуальную информацию о дате последней выдачи книги автора читателю.
ОЗадание 4.1.1 .TSK.B: создать кэширующую таблицу best_averages, содержащую в любой момент времени следующую актуальную информацию: а) сколько в среднем книг находится на руках у читателей, за время работы с библиотекой прочитавших более 20 книг; б) за сколько в среднем по времени (в днях) прочитывает книгу читатель,
никогда не державший у себя книгу больше двух недель; в) сколько в среднем книг прочитал читатель, не имеющий просроченных выдач книг.
Задание 4.1.1.TSK.C: оптимизировать MySQL-триггеры из решения{281} задачи 4.1.1.b{272} так, чтобы не выполнять лишних действий там, где в них нет необходимости (подсказка: не в каждом случае нам нужны все собираемые имеющимися запросами данные).
|
Задание 4.1.1.TSK.D: доработать решение{272} задачи 4.1.1.a{272} для |
( |
MySQL таким образом, чтобы оно учитывало изменения в таблице sub- |
scriptions, вызванные операцией каскадного удаления (при удалении |
|
книг). Убедиться, что решения для MS SQL Server и Oracle не требуют такой |
|
|
доработки. |
|
Задание 4.1.1.TSK.E: доработать решение{281} задачи 4.1.1.b{272} для |
|
MySQL таким образом, чтобы оно учитывало изменения в таблице sub- |
|
scriptions, вызванные операцией каскадного удаления (при удалении |
|
книг). Убедиться, что решения для MS SQL Server и Oracle не требуют |
|
такой доработки. |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 307/545
Пример 32: обеспечение консистентности данных
4.1.2.Пример 32: обеспечение консистентности данных
ОЗадача 4.1.2.a{292}: модифицировать схему базы данных «Библиотека» таким образом, чтобы таблица subscribers хранила информацию о том, сколько
в настоящий момент книг выдано каждому из читателей.
О Задача 4.1.2.b{305}: модифицировать схему базы данных «Библиотека» таким образом, чтобы таблица genres хранила информацию о том, сколько в
настоящий момент книг относится к каждому жанру.
Ожидаемый результат 4.1.2.a.
Таблица subscribers содержит дополнительное поле, хранящее актуальную информацию о количестве выданных каждому читателю книг:
s_id |
s_name |
s_books |
1 |
Иванов И.И. |
0 |
2 |
Петров П.П. |
0 |
3 |
Сидоров С.С. |
3 |
4 |
Сидоров С.С. |
2 |
Ожидаемый результат 4.1.2.b.
Таблица genres содержит дополнительное поле, хранящее актуальную информацию о количестве относящихся к каждому жанру книг:
g_id |
g_name |
g_books |
1 |
Поэзия |
2 |
2 |
Программирование |
3 |
3 |
Психология |
1 |
4 |
Наука |
0 |
5 |
Классика |
4 |
6 |
Фантастика |
1 |
уЦ7
"А Решение 4.1.2.a{292}.
Как и в решении{272} задачи 4.1.1.a{272} здесь нужно будет выполнить три шага:
•модифицировать таблицу subscribers (добавив туда поле для хранения количества выданных читателю книг);
•проинициализировать значения количества выданных книг для всех читателей;
•создать триггеры для поддержания этой информации в актуальном состоянии.
Вотличие от решения{272} задачи 4.1.1.a{272} здесь значение по умолчанию для нового поля представляет собой не NULL, а 0 (потому что «читатель ни разу не
приходил в библиотеку» — это «неизвестность», т.е. NULL, а «у читателя нет книг»
— это вполне чёткое и понятное значение, т.е. 0). Следуя той же логике, в инициализирующем запросе мы используем JOIN, а не LEFT JOIN — нет никакого смысла обновлять данные для читателей, ни разу не бравших книги.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 308/545
Пример 32: обеспечение консистентности данных
Итак, для MySQL первые два шага выполняются с помощью следующих запросов.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 309/545