oracle視圖總結（創建、查詢、修改、刪除等）

oracle視圖總結（創建、查詢、修改、刪除等）

視圖定義：

— —視圖是一種虛表。

— —視圖建立在已有表的基礎上, 視圖賴以建立的這些表稱為基表。
— —向視圖提供數據內容的語句為 SELECT 語句, 可以將視圖理解為存儲起來的 SELECT 語句.
— —視圖向用戶提供基表數據的另一種表現形式

為什麼使用視圖？

— —控制數據訪問
— —簡化查詢
— —避免重復訪問相同的數據

創建視圖：在 CREATE VIEW 語句中嵌入子查詢，子查詢可以是復雜的 SELECT 語句

<span style="font-size:14px;">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</span>

創建視圖時在子查詢中給列定義別名：在選擇視圖中的列時應使用別名

<span style="font-size:14px;">CREATE VIEW  salvu50
AS 
SELECT  employee_id  ID_NUMBER, 
            last_name    NAME,
            salary*12    ANN_SALARY
FROM    employees
WHERE   department_id = 50;
View created.</span>

查詢視圖：SELECT * FROM salvu50;

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

<span style="font-size:14px;">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;
View created.</span>

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

創建復雜視圖舉例：

<span style="font-size:14px;">CREATE VIEW	dept_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;
View created.</span>

視圖中使用DML的規定：

可以在簡單視圖中執行 DML 操作
當視圖定義中包含以下元素之一時不能使用delete:
— —組函數
— —GROUP BY 子句
— —DISTINCT 關鍵字
— —ROWNUM 偽列

<span style="font-size:14px;">create or replace view sal_view
as select
avg(salary) avg_sal from employees
group by department_id
</span>

當視圖定義中包含以下元素之一時不能使用update:
— —組函數
— —GROUP BY子句
— —DISTINCT 關鍵字
— —ROWNUM 偽列
— —列的定義為表達式

當視圖定義中包含以下元素之一時不能使insert:
— —組函數
— —GROUP BY 子句
— —DISTINCT 關鍵字
— —ROWNUM 偽列
— —列的定義為表達式
— —表中非空的列在視圖定義中未包括

屏蔽 DML 操作：

可以使用 WITH READ ONLY 選項屏蔽對視圖的DML 操作
任何 DML 操作都會返回一個Oracle server 錯誤

<span style="font-size:14px;">CREATE OR REPLACE VIEW empvu10
    (employee_number, employee_name, job_title)
AS SELECT	employee_id, last_name, job_id
   FROM     employees
   WHERE    department_id = 10
   WITH READ ONLY;
View created.</span>

刪除視圖：刪除視圖只是刪除視圖的定義，並不會刪除基表的數據

<span style="font-size:14px;">DROP VIEW empvu80;
View dropped.</span>

Top-N 分析：

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

<span style="font-size:14px;">SELECT [column_list], ROWNUM  
FROM   (SELECT [column_list] 
        FROM table
        ORDER  BY Top-N_column)
WHERE  ROWNUM <=  N;</span>

注意:

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

<span style="font-size:14px;">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</span>