利用user_updatable_columns视图来查看视图中可以更新的列
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条
=================================