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

yanchencheng

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