Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 29: модификация данных с использованием «прозрачных» представлений
MS SQL Решение 3.2.1 .а (создание представления)
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-ПОЛЄМ для таблицы sub-
scribers).
В строках 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 Стр: 260/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 ; |
|
10 |
ROLLBACK; |
|
|
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 Стр: 261/545
Пример 29: модификация данных с использованием «прозрачных» представлений
Теперь следующие запросы выполняются корректно (к слову, удаление и так не требовало никаких доработок, но проверить всё же стоит):
MS SQL Решение 3.2.1 .a |
обновления и удаления) |
||||
1 |
|
UPDATE [subscribers_upper_case] |
|
|
|
2 |
|
SET |
[s name] = N'Новое имя' |
|
|
3 |
4 |
WHERE |
[s_id] = 30; |
|
|
|
|
|
|
||
5 |
|
|
|
|
|
|
|
UPDATE [subscribers_upper_case] |
|
|
|
6 |
|
SET |
[s name] = N'H ещё одно имя' |
||
7 |
8 |
WHERE |
[s_id] >= 31 |
|
|
|
|
|
|
||
9 |
|
DELETE FROM [subscribers upper case] |
|||
|
|
||||
10 |
|
WHERE |
[s_id] = 30; |
|
|
11 |
|
|
|
|
|
12 |
|
DELETE FROM [subscribers_upper_case] |
|||
13 |
|
WHERE |
[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 .а (создание
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 Стр: 262/545
Пример 29: модификация данных с использованием «прозрачных» представлений
ключа.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 263/545
Пример 29: модификация данных с использованием «прозрачных» представлений
Oracl |
і Решение 3.2.1.a (создание триггера для реализации операции вставки) |
| |
||
e |
||||
|
|
|
||
1 |
CREATE OR REPLACE TRIGGER "subscribers upper case ins" |
|||
2 |
INSTEAD OF INSERT ON "subscribers upper case" |
|
||
3 |
FOR EACH ROW |
|
|
|
4 |
BEGIN |
|
|
|
5 |
INSERT INTO "subscribers" |
|
||
6 |
|
"s id" |
|
|
7 |
|
"s name") |
|
|
8 |
VALUES |
( new "s id" |
|
|
9 |
|
new "s name"); |
|
|
10 |
END; |
|
|
|
Oracle Решение 3.2.1.a (создание триггера для реализации операции обновления) |
|
|||
1CREATE OR REPLACE TRIGGER "subscribers upper case upd"
2INSTEAD OF UPDATE ON "subscribers upper case"
3FOR EACH ROW
4BEGIN
5UPDATE "subscribers"
6 |
SET |
"s |
id" = |
:new |
"s |
id" |
7 |
|
"s |
name" |
= new "s name" |
||
8 |
WHERE |
"s id" = :old |
"s |
id"; |
||
9 |
END; |
|
|
|
|
|
Код обоих триггеров сводится к выполнению запроса на вставку или обновление к таблице, на которой построено представление. Как и в MySQL, мы можем использовать в Oracle ключевые слова old и new для обращения к «старым» (удаляемым или обновляемым) и новым (добавляемым или обновлённым) данным.
Теперь следующие запросы выполняются корректно (к слову, удаление и так не требовало никаких доработок, но проверить всё же стоит):
Oracl |
і Решение 3.2.1.a (проверка работоспособности модификации данных) |
| |
|||
e |
|||||
|
|
|
|
||
1 |
INSERT ALL |
|
|
||
2 |
INTO "subscribers" ("s id" |
"s name") VALUES (1, Ы'Соколов С.С.') |
|||
3 |
INTO "subscribers" ("s id" |
"s name") VALUES (2, N'EepKyTOB Б.Б.') |
|||
4 |
INTO "subscribers" ("s id" |
"s name") VALUES (3, ^Филинов Ф.Ф.') |
|||
5 |
SELECT 1 FROM "DUAL" |
|
|
||
6 |
|
|
|
|
|
7 |
UPDATE "subscribers upper case" |
|
|||
8 |
SET |
"s name" = ^Синицын З.З.' |
|
||
9 |
WHERE |
"s id" = 6; |
|
|
|
10 |
|
|
|
|
|
11-- Такой запрос НЕ НАЙДЁТ искомое, т.к. имя должно быть в верхнем регистре:
12UPDATE "subscribers upper case"
13 |
SET |
"s name" = ^Синцын С.С.' |
14 |
WHERE |
"s name" = ^Синицын З.З.'; |
15 |
|
|
16-- А такой запрос найдёт искомое:
17UPDATE "subscribers upper case"
18 |
SET |
"s name" = ^Синцын С.С.' |
19 |
WHERE |
"s name" = ^СИНИЦЫН З.З.'; |
20 |
|
|
21DELETE FROM "subscribers upper case"
22WHERE "s id" = 6;
23
24-- Такой запрос НЕ НАЙДЁТ искомое, т.к. имя должно быть в верхнем регистре:
25DELETE FROM "subscribers upper case"
26WHERE "s name" = ^Филинов Ф.Ф.';
27
28-- А такой запрос найдёт искомое:
29DELETE FROM "subscribers upper case"
30WHERE "s name" = ^ФИЛИНОВ Ф.Ф.';
32DELETE FROM "subscribers upper case"
33WHERE "s__id" > 4;
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 264/545