由04年下半年数工下午第二题引发的系列实验5--视图更新

SQL> ed SQL> desc dupEmp Name Null? Type ----------------------------------------------------- -------- ------------------------------------ EMPNO NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) SQL> edit Wrote file afiedt.buf 1* select view_name FROM user_views SQL> / VIEW_NAME ------------------------------ TVIEW SQL> ed Wrote file afiedt.buf 1* SELECT * FROM tview SQL> / no rows selected SQL> ed Wrote file afiedt.buf 1 CREATE VIEW v(d,c,totals,averages) 2 AS 3 SELECT deptno, COUNT(*), SUM(sal), AVG(sal) 4 FROM dupEmp 5* GROUP BY deptno SQL> / View created. SQL> ed Wrote file afiedt.buf 1* SELECT * FROM v SQL> / D C TOTALS AVERAGES ---------- ---------- ---------- ---------- 10 3 8750 2916.66667 20 5 10875 2175 30 6 9400 1566.66667 SQL> ed Wrote file afiedt.buf 1* select dbms_metadata.get_ddl('VIEW','V') from dual SQL> / DBMS_METADATA.GET_DDL('VIEW','V') -------------------------------------------------------------------------------- CREATE OR REPLACE FORCE VIEW "YMC"."V" ("D", "C", "TOTALS", "AVERAGES") AS SELECT deptno, COUNT(*), SUM(sal), AVG(sal) FROM dupEmp GROUP BY deptno SQL> ed Wrote file afiedt.buf 1 SELECT * 2 FROM user_updatable_columns 3* WHERE table_name='V' SQL> / OWNER TABLE_NAME COLUMN_NAME UPD INS DEL ------------------------------ ------------------------------ ------------------------------ --- --- --- YMC V D NO NO NO YMC V C NO NO NO YMC V TOTALS NO NO NO YMC V AVERAGES NO NO NO SQL> spool off

嗯，含group by的视图确实不能更新