Oracle基礎筆記十

標籤：oracle   使用者   

第十章 視圖

1.視圖：從表中抽出的邏輯上相關的資料集合。

視圖是一種虛表。 

視圖建立在已有表的基礎上, 視圖賴以建立的這些表稱為基表。

向視圖提供資料內容的語句為 SELECT 語句, 可以將視圖理解為儲存起來的 SELECT 語句. 

視圖向使用者提供基表資料的另一種表現形式

2.為什麼使用視圖

控制資料訪問

簡化查詢

避免重複訪問相同的資料

3.分類：簡單視圖和複雜視圖

特性          簡單視圖       複雜視圖

表的數量        一個      一個或多個

函數            沒有                有

分組            沒有        有

DML 操作        可以      有時可以

4.建立視圖

4.1在 CREATE VIEW 語句中嵌入子查詢

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view

  [(alias[, alias]...)]

 AS subquery

[WITH CHECK OPTION [CONSTRAINT constraint]]

[WITH READ ONLY [CONSTRAINT constraint]];

例子：子查詢可以是複雜的 SELECT 語句

create or replace view empview 

as 

select employee_id emp_id,last_name name,department_name

from employees e,departments d

Where e.department_id = d.department_id

CREATE VIEW empvu80

 AS SELECT  employee_id, last_name, salary

    FROM    employees

    WHERE   department_id = 80;

描述視圖結構  DESCRIBE empvu80

4.2建立視圖時在子查詢中給列定義別名

CREATE VIEW salvu50

 AS SELECT  employee_id ID_NUMBER, last_name NAME,

            salary*12 ANN_SALARY

    FROM    employees

    WHERE   department_id = 50;

查詢檢視  SELECT *

FROMsalvu50;

複雜視圖舉例：

CREATE VIEWdept_sum_vu

  (name, minsal, maxsal, avgsal)

AS SELECT d.department_name, MIN(e.salary), 

             MAX(e.salary),AVG(e.salary)

   FROM      employees e, departments d

   WHERE     e.department_id = d.department_id 

   GROUP BY  d.department_name;

5.修改視圖

使用CREATE OR REPLACE VIEW 子句修改視圖

CREATE OR REPLACE VIEW empvu80

  (id_number, name, sal, department_id)

AS SELECT  employee_id, first_name || ‘ ‘ || last_name, 

           salary, department_id

   FROM    employees

   WHERE   department_id = 80;

CREATE VIEW 子句中各列的別名應和子查詢中各列相對應

視圖中使用DML的規定：

可以在簡單視圖中執行 DML 操作

當視圖定義中包含以下元素之一時不能使用delete:

組函數

GROUP BY 子句

DISTINCT 關鍵字

ROWNUM 偽列

當視圖定義中包含以下元素之一時不能使用update:

組函數

GROUP BY子句

DISTINCT 關鍵字

ROWNUM 偽列

列的定義為運算式

當視圖定義中包含以下元素之一時不能使insert:

組函數

GROUP BY 子句

DISTINCT 關鍵字

ROWNUM 偽列

列的定義為運算式

表中非空的列在視圖定義中未包括

6.屏蔽 DML 操作

可以使用 WITH READ ONLY 選項屏蔽對視圖的DML 操作

任何 DML 操作都會返回一個Oracle server 錯誤

例子：

CREATE OR REPLACE VIEW empvu10

    (employee_number, employee_name, job_title)

AS SELECTemployee_id, last_name, job_id

   FROM     employees

   WHERE    department_id = 10

   WITH READ ONLY;

7.刪除視圖

刪除視圖只是刪除視圖的定義，並不會刪除基表的資料

DROP VIEW view;

8.Top-N 分析

Top-N 分析查詢一個列中最大或最小的 n 個值:

最大和最小的值的集合是 Top-N 分析所關心的 。

查詢最大的幾個值的 Top-N 分析: 

SELECT [column_list], ROWNUM  

FROM   (SELECT [column_list] 

        FROM table

        ORDER  BY Top-N_column)

WHERE  ROWNUM <=  N;

注意: 

對 ROWNUM 只能使用 < 或 <=, 而用 =, >, >= 都將不能返回任何資料。

查詢工資最高的三名員工:

SELECT ROWNUM as RANK, last_name, salary 

FROM  (SELECT last_name,salary FROM employees

       ORDER BY salary DESC)

WHERE ROWNUM <= 3;

select *

from(

select rownum rn,employee_id,salary

from(

select employee_id,salary,last_name

from employees

order by salary desc

)

)where rn <=50 and rn >40

本文出自 “阿成的部落格” 部落格，轉載請與作者聯絡！

Oracle基礎筆記十