英文:
Do Changes Made on Table Created by Joining a View and an Another Table Affect Original Table View Based On?
问题
如果我在一个表上创建了一个视图，然后在该视图上更改了某些记录，那么原始表也会被更改。如果我将这个视图与另一个表进行连接，并在连接后的结果表上更改一些记录，那么基于视图的原始表也会被更改吗？
英文:
Suppose I have a view created on a table. If I change some record on my view, the original table is also changed too. What if I join this view with an another table and change some records on this result table I got by joining, would original table on which view based also be changed?
答案1
得分: 1
原则上是可以的，如果视图的构建方式使底层表格可以通过视图进行更新。例如，假设我有一个名为 TABLE_1 的表格，并创建一个名为 VIEW_1 的视图，如下所示：
CREATE OR REPLACE VIEW VIEW_1 AS
SELECT *
FROM TABLE_1
如果我执行以下更新语句：
UPDATE VIEW_1
SET FIELD_N = 'XYZ'
WHERE KEY_1 = 123
Oracle 足够聪明，会将 UPDATE 传递到底层表格，TABLE_1 将会被更新。
然而，任何复杂的视图很可能包含使视图无法更新的操作。所以，假设我有以下 VIEW_2：
CREATE OR REPLACE VIEW VIEW_2 AS
SELECT KEY_1,
FIELD_N,
SUM(SOME_OTHER_FIELD) AS OTHER_SUM,
MIN(YADDA_YADDA) AS MIN_YADDA
FROM TABLE_1
GROUP BY KEY_1,
FIELD_N
对这个视图的更新将会失败，并显示 ORA-01732: data manipulation operation not legal on this view 错误。因此，是否可以通过视图进行更新很大程度上取决于视图执行的操作。
英文:
In principle, yes, if the views are constructed in such a way that the underlying tables are updateable through the view. For example, let's say I have a table named TABLE_1 and create a view VIEW_1 as follows:
CREATE OR REPLACE VIEW VIEW_1 AS
SELECT *
FROM TABLE_1
If I issue the update statement
UPDATE VIEW_1
SET FIELD_N = 'XYZ'
WHERE KEY_1 = 123
Oracle is bright enough to pass the UPDATE through to the underlying table, and TABLE_1 will be updated.
A view of any complexity, however, will most likely contain operations that make the view non-updateable. So let's say I have the following VIEW_2:
CREATE OR REPLACE VIEW VIEW_2 AS
SELECT KEY_1,
FIELD_N,
SUM(SOME_OTHER_FIELD) AS OTHER_SUM,
MIN(YADDA_YADDA) AS MIN_YADDA
FROM TABLE_1
GROUP BY KEY_1,
FIELD_N
an UPDATE of this view will fail with an ORA-01732: data manipulation operation not legal on this view error. So whether you can update through a view or not very much depends on what operations the view is performing.
答案2
得分: 0
我觉得你的问题相当令人困惑，而且这篇文字对于一条评论来说太长了。
假设我在一个表上创建了一个视图。如果我在我的视图上更改了一些记录，原始表也会发生更改。
这听起来不太合理。一般来说，你不会修改视图，而是基本表。你改变基本表中的数据，视图会反映这些更改。
你所说的情况可能是使用视图上的代替触发器来实现的。这些触发器可以更改基本表，或者可以更改任何其他内容。它们完全由你控制，与Oracle的功能实际上没有太多关系（除了Oracle允许在视图上存在instead of触发器）。
JOIN操作是在SELECT语句中执行的，这些操作不会修改数据。再次强调，如果数据发生更改，那将通过代替触发器来实现，这些触发器完全由你控制。
我认为你可能对术语产生了困惑。如果你的数据库表现出你所描述的行为，那么你（或其他人）可能已经付出了一些努力来使其以这种方式运行。
英文:
I find your question quite confusing and this is too long for a comment.
> Suppose I have a view created on a table. If I change some record on my view, the original table is also changed too.
This doesn't make much sense. In general, you do not modify views but base tables. You change the data in the base tables and this is reflected in the views.
What you are saying is possible using instead of triggers on views. These can change the base tables -- or anything for that matter. They are entirely under your control and really have nothing to do with Oracle functionality (other than Oracle allowing the existence of instead of triggers on views).
JOINs are operations in SELECT statements, and these do not modify data. Once again, if the data is changing that would be through instead of triggers, which are entirely under your control.
I think you may be confused by terminology. If your database is behaving as your describe, then you (or someone else) has put some effort into making it behave that way.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论