oracle：为什么我不能插入到视图中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5035441/
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
oracle: why cant i insert into view
提问by Sinaesthetic
I created a simple view with columns from a single table. When i try to insert values into the table, i get the ORA_01732 error that dml is not legal on this view. I DO have an order by clause in the view definition which I've gathered is not allowed for it to be inherently updatable and I'm seeing that I probably have to use an INSTEAD OF type clause in the view definition. Can someone show me how I would build the view to be updatable this way?
我创建了一个简单的视图，其中包含来自单个表的列。当我尝试向表中插入值时，我收到 ORA_01732 错误，即 dml 在此视图中不合法。我确实在我收集的视图定义中有一个 order by 子句，不允许它本质上是可更新的，我看到我可能必须在视图定义中使用 INSTEAD OF 类型子句。有人可以告诉我如何构建可更新的视图吗？
here is the create view statement:
这是创建视图语句：
create view CUST_VIEW
as select customer#,firstname,lastname,state
from book_customer
order by state, lastname;
回答by René Nyffenegger
Are you sure that the order byis the cause for your observation?
你确定这order by是你观察的原因吗？
I can do an insert on a view with an order by clause:
我可以使用 order by 子句在视图上插入：
create table tq84_table (
a number,
b number
);
create view tq84_updateable_view as
select a, b from tq84_table
order by a;
insert into tq84_table values (4,1);
insert into tq84_table values (1,4);
insert into tq84_table values (3,9);
insert into tq84_table values (7,5);
select * from tq84_updateable_view;
insert into tq84_updateable_view values (1,9);
select * from tq84_updateable_view;
The above statements run with no problem on Oracle 11 R2.
上述语句在 Oracle 11 R2 上运行没有问题。
You might want to check with USER_UPDATABLE_COLUMNSwhich columns you can insert to:
您可能想检查USER_UPDATABLE_COLUMNS可以插入哪些列：
SQL> select * from user_updatable_columns where table_name = 'TQ84_UPDATEABLE_VIEW';
OWNER TABLE_NAME COLUMN_NAME UPD INS DEL
------------------------------ ------------------------------ ------------------------------ --- --- ---
RENE TQ84_UPDATEABLE_VIEW A YES YES YES
RENE TQ84_UPDATEABLE_VIEW B YES YES YES
回答by Indira Panneerselvam
You are right that a view with ORDER BY clause is not inherently updatable. All you have to do is create an INSTEAD OF trigger on the view to execute the INSERT you want. Eg: Lets say you have a view ACTIVE_CUST_VIEW on table ALL_CUST
您是对的，带有 ORDER BY 子句的视图本质上是不可更新的。您所要做的就是在视图上创建一个 INSTEAD OF 触发器来执行您想要的 INSERT。例如：假设您在表 ALL_CUST 上有一个视图 ACTIVE_CUST_VIEW
CREATE OR REPLACE TRIGGER INS_NEW_CUST_VIEW
INSTEAD OF INSERT
ON ACTIVE_CUST_VIEW
FOR EACH ROW
BEGIN
INSERT INTO ALL_CUST (CUST_ID,CUST_NAME,START_DATE) VALUES (:NEW.CUST_ID,:NEW.CUST_NAME,:NEW.START_DATE);
END INS_NEW_CUST_VIEW;
/
