视图进行 insert,delete,update 对基表的影响（视图初识）

一、问题： 解决官档部分词语不理解

官档中 oracle concepts=>partitions,views 有这样一段话不理解。

All operations performed on a view actually affect the base tables . （通过实验 1 有了初步了解）

二、实验解决问题

1、 对视图 t_view 进行 DML 操作，查看基表 t 数据

实验表明：对视图 T_VIEW 进行 DML 直接影响基表 T 的数据

doudou@TEST> insert into t_view values (11,' 兜兜 + 小鱼 ');

1 row created.

doudou@TEST> commit;

Commit complete.

doudou@TEST> update t_view set id=110 where id=11;

1 row updated.

doudou@TEST> commit;

Commit complete.

doudou@TEST> select * from t_view where id=110;

        ID NAME

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

       110 兜兜 + 小鱼

doudou@TEST> select * from t where id=110;

        ID NAME

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

       110 兜兜 + 小鱼

doudou@TEST> delete t_view where rownum<5;

4 rows deleted.

doudou@TEST> commit;

Commit complete.

doudou@TEST> select count(*) from t_view;

  COUNT(*)

----------

         7

doudou@TEST> select count(*) from t;

  COUNT(*)

----------

         7

2、 对基表 t 进行 DML ，查看视图 t_view 结果

实验表明：对基表 T 进行 DML ，数据库自动维护更新视图

doudou@TEST> insert into t values (100,' 兜兜 + 兜兜 ');

1 row created.

doudou@TEST> commit;

Commit complete.

doudou@TEST> select * from t_view where id=100;

        ID NAME

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

       100 兜兜 + 兜兜

doudou@TEST> update t set name=' 兜兜 +update' where id=100;

1 row updated.

doudou@TEST> commit;

Commit complete.

doudou@TEST> select * from t_view where id=100;

        ID NAME

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

       100 兜兜 +update

doudou@TEST> delete t where id=100;

1 row deleted.

doudou@TEST> commit;

Commit complete.

doudou@TEST> select * from t_view where id=100;

no rows selected

3、 drop view 查看基表 t 的影响

实验表明： drop view 对基表 t 没有影响

doudou@TEST> select count(*) from t;

  COUNT(*)

----------

         7

doudou@TEST> select count(*) from t_view;

  COUNT(*)

----------

         7

doudou@TEST> drop view t_view;

View dropped.

doudou@TEST> select count(*) from t;

  COUNT(*)

----------

         7

doudou@TEST> select count(*) from t_view;

select count(*) from t_view

                     *

ERROR at line 1:

ORA-00942: table or view does not exist

4、 drop 基表 t 查看对视图 t_view 影响

实验表明： drop base tables 对 view 有影响，视图的数据没了，但是视图在数据字典中的定义还是存在的。

删除基表对视图有影响，视图报错了，因为视图不包含数据，数据都来源于基表，所以基表没有了，视图也报错了，视图虽然不存储数据，但是会把定义保存在数据字典中。所以 user_views 还是可以看到视图记录

doudou@TEST> create view t_view as select * from t;

View created.

doudou@TEST> select count(*) from t;

  COUNT(*)

----------

         7

doudou@TEST> select count(*) from t_view;

  COUNT(*)

----------

         7

doudou@TEST> drop table t ;

Table dropped.

doudou@TEST> select count(*) from t;

select count(*) from t

                     *

ERROR at line 1:

ORA-00942: table or view does not exist

doudou@TEST> select count(*) from t_view;

select count(*) from t_view

                     *

ERROR at line 1:

ORA-04063: view "DOUDOU.T_VIEW" has errors

doudou@TEST> desc t_view;

ERROR:

ORA-24372: invalid object for describe

doudou@TEST> select view_name from user_views;

VIEW_NAME

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

T_VIEW -- （数据字典中还是存在视图的）

T1_VIEW

TEST_VIEW

【基表 T 被删除，视图 T_VIEW 没有删除，查看视图 T_VIEW 会报相应错误： ORA-0 4063 或是 ORA- 24372 】

拓展： 看到 oracle 报错，先看看 oracle 是怎么定义这个错误的，然后想想处理方法

[ora@dg-pp ~]$ oerr ora 4063

04063, 00000, "%s has errors"

// *Cause:   Attempt to execute a stored procedure or use a view that has

//           errors.   For stored procedures, the problem could be syntax errors

//           or references to other, non-existent procedures.   For views ,

//           the problem could be a reference in the view's defining query to

//           a non-existent table. （视图参考表不存在）

//           Can also be a table which has references to non-existent or

//           inaccessible types.

// *Action: Fix the errors and/or create referenced objects as necessary.

[ora@dg-pp ~]$ oerr ora 24372

24372, 00000, "invalid object for describe"

// *Cause:   The object to be described is not valid.

//           It either has compilation or authorization errors.

// *Action: The object to be described must be valid.

三、总结

1、   视图的 DML 操作对基表是有影响的，视图的 insert,update,delete 相关的基表也会被 insert,update,delete

2、   Drop 视图对基表没有影响，删除基表后视图会没有数据，因为视图是不包含数据的（物化视图除外）。数据字典中还有删除基表的视图记录，就有了 ORA-04063 报错

附表

一、 实验环境

建立实验表 t 、 t1 及插入数据 SQL

doudou@TEST> create table t (id number, name varchar2(40));

Table created.

doudou@TEST> begin

  2   for i in 1..10 loop

  3   insert into t values (i,' 兜兜 ');

  4   end loop;

  5   commit;

  6   end ;

  7   /

PL/SQL procedure successfully completed.

doudou@TEST> create table t1 (id number, city_name varchar2(40));

Table created.

doudou@TEST> begin

  2   for i in 1..10 loop

  3   insert into t1 values (i,' 北京 ');

  4   end loop;

  5   commit;

  6   end;

  7   /

PL/SQL procedure successfully completed.

User_updatable_columns 当前用户建立视图有适当的更新权限

doudou@TEST> select table_name,column_name,updatable,insertable,deletable from user_updatable_columns where table_name in ('T','T1');

TABLE_NAME            COLUMN_NAME                     UPDATA INSERT DELETA

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

T                     ID                              YES     YES     YES

T                     NAME                            YES     YES     YES

T1                    ID                              YES     YES     YES

T1                    CITY_NAME                       YES     YES     YES

建立视图 t 、 t1

doudou@TEST> create view t_view as select * from t;

View created.

doudou@TEST> create view t1_view as select * from t1;

View created.

二、 知识点：

1、 all operations performed on a view actually affect the base tables

oracle concepts=>partions,views=>overview of views

http://docs.oracle.com/cd/E11882_01/server.112/e25789/schemaob.htm#autoId9

2、 USER_UPDATABLE_COLUMNS

USER_UPDATABLE_COLUMNS describes all columns owned by the current user that are in a join view and are updatable by the current user, subject to appropriate privileges.

http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2113.htm#i1593275

3、 a view is not allocated storage space,nor does a view contain data

         Oracle concepts=>partions,views=>characteristics of views

Unlike a table, a view is not allocated storage space, nor does a view contain data. Rather, a view is defined by a query that extracts or derives data from the base tables referenced by the view. Because a view is based on other objects, it requires no storage other than storage for the query that defines the view in the data dictionary.

A view has dependencies on its referenced objects, which are automatically handled by the database. For example, if you drop and re-create a base table of a view, then the database determines whether the new base table is acceptable to the view definition.

http://docs.oracle.com/cd/E11882_01/server.112/e25789/schemaob.htm#autoId9

4、 user_views 使用 user_views 视图查看视图 t_view 的建立语句，从而找到基表

doudou@TEST> select view_name,text from user_views;

VIEW_NAME  TEXT
---------- --------------------------------------------------------------------------------
T_VIEW     select "ID","NAME" from t
T1_VIEW    select "ID","CITY_NAME" from t1
TEST_VIEW  select a.id,a.name,b.city_name from t a, t1 b where a.id=b.id

5、 小议 user_xxx,all_xxx,dba_xxx 视图查看权限区别

     1 、权限区别
USER_VIEWS describes the views owned by the current user.
ALL_VIEWS describes the views accessible to the current user.
DBA_VIEWS describes all views in the database.
     2 、（未完待续，后续学习中还会补充）

user_views
http://docs.oracle.com/cd/E11882_01/server.112/e25513/statviews_5499.htm#REFRN26305
all_views
dba_views
http://docs.oracle.com/cd/E11882_01/server.112/e25513/statviews_2132.htm#i1593583