學習筆記 檢視

建立強制檢視可更新檢視檢視相關的資料字典

[@more@]

--- 檢視中的列數限制1000
The maximum number of columns that can be defined in a view is 1000, just
as for a table.

--- 建立有錯誤的檢視(force view)
SQL> create force view test_view as
2 select c1,c2 from test_table;

Warning: View created with compilation errors.

SQL> desc test_view
ERROR:
ORA-24372: invalid object for describe

SQL> drop view test_view;

View dropped.

SQL> desc test_view
ERROR:
ORA-04043: object test_view does not exist

--- 帶約束的檢視，並定義約束的名字
SQL> create or replace view dept_above_250
2 as select department_id did,department_name
3 from departments
4 where department_id >250
5 with check option constraint check_dept_250;

View created.

SQL> select constraint_name,table_name from user_constraints where constraint_type='V';

CONSTRAINT_NAME
------------------------------------------------------------
TABLE_NAME
------------------------------------------------------------
CHECK_DEPT_250
DEPT_ABOVE_250

--- 相關的資料字典檢視
user_views 檢視定義及檢視名稱
user_tab_columns 檢視中的列名
user_updatable_columns 檢視中的各列是否可更新
user_constraints 檢視上CHECK約束名稱

-- 可更新的檢視的要點是哪個表是主鍵表，如果不在基表上定義主鍵，即便是在檢視中不重複，也不是主鍵表，也不能做更新。