視圖(學習筆記)，視圖學習筆記

視圖(學習筆記)，視圖學習筆記

視圖

視圖是從一個或幾個實體表（或視圖）導出的表。它與實體表不同，視圖本身是一個不包含任何真實數據的虛擬表。數據庫中只存放視圖的定義，而不存放視圖對應的數據，這些數據仍存放在原來的實體表中。所以實體表中的數據發生變化，從視圖中查詢出的數據也就隨之改變了。從這個意義上講，視圖就像一個窗口，通過它可以看到數據庫中自己感興趣的數據及其變化。    

視圖的優點:

• 視圖能夠簡化用戶的操作；
• 視圖使用戶能以多種角度看待同一數據；
• 視圖對重構數據庫提供了一定程度的邏輯獨立性；
• 視圖能夠對機密數據提供安全保護；
• 適當的利用視圖可以更清晰的表達查詢；

創建視圖語法

CREATE [FORCE | NOFORCE] [OR REPLACE] VIEW 視圖名稱 [(別名1,別名2,...)]

AS

子查詢 ;

語法參數：在創建視圖中的主要參數解釋如下： FORCE：表示要創建視圖的表不存在也可以創建視圖； NOFORCE：（默認）表示要創建視圖的表必須存在，否則無法創建； OR REPLACE：表示視圖的替換，如果創建的視圖不存在則創建新的，如果視圖已經存在，則將其進行替換。  

授權創建視圖的權限

 

GRANT CREATE VIEW TO tests;  --tests為用戶

 

簡單視圖:

示例一、創建一張基本工資大於2000的視圖

CREATE VIEW v_empview
AS
SELECT * FROM emp WHERE sal>2000;

查詢視圖是否創建成功

SELECT * FROM tab WHERE tabtype='VIEW';

查詢v_empview視圖

SELECT * FROM v_empview;

查詢視圖的具體信息

SELECT view_name,text_length,text FROM User_Views;

示例二、創建一張只包含20部門員工信息的視圖

CREATE OR REPLACE VIEW v_emp20
AS
SELECT * FROM emp WHERE deptno=20;

--查詢是否創建成功
SELECT * FROM tab WHERE tabtype='VIEW';
--查詢myemponly_dept20視圖
SELECT * FROM v_myemponly_dept20;

在視圖下執行DML操作

示例三、

創建一張只包含20部門員工信息的視圖，並增加一條信息

CREATE OR REPLACE VIEW v_emp20
AS
SELECT empno,ename,job,sal,deptno FROM emp WHERE deptno=20;

--查詢v_myview視圖
SELECT * FROM v_emp20;

--向v_emp20視圖中增加一條數據
INSERT INTO v_emp20(EMPNO,ENAME,JOB,sal,deptno)VALUES(8888,'JAVA','CLERK',1000,20);
COMMIT;
--查詢v_myview視圖
SELECT * FROM v_emp20;
--結果增加成功

示例四、對視圖執行修改操作

UPDATE v_emp20 SET ename='ORACLE',job='MANAGER',sal=2000 WHERE empno=8888;
COMMIT;
--查詢v_myview視圖
SELECT * FROM v_emp20;

示例五、刪除v_emp20視圖中的數據

DELETE FROM v_emp20 WHERE empno=8888;
COMMIT;
--查詢v_myview視圖
SELECT * FROM v_emp20;

復雜視圖

包含多個表的映射的視圖

示例六、顯示每個部門的詳細信息

CREATE OR REPLACE VIEW v_myview
AS
SELECT d.deptno,d.dname,d.loc,
COUNT(e.empno) COUNT,NVL(ROUND(AVG(sal),2),0) AVG, NVL(SUM(sal),0) SUM,
NVL(MAX(sal),0) MAX ,NVL(MIN(sal),0) MIN
FROM emp e,dept d
WHERE e.deptno(+)=d.deptno
GROUP BY d.deptno,d.dname,d.loc
--查詢v_myview視圖
SELECT * FROM v_myview;

示例七、為視圖中的查詢的列起別名

CREATE OR REPLACE VIEW v_myview
(部門編號，部門名稱，部門位置，人數，平均工資，總工資，最高工資，最低工資)
AS
SELECT d.deptno,d.dname,d.loc,
COUNT(e.empno) COUNT,NVL(ROUND(AVG(sal),2),0) AVG, NVL(SUM(sal),0) SUM,
NVL(MAX(sal),0) MAX ,NVL(MIN(sal),0) MIN
FROM emp e,dept d
WHERE e.deptno(+)=d.deptno
GROUP BY d.deptno,d.dname,d.loc
--查詢v_myview視圖
SELECT * FROM v_myview;

對於簡單視圖，可以直接執行INSERT，UPDATE和DELETE操作
但是對於復雜視圖，不允許直接執行INSERT，UPDATE和DELETE操作。當視圖符合以下任何一種情況時，都不允許直接執行DML操作。具體情況如下：
1）具有集合操作符（UNION，UNION ALL，INTERSECT，MINUS）；
2）具有分組函數（MIN,MAX,SUM,AVG,COUNT等）；
3）具有GROUP BY，CONNECT BY或START WITH等子句；
4）具有DISTINCT關鍵字；
5）具有連接查詢；
為了在具有以上情況的復雜視圖上執行DML操作需要征用觸發器來完成

 

WITH CHECK OPTION 子句----保證視圖的創建條件不被更改

在創建視圖的時候有時候需要使用一些WHERE子句做一些條件的限制，
但是默認情況下的視圖創建完成之後，是可以通過視圖去修改在WHERE子句之中所使用的字段內容的，
而在此時就需要通過WITH CHECK OPTION子句來保證視圖的創建條件不被更新。
語法:

     

WITH CHECK OPTION子句
CREATE [FORCE | NOFORCE] [OR REPLACE] VIEW 視圖名稱 [(別名1,別名2,...)]
        AS 
    子查詢 [WITH CHECK OPTION [ CONSTRAINT 約束名稱] ];

示例八、創建一張只包含20部門人數的視圖,添加約束,部門編號不可更改

CREATE OR REPLACE VIEW v_emp20
AS
SELECT * FROM emp WHERE deptno=20
WITH CHECK OPTION CONSTRAINT v_emp20_ck;---constraint後為可選不寫也可以
--查詢v_myview視圖
SELECT * FROM v_emp20;

--如果現在更新數據並將部門編號更改為其它就會報錯並提示違返了where條件
UPDATE v_emp20 SET deptno=40 WHERE empno=7369;

WITH READ ONLY子句--讓視圖中所有字段不可更新

語法:

 

WITH READ ONLY子句
CREATE [FORCE | NOFORCE] [OR REPLACE] VIEW 視圖名稱 [(別名1,別名2,...)]
        AS 
    子查詢 [WITH CHECK OPTION [ CONSTRAINT 約束名稱] ]
    [WITH READ ONLY] ;

 

示例八、創建一張只包含20部門人數的視圖,添加約束設置為只讀

CREATE OR REPLACE VIEW v_emp20
AS
SELECT * FROM emp WHERE deptno=20
WITH READ ONLY;                                 --設置為只讀屬性
--查詢v_myview視圖
SELECT * FROM v_emp20;

--如果現在更新數據就會報錯並提示違返了where條件
UPDATE v_emp20 SET ename='TTTT',comm=NULL WHERE empno=7369;

刪除視圖

DROP VIEW 視圖名

示例九、刪除v_myview視圖

DROP VIEW v_myview;

--查詢user_views數據字典
SELECT view_name,text_length,text,read_only FROM user_views;