oracle 更新视图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6297507/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Updating a View
提问by Mark
I have the below view and I am trying to update the view. The error : "virtual column not allowed here" is being displayed. How can I arrange this?
我有以下视图，我正在尝试更新视图。正在显示错误：“此处不允许使用虚拟列”。我该如何安排？
Thanks Keith Spiteri
感谢基思·斯皮特里
View:
看法：
CREATE OR REPLACE VIEW FilmDetailsView
(Film_Name, Actor_FullName, Hall_Number, Date_Time)
AS SELECT flm.film_name, actor.actor_name || ' ' || actor.actor_surname,
hall.cinemahall_number, schedule.schedule_date
FROM film flm
JOIN movieschedule schedule
ON (flm.film_id = schedule.schedule_filmid)
JOIN cinemahall hall
ON (schedule.schedule_hallid = hall.cinemahall_id)
JOIN FilmActor filmactor
ON (flm.film_id = filmactor.filmactor_filmid)
JOIN Actor actor
ON (actor.actor_id = filmactor.filmactor_actorid);
The Update:
更新：
UPDATE FILMDETAILSVIEW
SET ACTOR_FULLNAME = 'a'
WHERE HALL_NUMBER = 1;
The Error:
错误：
Error starting at line 312 in command:
UPDATE FILMDETAILSVIEW
SET ACTOR_FULLNAME = 'a'
WHERE HALL_NUMBER = 1
Error at Command Line:313 Column:4
Error report:
SQL Error: ORA-01733: virtual column not allowed here
01733. 00000 - "virtual column not allowed here"
*Cause:
*Action:
回答by DCookie
Your view contains a virtual column Actor_FullName, which is a concatenation of two other real columns.
您的视图包含一个虚拟列 Actor_FullName，它是其他两个真实列的串联。
You can only update views that meet certain criteria, or have an associated INSTEAD OF trigger. Read more about this at PSOUG.org.
您只能更新满足特定条件或具有关联 INSTEAD OF 触发器的视图。在PSOUG.org 上阅读更多相关信息。
回答by Dave Costa
A view is just a stored query -- when you update through it, it's the underlying tables that must be modified. How can you update a view column that is generated through a combination of multiple base columns?
视图只是一个存储查询——当您通过它进行更新时，必须修改的是基础表。如何更新通过多个基列组合生成的视图列？
The ACTOR_FULLNAME column in the view is a concatenation of two base columns and a literal. Oracle has no way of knowing what you mean by updating that column. Should it change ACTOR.ACTOR_NAME, ACTOR.ACTOR_SURNAME, or both? And how can it update either or both in a way that will behave correctly after the update, given that the expression for the virtual column always includes a space?
视图中的 ACTOR_FULLNAME 列是两个基本列和一个文字的串联。Oracle 无法通过更新该列来了解您的意思。是否应该更改 ACTOR.ACTOR_NAME、ACTOR.ACTOR_SURNAME 或两者？考虑到虚拟列的表达式总是包含一个空格，它如何以更新后正确行为的方式更新其中一个或两个？
If you have some meaningful logic you want to implement to handle such an UPDATE, you could use an INSTEAD OF trigger on the view to implement it.
如果您想实现一些有意义的逻辑来处理此类更新，则可以在视图上使用 INSTEAD OF 触发器来实现它。
回答by Phil
You are trying to update a view like you would a table. That is not possible--you need to instead change the definition of the view. I've never used Oracle, but in SQL Server, you would use a case statement like so:
您正在尝试像更新表格一样更新视图。这是不可能的——您需要改为更改视图的定义。我从未使用过 Oracle，但在 SQL Server 中，您将使用如下的 case 语句：
case hall.cinemahall_number when 1 then 'a'
else actor.actor_name + ' ' + actor.actor_surname
end as actor_fullname
That would go in the definition of your view, where you have
那将在您的视图的定义中，您有
actor.actor_name || ' ' || actor.actor_surname
right now. (Obviously convert what I wrote to PL/SQL).
马上。（显然将我写的内容转换为 PL/SQL）。
