SQL 基礎之創建其他方案對象（十五）

 視圖概述：

– 從視圖中創建、修改和檢索數據

– 在視圖上的數據操縱語言（DML）操作

– 刪除視圖

數據庫對象

什么是視圖？我的理解就是從一張表或多張表創建一個自定義的關聯虛擬表

視圖有如下幾個優點：

•  限制數據訪問

•  簡化查詢

•  數據獨立性

•  避免重復訪問相同的數據

簡單視圖和復雜視圖

創建視圖

 CREATE VIEW 子句中嵌入子查詢： 

create [or replace] [force|noforce] view myview

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

as subquery

[with check option [constraint constraint]]

[with read only [constraint constraint]];

• 子查詢可以包含復雜的 SELECT 語法

• With check option: 防止插入不可見的行，防止從視圖中丟失的更新。

1、創建視圖emp80，包含部門為80的員工詳細信息：

create view  emp80

as select employee_id, last_name, salary

from employees

where department_id = 80;

使用 SQL*Plus 的 DESCRIBE 命令描述視圖結構

desc emp80

2、在子查詢中使用列別名創建視圖：

create view  salv50

as select employee_id id_number, last_name name,

salary*12 ann_salary

from employees

where department_id = 50;

在選擇視圖中的列時應使用別名

修改視圖

使用CREATE OR REPLACE VIEW 子句修改EMPVU80視圖。為每一列都增加別名：

 create or replace view emp80

(id_number, name, sal, department_id)

as select employee_id, first_name || ' '

|| last_name, salary, department_id

from employees

where department_id = 80;

在 CREATE OR REPLACE VIEW 子句中列出來的別名要與子查詢中各列相對應

創建復雜視圖

創建一個包含組函數，從兩張表中顯示數據的復雜視圖：

create or replace 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 join departments d

on (e.department_id = d.department_id)

group by d.department_name;

視圖上執行 DML  操作的規則-- DELETE

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

當視圖定義中包含以下元素之一時不能刪除行

– 組函數

– GROUP BY 子句

– DISTINCT 關鍵字

– ROWNUM 偽列

視圖上執行 DML  操作的規則-- UPDATE

當視圖定義中包含以下元素之一時不能修改數據：

– 組函數

– GROUP BY 子句

– DISTINCT 關鍵字

– ROWNUM 偽列

– 表達式定義的列

視圖上執行 DML 操作的規則-- INSERT

 當視圖定義中包含以下元素之一時不能插入數據：

– 組函數

– GROUP BY 子句

– DISTINCT 關鍵字

– ROWNUM 偽列

– 表達式定義的列

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

 WITH CHECK OPTION 約束

使用 WITH CHECK OPTION 子句確保DML只能在特定的范圍內執行：

create or replace view empvu20

as select *

from employees

where department_id = 20

with check option constraint empvu20_ck ; 

任何違反WITH CHECK OPTION 約束的請求都會失敗

屏蔽 DML  操作

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

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

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;

刪除視圖

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

drop view emp80;

drop view slav;

序列

•  自動提供唯一的數值

•  共享對象

•  主要用于提供主鍵值

•  可代替應用程序生成序號

•  將序列值緩存到內存中，可以提高訪問效率

CREATE SEQUENCE  語法

 定義一個序列自動生成連續的數字：

 create sequence sequence

[increment by n]

[start with n]

[{maxvalue n | nomaxvalue}]

[{minvalue n | nominvalue}]

[{cycle | nocycle}]

[{cache n | nocache}];

•  創建序列 DEPT_DEPTID_SEQ 為表 DEPARTMENTS 提供主鍵。

•  不是用 CYCLE 選項

create sequence dept_deptid_seq

increment by 10

start with 120

maxvalue 9999

nocache

nocycle;

NEXTVAL 和 和 CURRVAL偽列

• NEXTVAL 返回下一個可用的序列值。它返回一個唯一的值每次引用它的時候，任何用戶都可以引用它

• CURRVAL得到當前的序列值

• 使用 CURRVAL 之前必須發出 NEXTVAL

使用序列

插入一個新的部門為“Support” 位置ID為 2500

insert into departments(department_id,

department_name, location_id)

values (dept_deptid_seq.nextval,

'support', 2500);

序列 DEPT_DEPTID_SEQ 顯示當前值

select dept_deptid_seq.currval from dual;

緩存序列值 

• 將序列值緩存到內存中，可提高訪問效率

• 序列在下列情況下出現“斷號”：

      – 發生回滾

      – 系統崩潰

      – 序列用于其他的表

可以修改序列的增量、最大值、最小值，循環選項或緩存：

alter sequence dept_deptid_seq

increment by 20

maxvalue 999999

nocache

nocycle;

修改序列的注意事項

• 必須是序列的擁有者或對序列有 ALTER 權限

• 只有將來的序列值會被改變

• 改變序列的初始值只能通過刪除序列之后重建序列的方法實現

• 執行一些驗證（例如，新的 MAXVALUE小于當前的序列號）

使用 DROP 語句刪除序列：

drop sequence dept_deptid_seq;

索引:

是一個方案對象

通過指針加速 Oracle 服務器的查詢速度

通過使用快速路徑訪問方法來快速定位數據，可以減少磁盤I/O

索引與表相互獨立

Oracle 服務器自動使用和維護索引

創建索引：

自動創建：在定義 PRIMARY KEY 或 UNIQUE 約束后系統自動在相應的列上創建唯一性索引。

手動創建：用戶可以在其它列上創建非唯一的索引，以加速查詢。

在一列或多列上創建索引語法：

create [unique][bitmap]index index

on table (column[, column]...);

2、在表 EMPLOYEES的 LAST_NAME字段上創建索引，提高查詢訪問速度：

create index emp_last_name_idx on  employees(last_name);

創建索引注意事項

刪除索引

使用 DROP INDEX 命令，從數據字典中刪除索引：

drop index index;

從數據字典中刪除索引：emp_last_name_idx

drop index emp_last_name_idx;

刪除索引，你必須是索引的擁有者或者擁有 DROP ANY INDEX 權限。

同義詞

創建對象的同義詞

通過創建同義詞簡化對象訪問(一個對象的另一個名字)，使用同義詞您可以：

方便訪問其它用戶的對象

縮短對象名字的長度

創建同義詞語法：

create [public] synonym synonym for object;

創建和刪除同義詞示例

1、為視圖 DEPT_SUM_VU 創建一個較短名稱的同義詞：

create synonym d_sum for dept_sum_vu;

2、刪除同義詞

drop synonym d_sum；