檢視insert,delete,update對基表的影響（檢視初識）

lovehewenyu發表於2013-01-23
delete

檢視進行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

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26442936/viewspace-753145/，如需轉載，請註明出處，否則將追究法律責任。

相關文章