oracle 数据库中的视图是否可更新？

Is a view in the database updatable?

提问by Avadhesh

Can you update a view in a database? If so, how? If not, why not?

你能更新数据库中的视图吗？如果是这样，如何？如果没有，为什么不呢？

回答by Murph

The actual answer is "it depends", there are no absolutes.

实际的答案是“视情况而定”，没有绝对的。

The basic criteria is it has to be an updateable view in the opinion of the database engine, that is to say can the engine uniquely identify the row(s) to be updated and secondly are the fields updateable. If your view has a calculated field or represents the product of a parent/child join then the default answer is probably no.

基本标准是它必须是数据库引擎认为的可更新视图，也就是说引擎可以唯一标识要更新的行，其次是字段可更新。如果您的视图具有计算字段或代表父/子联接的产品，则默认答案可能是否定的。

However its also possible to cheat... in MS SQL Server and Oracle (to take just two examples) you can have triggers that fire when you attempt to insert or update a view such that you can make something that the server doesn't think updateable into something that is - usually because you have knowledge that the server can't easily infer from the schema.

然而，它也有可能作弊......在 MS SQL Server 和 Oracle（仅举两个例子）中，您可以在尝试插入或更新视图时触发触发器，以便您可以制作服务器不认为的内容可更新为 - 通常是因为您知道服务器无法轻松从架构中推断出来。

回答by Lord Peter

The correct answer is "it depends". You can't update an aggregate column in a view for example. For Oracle views you can Google for "updatable join view" for some examples of when you can and cannot update a view.

正确答案是“视情况而定”。例如，您无法更新视图中的聚合列。对于 Oracle 视图，您可以在 Google 上搜索“可更新的联接视图”，以了解何时可以和不能更新视图的一些示例。

回答by Ishara

Yes, they are updatable but not always. Views can be updated under followings:

是的，它们是可更新的，但并非总是如此。可以在以下情况下更新视图：

• If the view consists of the primary key of the table based on which the view has been created.

• If the view is defined based on one and only one table.

• If the view has not been defined using groups and aggregate functions.
• If the view does not have any distinct clause in its definition.
• If the view that is supposed to be updated is based on another view, the later should be updatable.
• If the definition of the view does not have any sub queries.

• 如果视图包含创建视图所依据的表的主键。

• 如果视图是基于一张且仅一张表定义的。

• 如果未使用组和聚合函数定义视图。
• 如果视图在其定义中没有任何不同的子句。
• 如果应该更新的视图基于另一个视图，则后者应该是可更新的。
• 如果视图的定义没有任何子查询。

回答by Frank Heikens

PostgreSQL has RULEs to create updatable VIEWs. Check the examples in the manualto see how to use them.

PostgreSQL 有创建可更新视图的规则。查看手册中的示例以了解如何使用它们。

Ps. In PostgreSQL a VIEW is a RULE, a select rule.

附言。在 PostgreSQL 中，VIEW 是一个 RULE，一个选择规则。

回答by Stefan Steinegger

In the past it wasn't possible to update any views. The main purpose of a view is to lookat data, hence the name. It could also have been called a stored query.

过去无法更新任何视图。视图的主要目的是看在数据，因此而得名。它也可以称为存储查询。

Today, many database engines support to update views. It's bound to restrictions, some updates are virtually impossible (eg. calculated columns, group by etc).

今天，许多数据库引擎支持更新视图。它受到限制，有些更新几乎是不可能的（例如计算列、分组依据等）。

回答by Tegiri Nenashi

There are two approaches:

有两种方法：

1. INSTEAD OF trigger, which basically shifts the problem to the user. You write some procedural code that does the job. Certainly, no guarantees is made about correctness, consistency, etc. From RDBMS engine perspective a trigger that deletes everything from the base tables, no matter what update is made in the view, is perfectly fine.

2. Much more ambitious is view updates handled exclusively by RDBMS engine. Not much progress is made here: to put it mildly, if you have some good ideas there, then you can roll out PhD thesis. In practice, your favorite RDBMS might allow some limiting ad-hock view updates; check the manual:-)

1. INSTEAD OF 触发器，基本上将问题转移到用户身上。您编写了一些程序代码来完成这项工作。当然，不保证正确性、一致性等。从 RDBMS 引擎的角度来看，从基表中删除所有内容的触发器，无论在视图中进行什么更新，都非常好。

2. 更加雄心勃勃的是由 RDBMS 引擎专门处理的视图更新。这里没有太大进展：说得客气一点，如果你有一些好的想法，那么你可以推出博士论文。在实践中，您最喜欢的 RDBMS 可能允许一些限制性的临时视图更新；检查手册:-)

回答by Shashi

When a view is created in SQL Server, metadata for the referenced table columns (column name and ordinal position) is persisted in the database. Any change to the referenced base table(s) (column re-ordering, new column addition, etc) will not be reflected in the view until the view is either:

在 SQL Server 中创建视图时，被引用表列（列名和序号位置）的元数据将保留在数据库中。对引用基表的任何更改（列重新排序、新列添加等）都不会反映在视图中，直到视图是：

?Altered with an ALTER VIEW statement ?Recreated with DROP VIEW/CREATE VIEW statements ?Refreshed using system stored procedure sp_refreshview

? 使用 ALTER VIEW 语句更改 ? 使用 DROP VIEW/CREATE VIEW 语句重新创建 ? 使用系统存储过程 sp_refreshview 刷新

回答by codingbadger

Yes they are - the syntax is the same as updating a table

是的 - 语法与更新表相同

Update MyView
Set Col1 = "Testing"
Where Col2 = 3
Go

There a few conditions to creating an View that can be updated. They can be found here

创建可以更新的视图有几个条件。他们可以在这里找到

EDIT:

编辑：

I must add that is based on MS SQL

我必须添加基于 MS SQL

回答by Adriaan Stander

Yes you can, but have a look at CREATE VIEW (Transact-SQL)and see the section Updatable Views

是的，您可以，但请查看CREATE VIEW (Transact-SQL)并查看可更新视图部分

回答by gandjustas

http://msdn.microsoft.com/en-us/library/ms187956.aspx

http://msdn.microsoft.com/en-us/library/ms187956.aspx

See Remarks\updateable view

请参阅备注\可更新视图