利用user_updatable_columns视图来查看视图中可以更新的列

来源：互联网发布：淘宝天天特价怎么报名编辑：程序博客网时间：2024/05/22 03:46

SQL> SET LONG 9999

SQL> SELECT DBMS_METADATA.GET_DDL('VIEW','CLASSES')

2 FROM DUAL;

DBMS_METADATA.GET_DDL('VIEW','CLASSES')

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

CREATE OR REPLACE FORCE VIEW "TAISHAN_JAP"."CLASSES" ("CLASSNO", "CLASSNAME",

AR", "OTHERNAME", "BALANCE", "DEPARTMENTID", "STUDENTTYPECODE", "YEARLIMIT", "HE

ADER1", "HEADERPHONE1", "HEADER2", "HEADERPHONE2", "MONITORID", "CLASSROOMID", "

DORM", "CREATORID", "CREATORDATE", "LASTEDITID", "LASTEDITDATE", "CLASSESSTATUS"

) AS

SELECT

CLASSID CLASSNO, CLASSNAME CLASSNAME , SUBCAMPUSID SUBCAMPUSID,

C.SPECIALITYID SPECIALITYID,C.GRADE GRADEYEAR, CLASSALIAS OTHERNAME,

0 BALANCE, C.DEPID DEPARTMENTID, S.TRAINLEVEL STUDENTTYPECODE,

S.DEFAULTYEARS YEARLIMIT,(SELECT A.ADMINPERSONID FROM XJ_CLASSADMIN A WHERE A.

ROWNUM=1) HEADER1,

(SELECT A.TELEPHONE FROM XJ_CLASSADMIN A WHERE A.CLASSID=C.CLASSID AND ROWNUM=

' ' HEADER2, ' ' HEADERPHONE2, ' ' MONITORID,

C.FIXEDCLASSROOMID CLASSROOMID,' ' DORM, '' CREATORID,

SYSDATE CREATORDATE,'' LASTEDITID, SYSDATE LASTEDITDATE,

'0' CLASSESSTATUS

FROM XJ_CLASSES C,UP_SPECIALITYINYEAR US,UP_SPECIALITY S

WHERE C.SPECIALITYID=US.SPECIALITYID AND C.GRADE=US.GRADE AND US.SPECIALITYID

SPECIALITYID

查看classes视图的定义，有多个表连接构成，但并不是说该视图就不能更新。

SQL> SELECT COLUMN_NAME,UPDATABLE,INSERTABLE,DELETABLE

2 FROM USER_UPDATABLE_COLUMNS

3 WHERE TABLE_NAME='CLASSES';

COLUMN_NAME UPD INS DEL

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

CLASSNO YES YES YES

CLASSNAME YES YES YES

SUBCAMPUSID YES YES YES

SPECIALITYID YES YES YES

GRADEYEAR YES YES YES

OTHERNAME YES YES YES

BALANCE NO NO NO

DEPARTMENTID YES YES YES

STUDENTTYPECODE NO NO NO

YEARLIMIT NO NO NO

HEADER1 NO NO NO

HEADERPHONE1 NO NO NO

HEADER2 NO NO NO

HEADERPHONE2 NO NO NO

MONITORID NO NO NO

CLASSROOMID YES YES YES

DORM NO NO NO

CREATORID NO NO NO

CREATORDATE NO NO NO

LASTEDITID NO NO NO

LASTEDITDATE NO NO NO

CLASSESSTATUS NO NO NO

已选择22行。

SQL> SELECT CLASSID,CLASSNAME

2 FROM XJ_CLASSES

3 WHERE ROWNUM=1;

CLASSID CLASSNAME

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

C875F600867948568816847C9B513600 09G数维2班

SQL> UPDATE CLASSES

2 SET CLASSNAME='TEST'

3 WHERE CLASSNO='C875F600867948568816847C9B513600';

已更新 1 行。

当然也可以使用dba_updatable_columns和all_updatable_columns也可以实现此事！

记录下来，便于查阅！
=================================
摘自：http://blog.chinaunix.net/u/22472/showart.php?id=315113
西方经济学的百草园
作者：红红创建于： 2007-06-05 15:40:40，修改于： 2007-06-05 15:40:40，已浏览225次，有评论0条
=================================