SQL 基礎(chǔ)之創(chuàng)建其他方案對(duì)象（十五）

 視圖概述：

– 從視圖中創(chuàng)建、修改和檢索數(shù)據(jù)

– 在視圖上的數(shù)據(jù)操縱語(yǔ)言（DML）操作

– 刪除視圖

數(shù)據(jù)庫(kù)對(duì)象

什么是視圖？我的理解就是從一張表或多張表創(chuàng)建一個(gè)自定義的關(guān)聯(lián)虛擬表

視圖有如下幾個(gè)優(yōu)點(diǎn)：

•  限制數(shù)據(jù)訪問(wèn)

•  簡(jiǎn)化查詢

•  數(shù)據(jù)獨(dú)立性

•  避免重復(fù)訪問(wèn)相同的數(shù)據(jù)

簡(jiǎn)單視圖和復(fù)雜視圖

創(chuàng)建視圖

 CREATE VIEW 子句中嵌入子查詢： 

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

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

as subquery

[with check option [constraint constraint]]

[with read only [constraint constraint]];

• 子查詢可以包含復(fù)雜的 SELECT 語(yǔ)法

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

1、創(chuàng)建視圖emp80，包含部門(mén)為80的員工詳細(xì)信息：

create view  emp80

as select employee_id, last_name, salary

from employees

where department_id = 80;

使用 SQL*Plus 的 DESCRIBE 命令描述視圖結(jié)構(gòu)

desc emp80

2、在子查詢中使用列別名創(chuàng)建視圖：

create view  salv50

as select employee_id id_number, last_name name,

salary*12 ann_salary

from employees

where department_id = 50;

在選擇視圖中的列時(shí)應(yīng)使用別名

修改視圖

使用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 子句中列出來(lái)的別名要與子查詢中各列相對(duì)應(yīng)

創(chuàng)建復(fù)雜視圖

創(chuàng)建一個(gè)包含組函數(shù)，從兩張表中顯示數(shù)據(jù)的復(fù)雜視圖：

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;

視圖上執(zhí)行 DML  操作的規(guī)則-- DELETE

可以在簡(jiǎn)單視圖上執(zhí)行DML操作

當(dāng)視圖定義中包含以下元素之一時(shí)不能刪除行

– 組函數(shù)

– GROUP BY 子句

– DISTINCT 關(guān)鍵字

– ROWNUM 偽列

視圖上執(zhí)行 DML  操作的規(guī)則-- UPDATE

當(dāng)視圖定義中包含以下元素之一時(shí)不能修改數(shù)據(jù)：

– 組函數(shù)

– GROUP BY 子句

– DISTINCT 關(guān)鍵字

– ROWNUM 偽列

– 表達(dá)式定義的列

視圖上執(zhí)行 DML 操作的規(guī)則-- INSERT

 當(dāng)視圖定義中包含以下元素之一時(shí)不能插入數(shù)據(jù)：

– 組函數(shù)

– GROUP BY 子句

– DISTINCT 關(guān)鍵字

– ROWNUM 偽列

– 表達(dá)式定義的列

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

 WITH CHECK OPTION 約束

使用 WITH CHECK OPTION 子句確保DML只能在特定的范圍內(nèi)執(zhí)行：

create or replace view empvu20

as select *

from employees

where department_id = 20

with check option constraint empvu20_ck ; 

任何違反WITH CHECK OPTION 約束的請(qǐng)求都會(huì)失敗

屏蔽 DML  操作

• 可以使用 WITH READ ONLY 選項(xiàng)屏蔽對(duì)視圖的DML 操作

• 任何 DML 操作都會(huì)返回一個(gè)Oracle server 錯(cuò)誤

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;

刪除視圖

刪除視圖只是刪除視圖的定義，并不會(huì)刪除基表的數(shù)據(jù)

drop view emp80;

drop view slav;

序列

•  自動(dòng)提供唯一的數(shù)值

•  共享對(duì)象

•  主要用于提供主鍵值

•  可代替應(yīng)用程序生成序號(hào)

•  將序列值緩存到內(nèi)存中，可以提高訪問(wèn)效率

CREATE SEQUENCE  語(yǔ)法

 定義一個(gè)序列自動(dòng)生成連續(xù)的數(shù)字：

 create sequence sequence

[increment by n]

[start with n]

[{maxvalue n | nomaxvalue}]

[{minvalue n | nominvalue}]

[{cycle | nocycle}]

[{cache n | nocache}];

•  創(chuàng)建序列 DEPT_DEPTID_SEQ 為表 DEPARTMENTS 提供主鍵。

•  不是用 CYCLE 選項(xiàng)

create sequence dept_deptid_seq

increment by 10

start with 120

maxvalue 9999

nocache

nocycle;

NEXTVAL 和 和 CURRVAL偽列

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

• CURRVAL得到當(dāng)前的序列值

• 使用 CURRVAL 之前必須發(fā)出 NEXTVAL

使用序列

插入一個(gè)新的部門(mén)為“Support” 位置ID為 2500

insert into departments(department_id,

department_name, location_id)

values (dept_deptid_seq.nextval,

'support', 2500);

序列 DEPT_DEPTID_SEQ 顯示當(dāng)前值

select dept_deptid_seq.currval from dual;

緩存序列值 

• 將序列值緩存到內(nèi)存中，可提高訪問(wèn)效率

• 序列在下列情況下出現(xiàn)“斷號(hào)”：

      – 發(fā)生回滾

      – 系統(tǒng)崩潰

      – 序列用于其他的表

可以修改序列的增量、最大值、最小值，循環(huán)選項(xiàng)或緩存：

alter sequence dept_deptid_seq

increment by 20

maxvalue 999999

nocache

nocycle;

修改序列的注意事項(xiàng)

• 必須是序列的擁有者或?qū)π蛄杏?ALTER 權(quán)限

• 只有將來(lái)的序列值會(huì)被改變

• 改變序列的初始值只能通過(guò)刪除序列之后重建序列的方法實(shí)現(xiàn)

• 執(zhí)行一些驗(yàn)證（例如，新的 MAXVALUE小于當(dāng)前的序列號(hào)）

使用 DROP 語(yǔ)句刪除序列：

drop sequence dept_deptid_seq;

索引:

是一個(gè)方案對(duì)象

通過(guò)指針加速 Oracle 服務(wù)器的查詢速度

通過(guò)使用快速路徑訪問(wèn)方法來(lái)快速定位數(shù)據(jù)，可以減少磁盤(pán)I/O

索引與表相互獨(dú)立

Oracle 服務(wù)器自動(dòng)使用和維護(hù)索引

創(chuàng)建索引：

自動(dòng)創(chuàng)建：在定義 PRIMARY KEY 或 UNIQUE 約束后系統(tǒng)自動(dòng)在相應(yīng)的列上創(chuàng)建唯一性索引。

手動(dòng)創(chuàng)建：用戶可以在其它列上創(chuàng)建非唯一的索引，以加速查詢。

在一列或多列上創(chuàng)建索引語(yǔ)法：

create [unique][bitmap]index index

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

2、在表 EMPLOYEES的 LAST_NAME字段上創(chuàng)建索引，提高查詢?cè)L問(wèn)速度：

create index emp_last_name_idx on  employees(last_name);

創(chuàng)建索引注意事項(xiàng)

刪除索引

使用 DROP INDEX 命令，從數(shù)據(jù)字典中刪除索引：

drop index index;

從數(shù)據(jù)字典中刪除索引：emp_last_name_idx

drop index emp_last_name_idx;

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

同義詞

創(chuàng)建對(duì)象的同義詞

通過(guò)創(chuàng)建同義詞簡(jiǎn)化對(duì)象訪問(wèn)(一個(gè)對(duì)象的另一個(gè)名字)，使用同義詞您可以：

方便訪問(wèn)其它用戶的對(duì)象

縮短對(duì)象名字的長(zhǎng)度

創(chuàng)建同義詞語(yǔ)法：

create [public] synonym synonym for object;

創(chuàng)建和刪除同義詞示例

1、為視圖 DEPT_SUM_VU 創(chuàng)建一個(gè)較短名稱的同義詞：

create synonym d_sum for dept_sum_vu;

2、刪除同義詞

drop synonym d_sum；