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

利用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条 
=================================