Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 29: модификация данных с использованием «прозрачных» представлений
3.2. Модификация данных с использованием представлений
3.2.1.Пример 29: модификация данных с использованием «прозрачных» представлений
Задача 3.2.1.a{246}: создать представление, извлекающее информацию о читателях, переводя весь текст в верхний регистр и при этом допускающее модификацию списка читателей.
Задача 3.2.1.b{251}: создать представление, извлекающее информацию о датах выдачи и возврата книг в виде единой строки и при этом допускающее обновление информации в таблице subscriptions.
Ожидаемый результат 3.2.1.a.
Выполнение запроса вида SELECT * FROM {представление} позволяет получить представленный ниже результат, и при этом выполненные с представлением операции INSERT, UPDATE, DELETE модифицируют соответствующие данные в исходной таблице.
s_id |
s_name |
1 |
ИВАНОВ И.И. |
2 |
ПЕТРОВ П.П. |
3 |
СИДОРОВ С.С. |
4 |
СИДОРОВ С.С. |
Ожидаемый результат 3.2.1.b.
Выполнение запроса вида SELECT * FROM {представление} позволяет получить представленный ниже результат, и при этом выполненные с представлением операции INSERT, UPDATE, DELETE модифицируют соответствующие данные в исходной таблице.
sb_id |
sb_subscriber |
sb_book |
sb_dates |
sb_is_active |
2 |
1 |
1 |
2011-02-12 - 2011-02-12 |
N |
3 |
3 |
3 |
2012-05-17 - 2012-07-17 |
Y |
42 |
1 |
2 |
2012-06-11 - 2012-08-11 |
N |
57 |
4 |
5 |
2012-06-11 - 2012-08-11 |
N |
61 |
1 |
7 |
2014-08-03 - 2014-10-03 |
N |
62 |
3 |
5 |
2014-08-03 - 2014-10-03 |
Y |
86 |
3 |
1 |
2014-08-03 - 2014-09-03 |
Y |
91 |
4 |
1 |
2015-10-07 - 2015-03-07 |
Y |
95 |
1 |
4 |
2015-10-07 - 2015-11-07 |
N |
99 |
4 |
4 |
2015-10-08 - 2025-11-08 |
Y |
100 |
1 |
3 |
2011-01-12 - 2011-02-12 |
N |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 245/545
Пример 29: модификация данных с использованием «прозрачных» представлений
Решение 3.2.1.a{245}.
Создать представление, позволяющее извлечь из базы данных информацию в требуемой форме, легко. Достаточно использовать функцию UPPER для приведения имени читателя к верхнему регистру (строка 4).
MySQL Решение 3.2.1.a (представление, допускающее только удаление данных)
1CREATE VIEW `subscribers_upper_case`
2AS
3SELECT `s_id`,
4UPPER(`s_name`) AS `s_name`
5FROM `subscribers`
Проблема заключается в том, что MySQL налагает широкий спектр ограничений11 на обновляемые представления, среди которых есть и использование функций для преобразования значений полей. Мы используем функцию UPPER, что приводит к невозможности выполнить операции вставки и обновления с использованием полученного представления (удаление будет работать).
Обойти ограничение на обновление можно следующим образом: в представлении нужно выбирать как «нетронутое» исходное поле, так и его обработанную копию. Этим мы частично нарушим условие задачи, по которому представление должно возвращать только два поля, одноимённые полям исходной таблицы, но зато получим возможность выполнять обновление:
MySQL Решение 3.2.1.a (представление, допускающее удаление и обновление данных)
1CREATE VIEW `subscribers_upper_case_trick`
2AS
3SELECT `s_id`,
4`s_name`,
5UPPER(`s_name`) AS `s_name_upper`
6FROM `subscribers`
Теперь у нас уже работают и удаление, и обновление. Вы можете выполнить следующие запросы, чтобы проверить данное утверждение.
MySQL Решение 3.2.1.a (проверка работы обновления и удаления)
1 |
|
UPDATE |
`subscribers_upper_case_trick` |
|
2 |
|
SET |
`s_name` |
= 'Сидоров А.А.' |
3 |
|
WHERE |
`s_id` = |
4; |
4 |
|
|
|
|
5 |
|
UPDATE |
`subscribers_upper_case_trick` |
|
6 |
|
SET |
`s_id` = |
10 |
7 |
|
WHERE |
`s_id` = |
4; |
8 |
|
|
|
|
9DELETE FROM `subscribers_upper_case`
10WHERE `s_id` = 10;
Ксожалению, реализовать вставку через такое представление не получится: чтобы вставка работала, представление не должно несколько раз ссылаться на одно и то же поле исходной таблицы.
Таким образом, для MySQL поставленная задача решается лишь частично.
В MS SQL Server тоже есть серия ограничений12, налагаемых на представления, с помощью которых планируется модифицировать данные. Однако (в отличие от MySQL) MS SQL Server допускает создание на представлениях триггеров, с помощью которых мы можем решить поставленную задачу.
11http://dev.mysql.com/doc/refman/5.6/en/view-updatability.html
12https://msdn.microsoft.com/en-us/library/ms187956.aspx
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 246/545
Пример 29: модификация данных с использованием «прозрачных» представлений
MS SQL Решение 3.2.1.a (создание представления)
1CREATE VIEW [subscribers_upper_case]
2WITH SCHEMABINDING
3AS
4SELECT [s_id],
5UPPER([s_name]) AS [s_name]
6FROM [dbo].[subscribers]
Такое представление уже позволяет извлекать данные в указанном в условии задачи формате, а также выполнять удаление данных. Для того, чтобы через это представление можно было выполнять вставку и обновление данных, нужно создать на нём два триггера — на операциях вставки и обновления.
MS SQL Решение 3.2.1.a (создание триггера для реализации операции вставки)
1CREATE TRIGGER [subscribers_upper_case_ins]
2ON [subscribers_upper_case]
3INSTEAD OF INSERT
4AS
5SET IDENTITY_INSERT [subscribers] ON;
6INSERT INTO [subscribers]
7 |
|
([s_id], |
8 |
|
[s_name]) |
9 |
|
SELECT ( CASE |
10 |
|
WHEN [s_id] IS NULL |
11 |
|
OR [s_id] = 0 THEN IDENT_CURRENT('subscribers') |
12 |
|
+ IDENT_INCR('subscribers') |
13 |
|
+ ROW_NUMBER() OVER (ORDER BY |
14 |
|
(SELECT 1)) |
15 |
|
- 1 |
16 |
|
ELSE [s_id] |
17 |
|
END ) AS [s_id], |
18 |
|
[s_name] |
19 |
|
FROM [inserted]; |
|
|
|
20SET IDENTITY_INSERT [subscribers] OFF;
21GO
Такой триггер выполняется вместо (INSTEAD OF) операции вставки данных в представление и внутри себя выполняет вставку данных в таблицу, на которой построено представление.
Некоторая сложность обусловлена тем, что мы хотим разрешить вставку как только одного поля (имени читателя), так и обоих полей (идентификатора читателя и имени читателя), и мы не знаем заранее, будет ли при операции вставки передано значение идентификатора s_id.
В строках 5 и 20 мы соответственно разрешаем и снова запрещаем явную вставку данных в поле s_id (оно является IDENTITY-полем для таблицы subscribers).
В строках 9-17 мы проверяем, получили ли мы явно указанное значение s_id. Если явно указанное значение не было передано, поле s_id псевдотаблицы inserted может принять значение NULL или 0 — в таком случае мы вычисляем новое значение поля s_id для таблицы subscribers на основе функций, возвращающих текущее значение IDENTITY-поля (IDENT_CURRENT) и шаг его инкремента (IDENT_INCR), а также номера строки из таблицы inserted. Иными словами, формула вычисления нового значения IDENTITY-поля такова: текущее_значение +
шаг_инкремента + номер_вставляемой_строки - 1.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 247/545
Пример 29: модификация данных с использованием «прозрачных» представлений
Теперь одинаково корректно будет выполняться каждый из следующих запросов на вставку данных:
MS SQL 
Решение 3.2.1.a (проверка работоспособности вставки)
1 |
INSERT INTO [subscribers_upper_case] |
|
2 |
|
([s_name]) |
3 |
VALUES |
(N'Орлов О.О.'); |
4 |
|
|
5 |
INSERT INTO [subscribers_upper_case] |
|
6 |
|
([s_name]) |
7 |
VALUES |
(N'Соколов С.С.'), |
8 |
|
(N'Беркутов Б.Б.'); |
9 |
|
|
10 |
INSERT INTO [subscribers_upper_case] |
|
11 |
|
([s_id], |
12 |
|
[s_name]) |
13 |
VALUES |
(30, |
14 |
|
N'Ястребов Я.Я.'); |
15 |
|
|
16 |
INSERT INTO [subscribers_upper_case] |
|
17 |
|
([s_id], |
18 |
|
[s_name]) |
19 |
VALUES |
(31, |
20 |
|
N'Синицын С.С.'), |
21 |
|
(32, |
22 |
|
N'Воронов В.В.'); |
|
|
|
Переходим к реализации обновления данных.
MS SQL Решение 3.2.1.a (создание триггера для реализации операции обновления)
1CREATE TRIGGER [subscribers_upper_case_upd]
2ON [subscribers_upper_case]
3INSTEAD OF UPDATE
4AS
5IF UPDATE([s_id])
6BEGIN
7 |
|
RAISERROR ('UPDATE |
of Primary Key through |
8 |
|
[subscribers_upper_case_upd] |
|
9 |
|
view is |
prohibited.', 16, 1); |
10ROLLBACK;
11END
12ELSE
13UPDATE [subscribers]
14 |
|
SET |
[subscribers].[s_name] |
= [inserted].[s_name] |
15 |
|
FROM |
[subscribers] |
|
16 |
|
JOIN |
[inserted] |
|
17 |
|
ON |
[subscribers].[s_id] = |
[inserted].[s_id]; |
18 |
|
GO |
|
|
|
|
|
|
|
При выполнении обновления данных «старые» строки копируются в псевдотаблицу deleted, а «новые» в псевдотаблицу inserted, но существует непреодолимая проблема: не существует никакого способа гарантированно определить взаимное соответствие строк в этих двух псевдотаблицах, т.е. в случае изменения значения первичного ключа, мы не сможем определить его новое значение.
Потому в строках 5-11 кода триггера мы проверяем, была ли попытка обновить значение первичного ключа, с помощью функции UPDATE (да, здесь это не оператор вставки, а функция). Если такая попытка была, мы запрещаем выполнение операции и откатываем транзакцию. Если же первичный ключ не был затронут обновлением, мы можем легко определить новое значение имени читателя и использовать его для настоящего обновления данных в таблице subscribers
(строки 12-17).
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 248/545
Пример 29: модификация данных с использованием «прозрачных» представлений
Теперь следующие запросы выполняются корректно (к слову, удаление и так не требовало никаких доработок, но проверить всё же стоит):
MS SQL Решение 3.2.1.a (проверка работоспособности обновления и удаления)
1 |
|
UPDATE |
[subscribers_upper_case] |
2 |
|
SET |
[s_name] = N'Новое имя' |
3 |
|
WHERE |
[s_id] = 30; |
4 |
|
|
|
5 |
|
UPDATE |
[subscribers_upper_case] |
6 |
|
SET |
[s_name] = N'И ещё одно имя' |
7 |
|
WHERE |
[s_id] >= 31; |
8 |
|
|
|
9 |
|
DELETE |
FROM [subscribers_upper_case] |
10 |
|
||
|
WHERE |
[s_id] = 30; |
|
11 |
|
|
|
|
|
|
|
|
|
|
|
12DELETE FROM [subscribers_upper_case]
13WHERE [s_id] >= 31;
Попытка обновить значение первичного ключа закономерно приведёт к блокировке операции:
|
MS SQL |
|
Решение 3.2.1.a (проверка невозможности обновления значения первичного ключа) |
||
|
1 |
|
UPDATE [subscribers_upper_case] |
||
|
2 |
|
SET |
[s_id] = 50 |
|
3WHERE [s_id] = 1;
Врезультате выполнения такого запроса будет получено следующее сообщение об ошибке:
Msg 50000, Level 16, State 1, |
Procedure subscribers_upper_case_upd, Line 7 |
||||
UPDATE of |
Primary Key |
through |
[subscribers_upper_case_upd] view is prohibited. |
||
Msg |
3609, |
Level |
16, State 1, |
Line 1 |
|
The |
transaction |
ended |
in the |
trigger. The batch has been aborted. |
|
На этом решение данной задачи для MS SQL Server завершено, и мы переходим к рассмотрению решения для Oracle.
Создадим представление.
Oracle Решение 3.2.1.a (создание представления)
1CREATE VIEW "subscribers_upper_case"
2AS
3SELECT "s_id",
4UPPER("s_name") AS "s_name"
5FROM "subscribers"
Через это представление уже можно извлекать данные в требуемом формате и удалять данные.
Важно: если поиск записей для удаления происходит по полю s_name, необходимо передавать искомые данные в верхнем регистре. Эта особенность касается только Oracle, т.к. MySQL и MS SQL Server не налагают подобного ограничения.
Для того, чтобы через это представление можно было выполнять вставку и обновление данных, нужно создать на нём два триггера — на операциях вставки и обновления.
Поскольку (в отличие от MS SQL Server) Oracle поддерживает триггеры уровня отдельных записей, их код получается очень простым, а реализация позволяет обойти имеющееся в MS SQL Server ограничение на обновление первичного ключа.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 249/545