Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 27: выборка данных с использованием кэширующих представлений и таблиц
Наконец, удалим все книги (что также приведёт к каскадному удалению всех выдач):
|
|
MySQL |
|
|
Решение 3.1.2.a (проверка реакции на удаление всех книг) |
| |
||
|
1 |
DELETE |
FROM 'books' |
|
||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
total |
|
given |
rest |
|
|
|
Было |
51 |
|
4 |
47 |
|
|
|
|
Стало |
0 |
|
0 |
0 |
|
|
|
Итак, все операции модификации данных в таблицах books и subscriptions вызывают соответствующие изменения в агрегирующей таблице books_statistics, которая в MySQL выступает в роли кэширующего представления.
Переходим к MS SQL Server. Теоретически, здесь всё должно быть хорошо, т.к. эта СУБД поддерживает т.н. индексированные представления, но если мы внимательно изучим перечень ограничений10, то придём к неутешительному выводу: придётся идти по пути MySQL и создавать агрегирующую таблицу и триггеры.
Создадим агрегирующую таблицу:
MS SQL Решение 3.1.2.a (создание агрегирующей таблицы)
CREATE TABLE [books_statistics] 2 (
3[total] INTEGERNOT NULL,
4[given] INTEGERNOT NULL,
5 [rest] INTEGERNOT NULL
6 )
|
Проинициализируем данные в созданной таблице: |
|
|
MS |
QL | решение 3.1.2.a (очистка таблицы и инициализация данных) І |
|
|
1 |
-- Очистка таблицы: |
2 |
TRUNCATE TABLE [books_statistics]; |
3 |
|
4 |
-- Инициализация данных: |
5 |
INSERT INTO [books_statistics] |
6 |
([total], |
7 |
[given], |
8 |
[rest]) |
9SELECT ISNULL [total], 0) AS [total],
10ISNULL [given], 0) AS [given],
11ISNULL [total] - [given], 0 AS [rest]
12 |
FROM |
(SELECT (SELECT SUM([b_quantity]) |
|
|
13 |
|
FROM |
[books] |
AS [total], |
14 |
|
(SELECT COUNT [sb_book] |
|
|
15 |
|
FROM |
[subscriptions] |
|
16 |
|
WHERE |
[sb_is_active] = |
'Y') AS [given]) |
17 |
|
AS [prepared data]; |
|
|
До сих пор всё было совершенно идентично MySQL, но внутренняя логика работы триггеров у MS SQL Server совершенно иная, хотя нам по-прежнему придётся создать триггеры на всех трёх операциях (вставки, обновления удаления) для обеих таблиц (books и subscriptions).
10 https://msdn.microsoft.com/en-us/library/ms191432%28v=sql.110%29.aspx
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 235/545
Пример 27: выборка данных с использованием кэширующих представлений и таблиц
MS SQL I |
Решение 3.1.2.a (триггеры для таблицы books) | |
1-- Удаление старых версий триггеров
2-- (удобно в процессе разработки и отладки):
3DROP TRIGGER [upd bks sts on books ins];
4DROP TRIGGER [upd bks sts on books del];
5DROP TRIGGER [upd bks sts on books upd];
6GO
7
8-- Создание триггера, реагирующего на добавление книг:
9CREATE TRIGGER [upd bks sts on books ins]
10ON [books]
11AFTER INSERT
12AS
13UPDATE [books statistics] SET
14[total] = [total] + (SELECT SUMI[b quantity])
15 |
FROM |
[inserted]); |
16UPDATE [books statistics] SET
17[rest] = [total] - [given];
18GO
19
20-- Создание триггера, реагирующего на удаление книг:
21CREATE TRIGGER [upd bks sts on books del]
22ON [books]
23AFTER DELETE
24AS
25UPDATE [books statistics] SET
26[total] = [total] - (SELECT SUM [b quantity]
27 |
FROM |
[deleted]), |
28 |
[given] = [given] - (SELECT COUNT([sb book]! |
|
29 |
FROM |
[subscriptions] |
30 |
WHERE |
[sb book] IN (SELECT [b id] |
31 |
|
FROM [deleted]) |
32 |
AND [sb is active] = 'Y'); |
|
33UPDATE [books statistics] SET
34[rest] = [total] - [given];
35GO
36
37-- Создание триггера, реагирующего на
38-- изменение количества книг:
39CREATE TRIGGER [upd bks sts on books upd]
40ON [books]
42 AFTER UPDATE
42AS
43UPDATE [books statistics] SET
44[total] = [total] - (SELECT SUM [b quantity]
45 |
FROM |
[deleted]) + (SELECT |
SUM([b quantity]) |
46 |
|
FROM |
[inserted] ; |
47UPDATE [books statistics] SET
48[rest] = [total] - [given];
49GO
Основных отличия от решения для MySQL здесь два:
•тело триггера выполняется не для каждого ряда модифицируемых данных (как это происходит в MySQL), а один раз для всего набора данных — отсюда следует не обращение к отдельному полю через ключевые слова OLD и NEW, а работа с «псевдотаблицами» [deleted] (содержит информацию об удаляемых строках и старые данные обновляемых строк) и [inserted] (содержит информацию о добавляемых строках и новые данные обновляемых строк);
•MS SQL Server не позволяет в одном запросе модифицировать значения полей и сразу же использовать их новые значения — потому во всех трёх триггерах для вычисления значения поля [rest] используется отдельный запрос.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 236/545
Пример 27: выборка данных с использованием кэширующих представлений и таблиц
В остальном поведение триггеров в MS SQL Server на таблице books идентично поведению соответствующих триггеров в MySQL. А в триггерах на таблице subscriptions есть существенные отличия.
MS SQL I |
Решение 3.1.2.a (триггеры для таблицы subscriptions) |
| |
1-- Удаление старых версий триггеров
2-- (удобно в процессе разработки и отладки):
3DROP TRIGGER [upd bks sts on subscriptions ins];
4DROP TRIGGER [upd bks sts on subscriptions del];
5DROP TRIGGER [upd bks sts on subscriptions upd];
6GO
7 8 -- Создание триггера, реагирующего на добавление выдачи книг:
9 CREATE TRIGGER [upd bks sts on subscriptions ins]
10ON [subscriptions]
11AFTER INSERT
12AS
13DECLARE @delta INT = (SELECT COUNT(*)
14 |
FROM |
[inserted] |
|
15 |
WHERE |
[sb is |
= 'Y'); |
16UPDATE [books statistics] SET
17[rest] = [rest] - @delta,
18[given] = [given] + @delta
19GO
20 21 -- Создание триггера, реагирующего на удаление выдачи книг:
22 CREATE TRIGGER [upd bks sts on subscriptions del]
23ON [subscriptions]
24AFTER DELETE
25AS
26DECLARE @delta INT = (SELECT COUNT(*)
27 |
FROM |
[deleted] |
|
28 |
WHERE |
[sb is |
= 'Y'); |
29UPDATE [books statistics] SET
30[rest] = [rest] + @delta,
31[given] = [given] - @delta
32GO
33 34 -- Создание триггера, реагирующего на обновление выдачи книг:
35 CREATE TRIGGER [upd bks sts on subscriptions upd]
36ON [subscriptions]
37AFTER UPDATE
38AS
39DECLARE @taken INT = (
40SELECT COUNT(*)
41 |
FROM |
[inserted] |
42 |
|
JOIN [deleted] |
43 |
|
ON [inserted] [sb id] = [deleted] [sb id] |
44WHERE [inserted] [sb is active] = 'Y'
45AND [deleted] [sb is active] = 'N');
47DECLARE @returned INT = (
48SELECT COUNT(*)
49 |
FROM |
[inserted] |
50 |
|
JOIN [deleted] |
51 |
|
ON [inserted] [sb id] = [deleted] [sb id] |
52WHERE [inserted] [sb is active] = 'N'
53AND [deleted] [sb is active] = 'Y');
55DECLARE @delta INT = @taken - @returned;
57UPDATE [books statistics] SET
58[rest] = [rest] - @delta,
59[given] = [given] + @delta
60GO
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 237/545
Пример 27: выборка данных с использованием кэширующих представлений и таблиц
ВMySQL мы вычисляли значение переменной @delta, которое могло становиться равным 0, 1, -1 в зависимости от того, как изменение в анализируемой строке должно повлиять на данные в агрегирующей таблице.
ВMS SQL Server мы должны реализовывать реакцию на изменение не одной отдельной строки, а всего набора модифицируемых строк целиком. Именно поэтому
встроках 13-15 и 26-28 значение переменной @delta определяется как количество
записей, удовлетворяющих условию работы триггера.
В строках 39-55 этот подход ещё больше усложняется: мы должны определить количество выданных (строки 39-45) и возвращённых (строки 47-53) книг, а затем в строке 55 мы можем определить разность полученных чисел и использовать её значение (строки 57-59) для изменения данных в агрегирующей таблице.
Снова (как и в случае с MySQL) проверим, как работает то, что мы создали. Будем модифицировать данные в таблицах books и subscriptions и выбирать данные из таблицы books_statistics.
Добавим две книги с количеством экземпляров 5 и 10:
MS SQL |
Решение 3.1.2.a (проверка реакции на добавление книг) | |
||||
|
INSERT INTO [books] |
||||
2 |
|
|
([b_name], |
||
3 |
|
|
[b_quantity] , |
||
4 |
|
|
[b_year]) |
||
5 |
VALUES |
(N'Новая книга 1', |
|||
6 |
|
|
5, |
|
|
7 |
|
|
2001), |
|
|
8 |
|
|
(N'Новая книга 2', |
||
9 |
|
|
10 , |
|
|
10 |
|
|
2002) |
|
|
|
|
|
|
|
|
|
|
total |
given |
rest |
|
Было |
33 |
5 |
28 |
|
|
Стало |
48 |
5 |
43 |
|
|
Увеличим на пять единиц количество экземпляров книги, которой сейчас в библиотеке зарегистрировано 10 экземпляров (такая книга у нас одна):
MS SQL |
Решение 3.1.2.а (проверка реакции на изменение количества книги) |
|||||||
|
|
1 |
UPDATE [books] |
|
||||
|
|
2 |
SET |
[b_quantity] = [b_quantity] + 5 |
||||
|
|
3 |
WHERE |
[b quantity] = 10 |
|
|||
|
|
|
|
|
|
|
||
|
|
total |
|
given |
rest |
|
||
Было |
48 |
|
5 |
|
43 |
|
|
|
Стало |
53 |
|
5 |
|
48 |
|
|
|
Удалим книгу, оба экземпляра которой сейчас находится на руках у читателей (книга с идентификатором 1).
|
MS SQL |
|
Решение 3.1.2.а (проверка реакции на удаление книги) |
||
|
|
1 DELETE FROM [books] |
|||
2 |
WHERE [b id] = 1 |
|
|
||
|
|
|
|
|
|
|
|
total |
given |
rest |
|
Было |
53 |
5 |
48 |
|
|
Стало |
51 |
3 |
48 |
|
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 238/545
Пример 27: выборка данных с использованием кэширующих представлений и таблиц
Отметим, что по выдаче с идентификатором 3 книга возвращена:
|
MS SQL |
|
Решение 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 |
|
|
Отменим эту операцию (снова отметим книгу как невозвращённую):
|
MS SQL |
|
|
Решение 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:
MS SQL і |
|
|
|
|
Решение 3.1.2.a (проверка реакции на выдачу книг) |
[ |
||||
|
1 |
|
INSERT INTO [subscriptions] |
|
||||||
|
2 |
|
|
|
|
|
([sb subscriber], |
|
||
|
3 |
|
|
|
|
|
[sb book], |
|
||
|
4 |
|
|
|
|
|
[sb start] |
|
||
|
5 |
|
|
|
|
|
[sb finish], |
|
||
|
6 |
|
|
|
|
|
[sb is active] |
|
||
|
7 |
|
VALUES |
(2, |
|
|
|
|||
|
8 |
|
|
|
|
|
5, |
|
|
|
|
9 |
|
|
|
|
|
CAST(N'2016-01-10' AS DATE), |
|
||
|
10 |
|
|
|
|
|
CAST(N'2016-02-10' AS DATE), |
|
||
|
11 |
|
|
|
|
|
'Y'), |
|
||
|
12 |
|
|
|
|
|
(2, |
|
|
|
|
13 |
|
|
|
|
|
6, |
|
|
|
|
14 |
|
|
|
|
|
CAST(N'2016-01-10' AS DATE), |
|
||
|
15 |
|
|
|
|
|
CAST(N'2016-02-10' AS DATE), |
|
||
|
16 |
|
|
|
|
|
'Y' ) |
|
||
|
|
|
|
|
|
|
|
|
||
|
|
|
|
total given rest |
|
|||||
|
Было |
51 |
3 |
48 |
|
|
||||
|
Стало 51 |
5 |
46 |
|
|
|||||
|
|
|
Удалим информацию о выдаче с идентификатором 42 (книга по этой выдаче |
|||||||
уже возвращена): |
|
|||||||||
|
MS SQL |
Решение 3.1.2.а (проверка реакции на удаление выдачи с возвращённой книгой) |
||||||||
|
|
|
1 |
DELETE FROM [subscriptions] |
|
|||||
2 |
WHERE [sb id] = 42 |
|
||||||||
|
|
|
|
|
|
|
|
|
||
|
|
|
|
total |
given |
rest |
|
|
||
|
Было |
|
51 |
|
5 |
|
46 |
|
|
|
|
Стало |
|
51 |
|
5 |
|
46 |
|
|
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 239/545