允許進行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。

更多詳情見請繼續閱讀下一頁的精彩內容：

1 2 下一頁