視圖可以屏蔽某些基表的資訊，或是join多個基表組成一個複雜查詢，視圖本身也是可以進行DML操作，但受一些條件的限制。
首先我們看下官方文檔對視圖進行DML操作的要求說明：
The following notes apply to updatable views:
An updatable view is one you can use to insert, update, or delete base table rows. You can create a view to be inherently updatable, or you can create an <code>INSTEAD OF</code> trigger on any
view to make it updatable.
這裡說明了兩種可updateable(包括增删改基表)視圖的方法：一是繼承基表的視圖，二是使用INSTEAD OF的觸發器來實作任意視圖的updatable。
To learn whether and in what ways the columns of an inherently updatable view can be modified, query the <code>USER_UPDATABLE_COLUMNS</code> data dictionary view. The information displayed by
this view is meaningful only for inherently updatable views.
USER_UPDATABLE_COLUMNS資料字典視圖可以找到視圖的哪些字段可以進行增加、更新和删除。
For a view to be inherently updatable, the following conditions must be met:
對于這種updatable繼承的視圖，需要滿足以下條件：
1. Each column in the view must map to a column of a single table. For example, if a view column maps to the output of a <code>TABLE</code> clause (an unnested collection), then the view is
not inherently updatable.
2. The view must not contain any of the following constructs:
A set operator
A <code>DISTINCT</code> operator
An aggregate or analytic function
A <code>GROUP</code> <code>BY</code>, <code>ORDER</code> <code>BY</code>, <code>MODEL</code>, <code>CONNECT</code> <code>BY</code>, or <code>START</code> <code>WITH</code> clause
A collection expression in a <code>SELECT</code> list
A subquery in a <code>SELECT</code> list
A subquery designated <code>WITH READ ONLY</code>
Joins, with some exceptions, as documented in
3.
In addition, if an inherently updatable view contains pseudocolumns or expressions, then you cannot update base table rows with an <code>UPDATE</code> statement
that refers to any of these pseudocolumns or expressions.
4. If you want a join view to be updatable, then all of the following conditions must be true:
對于一個join視圖，如果需要可updatable，那麼就需要滿足如下條件：
(1) The DML statement must affect only one table underlying the join.
DML必須僅影響一個join連接配接的表。
(2) For an <code>INSERT</code> statement, the view must not be created <code>WITH</code> <code>CHECK</code> <code>OPTION</code>,
and all columns into which values are inserted must come from a key-preserved table. A key-preserved table is one for which every primary key or
unique key value in the base table is also unique in the join view.
INSERT語句，不能使用WITH CHECK OPTION，并且所有待插入的列都來自于key-preserved表。
key-preserved表是指基表中每個主鍵或唯一鍵也必須是在join視圖中唯一。
(3) For an <code>UPDATE</code> statement, the view must not be created <code>WITH</code> <code>CHECK</code> <code>OPTION</code>,
and all columns updated must be extracted from a key-preserved table.
UPDATE語句，視圖不能使用WITH CHECK OPTION建立，同樣更新字段也必須來自于key-preserved表。
5. For a <code>DELETE</code> statement, if the join results in more than one key-preserved table, then Oracle
Database deletes from the first table named in the <code>FROM </code>clause, whether or not the view was created <code>WITH</code> <code>CHECK</code> <code>OPTION</code>.
DELETE語句，如果join結果有多個key-preserved表，Oracle隻會删除FROM子句中第一個表的記錄，不管視圖是否使用WITH CHECK OPTION。
下面通過一系列實驗來說明。
建立測試表：
create table dept(deptid int primary key, deptname varchar2(20));
create table employee(empid int primary key, empname varchar2(20), deptid int);
建立測試資料：
insert into dept values(1,‘dept1‘);
insert into dept values(2,‘dept2‘);
insert into dept values(3,‘dept3‘);
insert into employee values(1,‘emp1‘,1);
insert into employee values(2,‘emp2‘,1);
insert into employee values(3,‘emp3‘,2);
建立視圖：
create view testv
as select d.deptid deptid, deptname, empid, empname, e.deptid edeptid
from dept d join employee e
on d.deptid = e.deptid;
SQL> select * from testv;
DEPTID DEPTNAME EMPID EMPNAME EDEPTID
---------- -------------------- ---------- -------------------- ----------
1 dept1 1 emp1 1
1 dept1 2 emp2 1
2 dept2 3 emp3 2
僅employee表是key-preserved表。
測試1：對key-preserved表字段進行增加、更新的操作。
update testv set empname=‘empx‘ where edeptid=1;
update testv set empname=‘empx‘ where empid=1;
update testv set empname=‘empx‘ where deptid=1;
insert into testv(empid,empname,edeptid) values(4,‘emp4‘,2);
以上SQL可以執行，因為修改或添加的字段都是employee的，即key-preserved表。
測試2：驗證上述“DELETE語句，如果join結果有多個key-preserved表，Oracle隻會删除FROM子句中第一個表的記錄，不管視圖是否使用WITH CHECK OPTION”。
from employee e join dept d
或
on d.deptid = e.deptid
WITH CHECK OPTION;
select * from testv;
DEPTID DEPTNAME EMPID EMPNAME EDEPTID
1 dept1 1 emp1 1
1 dept1 2 emp2 1
2 dept2 3 emp3 2
delete from testv where deptid = 1;
2 rows deleted.
select * from dept;
DEPTID DEPTNAME
---------- --------------------
1 dept1
2 dept2
3 dept3
select * from employee;
EMPID EMPNAME DEPTID
---------- -------------------- ----------
3 emp3 2
delete from testv where empid = 1;
1 row deleted.
select * from testv;
DEPTID DEPTNAME EMPID EMPNAME EDEPTID
1 dept1 2 emp2 1
2 dept2 3 emp3 2
DEPTID DEPTNAME
EMPID EMPNAME DEPTID
2 emp2 1
3 emp3 2
測試3：對于INSERT和UPDATE語句，不能使用WITH CHECK OPTION建立視圖。
create view test1v
as select t1id ,t1v,t2id,t2v
from test1 join test2
on test1.t1id=test2.t2id
with check option;
insert into test1v(t1id,t1v) values(4,‘t4‘);
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
update test1v set t1id=4 where t1id=1;
*
測試4：非key-preserved表字段不能更新或插入。
update testv set deptname=‘deptx‘ where deptid=1
update testv set deptname=‘deptx‘ where empid=1
insert into testv(deptid,deptname) values(4,‘dept4‘)
ORA-01779: cannot modify a column which maps to a non key-preserved table
測試5：檢視視圖中哪些字段可以增删改。
select * from USER_UPDATABLE_COLUMNS where table_name=‘TESTV‘;
OWNER TABLE_NAME
COLUMN_NAME UPD INS DEL
-------------------------------------------------------------------------------------------
DCSOPEN TESTV
DEPTID NO NO NO
DEPTNAME NO NO NO
EMPID YES YES YES
EMPNAME YES YES YES
EDEPTID YES YES YES
If
you want a join view to be updatable, then all of the following conditions must be true:
![oracle中的start with connect by用法[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)