Возможно ли выполнить изменения записи DML предложениями - INSERT, UPDATE, DELETE - через представление (view)?

#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