如题
代码如下:SQL> create table salemay
2 (
3 sale_id char(6) not null,
4 sale_name varchar2(20),
5 sale_qua number
6 );
Table created
SQL> create table salejune
2 (
3 sale_id char(6) not null,
4 sale_name varchar2(20),
5 sale_qua number
6 );
Table created
SQL> create table salejuly
2 (
3 sale_id char(6) not null,
4 sale_name varchar2(20),
5 sale_qua number
6 );
Table created
SQL>
SQL> create or replace view saleview
2 as
3 select * from salemay
4 union all
5 select * from salejune
6 union all
7 select * from salejuly
8 ;
View created
SQL> insert into saleview values('1','june01',500);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from saleview;
SALE_ID SALE_NAME SALE_QUA
------- -------------------- ----------
代码如下:SQL> create table salemay
2 (
3 sale_id char(6) not null,
4 sale_name varchar2(20),
5 sale_qua number
6 );
Table created
SQL> create table salejune
2 (
3 sale_id char(6) not null,
4 sale_name varchar2(20),
5 sale_qua number
6 );
Table created
SQL> create table salejuly
2 (
3 sale_id char(6) not null,
4 sale_name varchar2(20),
5 sale_qua number
6 );
Table created
SQL>
SQL> create or replace view saleview
2 as
3 select * from salemay
4 union all
5 select * from salejune
6 union all
7 select * from salejuly
8 ;
View created
SQL> insert into saleview values('1','june01',500);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from saleview;
SALE_ID SALE_NAME SALE_QUA
------- -------------------- ----------
望高手解疑惑之!
Error at line 1
ORA-01732: data manipulation operation not legal on this view
条件一:在连接视图中不能有Order by排序语句。
条件二:基础表中所有的NOT NULL列都必须在这个视图中。
条件三:需要更新的列不是虚拟列。
条件四:不能够具有分组函数。
条件四:不能够具有集合操作union、minus、intersect。
................如果非要更新,可以考虑使用替代(instead)触发器
instead of insert on saleview
declare
l_sale_id char(6);
begin
select sale_id
into l_sale_id
from saleview; if substr(:new.sale_id, 1, 3) = 'may' then
insert into salemay
select sale_id, sale_name, sale_qua
from saleview;
end if; if substr(:new.sale_id, 1, 3) = 'jun' then
insert into salejune
select sale_id, sale_name, sale_qua
from saleview;
end if; if substr(:new.sale_id, 1, 3) = 'jul' then
insert into salejuly
select sale_id, sale_name, sale_qua
from saleview;
end if;
end;
into l_sale_id
from saleview;
z这个有什么用?
insert into saleview values('june01','xxxx',500);或者触发器中的判断条件是
substr(:new.sale_name, 1, 3) = 'jun'