什么是Oracle Key-Preserved Table跟什么样的视图可以进行DML操作
什么是Oracle Key-Preserved Table和什么样的视图可以进行DML操作
在通过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视图[sql] view plaincopyprint?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
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
一个View中可以有多个key preserved tables
[sql] view plaincopyprint?create table test1(t1id int primary key,t1v varchar2(10))
create table test2(t2id int primary key,t2v varchar2(10))
insert into test1 values(1,'t1');
insert into test1 values(2,'t2');
insert into test1 values(3,'t3');
insert into test2 values(1,'t21');
insert into test2 values(2,'t22');
insert into test2 values(3,'t23');
create view test1v
as select t1id ,t1v,t2id,t2v
from test1 join test2
on test1.t1id=test2.t2id
create table test1(t1id int primary key,t1v varchar2(10))
create table test2(t2id int primary key,t2v varchar2(10))
insert into test1 values(1,'t1');
insert into test1 values(2,'t2');
insert into test1 values(3,'t3');
insert into test2 values(1,'t21');
insert into test2 values(2,'t22');
insert into test2 values(3,'t23');
create view test1v
as select t1id ,t1v,t2id,t2v
from test1 join test2
on test1.t1id=test2.t2id
在这个视图中,test1,test2都是key preserved table.但是对view的DML操作一次只能作用于一个表,不能同时对多个base 表操作.
select * from test1v
如下面的DML是可以的
update test1v set t1v='t11'
update test1v set t2v='t22'
insert into test1v(t1id,t1v) values(4,'t4')
insert into test1v(t2id,t2v) values(4,'t24')
delete from test1v where t1id=4
delete from test1v where t2id=4(这两条delete语句都是将test1表中的数据delete了??)
但是下面的DML操作是不可以的:
update test1v set t1v='t11',t2v='t22'
insert into test1v values(5,'t5',5,'t25')
ORA-01776: cannot modify more than one base table through a join view
对于View是否可以进行DML操作还有一些其他限制:
不能有start with和connect by.
不能有group by和having.
不能有集合操作union,intersect和minus.
不能有聚合函数,如AVG,COUNT,MAX等等.
不能有rownum伪列.
当view有check option的时候:[sql] view plaincopyprint?create view test1v
as select t1id ,t1v,t2id,t2v
from test1 join test2
on test1.t1id=test2.t2id
with check option
create view test1v
as select t1id ,t1v,t2id,t2v
from test1 join test2
on test1.t1id=test2.t2id
with check optioninsert into test1v(t1id,t1v) values(4,'t4')
update test1v set t1id=4 where t1id=1
ORA-01733: virtual column not allowed here
如何查看那些字段可以插入更新
select * from USER_UPDATABLE_COLUMNS where table_name='TESTV'
在通过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视图[sql] view plaincopyprint?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
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
一个View中可以有多个key preserved tables
[sql] view plaincopyprint?create table test1(t1id int primary key,t1v varchar2(10))
create table test2(t2id int primary key,t2v varchar2(10))
insert into test1 values(1,'t1');
insert into test1 values(2,'t2');
insert into test1 values(3,'t3');
insert into test2 values(1,'t21');
insert into test2 values(2,'t22');
insert into test2 values(3,'t23');
create view test1v
as select t1id ,t1v,t2id,t2v
from test1 join test2
on test1.t1id=test2.t2id
create table test1(t1id int primary key,t1v varchar2(10))
create table test2(t2id int primary key,t2v varchar2(10))
insert into test1 values(1,'t1');
insert into test1 values(2,'t2');
insert into test1 values(3,'t3');
insert into test2 values(1,'t21');
insert into test2 values(2,'t22');
insert into test2 values(3,'t23');
create view test1v
as select t1id ,t1v,t2id,t2v
from test1 join test2
on test1.t1id=test2.t2id
在这个视图中,test1,test2都是key preserved table.但是对view的DML操作一次只能作用于一个表,不能同时对多个base 表操作.
select * from test1v
如下面的DML是可以的
update test1v set t1v='t11'
update test1v set t2v='t22'
insert into test1v(t1id,t1v) values(4,'t4')
insert into test1v(t2id,t2v) values(4,'t24')
delete from test1v where t1id=4
delete from test1v where t2id=4(这两条delete语句都是将test1表中的数据delete了??)
但是下面的DML操作是不可以的:
update test1v set t1v='t11',t2v='t22'
insert into test1v values(5,'t5',5,'t25')
ORA-01776: cannot modify more than one base table through a join view
对于View是否可以进行DML操作还有一些其他限制:
不能有start with和connect by.
不能有group by和having.
不能有集合操作union,intersect和minus.
不能有聚合函数,如AVG,COUNT,MAX等等.
不能有rownum伪列.
当view有check option的时候:[sql] view plaincopyprint?create view test1v
as select t1id ,t1v,t2id,t2v
from test1 join test2
on test1.t1id=test2.t2id
with check option
create view test1v
as select t1id ,t1v,t2id,t2v
from test1 join test2
on test1.t1id=test2.t2id
with check optioninsert into test1v(t1id,t1v) values(4,'t4')
update test1v set t1id=4 where t1id=1
ORA-01733: virtual column not allowed here
如何查看那些字段可以插入更新
select * from USER_UPDATABLE_COLUMNS where table_name='TESTV'