Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 29: модификация данных с использованием «прозрачных» представлений
Значения полей sb_start и sb_finish (как и в решении для MS SQL Server) в обоих триггерах приходится вычислять с помощью строковых функций, но в Oracle их синтаксис немного проще, и решение получается короче.
Теперь все следующие запросы работают корректно:
Oracle I Решение 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 |
|
|
15 |
DELETE FROM "subscriptions wcd" |
|
16 |
WHERE |
"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 Стр: 270/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 Стр: 271/545
Пример 30: модификация данных с использованием триггеров на представлениях
Решение 3.2.2.a{257}.
К сожалению, для MySQL эта задача не имеет решения, т.к. MySQL не позволяет создавать триггеры на представлениях. Максимум, что мы можем сделать, это создать само представление, но данные через него модифицировать не получится:
MySQL I Решение 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',
'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 I Решение 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 Стр: 272/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('%[A0-9]%', [sb subscriber]) > 0 |
8 |
OR PATINDEX('%[A0-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 Стр: 273/545
Пример 30: модификация данных с использованием триггеров на представлениях
MS SQL і Решение 3.2.2.a (проверка работоспособности операции вставки)
1-- Запрос 1 (вставка выполняется) :
2INSERT INTO [subscriptions with text]
3 |
|
([sb id], |
4 |
|
[sb subscriber], |
5 |
|
[sb book], |
6 |
|
[sb start] |
7 |
|
[sb finish], |
8 |
|
[sb is active] |
9 |
VALUES |
(5000, |
10 |
|
1, |
11 |
|
3, |
12 |
|
'2015-01-12', |
13 |
|
'2015-02-12', |
14 |
|
'N'), |
15 |
|
(5005, |
16 |
|
1, |
17 |
|
1, |
18 |
|
'2015-01-12', |
19 |
|
'2015-02-12', |
20 |
|
'N') ; |
21 |
|
|
22-- Запрос 2 (вставка выполняется):
23INSERT INTO [subscriptions with text]
24 |
|
([sb subscriber], |
25 |
|
[sb book], |
26 |
|
[sb start] |
27 |
|
[sb finish], |
28 |
|
[sb is active] |
29 |
VALUES |
(1, |
30 |
|
3, |
31 |
|
'2015-01-12', |
32 |
|
'2015-02-12', |
33 |
|
'N'), |
34 |
|
(1, |
35 |
|
1, |
36 |
|
'2015-01-12', |
37 |
|
'2015-02-12', |
38 |
|
'N') ; |
39 |
|
|
40-- Запрос 3 (вставка НЕ выполняется):
41INSERT INTO [subscriptions with text]
42 |
|
([sb subscriber], |
43 |
|
[sb book], |
44 |
|
[sb start] |
45 |
|
[sb finish], |
46 |
|
[sb is active] |
47 |
VALUES |
(N'Иванов И.И.', |
48 |
|
3, |
49 |
|
'2015-01-12', |
50 |
|
'2015-02-12', |
51 |
|
'N'); |
52 |
|
|
53-- Запрос 4 (вставка НЕ выполняется):
54INSERT INTO [subscriptions with text]
55 |
|
([sb subscriber], |
56 |
|
[sb book], |
57 |
|
[sb start] |
58 |
|
[sb finish], |
59 |
|
[sb is active] |
60 |
VALUES |
(1, |
61 |
|
N'Какая-то книга', |
62 |
|
'2015-01-12', |
63 |
|
'2015-02-12', |
64 |
|
'N'); |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 274/545