oracle：为什么我不能插入视图

发布于 2024-10-17 16:54:54 字数 328 浏览 1 评论 0原文

我创建了一个简单的视图，其中包含单个表中的列。当我尝试将值插入表中时，出现 ORA_01732 错误，表明 dml 在此视图上不合法。我确实在视图定义中有一个 order by 子句，我收集的视图定义不允许它本质上可更新，而且我发现我可能必须在视图定义中使用 INSTEAD OF 类型子句。有人可以告诉我如何构建可更新的视图吗？

这是创建视图语句：

create view CUST_VIEW
as select customer#,firstname,lastname,state
from book_customer
order by state, lastname;

原文

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?

here is the create view statement:

create view CUST_VIEW
as select customer#,firstname,lastname,state
from book_customer
order by state, lastname;

分享到QQ

分享到微博

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

发布评论

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

所有深爱都是秘密 2024-10-24 16:54:54

您确定 order by 是您观察结果的原因吗？

我可以使用 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;

上述语句在 Oracle 11 R2 上运行没有问题。

您可能需要使用 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

Are you sure that the order by is the cause for your observation?

I can do an insert on a view with an order by clause:

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.

You might want to check with USER_UPDATABLE_COLUMNS which columns you can insert to:

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

回复收藏 0 原文

゛时过境迁 2024-10-24 16:54:54

您是对的，带有 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;
/

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

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;
/

回复收藏 0 原文

~没有更多了~