#sql #oracle #plsql #view
Возможно ли выполнить изменения записи DML выражениями - INSERT, UPDATE, DELETE через
представление (view)?
Ответы
Ответ 1
Представления в БД могут быть изменяемыми (updateable views) при определённых условиях.
Выдержка из Oracle SQL Reference:
Замечания для изменяемых представлений
Изменяемое представление значит, что оно может быть использовано для изменения записей
в его базовых таблицах. Можно создать представление, которое в принципе изменяемо (т.е.
само по себе изменяемо), или также возможно создать для любого представления INSTEAD
OF триггер, чтобы сделать его изменяемым.
Узнать, какие и каким образом колонки в принципе изменяемого (т.е изменяемого без
триггера) представления могут быть изменены, можно посмотреть через представлние USER_UPDATABLE_COLUMNS.
Для того, чтобы представление могло быть изменено, все следующие условия должны выполнятся:
Каждая колонка представления должна представлять колонку одной таблицы. Например,
если колонка представляет вывод TABLE оператора, то это условие не выполняется.
Представление не должно содержать одну из следующих конструкций:
SET оператор
DISTINCT оператор
Агрегатную или аналитическую функцию
GROUP BY, ORDER BY, MODEL, CONNECT BY, или START WITH выражения
Выражение для коллекций в листе SELECT
Подзапрос в листе SELECT
Подзапрос с WITH READ ONLY
Соединения (joins), с некоторыми исключениями указаными в документе Oracle Database
Administrator's Guide
Дополненительно, если в принципе изменяемое представление содержит псевдо колонки
или выражения, то нельзя изменить записи таблицы с UPDATE предложением, которое обращается
к этим псевдоколонкам или выражениям.
Если надо сделать изменяемым представление содержащее соединение таблиц, то все следующие
условия должны быть соблюдены:
DML предложение должно затрагивавть только одну таблицу соединения
Для INSERT предложения, представление не должно быть создано с WITH CHECK OPTION,
и все колонки, в которые вставляются значения, должны происходить из таблицы с сохранёнными
ключами. Таблица с сохранёнными ключами (key-preserved table), это базовая таблица,
в которой каждое значение первичного или уникального ключа сохранит свою уникальность
также в представлении после соединения.
Для UPDATE предложения, представление не должно быть создано с WITH CHECK OPTION,
и все колонки, которые подлежат изменению, должны происходить из таблицы с сохранёнными
ключами.
Для DELETE предложения, если в результате соединения более чем одна таблица будет
с сохранёнными ключами, то удаление будет из первой таблицы указанной в FROM выражении,
независимо от того, было ли создано представление с WITH CHECK OPTION или без него.
Источник ответа @DCookie. При переводе сверенно с офф. документацией актуального
релиза 19c
Ответ 2
Если в созданном представлении существуют ограничения описанные в ранее данном ответе
и оно в принципе не изменяемо, то можно сделать его изменяемым через INSTEAD OF триггер.
Простейший случай - изменения представления с 1:N (one-to-many) связью:
create table items (id number primary key, item varchar2 (64));
create table parts (
id number primary key, itemid number, part varchar2 (64),
constraint fk_part foreign key (itemid) references items (id)
);
create or replace view itemparts as
select i.id itemid, i.item, p.part
from items i
join parts p on p.itemid = i.id;
Попытки изменить таблицу items:
insert into itemparts (itemid, item) values (1, 'item 1');
update itemparts set
item = item||'*'
where itemid = 1;
закончатся безуспешно потому, что первичный ключ items.id в преставлении не сохранил
свою уникальность:
ORA-01779: cannot modify a column which maps to a non key-preserved table
Cause: An attempt was made to insert or update columns of a join view which
map to a non-key-preserved table.
Action: Modify the underlying base tables directly.
Сделаем представление изменяемым через триггер:
create or replace trigger trig_itemparts
instead of insert or update or delete on itemparts
declare dummypartid constant number := 1e20;
begin
--dbms_output.put_line ('trig_mail_address_book: '||:new.pk_serial_no||'|'||:new.address_a||'|'||:new.address_b);
if inserting then -- the same for updating, deleting
insert into items values (:new.itemid, :new.item);
insert into parts values (dummypartid, :new.itemid, 'dummy part');
elsif updating then
update items set item = :new.item
where id = :new.itemid;
end if;
end;
/
Повторим попытки изменения (см. выше) и результат на лицо:
select * from itemparts
order by itemid;
ITEMID ITEM PART
---------- ---------- ----------
1 item 1* dummy part
