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

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

Пример 31: обновление кэширующих таблиц и полей

MS S QL 1 Решение 4.1.1 .a (запросы для проверки работоспособности) (продолжение)

47	-- Удаление этой откорректированной выдачи: DELETE FROM
48	[subscriptions] WHERE [sb_id]	= 200;
49

50-- Удаление единственной выдачи Петрову П.П.: DELETE

51FROM [subscriptions]

52WHERE [sb_id] = 201;

55 SET IDENTITY INSERT [subscriptions] OFF;

Переходим к решению для Oracle, которое отличается от решения для MS SQL Server только синтаксическими особенностями реализации той же логики:

	Oracle	Решение 4.1.1.a (модификация таблицы и инициализация данных)
	1	-- Модификация таблицы:
2	ALTER TABLE "subscribers"
3	ADD	("s_last_visit"	DATE DEFAULT NULL NULL);
4

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

6UPDATE "subscribers" "outer"

7	SET	"s_last_visit" =
8		(
9		SELECT	"last_visit"
10		FROM	"subscribers"
11		LEFT JOIN (SELECT		"sb_subscriber",
12				MAX "sb_start")	AS "last_visit"
13			FROM	"subscriptions"
14			GROUP BY "sb_subscriber"l		"prepared_data"
15		ON	"s_id"	= "sb_subscriber"
16		WHERE "outer" "s id" = "sb subscriber");

Oracl	і	Решение 4.1.1.a (создание триггеров)			\|
e	і	Решение 4.1.1.a (создание триггеров)			\|
e
1	CREATE TRIGGER "last visit on scs ins upd del"
2	AFTER INSERT OR UPDATE OR DELETE
3	ON "subscriptions"
4	BEGIN
5	UPDATE "subscribers" "outer"
6	SET	"s last visit" =
7		(
8		SELECT	"last visit"
9		FROM	"subscribers"
10		LEFT JOIN (SELECT		"sb subscriber",
11				MAX "sb start"		AS "last visit"
12			FROM	"subscriptions"
13			GROUP BY "sb subscriber") "prepared data"
14		ON	"s id" = "sb subscriber"

15WHERE "outer" "s id" = "sb subscriber");

16END;

Вданном решении мы использовали возможность Oracle создавать т.н. «триггеры уровня выражения» (statement level triggers), работающие аналогично триггерам MS SQL Server: такой триггер активируется после выполнения всей операции один раз, а не для каждого модифицируемого ряда отдельно, как это происходит, например, в MySQL, где поддерживаются только «триггеры уровня записи» (row level triggers), активирующиеся отдельно для каждого модифицируемого ряда.

Вкорректности работы полученного решения вы можете убедиться, выполнив следующие запросы (они выполнены пошагово с пояснениями и показом результатов в решении для MySQL):

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

Пример 31: обновление кэширующих таблиц и полей

Oracle

! і Решение 4.1.1.a (запросы для проверки работоспособности) [ ALTER TRIGGER

"TRG^subscriptions^sbTid" ......... DISABLE;

-- Добавление выдачи книги читателю с идентификатором 2 — (ранее он никогда не был в библиотеке): INSERT INTO "subscriptions"

"sb_id", "sb_subscriber", "sb_book", "sb_start", "sb_finish", "sb_is_active")

VALUES (200, 2,

TO_DATE('2019-01-12', 'YYYY-MM-DD'),

TO_DATE('2019-02-12', 'YYYY-MM-DD'), 'N');

-- Изменение идентификатора читателя в только что -- добавленной выдаче с 2

на 1: UPDATE "subscriptions"

SET		"sb_subscriber"	= 1
WHERE	"sb_id" = 200;
-- Ещё одна		выдача книги Петрову П.П.
-- (идентификатор читателя = 2):
INSERT	INTO	"subscriptions"
	"sb_id",
	"sb_subscriber",
	"sb_book",
	"sb_start",
	"sb_finish",
	"sb_is_active")
VALUES	(201,
	2,

TO_DATE('2020-01-12', 'YYYY-MM-DD'), TO_DATE('2020-02-12', 'YYYY-MM-DD'), 'N');

--Изменение значения даты ранее откорректированной

--выдачи книги (которую переписали с Петрова П.П.

--на Иванова И.И.):

UPDATE "subscriptions"
SET			"sb_start" = TO_DATE '2018-01-12',
'YYYY-MM-DD')
WHERE "sb_id"		= 200;
-- Удаление этой откорректированной выдачи:
DELETE FROM		"subscriptions"
WHERE	"sb_id"		= 200;
-- Удаление			единственной выдачи	Петрову П.П.:
DELETE FROM		"subscriptions"
WHERE	"sb_id"		= 201;
ALTER TRIGGER			"TRG subscriptions sb id" ENABLE;

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

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

Пример 31: обновление кэширующих таблиц и полей

'ЙАЙ'

Решение 4.1.1. b{272}.

Во многом решение данной задачи похоже на решение{216} задачи 3.1.2.a{215}, которое рекомендуется повторить перед тем, как продолжить чтение.

Как обычно, начнём с MySQL. Создадим агрегирующую таблицу:

MySQL і Решение 4.1.1.b (создание агрегирующей таблицы)

1CREATE. TABLE 'averages' ..............

3'books_taken' DOUBLE NOT NULL,

4'days_to_read' DOUBLE NOT NULL,

5'books_returned' DOUBLE NOT NULL

Проинициализируем данные в созданной таблице:

MyS	QL \| решение 4.1.1 .b (очистка таолицы и инициализация данных) І

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	(SELECT COUNT('s_id') AS		'subscribers_count'
13		FROM	'subscribers') AS 'tmp_subscribers_count',
14		(SELECT COUNT('sb_id') AS 'active_count'
15		FROM	'subscriptions'
16		WHERE	'sb_is_active' =	'Y') AS 'tmp_active_count',
17		(SELECT COUNT('sb_id') AS 'inactive_count'
18		FROM	'subscriptions'
19		WHERE	'sb_is_active' =	'N') AS 'tmp_inactive_count',
20		(SELECT SUM(DATEDIFF('sb_finish', 'sb_start')) AS 'days_sum'
21		FROM	'subscriptions'
22		WHERE	'sb is active' = 'N') AS 'tmp days sum';

Напишем триггеры, модифицирующие данные в агрегирующей таблице. Агрегация происходит на основе информации, представленной в таблицах subscribers и subscriptions, потому придётся создавать триггеры для обеих этих таблиц.

Чтобы не усложнять решение, мы будем использовать один и тот же код для всех пяти триггеров (на таблице subscribers должны быть только INSERT- и DE- LETE-триггеры, т.к. обновление этой таблицы не влияет на результаты вычислений, а на таблице subscriptions должны быть все три триггера: INSERT, UPDATE, DE-

LETE).

(В Важно! В MySQL триггеры не активируются каскадными операциями, потому изменения в таблице subscriptions, вызванные удалением книг, останутся «незаметными» для триггеров на этой таблице. В задании 4.1.1. TSK.E{291} вам предлагается доработать данное решение, устранив эту проблему.

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

Пример 31: обновление кэширующих таблиц и полей

для

1 -- Удаление старых версий триггеров 2 -- (удобно в процессе разработки и отладки):

DROP TRIGGER 'upd_avgs_on_subscribers_ins'; 4 DROP TRIGGER 'upd_avgs_on_subscribers_del'; 5

6-- Переключение разделителя завершения запроса,

7-- т.к. сейчас запросом будет создание триггера,

8-- внутри которого есть свои, классические запросы:

9DELIMITER $$

11-- Создание триггера, реагирующего на добавление читателей:

12CREATE TRIGGER 'upd_avgs_on_subscribers_ins'

13AFTER INSERT

14ON 'subscribers'

15FOR EACH ROW

16BEGIN

17UPDATE 'averages',

18(SELECT COUNT('s_id') AS 'subscribers_count'

19		FROM	'subscribers') AS 'tmp_subscribers_count',
20		(SELECT COUNT('sb_id') AS 'active_count'
21		FROM	'subscriptions'
22		WHERE	'sb_is_active' =	'Y') AS 'tmp_active_count',
23		(SELECT COUNT('sb_id') AS		'inactive_count'
24		FROM	'subscriptions'
25		WHERE	'sb_is_active' =	'N') AS 'tmp_inactive_count',
26		(SELECT SUM(DATEDIFF('sb_finish', 'sb_start')) AS 'days_sum'
27		FROM	'subscriptions'
28		WHERE	'sb_is_active' =	'N') AS 'tmp_days_sum'
29	SET	'books_taken' = 'active_count' / 'subscribers_count',

30'days_to_read' = 'days_sum' / 'inactive_count',

31'books_returned' = 'inactive_count' / 'subscribers_count';

32END;

33$$

35-- Создание триггера, реагирующего на удаление читателей:

36CREATE TRIGGER 'upd_avgs_on_subscribers_del'

37AFTER DELETE

38ON 'subscribers'

39FOR EACH ROW

40BEGIN

41UPDATE 'averages',

42(SELECT COUNT('s_id') AS 'subscribers_count'

43		FROM	'subscribers') AS 'tmp_subscribers_count',
44		(SELECT COUNT('sb_id') AS 'active_count'
45		FROM	'subscriptions'
46		WHERE	'sb_is_active' = 'Y') AS 'tmp_active_count',
47		(SELECT COUNT('sb_id') AS		'inactive_count'
48		FROM	'subscriptions'
49		WHERE	'sb_is_active' =	'N') AS 'tmp_inactive_count',
50		(SELECT SUM(DATEDIFF('sb_finish', 'sb_start')) AS
51		FROM	'subscriptions'
52		WHERE	'sb_is_active' = 'N') AS 'tmp_days_sum'
53	SET	'books_taken' = 'active_count' / 'subscribers_count',
45		'days_to_read' = 'days_sum' / 'inactive_count',

55'books_returned' = 'inactive_count' / 'subscribers_count';

56END;

57$$

59-- Восстановление разделителя завершения запросов:

60DELIMITER ;

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

Пример 31: обновление кэширующих таблиц и полей

Создадим триггеры на таблице subscriptions.

для

1 -- Удаление старых версий триггеров

2-- (удобно в процессе разработки и отладки):

DROP TRIGGER 'upd_avgs_on_subscriptions_ins';

4DROP TRIGGER 'upd_avgs_on_subscriptions_upd';

5DROP TRIGGER 'upd_avgs_on_subscriptions_del';

7-- Переключение разделителя завершения запроса,

8-- т.к. сейчас запросом будет создание триггера,

9-- внутри которого есть свои, классические запросы:

10DELIMITER $$

12-- Создание триггера, реагирующего на добавление выдачи книги:

13CREATE TRIGGER 'upd_avgs_on_subscriptions_ins'

14AFTER INSERT

15 ON 'subscriptions'

16FOR EACH ROW

17BEGIN

18UPDATE 'averages',

19(SELECT COUNT('s_id') AS 'subscribers_count'

20		FROM	'subscribers') AS 'tmp_subscribers_count',
21		(SELECT COUNT('sb_id') AS 'active_count'
22		FROM	'subscriptions'
23		WHERE	'sb_is_active' =	'Y') AS 'tmp_active_count',
24		(SELECT COUNT('sb_id') AS		'inactive_count'
25		FROM	'subscriptions'
26		WHERE	'sb_is_active' =	'N') AS 'tmp_inactive_count',
27		(SELECT SUM(DATEDIFF('sb_finish', 'sb_start')) AS 'days_sum'
28		FROM	'subscriptions'
29		WHERE	'sb_is_active' =	'N') AS 'tmp_days_sum'
30	SET	'books_taken' = 'active_count' / 'subscribers_count',

31'days_to_read' = 'days_sum' / 'inactive_count',

32'books_returned' = 'inactive_count' / 'subscribers_count';

33END;

34$$

36-- Создание триггера, реагирующего на обновление выдачи книги:

37CREATE TRIGGER 'upd_avgs_on_subscriptions_upd'

38AFTER UPDATE

39ON 'subscriptions'

40FOR EACH ROW

41BEGIN

42UPDATE 'averages',

43(SELECT COUNT('s_id') AS 'subscribers_count'

44		FROM	'subscribers') AS 'tmp_subscribers_count',
45		(SELECT COUNT('sb_id') AS 'active_count'
46		FROM	'subscriptions'
47		WHERE	'sb_is_active' = 'Y') AS 'tmp_active_count',
48		(SELECT COUNT('sb_id') AS 'inactive_count'
49		FROM	'subscriptions'
50		WHERE	'sb_is_active' = 'N') AS 'tmp_inactive_count',
51		(SELECT SUM(DATEDIFF('sb_finish', 'sb_start')) AS 'days_sum'
52		FROM	'subscriptions'
53		WHERE	'sb_is_active' = 'N') AS 'tmp_days_sum'
45	SET	'books_taken' = 'active_count' / 'subscribers_count',

55'days_to_read' = 'days_sum' / 'inactive_count',

56'books_returned' = 'inactive_count' / 'subscribers_count';

57END;

58$$

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

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

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