今天跟幾個兄弟討論強制建立檢視的問題,從網上搜尋了一下資料,儲存下來。
1.1 概念
檢視-----是由SELECT查詢語句(可以是單表或者多表查詢)定義的一個"邏輯表",只有定義而無資料,是一個"虛表". 在建立檢視時,只是將檢視的定義資訊儲存在資料字典中, 而並不將實際的資料複製到任何地方, 即不需要在表空間中為檢視分配儲存空間. 檢視是檢視和操縱基表資料的一種方法, 可以像使用表一樣使用檢視.
tips: 查詢檢視沒有什麼限制, 插入/更新/刪除檢視的操作會受到一定的限制; 所有針對檢視的操作都會影響到檢視的基表; 為了防止使用者透過檢視間接修改基表的資料, 可以將檢視建立為只讀檢視(帶上with read only選項).
檢視中的資料會隨基表的更新而自動更新.
檢視猶如基表的一個"視窗", 透過這個"視窗", 可以實施許多管理. 在一個檢視中可以定義的最大列數為1000, 與表的限制相同.
檢視約束: 允許在檢視上生成約束, 如"主鍵約束、唯一鍵約束、外來鍵約束、檢查約束"等. 但檢視上的約束不是強制性的, 而是宣告性的. 檢視約束的語法與表相同. 在建立檢視時, 可以使用with check option選項,給檢視定義check約束,使其只能查詢、操作滿足check約束的記錄行.1.2 作用 1)提供各種資料表現形式, 可以使用各種不同的方式將基表的資料展現在使用者面前, 以便符合使用者的使用習慣(主要手段: 使用別名).
2)隱藏資料的邏輯複雜性並簡化查詢語句, 多表查詢語句一般是比較複雜的, 而且使用者需要了解表之間的關係, 否則容易寫錯; 如果基於這樣的查詢語句建立一個檢視, 使用者就可以直接對這個檢視進行"簡單查詢"而獲得結果. 這樣就隱藏了資料的複雜性並簡化了查詢語句. 這也是oracle提供各種"資料字典檢視"的原因之一,all_constraints就是一個含有2個子查詢並連線了9個表的檢視(在catalog.sql中定義).
3)執行某些必須使用檢視的查詢. 某些查詢必須藉助檢視的幫助才能完成. 比如, 有些查詢需要連線一個分組統計後的表和另一表, 這時就可以先基於分組統計的結果建立一個檢視, 然後在查詢中連線這個檢視和另一個表就可以了.
4)提供某些安全性保證. 檢視提供了一種可以控制的方式, 即可以讓不同的使用者看見不同的列, 而不允許訪問那些敏感的列, 這樣就可以保證敏感資料不被使用者看見.
5)簡化使用者許可權的管理. 可以將檢視的許可權授予使用者, 而不必將基表中某些列的許可權授予使用者, 這樣就簡化了使用者許可權的定義.1.3 建立檢視
許可權: 要在當前方案中建立檢視, 使用者必須具有create view系統許可權; 要在其他方案中建立檢視, 使用者必須具有create any view系統許可權. 檢視的功能取決於檢視擁有者的許可權.
語法: create [ or replace ] [ force ] view [schema.]view_name
[ (column1,column2,...) ]
as
select ...
[ with check option ] [ constraint constraint_name ]
[ with read only ];
tips:
or replace: 如果存在同名的檢視, 則使用新檢視"替代"已有的檢視
force: "強制"建立檢視,不考慮基表是否存在,也不考慮是否具有使用基表的許可權
column1,column2,...:檢視的列名, 列名的個數必須與select查詢中列的個數相同; 如果select查詢包含函式或表示式, 則必須為其定義列名. 此時, 既可以用column1, column2指定列名, 也可以在select查詢中指定列名.
with check option: 指定對檢視執行的dml操作必須滿足“檢視子查詢”的條件即,對透過檢視進行的增刪改操作進行"檢查",要求增刪改操作的資料, 必須是select查詢所能查詢到的資料, 否則不允許操作並返回錯誤提示. 預設情況下, 在增刪改之前"並不會檢查"這些行是否能被select查詢檢索到.
with read only:建立的檢視只能用於查詢資料, 而不能用於更改資料.1.3.1 建立簡單檢視
是指基於單個表建立的,不包含任何函式、表示式和分組資料的檢視。
示例1:基於emp表建立一個vw_emp檢視
create view vw_emp
as
select empno,ename,job,hiredate,deptno from emp; --------建立簡單檢視
select * from vw_emp where deptno=10;--------查詢
insert into vw_emp values(1234,'JACK','CLERK','29-4月-1963',10);--------增加
update vw_emp set ename='劉德華' where ename='JACK';--------更新
delete vw_emp where ename='劉德華'; --------刪除
create view vw_emp_readonly
as
select empno,ename,job,hiredate,deptno from emp
with read only ; --------建立只讀檢視,只能用於執行select語句
as
select empno,ename,job,hiredate,deptno
from emp where deptno=10
with check option constraint vw_emp_chk ;
--------建立檢查檢視:對透過檢視進行的增刪改操作進行檢查,
要求增刪改操作的資料必須是select查詢所能查詢到的資料。
insert into vw_emp_check
values(1235,'JACK','CLERK','29-4月-1963',20);
--------20號部門不在查詢範圍內,違反檢查約束
delete from vw_emp_check where empno=1234;
--------所刪除的資料在查詢範圍內,不違反檢查約束
是指基於多個表所建立的檢視,即,定義檢視的查詢是一個連線查詢。 主要目的是為了簡化連線查詢
示例1: 查詢部門編號為10和30的部門及僱員資訊
create view vw_dept_emp
as
select a.deptno,a.dname,a.loc,b.empno,b.ename,b.sal
from dept a , emp b
where a.deptno=b.deptno
and a.deptno in (10,30);
1.3.3 建立複雜檢視
是指包含函式、表示式、或分組資料的檢視。主要目的是為了簡化查詢。 主要用於執行查詢操作,並不用於執行DML操作。
注意:當檢視的select查詢中包含函式或表示式時,必須為其定義列別名。
示例1:查詢目前每個崗位的平均工資、工資總和、最高工資和最低工資。
create view vw_emp_job_sal
(job,avgsal,sumsal,maxsal,minsal)
as
select job,avg(sal),sum(sal),max(sal),min(sal)
from emp
group by job;
正常情況下,如果基表不存在,建立檢視就會失敗。但是可以使用force選項強制建立檢視(前提:建立檢視的語句沒有語法錯誤!),此時該檢視處於失效狀態。
示例1:
create force view vw_test_tab
as
select c1,c2 from test_tab;--------會出現“警告: 建立的檢視帶有編譯錯誤。”
where object_name='VW_TEST_TAB';--------檢視狀態:INVALID
select * from vw_test_tab;--------報錯
(c1 number(9) primary key, c2 varchar2(20),c3 varchar2(30));
select * from vw_test_tab;--------自動編譯失效的檢視
select object_name,status from user_objects
where object_name='VW_TEST_TAB';--------檢視狀態:VALID
在對檢視進行更改(或重定義)之前,需要考慮如下幾個問題:
之一——由於檢視只是一個虛表,其中沒有資料,所以更改檢視只是改變資料字典中對該檢視的
定義資訊,檢視的所有基礎物件都不會受到任何影響
之二——更改檢視之後,依賴於該檢視的所有檢視和PL/SQL程式都將變為INVALID(失效)狀態
之三——如果以前的檢視中具有with check option選項,但是重定義時沒有使用該選項,
則以前的此選項將自動刪除。
方法——執行create or replace view語句。這種方法代替了先刪除(“許可權也將隨之刪除”)
後建立的方法,會保留檢視上的許可權,但與該檢視相關的儲存過程和檢視會失效。
示例1:create or replace view v_test_tab
as
select c1,c2||' + '||c3 c23 from test_tab;
1.4.2 檢視的重新編譯
語法:alter view 檢視名 compile;
作用:當檢視依賴的基表改變後,檢視會“失效”。為了確保這種改變“不影響”檢視和依賴於該檢視的
其他物件,應該使用 alter view 語句“明確的重新編譯”該檢視,從而在執行檢視前發現重新
編譯的錯誤。檢視被重新編譯後,若發現錯誤,則依賴該檢視的物件也會失效;若沒有錯誤,
檢視會變為“有效”。
許可權:為了重新編譯其他模式中的檢視,必須擁有alter any table系統許可權。
注意:當訪問基表改變後的檢視時,oracle會“自動重新編譯”這些檢視。
示例1:select last_ddl_time,object_name,status
from user_objects
where object_name='V_TEST_TAB';——檢視的狀態:有效
from user_objects
where object_name='V_TEST_TAB';——檢視的狀態:失效
from user_objects
where object_name='V_TEST_TAB';——檢視的狀態:有效
思考:若上述程式碼修改的不是列長,而是表名,結果又會如何?
1.5 刪除檢視
可以刪除當前模式中的任何檢視;
如果要刪除其他模式中的檢視,必須擁有DROP ANY VIEW系統許可權;
檢視被刪除後,該檢視的定義會從詞典中被刪除,並且在該檢視上授予的“許可權”也將被刪除。
檢視被刪除後,其他引用該檢視的檢視及儲存過程等都會失效。
示例1:drop view vw_test_tab;
使用資料字典檢視
dba_views——DBA檢視描述資料庫中的所有檢視
all_views——ALL檢視描述使用者“可訪問的”檢視
user_views——USER檢視描述“使用者擁有的”檢視
all_tab_columns——ALL檢視描述使用者“可訪問的”檢視的列(或表的列)
user_tab_columns——USER檢視描述“使用者擁有的”檢視的列(或表的列)
desc user_views;
set long 400;
select view_name,text from user_views;
示例1:查詢當前方案中指定檢視(或表)的列名資訊
select * from user_tab_columns where table_name='VW_DEPT';
select 檢視沒有什麼限制,但insert/delete/update有一些限制
第一步:將針對檢視的SQL語句與檢視的定義語句(儲存在資料字典中)“合併”成一條SQL語句
第二步:在記憶體結構的共享SQL區中“解析”(並最佳化)合併後的SQL語句
第三步:“執行”SQL語句
示例:
假設檢視v_emp的定義語句如下:
create view v_emp
as
select empno,ename,loc
from employees emp,departments dept
where emp.deptno=dept.deptno and dept.deptno=10;
select ename from v_emp
where empno=9876;
select ename
from employees emp,departments dept
where emp.deptno=dept.deptno and dept.deptno=10
and empno=9876;
使用資料字典檢視
dba_updatable_columns——顯示資料庫所有檢視中的所有列的可更新狀態
all_updatable_columns——顯示使用者可訪問的檢視中的所有列的可更新狀態
user_updatable_columns——顯示使用者擁有的檢視中的所有列的可更新狀態
示例1: 查詢v_stu_dept中的哪些列是可更新的
select table_name,column_name,insertable,updatable,deletable
from user_updatable_columns
where table_name='V_STU_DEPT';
如果建立連線檢視的select查詢“不包含”如下結構,
並且遵守連線檢視的“更新準則”,
則這樣的連線檢視是“可更新”的:
之一:集合運算子(union,intersect,minus)
之二:DISTINCT關鍵字
之三:GROUP BY,ORDER BY,CONNECT BY或START WITH子句
之四:子查詢
之五:分組函式
之六:需要更新的列不是由“列表示式”定義的
之七:基表中所有NOT NULL列均屬於該檢視
如果連線檢視中的一個“基表的鍵”(主鍵、唯一鍵)在它的檢視中仍然存在,
並且“基表的鍵”仍然是“連線檢視中的鍵”(主鍵、唯一鍵);
即,某列在基表中是主鍵|唯一鍵,在檢視中仍然是主鍵|唯一鍵
則稱這個基表為“鍵值儲存表”。
一般地,由主外來鍵關係的2個表組成的連線檢視,外來鍵表就是鍵值儲存表,而主鍵表不是。
之一:一般準則——(講)
任何DML操作,只能對檢視中的鍵值儲存表進行更新,
即,“不能透過連線檢視修改多個基表”;
在DML操作中,“只能使用連線檢視定義過的列”;
“自連線檢視”的所有列都是可更新(增刪改)的
之二:insert準則
在insert語句中不能使用“非鍵值儲存表”中的列(包括“連線列”);
執行insert操作的檢視,至少應該“包含”鍵值儲存表中所有設定了約束的列;
如果在定義連線檢視時使用了WITH CHECK OPTION 選項,
則“不能”針對連線檢視執行insert操作
之三:update準則
鍵值儲存表中的列是可以更新的;
如果在定義連線檢視時使用了WITH CHECK OPTION 選項,
則連線檢視中的連線列(一般就是“共有列”)和基表中的“其他共有列”是“不可”更新的,
連線列和共有列之外的其他列是“可以”更新的
之四:delete準則
如果在定義連線檢視時使用了WITH CHECK OPTION 選項,
依然“可以”針對連線檢視執行delete操作
資料庫中索引的概念與書索引的概念非常類似,
不同之處在於資料庫索引用來在表中查詢特定的行。
索引缺點:
向表中“新增/刪除”行時,必須花費額外的時間來更新該行的索引。
建立索引的時機:
當需要從大表中檢索少數幾行時,都應該對列建立索引。
基本準則:
當任何單個查詢要檢索的行<=整個錶行數的10%時,索引就非常有用。
索引的候選列:
應該是用來儲存很大範圍的值的列
自動建立索引:
表的主鍵和唯一鍵將自動建立索引
2.1 語法
create [unique] index 索引名 -------unique指定索引列中的值是唯一的,索引名建議以idx打頭
on 表名(列1, 列2... ...) --------可以對多列建立索引,這種索引稱為“複合索引”
[tablespace 表空間名]; --------省去後,索引將被儲存到使用者的預設表空間中
提示:
出於效能方面的原因,通常應該將索引與表儲存到不同的表空間中
示例:
create index idx_customers_lastname on customers(last_name);
2.2 查詢索引和索引列的資訊
select * from user_indexes where table_name in('CUSTOMERS', 'EMPLOYEES');
select * from user_ind_columns where table_name in('CUSTOMERS', 'EMPLOYEES');
2.3 修改索引
alter index 索引名 rename to 新索引名
2.4 刪除索引
drop index 索引名
另外使用force可以強制建立基表不存在的view。
實驗如下:
SQL> drop view b;
檢視已刪除。
SQL> conn hr/hr
已連線。
SQL> create or replace force view b as select * from j ;
警告: 建立的檢視帶有編譯錯誤。
SQL> select view_name from user_views where view_name=upper('b');
VIEW_NAME
------------------------------
B
SQL> create or replace force view v as select * from mm;
警告: 建立的檢視帶有編譯錯誤。
SQL> create or replace force view love as select * from plmm;
警告: 建立的檢視帶有編譯錯誤。
SQL> desc plmm;
ERROR:
ORA-04043: 物件 plmm 不存在
SQL> drop love
2 /
drop love
*
第 1 行出現錯誤:
ORA-00950: 無效 DROP 選項
SQL> drop view log;
drop view log
*
第 1 行出現錯誤:
ORA-00942: 表或檢視不存在
SQL> drop view love;
檢視已刪除。
SQL>
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/564597/viewspace-981414/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 專案需求討論-標題欄上的搜尋功能
- 討論幾種資料列Column的特性(上)
- 關於資料庫 Block 儲存細節問題的討論資料庫BloC
- 解決 PbootCMS 搜尋未搜尋到任何資料的問題boot
- 一個XML資料統計問題,期待大家的討論XML
- 各位道友,進來討論個介面設計問題!
- 板橋,J道上搜尋有問題呀,搜尋出來的都是亂碼!麻煩你解決一下!
- XMPP從今天開始強制加密加密
- 提一個巨難的問題,請高手們來討論..
- 資料倉儲資料質量的問題探討(轉)
- 推薦幾個高效文章搜尋網站網站
- 新一代海量資料搜尋引擎 TurboSearch 來了!
- 資料檢視的重複問題
- 檢視gcc搜尋路徑GC
- 討論幾種資料列Column的特性(下)
- 聽說最近國內有個搜尋引擎很火,今天我就以程式設計師視角帶大家來體驗一下程式設計師
- 資料分析主題討論
- 網站搜尋引擎優化問題網站優化
- 上週我面了個三年 Javaer,這幾個問題都沒答出來Java
- 在個稅App上填資料遇到的這些問題,權威解答來了APP
- Fiori UI上my contact加了Dr. 後搜尋不出資料的奇怪問題UI
- 我來討論一下純理論的老問題.長方形和正方形的繼承關係.繼承
- 跟著辛星深入探討一下PHP的反射機制PHP反射
- 搜尋的未來 谷歌勝算幾何谷歌
- 從一個問題討論到Decode函式返回值函式
- 海量資料搜尋---搜尋引擎
- 加密後的資料如何進行模糊查詢?今天面了 10 個,沒一個能答上來的!加密
- google圖書搜尋上線了~~Go
- 討論個有關模組化設計的問題
- 資料安全認證你有幾個?來了解一下
- 多個資料來源的問題
- 【問題】 檔案搜尋
- MySQL資料庫檢視:檢視定義、建立檢視、修改檢視MySql資料庫
- 關於BSS資料化轉型的幾點討論
- 智商狂飆,問了ChatGPT幾個資料庫問題後,我的眼鏡掉了ChatGPT資料庫
- 資料倉儲專題(4)-分散式資料倉儲事實表設計思考---討論精華分散式
- bang 的論壇也沒徹底解決中文搜尋問題
- 關於搜尋地址的問題