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

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

视图可以屏蔽某些基表的信息，或是join多个基表组成一个复杂查询，视图本身也是可以进行DML操作，但受一些条件的限制。

Oracle DML流程

PL/SQL“ ORA-14551: 无法在查询中执行 DML 操作”解决

MySQL常用DDL、DML、DCL语言整理（附样例）

Oracle基本事务和ForAll执行批量DML练习

Oracle DML语句(insert,update,delete) 回滚开销估算

首先我们看下官方文档对视图进行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。

更多详情见请继续阅读下一页的精彩内容：