oracle视图无法使用rowid,请问：无法从没有键值保存表的连接视图中选择 ROWID 这个是什么原因啊？...

rowid 在join 视图的情况

select column_name, insertable, updatable, deletable from user_updatable_columns

where table_name = 'view_name'; // 视图

在join视图中的有more then one table能updateable -- 视图的 rowid 不是可靠的

下面是列子

create table emp as select * from scott.emp where deptno = 10 and rownum = 1;

create table dept as select * from scott.dept where deptno = 10;

create or replace view V as select emp.ename, dept.dname

from emp, dept where emp.deptno = dept.deptno;

select rowid from V; // error

select rowid from V

*

ERROR at line 1:

ORA-01445: cannot select ROWID from a join view without a key-preserved table

select column_name, insertable, updatable, deletable from user_updatable_columns

where table_name = 'V';

COLUMN_NAME INS UPD DEL

------------------------------ --- --- ---

ENAME NO NO NO

DNAME NO NO NO

In this case -- the view is not updatable -- there are NO rowids to be had

here. We need a primary/unique key in order to be updatable:

alter table dept add constraint dept_pk primary key(deptno); // 建主键

select rowid from V; // ok

select column_name, insertable, updatable, deletable from user_updatable_columns

where table_name = 'V';

COLUMN_NAME INS UPD DEL

------------------------------ --- --- ---

ENAME YES YES YES

DNAME NO NO NO

Now, the emp table is updatable in this view -- since Oracle knows that each

row in EMP will join to AT MOST one row in dept -- so the update against EMP

will not be "ambigous" -- a row in emp appears in the view at most once...

select ename from emp where rowid = (select rowid from V); // ok

select dname from dept where rowid = (select rowid from V); // error

select dname from dept where rowid = (select rowid from V)

*

ERROR at line 1:

ORA-01410: invalid ROWID

that just proves the rowid belongs to EMP, not dept...

alter table emp add constraint emp_pk primary key(deptno); // 建主键

now, we make BOTH tables updatable...

select rowid from V;

ROWID

------------------

AAANjqAAGAAAABSAAA

select column_name, insertable, updatable, deletable from user_updatable_columns

where table_name = 'V';

COLUMN_NAME INS UPD DEL

------------------------------ --- --- ---

ENAME YES YES YES

DNAME YES YES YES

still have a rowid -- but from which table??

select ename from emp where rowid = (select rowid from V); // error

select ename from emp where rowid = (select rowid from V)

*

ERROR at line 1:

ORA-01410: invalid ROWID

select dname from dept where rowid = (select rowid from V); // ok

create or replace view V // 重建view

2 as

3 select emp.ename, dept.dname

4 from dept, emp

where emp.deptno = dept.deptno;

select rowid from V; // ok

select column_name, insertable, updatable, deletable

2 from user_updatable_columns

3 where table_name = 'V';

COLUMN_NAME INS UPD DEL

------------------------------ --- --- ---

ENAME YES YES YES

DNAME YES YES YES

select ename from emp where rowid = (select rowid from V); // ok

select dname from dept where rowid = (select rowid from V); // error

select dname from dept where rowid = (select rowid from V)

*

ERROR at line 1:

ORA-01410: invalid ROWID