在通过DML操作Oracle的视图的时候,有一个很重要的条件是操作的base表必须是Key-Preserved Table.
那么,什么是Key-Preserved Table呢.Oracle给出的定义是:
A table is key preserved if every key of the table can also be a key of the result of the join.
It is not necessary that the key or keys of a table be selected for it to be key preserved.
It is sufficient that if the key or keys were selected, then they would also be key(s) of the result of the join.
如果某一个表的主键可以作为这个join结果(view通常是几个表的join结果)的主键,那么这个表就是key preserved table.
这个表的主键并非一定要出现在select出来的结果集中(select list里面),但是如果其出现在结果集中,那么它必须可以满足作为这个结果集的主键的要求.
来看一个例子,有这样两个表,dept和employee,以及基于这两个表的testv视图
- create table dept(deptid int primary key,deptname varchar2(20))
- create table employee(empid int primary key,empname varchar2(20),deptid int)
- insert into dept values(1,'dept1');
- insert into dept values(2,'dept2');
- insert into dept values(3,'dept3');
- insert into employee values(1,'emp1',1);
- insert into employee values(2,'emp2',1);
- insert into employee values(3,'emp3',2);
- create view testv
- as select d.deptid deptid,deptname,empid,empname,e.deptid edeptid
- from dept d join employee e
- on d.deptid=e.deptid
查询这个视图:select * from testv
在testv这个视图中,employee就是一个key preserved table,而dept不是.
那么这个视图可以进行的DML为
delete from testv where empid=1(操作的结果是将employee表中的empid=1的记录delete了,dept表不会有改变)
delete from testv where deptid=1(操作的结果是将employee表中的empid=1和2的记录都delete了,dept表不会有改变)
update testv set empname='empx' where edeptid=1
update testv set empname='empx' where empid=1
update testv set empname='empx' where deptid=1
insert into testv(empid,empname,edeptid) values(4,'emp4',2)
这个视图不可以进行的DML为
update testv set deptname='deptx' where deptid=1
update testv set deptname='deptx' where empid=1
insert into testv(deptid,deptname) values(4,'dept4')
ORA-01779: cannot modify a column which maps to a non key-preserved table
