oracle視圖基礎詳解與實例

摘要：本篇筆記記錄關於視圖的一些認識、從概念、優缺點到應用以及注意事項。

一：概念

視圖是基於一張表或多張表或另外一個視圖的邏輯表。視圖不同於表，視圖本身不包含任何數據。表是實際獨立存在的實體，是用於存儲數據的基本結構。而視圖只是一種定義，對應一個查詢語句。視圖的數據都來自於某些表，這些表被稱為基表。數據庫中只在數據字典中存儲對視圖的定義。

二：優點

1、為用戶集中數據，簡化用戶的數據查詢和處理。

2、屏蔽數據庫的復雜性，用戶不必了解數據庫的復雜性。

3、簡化用戶權限的管理，只授予用戶使用視圖的權限。

4、可以提高數據訪問的安全性，通過視圖往往只可以訪問數據庫中表的特定部分，限制了用戶訪問表的全部行和列。

5、便於數據共享，多個用戶不必都定義所需的數據。

三：視圖類別

1、簡單視圖

指基於單個表並且不包含函數或表達式的視圖，在該視圖上可以執行DML語句（即可執行增、刪、改操作）。

2、復雜視圖

指基於單個或者多個表或者包含函數、表達式或者分組數據的視圖，在該視圖上執行DML語句時必須要符合特定條件。注意：在定義復雜視圖時必須為函數或表達式定義別名

3、連接視圖

指基於多個表建立的視圖，一般來說不會在該視圖上執行INSERT、UPDATE、DELETE操作。

4、只讀視圖

指只允許進行SELECT操作的視圖，在該視圖時指定WITH READ ONLY選項。該視圖上不能執行INSERT、UPDATE、DELETE操作。

5、check約束視圖

WITH CHECK OPTION用於在視圖上定義CHECK約束,即在該視圖上執行INSERT或UPDATE操作時,數據必須符合查詢結果.

四：創建視圖

1、基本語法

CREATE [OR REPLACE] VIEW  view_name  [(column_name1[,column_name2…
 AS 
select_statement 
[WITH CHECK OPTION] 
[WITH READ ONLY]

2、參數說明

CREATE OR Repalce：用於創建和修改視圖
WITH CHECK OPTION ：用於創建限制數據訪問的視圖
WITH READ ONLY ：用於創建只讀視圖

3、DML操作遵循的原則

a）簡單視圖可以執行DML操作。

b）在視圖出現下列情況時不可以通過視圖修改基表數據或插入數據：

i、集合運算符(union,intersect,minus)

ii、DISTINCT關鍵字

iii、GROUP BY，ORDER BY，CONNECT BY或START WITH子句

v、子查詢

vi、分組函數

vii、需要更新的列不是由“列表達式”定義的

vx、基表中所有NOT NULL列均屬於該視圖

五：如何查詢視圖和表的更新權限

select table_name,column_name,updatable,insertable,deletable 
from user_updatable_columns; 

說明

updatable 表示當前字段是否可以執行修改操作

insertable 表示當前字段是否可以執行添加操作

deletable 表示當前字段是否可以執行刪除操作

六：示例

--對簡單視圖的操作
drop table emp1;
create table emp1 as select * from emp;

--簡單視圖
create or replace view v_emp1
as
select * from emp1 ;


--查詢
select * from v_emp1;

--更新
update v_emp1 set v_emp1.ENAME='andy' where v_emp1.JOB='CLERK';

--增加
insert into v_emp1 values (7777, 'chy', 'MANAGER', 8888, sysdate, 10000,1111.11,20);

--刪除
delete from v_emp1 where v_emp1.EMPNO=7777;



--復雜視圖、僅兩基表相連、不包含各種分組函數、group by、distinct命令等。
create or replace view v_complex
as
select emp1.ename, emp1.job, dept.dname from emp1, dept where emp1.deptno=dept.deptno
with check option ;

--查詢
select * from v_complex;

--修改
update v_complex set v_complex.ename='andy' where v_complex.job='MANAGER';

--增加
--報錯：ORA-01776：無法通過連接視圖修改多個基表
insert into v_complex (v_complex.ename, v_complex.job, v_complex.dname) values ('chy', 'MANAGER', 'SALES');

--刪除
delete from v_complex where v_complex.ename='chy';

--復雜視圖、包含不能進行DML的元素、一般僅用與查詢、可以加上 with read only;
create or replace view v_complex_readonly
as
--對使用聚合函數的列必須使用別名！
select max(emp1.sal) max_sal from emp1, dept where emp1.deptno=dept.deptno group by dept.deptno
with read only;

--查詢
select * from v_complex_readonly;

--刪除視圖
create or replace view v_for_delete
as
select * from emp
with read only;

drop view v_for_delete;