当前位置：文江博客话题详情

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

发布于 2024-08-10 03:29:11 字数 38 浏览 7 评论 0原文

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

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

软糖 2024-08-17 03:29:11

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

来自 Oracle 10g SQL 参考

：可更新视图可

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

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

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

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

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

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

Views in Oracle may be updateable under specific conditions. It can be tricky, and usually is not advisable.

From the Oracle 10g SQL Reference:

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.

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:

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

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.

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.

回复收藏 0 原文