允许进行DML操作的视图条件

视图可以屏蔽某些基表的信息，或是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 INSTEAD OF 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 USER_UPDATABLE_COLUMNS 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 TABLE 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 DISTINCT operator

    An aggregate or analytic function

    A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause

    A collection expression in a SELECT list

    A subquery in a SELECT list

    A subquery designated WITH READ ONLY

    Joins, with some exceptions, as documented in Oracle Database Administrator's Guide

3. In addition, if an inherently updatable view contains pseudocolumns or expressions, then you cannot update base table rows with an UPDATE 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 INSERT statement, the view must not be created WITH CHECK OPTION, 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 UPDATE statement, the view must not be created WITH CHECK OPTION, and all columns updated must be extracted from a key-preserved table.

UPDATE语句，视图不能使用WITH CHECK OPTION创建，同样更新字段也必须来自于key-preserved表。

5. For a DELETE statement, if the join results in more than one key-preserved table, then Oracle Database deletes from the first table named in the FROM clause, whether or not the view was created WITH CHECK OPTION.

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”。

create view testv

as select d.deptid deptid, deptname, empid, empname, e.deptid edeptid

from employee e join dept d

on d.deptid = e.deptid; 或 create view testv

as select d.deptid deptid, deptname, empid, empname, e.deptid edeptid

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 select * from dept;   DEPTID   DEPTNAME ---------- --------------------          1      dept1          2      dept2          3      dept3 select * from employee;    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;                   * ERROR at line 1: ORA-01733: virtual column not allowed here

测试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 DCSOPEN TESTV DEPTNAME NO  NO  NO DCSOPEN TESTV EMPID YES YES YES DCSOPEN TESTV EMPNAME YES YES YES DCSOPEN TESTV EDEPTID YES YES YES

If you want a join view to be updatable, then all of the following conditions must be true: