Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 29: модификация данных с использованием «прозрачных» представлений
На этом решение данной задачи для MS SQL Server завершено, и мы переходим к рассмотрению решения для Oracle.
Oracle Решение 3.2.1.b (создание представления)
1CREATE VIEW "subscriptions_wcd"
2AS
3SELECT "sb_id",
4"sb_subscriber",
5"sb_book",
6TO_CHAR("sb_start", 'YYYY-MM-DD') || ' - ' ||
7TO_CHAR("sb_finish", 'YYYY-MM-DD') AS "sb_dates",
8"sb_is_active"
9FROM "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; |
|
|
|
|
|
Oracle Решение 3.2.1.b (создание триггера для реализации операции обновления)
1CREATE OR REPLACE TRIGGER "subscriptions_wcd_ins"
2INSTEAD OF UPDATE ON "subscriptions_wcd"
3FOR EACH ROW
4BEGIN
5UPDATE "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" |
16WHERE "sb_id" = :old."sb_id";
17END;
Благодаря поддержке триггеров уровня отдельных записей, решение этой задачи в Oracle оказывается достаточно простым: код обоих триггеров сводится к выполнению запроса на вставку или обновление к таблице, на которой построено представление. Как и в MySQL мы можем использовать в Oracle ключевые слова old и new для обращения к «старым» (удаляемым или обновляемым) и новым (добавляемым или обновлённым) данным.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 255/545
Пример 29: модификация данных с использованием «прозрачных» представлений
Значения полей sb_start и sb_finish (как и в решении для MS SQL Server) в обоих триггерах приходится вычислять с помощью строковых функций, но в Oracle их синтаксис немного проще, и решение получается короче.
Теперь все следующие запросы работают корректно:
Oracle Решение 3.2.1.b (проверка работоспособности модификации данных)
1 |
|
INSERT INTO "subscriptions_wcd" |
|
2 |
|
|
("sb_subscriber", |
3 |
|
|
"sb_book", |
4 |
|
|
"sb_dates", |
5 |
|
|
"sb_is_active") |
6 |
|
VALUES |
(1, |
7 |
|
|
3, |
8 |
|
|
'2019-01-12 - 2019-02-12', |
9 |
|
|
'N'); |
10 |
|
|
|
11 |
|
UPDATE |
"subscriptions_wcd" |
12 |
|
SET |
"sb_dates" = '2019-01-12 - 2019-02-12' |
13 |
|
WHERE |
"sb_id" = 100; |
14 |
|
|
|
15DELETE FROM "subscriptions_wcd"
16WHERE "sb_id" = 100;
17
18DELETE FROM "subscriptions_wcd"
19WHERE "sb_dates" = '2012-05-17 - 2012-07-17';
Важно! Oracle допускает вставку данных через представление только с использованием синтаксиса вида
INSERT INTO ... (...) VALUES (...)
но не вида
INSERT ALL
INTO ... (...) VALUES (…)
INTO ... (...) VALUES (…)
SELECT 1 FROM "DUAL"
При попытке использовать второй вариант вы получите сообщение об ошибке «ORA-01702: a view is not appropriate here».
Задание 3.2.1.TSK.A: создать представление, извлекающее информацию о книгах, переводя весь текст в верхний регистр и при этом допускающее модификацию списка книг.
Задание 3.2.1.TSK.B: создать представление, извлекающее информацию о датах выдачи и возврата книг и состоянии выдачи книги в виде единой строки в формате «ГГГГ-ММ-ДД - ГГГГ-ММ-ДД - Возвращена» и при этом допускающее обновление информации в таблице subscriptions.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 256/545
Пример 30: модификация данных с использованием триггеров на представлениях
3.2.2.Пример 30: модификация данных с использованием триггеров на представлениях
Поскольку MySQL не позволяет создавать триггеры на представлениях, все задачи этого примера имеют полноценные решения только для MS SQL Server и Oracle.
Задача 3.2.2.a{258}: создать представление, извлекающее из таблицы subscriptions человекочитаемую (с именами читателей и названиями книг вместо идентификаторов) информацию, и при этом позволяющее модифицировать данные в таблице subscriptions.
Задача 3.2.2.b{270}: создать представление, показывающее список книг с относящимися к этим книгам жанрами, и при этом позволяющее добавлять новые жанры.
Ожидаемый результат 3.2.2.a.
Выполнение запроса вида SELECT * FROM {представление} позволяет получить представленный ниже результат, и при этом выполненные с представлением операции INSERT, UPDATE, DELETE модифицируют соответствующие данные в исходной таблице.
sb_id |
sb_subscriber |
sb_book |
sb_start |
sb_finish |
sb_is_active |
2 |
Иванов И.И. |
Евгений Онегин |
2011-01-12 |
2011-02-12 |
N |
3 |
Сидоров С.С. |
Основание и империя |
2012-05-17 |
2012-07-17 |
Y |
42 |
Иванов И.И. |
Сказка о рыбаке и рыбке |
2012-06-11 |
2012-08-11 |
N |
57 |
Сидоров С.С. |
Язык программирования С++ |
2012-06-11 |
2012-08-11 |
N |
61 |
Иванов И.И. |
Искусство программирования |
2014-08-03 |
2014-10-03 |
N |
62 |
Сидоров С.С. |
Язык программирования С++ |
2014-08-03 |
2014-10-03 |
Y |
86 |
Сидоров С.С. |
Евгений Онегин |
2014-08-03 |
2014-09-03 |
Y |
91 |
Сидоров С.С. |
Евгений Онегин |
2015-10-07 |
2015-03-07 |
Y |
95 |
Иванов И.И. |
Психология программирования |
2015-10-07 |
2015-11-07 |
N |
99 |
Сидоров С.С. |
Психология программирования |
2015-10-08 |
2025-11-08 |
Y |
100 |
Иванов И.И. |
Основание и империя |
2011-01-12 |
2011-02-12 |
N |
Ожидаемый результат 3.2.2.b.
Выполнение запроса вида SELECT * FROM {представление} позволяет получить представленный ниже результат, и при этом выполненная с представлением операция INSERT приводит к добавлению нового жанра в таблицу genres.
b_id |
b_name |
genres |
1 |
Евгений Онегин |
Классика,Поэзия |
2 |
Сказка о рыбаке и рыбке |
Классика,Поэзия |
3 |
Основание и империя |
Фантастика |
4 |
Психология программирования |
Программирование,Психология |
5 |
Язык программирования С++ |
Программирование |
6 |
Курс теоретической физики |
Классика |
7 |
Искусство программирования |
Программирование,Классика |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 257/545
Пример 30: модификация данных с использованием триггеров на представлениях
Решение 3.2.2.a{257}.
К сожалению, для MySQL эта задача не имеет решения, т.к. MySQL не позволяет создавать триггеры на представлениях. Максимум, что мы можем сделать, это создать само представление, но данные через него модифицировать не получится:
MySQL Решение 3.2.2.a (создание представления)
1CREATE VIEW `subscriptions_with_text`
2AS
3SELECT `sb_id`,
4`s_name` AS `sb_subscriber`,
5`b_name` AS `sb_book`,
6`sb_start`,
7`sb_finish`,
8`sb_is_active`
9 FROM `subscriptions`
10JOIN `subscribers` ON `sb_subscriber` = `s_id`
11JOIN `books` ON `sb_book` = `b_id`
В MS SQL Server код самого представления идентичен:
MS SQL Решение 3.2.2.a (создание представления)
1CREATE VIEW [subscriptions_with_text]
2WITH SCHEMABINDING
3AS
4SELECT [sb_id],
5[s_name] AS [sb_subscriber],
6[b_name] AS [sb_book],
7[sb_start],
8[sb_finish],
9[sb_is_active]
10FROM [dbo].[subscriptions]
11JOIN [dbo].[subscribers] ON [sb_subscriber] = [s_id]
12JOIN [dbo].[books] ON [sb_book] = [b_id]
Создадим триггер, позволяющий реализовать операцию вставки данных. Поскольку исходная таблица subscriptions содержит в полях sb_sub-
scriber и sb_book числовые идентификаторы читателя и книги, мы обязаны получать вставляемые значения этих полей в числовом виде.
Однако попытка «на лету» получить идентификаторы читателя или книги на основе их имени или названия не может быть реализована потому, что как имена читателей, так и названия книг могут дублироваться, и мы не можем гарантировать получение корректного значения.
Чтобы минимизировать вероятность неверного использования полученного триггера, в его строках 5-14 происходит проверка того, не переданы ли во время вставки нечисловые значения в поля sb_subscriber или sb_book. Если такая ситуация возникла, выводится сообщение об ошибке и транзакция отменяется.
В остальном весь код триггера предельно похож на рассмотренный в решении{246} задачи 3.2.1.a{245} (там же подробно описана логика вычисления нового значения первичного ключа, если таковое не передано явно в запросе на вставку данных — строки 25-33 представленного ниже триггера).
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 258/545
Пример 30: модификация данных с использованием триггеров на представлениях
MS SQL Решение 3.2.2.a (создание триггера для реализации операции вставки)
1CREATE TRIGGER [subscriptions_with_text_ins]
2ON [subscriptions_with_text]
3INSTEAD OF INSERT
4AS
5IF EXISTS(SELECT 1
6 |
|
FROM |
[inserted] |
7 |
|
WHERE |
PATINDEX('%[^0-9]%', [sb_subscriber]) > 0 |
8 |
|
OR |
PATINDEX('%[^0-9]%', [sb_book]) > 0) |
|
|
|
|
9BEGIN
10RAISERROR ('Use digital identifiers for [sb_subscriber]
11 |
|
and [sb_book]. Do not use subscribers'' names |
12 |
|
or books'' titles', 16, 1); |
13ROLLBACK;
14END
15ELSE
16BEGIN
17SET IDENTITY_INSERT [subscriptions] ON;
18INSERT INTO [subscriptions]
19 |
|
|
([sb_id], |
20 |
|
|
[sb_subscriber], |
21 |
|
|
[sb_book], |
22 |
|
|
[sb_start], |
23 |
|
|
[sb_finish], |
24 |
|
|
[sb_is_active]) |
25 |
|
SELECT ( CASE |
|
26 |
|
|
WHEN [sb_id] IS NULL |
27 |
|
|
OR [sb_id] = 0 THEN IDENT_CURRENT('subscriptions') |
28 |
|
|
+ IDENT_INCR('subscriptions') |
29 |
|
|
+ ROW_NUMBER() OVER (ORDER BY |
30 |
|
|
(SELECT 1)) |
31 |
|
|
- 1 |
32 |
|
|
ELSE [sb_id] |
33 |
|
|
END ) AS [sb_id], |
34 |
|
|
[sb_subscriber], |
35 |
|
|
[sb_book], |
36 |
|
|
[sb_start], |
37 |
|
|
[sb_finish], |
38 |
|
|
[sb_is_active] |
39 |
|
FROM |
[inserted]; |
|
|
|
|
40SET IDENTITY_INSERT [subscriptions] OFF;
41END
42GO
Проверим, как выполняются следующие запросы на вставку. Вполне ожидаемо запросы 1-2 выполняются корректно, а запросы 3-5 приводят к срабатыванию кода в строках 5-14 триггера и отмене транзакции, т.к. передача нечисловых значений для полей sb_subscriber и/или sb_book запрещена.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 259/545