Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 29: модификация данных с использованием «прозрачных» представлений
'ЬАЙ'
Решение 3.2.1.b{245}.
Решение этой задачи для MySQL подпадает под все ограничения, характерные для решения{246} задачи 3.2.1.a{245}: мы также можем создать представление или удовлетворяющее формату выборки и допускающее лишь удаление данных, или не удовлетворяющее формату выборки (с дополнительными полями) и допускающее обновление и удаление данных. Но вставка данных всё равно работать не будет.
MySQL I Решение 3.2.1.b (представление, допускающее только удаление данных) |
1CREATE VIEW 'subscriptions wcd'
2AS
3SELECT 'sb id',
4 |
|
'sb subscriber', |
5 |
|
'sb book', |
6 |
|
CONCAT('sb start', ' - ', 'sb finish') AS 'sb dates' |
7 |
|
'sb is active' |
8 |
FROM |
'subscriptions' |
|
|
|
|
|
|
MySQL I |
Решение 3.2.1.b (представление, допускающее удаление и обновление данных) | |
|
1CREATE VIEW 'subscriptions wcd trick'
2AS
3SELECT 'sb id',
4 |
|
'sb subscriber' |
5 |
|
'sb book', |
6 |
|
CONCAT('sb start', ' - ', 'sb finish') AS 'sb dates', |
7 |
|
'sb start', |
8 |
|
'sb finish', |
9 |
|
'sb is active' |
10 |
FROM |
'subscriptions' |
За исключением уже неоднократно упомянутой проблемы со вставкой, не позволяющей полностью решить поставленную задачу в MySQL, код представлений совершенно тривиален и построен на элементарных запросах на выборку.
Упомянутые в решении{246} задачи 3.2.1. a{245} ограничения MS SQL Server относительно обновляемых представлений актуальны и в данной задаче: мы снова будем вынуждены создавать INSTEAD OF триггеры для реализации обновления и вставки данных.
MS SQL I Решение 3.2.1.b (создание представления) |
1CREATE VIEW [subscriptions_wcd]
2WITH SCHEMABINDING
3AS
4SELECT [sb id]
5 |
|
[sb |
subscriber] |
6 |
|
[sb |
book], |
7 |
|
CONCAT [sb start], ' - , [sb finish] AS [sb dates] , |
|
8 |
|
[sb_is_active] |
|
9 |
FROM |
[dbo] [subscriptions] |
|
Такое представление уже позволяет извлекать данные в указанном в условии задачи формате, а также выполнять удаление данных. Для того, чтобы через это представление можно было выполнять вставку и обновление данных, нужно создать на нём два триггера — на операциях вставки и обновления.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 265/545
Пример 29: модификация данных с использованием «прозрачных» представлений
MS SQL |
Решение 3.2.1.b (создание триггера для реализации операции вставки) | |
1CREATE TRIGGER [subscriptions wcd ins]
2ON [subscriptions_wcd]
3INSTEAD OF INSERT
4AS
5SET IDENTITY INSERT [subscriptions] ON;
6INSERT INTO [subscriptions]
7 |
|
[sb id], |
8 |
|
[sb subscriber], |
9 |
|
[sb book] |
10 |
|
[sb start], |
11 |
|
[sb finish], |
12 |
|
[sb is active]) |
13 |
SELECT ( CASE |
|
14 |
|
WHEN [sb id] IS NULL |
15 |
|
OR [sb id] = 0 THEN IDENT CURRENT('subscriptions') |
16 |
|
+ IDENT INCR('subscriptions') |
17 |
|
+ ROW NUMBER() OVER (ORDER BY |
18 |
|
(SELECT 1)) |
19 |
|
- 1 |
20 |
|
ELSE [sb id] |
21 |
|
END ) AS [sb id], |
22 |
|
[sb subscriber] |
23 |
|
[sb book], |
24 |
|
SUBSTRING [sb dates], 1 (CHARINDEX(' ', [sb dates]) - 1)) |
25 |
|
AS [sb start], |
26 |
|
SUBSTRING [sb dates], (CHARINDEX(' ', [sb dates]) + 3 , |
27 |
|
DATALENGTH([sb dates]) - |
28 |
|
(CHARINDEX(' ', [sb dates]) + 2)) |
29 |
|
AS [sb finish], |
30 |
|
[sb is active] |
31 |
FROM |
[inserted]; |
32SET IDENTITY INSERT [subscriptions] OFF;
33GO
Нетривиальная логика получения значения первичного ключа (строки 13-21) подробно объяснена в решении{246} задачи 3.2.1.a{245}.
Что касается получения значений полей sb_start и sb_finish (строки 2425 и 26-29), то здесь мы наблюдаем последствия ещё одного ограничения MS SQL Server: в псевдотаблице inserted нет полей, которых нет в представлении, на котором построен триггер. Т.е. единственный способ13 получить значения этих полей — извлечь их из значения поля sb_dates. Это выглядит следующим образом (части строки, содержащей две даты, извлекаются с помощью строковых функций):
sb start |
sb finish |
< ---- _ |
_ |
ГГГГ-ММ-ДД - ГГГГ-ММ-ДД
Такой подход является медленным и ненадёжным, но альтернатив ему нет. Если мы хотим повысить надёжность работы триггера, можно добавить дополнительную проверку на корректность формата «комбинированной даты» в поле sb_dates, но каждая такая дополнительная операция негативно отразится на производительности.
13 https://technet.microsoft.com/en-us/library/ms190188%28v=sql.105%29.aspx
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 266/545
Пример 29: модификация данных с использованием «прозрачных» представлений
Проверим, как будет работать вставка данных с использованием созданного триггера:
MS SQL I Решение 3.2.1.b (проверка работоспособности вставки) |
1 |
INSERT INTO |
[subscriptions wcd] |
2 |
|
([sb id] , |
3 |
|
[sb subscriber], |
4 |
|
[sb book], |
5 |
|
[sb dates] |
6 |
|
[sb is active] |
7 |
VALUES |
(1000, |
8 |
|
1, |
9 |
|
3, |
10 |
|
'2017-01-12 - 2017-03-15', |
11 |
|
'N'), |
12 |
|
(2000, |
13 |
|
1, |
14 |
|
1, |
15 |
|
'2017-01-12 - 2017-03-15', |
16 |
|
'N'); |
17 |
|
|
18 |
INSERT INTO |
[subscriptions wcd] |
19 |
|
([sb subscriber], |
20 |
|
[sb book], |
21 |
|
[sb dates] |
22 |
|
[sb is active] |
23 |
VALUES |
(1, |
24 |
|
3, |
25 |
|
'2019-01-12 - 2019-03-15', |
26 |
|
'N'), |
27 |
|
(1, |
28 |
|
1, |
29 |
|
'2019-01-12 - 2019-03-15', |
30 |
|
'N'); |
31 |
|
|
32 |
INSERT INTO |
[subscriptions wcd] |
33 |
|
([sb subscriber], |
34 |
|
[sb book], |
35 |
|
[sb dates] |
36 |
|
[sb is active] |
37 |
VALUES |
(1, |
38 |
|
3, |
39 |
|
'Это — не даты, а ерунда.', |
40 |
|
'N'); |
|
|
|
Первые два запроса работают корректно, а третий ожидаемо приводит к возникновению ошибочной ситуации:
Msg 241, Level 16, State 1, Procedure subscriptions_wcd_ins, Line 6 Conversion failed when converting date and/or time from character string.
Переходим к реализации обновления данных.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 267/545
Пример 29: модификация данных с использованием «прозрачных» представлений
MS SQL |
Решение 3.2.1.b |
(создание триггера для реализации операции обновления) | |
1CREATE TRIGGER [subscriptions wcd upd]
2ON [subscriptions_wcd]
3INSTEAD OF UPDATE
4AS
5IF UPDATE( [sb id])
6BEGIN
7 |
RAISERROR |
('UPDATE |
of Primary |
Key through |
8 |
|
[subscriptions wcd |
upd] |
|
9 |
|
view is |
prohibited.', 16, 1 ; |
|
10 |
ROLLBACK; |
|
|
|
11END
12ELSE
13UPDATE [subscriptions]
14 |
SET |
[subscriptions] [sb subscriber] = [inserted] [sb subscriber], |
15 |
|
[subscriptions] [sb book] = [inserted] [sb book], |
16 |
|
[subscriptions] [sb start] = |
17 |
|
SUBSTRING([sb dates] 1, |
18 |
|
(CHARINDEX(' ', [sb dates]) - 1 ), |
19 |
|
[subscriptions] [sb finish] = |
20 |
|
SUBSTRING([sb dates] |
21 |
|
(CHARINDEX(' ', [sb dates]) + 3 , |
22 |
|
DATALENGTH([sb dates]) - |
23 |
|
(CHARINDEX(' ', [sb dates]) + 2 ), |
24 |
|
[subscriptions] [sb is active] = [inserted] [sb is active] |
25 |
FROM |
[subscriptions] |
26 |
JOIN |
[inserted] |
27 |
ON [subscriptions] [sb id] = [inserted] [sb id]; |
|
28 |
GO |
|
Логика запрета обновления первичного ключа (строки 5-11) подробно объяснена в решении задачи 3.2.1.a.
Необходимость получать значения полей sb_start и sb_finish (строки 1623) только что была рассмотрена в реализации iNSERT-триггера.
В остальном запрос в строках 14-27 представляет собой классическую реализацию обновления на основе выборки.
Остаётся убедиться, что обновление и удаление работает корректно:
|
MS SQL Решение 3.2.1 .b (проверка работоспособности обновления и удаления) |
|
|
1 |
UPDATE [subscriptions_wcd] |
2 |
SET |
[sb_dates] = '2021-01-12 - 2021-03-15' |
3 |
WHERE |
[sb_id] = 1000; |
4 |
|
|
5 |
DELETE FROM [subscriptions_wcd] |
|
6 |
WHERE |
[sb_id] = 2000 |
7 |
|
|
8DELETE FROM [subscriptions_wcd]
9WHERE [sb dates] = '2021-01-12 - 2021-03-15';
Попытка обновить значение первичного ключа закономерно приведёт к блокировке операции:
MS SQL Решение 3.2.1.b (проверка невозможности обновления значения первичного ключа)
1 UPDATE [subscriptions_wcd]
2 SET [sb_id] = 999 WHERE [sb id] = 1000
В результате выполнения такого запроса будет получено следующее сообщение об ошибке:
Msg 50000, Level 16, State 1, |
Procedure subscriptions_wcd_upd, Line 7 |
||||
UPDATE of |
Primary Key |
through |
[subscriptions_wcd_upd] view is prohibited. |
||
Msg |
3609, |
Level |
16, State 1, |
Line 1 |
|
The |
transaction |
ended |
in the |
trigger. The batch has been aborted. |
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 268/545
Пример 29: модификация данных с использованием «прозрачных» представлений
На этом решение данной задачи для MS SQL Server завершено, и мы переходим к рассмотрению решения для Oracle.
|
Oracl |
і Решение 3.2.1.b (создание представления) | |
|
|
||
e |
|
|
|
|||
|
|
|
|
|
|
|
1 |
|
CREATE VIEW "subscriptions wcd" |
|
|
||
2 |
|
AS |
|
|
|
|
3 |
|
SELECT "sb id", |
|
|
|
|
4 |
|
|
"sb subscriber" |
|
|
|
5 |
|
|
"sb book", |
|
|
|
6 |
|
|
TO CHAR "sb start" |
'YYYY-MM-DD') || |
' - |
' || |
7 |
|
|
TO CHAR "sb finish", 'YYYY-MM-DD') AS "sb dates" |
|||
8 |
|
|
"sb is active" |
|
|
|
9 |
|
FROM |
"subscriptions" |
|
|
|
Использование функции TO_CHAR в строках 6-7 позволяет получить строку с датами в определённом условием задачи формате.
Oracle Решение 3.2.1.b (создание триггера для реализации операции вставки)
1CREATE OR REPLACE TRIGGER "subscriptions wcd ins"
2INSTEAD OF INSERT ON "subscriptions wcd"
3FOR EACH ROW
4BEGIN
5INSERT INTO "subscriptions"
6 |
|
"sb id", |
|
7 |
|
"sb subscriber", |
|
8 |
|
"sb book" |
|
9 |
|
"sb start", |
|
10 |
|
"sb finish", |
|
11 |
|
"sb is active") |
|
12 |
VALUES |
(:new "sb id", |
|
13 |
|
new "sb subscriber" |
|
14 |
|
new "sb book", |
|
15 |
|
TO DATE(SUBSTR( new "sb dates", 1 |
|
16 |
|
(INSTR( new "sb dates" |
' ') - 1 )), 'YYYY-MM-DD'), |
17 |
|
TO DATE(SUBSTR( new "sb dates", |
|
18 |
|
(INSTR( new "sb dates" |
' ') + 3 )), 'YYYY-MM-DD'), |
19 |
|
new "sb is active"!; |
|
20 |
END; |
|
|
Oracl |
і Решение 3.2.1.b (создание триггера для реализации операции обновления) | |
|
||||
e |
|
|||||
|
|
|
|
|
|
|
1 |
CREATE OR REPLACE TRIGGER "subscriptions wcd ins" |
|
||||
2 |
INSTEAD OF UPDATE ON "subscriptions wcd" |
|
||||
3 |
FOR EACH ROW |
|
|
|
|
|
4 |
BEGIN |
|
|
|
|
|
5 |
UPDATE "subscriptions" |
|
|
|||
6 |
SET |
"sb id" = |
new "sb id", |
|
||
7 |
|
"sb subscriber" = |
new "sb subscriber" |
|
||
8 |
|
"sb book" = |
new "sb book", |
|
||
9 |
|
"sb start" = TO DATE(SUBSTR( new "sb dates" |
1 |
|||
10 |
|
|
|
|
(INSTR(:new "sb dates" |
' ') - 1)), |
11 |
|
|
|
|
'YYYY-MM-DD'), |
|
12 |
|
"sb finish" = TO DATE(SUBSTR( new "sb dates", |
||||
13 |
|
|
|
|
(INSTR(:new "sb dates" |
' ') + 3 )), |
14 |
|
|
|
|
'YYYY-MM-DD'), |
|
15 |
|
"sb is active" = |
new "sb is active" |
|
||
16 |
WHERE |
"sb id" = |
|
old "sb id"; |
|
|
17 |
END; |
|
|
|
|
|
Благодаря поддержке триггеров уровня отдельных записей, решение этой задачи в Oracle оказывается достаточно простым: код обоих триггеров сводится к выполнению запроса на вставку или обновление к таблице, на которой построено представление. Как и в MySQL мы можем использовать в Oracle ключевые слова old и new для обращения к «старым» (удаляемым или обновляемым) и новым (добавляемым или обновлённым) данным.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 269/545