SQL 在 Oracle 中，是否可以通过视图插入或更新记录？

In Oracle, is it possible to INSERT or UPDATE a record through a view?

提问by Bastien Vandamme

In Oracle, is it possible to INSERT or UPDATE a record (a row) through a view?

在 Oracle 中，是否可以通过视图插入或更新记录（一行）？

回答by DCookie

Views in Oracle maybe updateable under specific conditions. It can be tricky, and usuallyis not advisable.

Oracle 中的视图在特定条件下可能是可更新的。这可能很棘手，通常是不可取的。

From the Oracle 10g SQL Reference:

来自Oracle 10g SQL 参考：

Notes on 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.

可更新视图是一种可用于插入、更新或删除基表行的视图。您可以创建一个本质上可更新的视图，或者您可以在任何视图上创建一个 INSTEAD OF 触发器以使其可更新。

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. For a view to be inherently updatable, the following conditions must be met:

要了解是否可以以及以何种方式可以修改固有可更新视图的列，请查询 USER_UPDATABLE_COLUMNS 数据字典视图。此视图显示的信息仅对固有可更新视图有意义。对于本质上可更新的视图，必须满足以下条件：

• 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.
• 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

• 视图中的每一列都必须映射到单个表的一列。例如，如果视图列映射到 TABLE 子句（非嵌套集合）的输出，则视图本身不可更新。
• 视图不得包含以下任何结构：
  • 集合运算符
  • DISTINCT 运算符
  • 聚合或分析函数
  • GROUP BY、ORDER BY、MODEL、CONNECT BY 或 START WITH 子句
  • SELECT 列表中的集合表达式
  • SELECT 列表中的子查询
  • 指定为 WITH READ ONLY 的子查询
  • 加入，但有一些例外，如 Oracle 数据库管理员指南中所述

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.

此外，如果本质上可更新的视图包含伪列或表达式，则不能使用引用这些伪列或表达式中的任何一个的 UPDATE 语句更新基表行。

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

如果您希望联接视图可更新，则必须满足以下所有条件：

• The DML statement must affect only one table underlying the join.
• 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.
• For an UPDATE statement, all columns updated must be extracted from a key-preserved table. If the view was created WITH CHECK OPTION, then join columns and columns taken from tables that are referenced more than once in the view must be shielded from UPDATE.
• 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.

• DML 语句必须仅影响连接基础的一个表。
• 对于 INSERT 语句，不能使用 WITH CHECK OPTION 创建视图，并且所有插入值的列必须来自保留键的表。键保留表是指基表中的每个主键或唯一键值在连接视图中也是唯一的。
• 对于 UPDATE 语句，必须从保留键的表中提取所有更新的列。如果视图是使用 WITH CHECK OPTION 创建的，则连接列和取自在视图中多次引用的表中的列必须屏蔽 UPDATE。
• 对于 DELETE 语句，如果连接产生多个保留键的表，则 Oracle 数据库将从 FROM 子句中命名的第一个表中删除，无论视图是否是用 WITH CHECK OPTION 创建的。

回答by WW.

Oracle has two different ways of making views updatable:-

Oracle 有两种不同的方法来使视图可更新：-

1. The view is "key preserved" with respect to what you are trying to update. This means the primary key of the underlying table is in the view and the row appears only once in the view. This means Oracle can figure out exactly which underlying table row to update OR
2. You write an instead of trigger.

1. 对于您要更新的内容，该视图是“关键保留的”。这意味着基础表的主键在视图中，并且该行在视图中只出现一次。这意味着 Oracle 可以准确找出要更新的底层表行 OR
2. 你写了一个而不是触发器。

I would stay away from instead-of triggers and get your code to update the underlying tables directly rather than through the view.

我会远离代替触发器，让你的代码直接更新底层表，而不是通过视图。

回答by LBushkin

There are two times when you can update a record through a view:

有两种情况可以通过视图更新记录：

1. If the view has no joins or procedure calls and selects data from a single underlying table.
2. If the view has an INSTEAD OF INSERT triggerassociated with the view.

1. 如果视图没有联接或过程调用并从单个基础表中选择数据。
2. 如果视图具有与视图关联的INSTEAD OF INSERT 触发器。

Generally, you should not rely on being able to perform an insert to a view unless you have specifically written an INSTEAD OF trigger for it. Be aware, there are also INSTEAD OF UPDATE triggers that can be written as well to help perform updates.

通常，除非您专门为视图编写了 INSTEAD OF 触发器，否则不应依赖于能够对视图执行插入操作。请注意，还可以编写 INSTEAD OF UPDATE 触发器来帮助执行更新。

回答by Radi Soufan

YES, you can Update and Insert into view and that edit will be reflected on the original table....
BUT
1-the view should have all the NOT NULLvalues on the table
2-the update should have the same rules as table... "updating primary key related to other foreign key.. etc"...

是的，您可以更新并插入到视图中，并且该编辑将反映在原始表上......
但是
1-视图应该具有表 2上的所有NOT NULL值
-更新应该具有与 table 相同的规则。 ..“更新与其他外键相关的主键......等”......