视图进行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-04063或是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