Oracle Les11_創(chuàng)建其它數(shù)據(jù)庫(kù)對(duì)象

1、創(chuàng)建其它數(shù)據(jù)庫(kù)對(duì)象創(chuàng)建其它數(shù)據(jù)庫(kù)對(duì)象11 - 2ObjectivesAfter completing this lesson, you should be able to do the following:創(chuàng)建視圖通過(guò)視圖檢索數(shù)據(jù)創(chuàng)建、維護(hù)和使用序列創(chuàng)建和維護(hù)索引創(chuàng)建私有和公有的同義詞11 - 3Database Objects邏輯上代表一個(gè)或多個(gè)表中數(shù)據(jù)的子集視圖View 數(shù)字值生成器序列Sequence 基本的存儲(chǔ)單元，由行和列組成 表Table給出對(duì)象的替代名稱同義詞Synonym 提高某些查詢的性能索引IndexDescriptionObject11 - 4什么是視圖什么是視圖?EMP

2、LOYEES 表表11 - 5視圖的特點(diǎn)視圖的特點(diǎn)限制數(shù)據(jù)訪問(wèn)限制數(shù)據(jù)訪問(wèn)簡(jiǎn)化復(fù)雜的簡(jiǎn)化復(fù)雜的查詢查詢提供提供數(shù)據(jù)的數(shù)據(jù)的獨(dú)立性獨(dú)立性提供相同提供相同數(shù)據(jù)的數(shù)據(jù)的不同視圖不同視圖11 - 6簡(jiǎn)單視圖和復(fù)雜視圖簡(jiǎn)單視圖和復(fù)雜視圖能否否一個(gè)簡(jiǎn)單視圖簡(jiǎn)單視圖是是否包含函數(shù)是是否包含數(shù)據(jù)組一個(gè)或多個(gè)表的數(shù)量不一定能否通過(guò)視圖執(zhí)行DML操作復(fù)雜視圖復(fù)雜視圖特性特性11 - 7創(chuàng)建視圖創(chuàng)建視圖在CREATE VIEW 語(yǔ)句中嵌入了一個(gè)子查詢:在此語(yǔ)法中：OR REPLACE如果該視圖已經(jīng)存在則重新創(chuàng)建它FORCE不管基表是否存在都創(chuàng)建視圖NOFORCE僅當(dāng)基表存在時(shí)才創(chuàng)建視圖（默認(rèn)）view是視圖的名稱a

3、lias指定由視圖的查詢所選擇的表達(dá)式的名稱subquery是一個(gè)完整的SELECT語(yǔ)句（可以在查詢中使用別名）WITH CHECK OPTION指定只能插入或更新那些視圖可以訪問(wèn)的行WITH READ ONLY確保不能對(duì)視圖執(zhí)行DML操作constraint為兩個(gè)WITH選項(xiàng)命名該子查詢可以包含復(fù)雜的SELECT 語(yǔ)句.CREATE OR REPLACE FORCE|NOFORCE VIEW view (alias, alias.) AS subqueryWITH CHECK OPTION CONSTRAINT constraintWITH READ ONLY CONSTRAINT cons

4、traint;11 - 8創(chuàng)建視圖創(chuàng)建視圖創(chuàng)建視圖EMPVU80 , 該視圖要包含部門80中員工的詳細(xì)信息:像描述表的結(jié)構(gòu)信息一樣，使用DESCRIBE描述視圖的結(jié)構(gòu) :DESCRIBE empvu80CREATE VIEW empvu80 AS SELECT employee_id, last_name, salary FROM employees WHERE department_id = 80;11 - 9創(chuàng)建視圖的準(zhǔn)則創(chuàng)建視圖的準(zhǔn)則 子查詢，可以包含復(fù)雜的查詢，包括多表聯(lián)接，組函數(shù)。 如果創(chuàng)建的時(shí)候，使用了WITH CHECK OPTION這種約束，我們可以給此約束自定義約束的名稱，或

5、者我們不進(jìn)行指定，系統(tǒng)將自動(dòng)分配一個(gè)SYS_Cn這種格式的名字給此約束命名. 我們可以使用or replace來(lái)重新創(chuàng)建視圖，以實(shí)現(xiàn)修改的目的11 - 10創(chuàng)建視圖創(chuàng)建視圖在子查詢中使用列別名來(lái)創(chuàng)建視圖:通過(guò)給定的別名從此視圖中選擇列.CREATE VIEW salvu50 AS SELECT employee_id ID_NUMBER, last_name NAME, salary*12 ANN_SALARY FROM employees WHERE department_id = 50;11 - 11SELECT *FROM salvu50;從視圖中檢索數(shù)據(jù)從視圖中檢索數(shù)據(jù)11 - 12

6、從視圖中檢索數(shù)據(jù)從視圖中檢索數(shù)據(jù)Oracle ServerUSER_VIEWSSALVU50SELECT employee_id ID_NUMBER, last_name NAME,salary*12 ANN_SALARYFROM employeesWHERE department_id = 50;EMPLOYEES使用視圖進(jìn)行使用視圖進(jìn)行查詢查詢SELECT *FROM salvu50;12311 - 13修改視圖修改視圖使用CREATE OR REPLACE VIEW子句修改EMPVU80視圖，為每個(gè)列名添加一個(gè)別名:CREATE VIEW子句中的列別名與子查詢中的列排列順序相同.CREA

7、TE 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;11 - 14創(chuàng)建復(fù)雜視圖創(chuàng)建復(fù)雜視圖創(chuàng)建包含分組函數(shù)的復(fù)雜視圖以顯示兩個(gè)表中的值:CREATE OR REPLACE VIEW dept_sum_vu (name, minsal, maxsal, avgsal)AS SELECT d.depart

8、ment_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;11 - 15在視圖上執(zhí)行在視圖上執(zhí)行DML操作的規(guī)則操作的規(guī)則可以在簡(jiǎn)單的視圖上執(zhí)行DML操作.如果視圖包含以下內(nèi)容，則不能通過(guò)視圖刪除行: 分組函數(shù) GROUP BY 子句 DISTINCT 關(guān)鍵字 偽列 ROWNUM 關(guān)鍵字11 - 16在視圖上執(zhí)行在視圖上執(zhí)行DML操作的規(guī)則操作

9、的規(guī)則如果視圖中包含以下內(nèi)容，則不能通過(guò)視圖修改數(shù)據(jù):分組函數(shù)GROUP BY 子句DISTINCT 關(guān)鍵字偽列 ROWNUM 關(guān)鍵字由表達(dá)式定義的列11 - 17在視圖上執(zhí)行在視圖上執(zhí)行DML操作的規(guī)則操作的規(guī)則如果視圖中包含以下內(nèi)容，則不能通過(guò)視圖添加數(shù)據(jù):分組函數(shù)GROUP BY 子句DISTINCT 關(guān)鍵字偽列 ROWNUM 關(guān)鍵字由表達(dá)式定義的列基表中NOT NULL的列未包含在視圖中11 - 18使用使用 WITH CHECK OPTION 子句子句使用 WITH CHECK OPTION 子句可以確保在視圖上執(zhí)行的DML操作發(fā)生在視圖的范圍內(nèi): 嘗試在視圖中更改任一行的部門編號(hào)都

10、不會(huì)成功，因?yàn)樗`反了 WITH CHECK OPTION 約束.With read only;CREATE OR REPLACE VIEW empvu20AS SELECT* FROM employees WHERE department_id = 20 WITH CHECK OPTION CONSTRAINT empvu20_ck ;11 - 19使用使用 WITH CHECK OPTION 子句子句WITH CHECK OPTION子句指定通過(guò)視圖執(zhí)行的INSERT和UPDATE命令不能創(chuàng)建該視圖無(wú)法選擇的行，從而允許對(duì)插入或更新的數(shù)據(jù)強(qiáng)制執(zhí)行數(shù)據(jù)驗(yàn)證檢查。如果試圖對(duì)視圖未選中的行執(zhí)行D

11、ML操作，就會(huì)報(bào)錯(cuò)。上例中，如果部門編號(hào)更改為10，該視圖就無(wú)法看到該員工了，因此不會(huì)更新任何行。所以，使用WITH CHECK OPTION子句，該視圖只能看到部門20的員工，并且不允許通過(guò)該視圖更改這些員工的部門編號(hào)。UPDATE empvu20 SETdepartment_id=10 WHEREemployee_id=201;11 - 20拒絕拒絕DML操作操作通過(guò)在視圖中定義 WITH READ ONLY 選項(xiàng)可以確保不能執(zhí)行DML操作.任何對(duì)視圖中的執(zhí)行 DML 的嘗試都會(huì)收到一個(gè)Oracle server 錯(cuò)誤.11 - 21CREATE OR REPLACE VIEW empvu

12、10 (employee_number, employee_name, job_title)AS SELECTemployee_id, last_name, job_id FROM employees WHERE department_id = 10 WITH READ ONLY ;拒絕拒絕DML操作操作11 - 22刪除視圖刪除視圖因?yàn)橐晥D是基于數(shù)據(jù)庫(kù)中的基表，所以刪除視圖不會(huì)導(dǎo)致丟失數(shù)據(jù).DROP VIEW view;DROP VIEW empvu80;11 - 23序列序列邏輯上代表一個(gè)或多個(gè)表中數(shù)據(jù)的子集視圖View 數(shù)字值生成器序列Sequence 基本的存儲(chǔ)單元，由行和列組成 表T

13、able給出對(duì)象的替代名稱同義詞Synonym 提高某些查詢的性能索引IndexDescriptionObject11 - 24什么是序列什么是序列序列具有以下特性:自動(dòng)生成唯一編號(hào)是一個(gè)可共享的對(duì)象通常用于創(chuàng)建主鍵值替換應(yīng)用程序代碼如果將序列緩存到內(nèi)存中，可以提高訪問(wèn)序列值的效率2435687109111 - 25CREATE SEQUENCE 語(yǔ)句語(yǔ)法語(yǔ)句語(yǔ)法定義一個(gè)可以自動(dòng)生成序列號(hào)的序列:sequence是序列生成器的名稱INCREMENT BY n指定序列號(hào)之間的間隔，n是一個(gè)整數(shù)，如果省略，則為1START WITH n指定生成的第一個(gè)序列號(hào)，如果省略，則為1MAXVALUE n指

14、定序列可以生成的最大值NOMAXVALUE指定10的27次冪做為序列的最大值MINVALUE n指定序列的最小值NOMINVALUE指定-（1026）作為遞減序列的最小值CYCLE|NOCYCLE指定達(dá)到最大或最小值后，序列是否繼續(xù)生成值，默認(rèn)NOCACHE n|NOCACHE指定Oracle服務(wù)器預(yù)先分配并保留內(nèi)存中的值的數(shù)量，默認(rèn)20個(gè)CREATE SEQUENCE sequence INCREMENT BY n START WITH n MAXVALUE n | NOMAXVALUE MINVALUE n | NOMINVALUE CYCLE | NOCYCLE CACHE n | NO

15、CACHE;11 - 26創(chuàng)建序列創(chuàng)建序列創(chuàng)建一個(gè)名為 DEPT_DEPTID_SEQ 的序列，將它用作表DEPARTMENTS的主鍵.不使用CYCLE選項(xiàng).CREATE SEQUENCE dept_deptid_seq INCREMENT BY 10 START WITH 120 MAXVALUE 9999 NOCACHE NOCYCLE;11 - 27確認(rèn)確認(rèn)序列序列在USER_SEQUENCES數(shù)據(jù)字典中驗(yàn)證序列值.LAST_NUMBER列顯示下一個(gè)可用的序列號(hào)（如果指定了NOCACHE）SELECT sequence_name,min_value,max_value, incremen

16、t_by,last_numberFROM user_sequences;11 - 28NEXTVAL and CURRVAL 偽列偽列NEXTVAL 會(huì)返回下一個(gè)可用的序列值，每次被引用時(shí)，它都會(huì)返回一個(gè)唯一的值，即使對(duì)于不同的用戶也是這樣.CURRVAL 會(huì)獲得當(dāng)前的序列值.必須對(duì)該序列發(fā)出 NEXTVAL，然后 CURRVAL.才能包含值。11 - 29 使用使用NEXTVAL和和CURRVAL的規(guī)則的規(guī)則可以在以下情況中使用NEXTVAL和CURRVAL： 不屬于子查詢一部分的SELECT語(yǔ)句的SELECT列表 INSERT語(yǔ)句中的子查詢SELECT列表 INSERT語(yǔ)句中的VALUES

17、子句 UPDATE語(yǔ)句中的SET子句不能在以下情況中使用NEXTVAL和CURRVAL： 視圖的SELECT列表 帶DISTINCT關(guān)鍵字的SELECT語(yǔ)句 帶有GROUP BY、HAVING和ORDER BY子句的SELECT語(yǔ)句 SELECT、DELETE或UPDATE語(yǔ)句中的子查詢 CREATE TABLE或ALTER TABLE語(yǔ)句中的DEFAULT表達(dá)式11 - 30使用序列使用序列在地點(diǎn)標(biāo)識(shí)為2500中插入一個(gè)名為Support的新部門:查看DEPT_DEPTID_SEQ 序列的當(dāng)前值:INSERT INTO departments(department_id, departmen

18、t_name, location_id)VALUES (dept_deptid_seq.NEXTVAL, Support, 2500);SELECTdept_deptid_seq.CURRVALFROMdual;11 - 31緩存序列的值緩存序列的值在內(nèi)存中高速緩存序列值可以更快地訪問(wèn)緩存的序列值.當(dāng)發(fā)生以下情況時(shí)，序列值會(huì)出間斷: 發(fā)生回退 系統(tǒng)崩潰 在其它表中使用了序列如果創(chuàng)建序列時(shí)使用了NOCACHE選項(xiàng)，可以通過(guò)查詢USER_SEQUENCES表來(lái)查看下一個(gè)可用的值。11 - 32修改序列修改序列更改增量值、最大值、最小值、循環(huán)選項(xiàng)或高速緩存選項(xiàng):ALTER SEQUENCE dept

19、_deptid_seq INCREMENT BY 20 MAXVALUE 999999 NOCACHE NOCYCLE;11 - 33修改序列的準(zhǔn)則修改序列的準(zhǔn)則必須是序列的所有者或?qū)υ撔蛄芯哂蠥LTER權(quán)限.修改只會(huì)影響以后生成的序列號(hào).如果要從不同的序列處重新開(kāi)始，則必須刪除原有的序列然后重新創(chuàng)建它.(不能修改start with)系統(tǒng)會(huì)執(zhí)行一些驗(yàn)證操作.通過(guò)DROP SEQUENCE語(yǔ)句可以從數(shù)據(jù)字典中刪除序列:序列一旦刪除，就不能再引用它。DROP SEQUENCE dept_deptid_seq;11 - 34索引索引邏輯上代表一個(gè)或多個(gè)表中數(shù)據(jù)的子集視圖View 數(shù)字值生成器序列S

20、equence 基本的存儲(chǔ)單元，由行和列組成 表Table給出對(duì)象的替代名稱同義詞Synonym 提高某些查詢的性能索引IndexDescriptionObject11 - 35什么是索引什么是索引索引具有以下特點(diǎn):是一個(gè)schema對(duì)象通過(guò)指針可以讓ORACLE服務(wù)器快速定位行通過(guò)使用快速路徑訪問(wèn)方法來(lái)快速查找數(shù)據(jù)，降低磁盤I/O開(kāi)銷獨(dú)立于它索引的表由ORACLE服務(wù)器自動(dòng)的維護(hù)和使用11 - 36如何創(chuàng)建索引如何創(chuàng)建索引自動(dòng)創(chuàng)建: 如果在表定義中定義了 PRIMARY KEY 和 UNIQUE 約束，系統(tǒng)會(huì)自動(dòng)創(chuàng)建一個(gè)唯一性索引.手動(dòng)創(chuàng)建: 用戶可以在列上創(chuàng)建非唯一的索引，以加速對(duì)行的訪問(wèn)

21、.11 - 37創(chuàng)建索引創(chuàng)建索引在一個(gè)或多個(gè)列上創(chuàng)建索引:提高對(duì)表EMPLOYEES 的LAST_NAME列的查詢?cè)L問(wèn)速度:CREATE INDEX emp_last_name_idxON employees(last_name);CREATE UNIQUEBITMAPINDEX indexON table (column, column.);11 - 38索引創(chuàng)建準(zhǔn)則索引創(chuàng)建準(zhǔn)則什么情況下不要?jiǎng)?chuàng)建索引什么情況下不要?jiǎng)?chuàng)建索引:在查詢中不經(jīng)常該列作為條件預(yù)計(jì)多數(shù)查詢檢索的行要超過(guò)表中總行數(shù)的2% 到 4%表的更新比較頻繁列包含大量的空值在 WHERE 子句或聯(lián)結(jié)條件中頻繁使用的一個(gè)或多個(gè)列列包含

22、較大范圍的值被索引的列將作為表達(dá)式的一部分進(jìn)行引用 表的行數(shù)相當(dāng)?shù)亩?，但是大多?shù)查詢檢索的行，不到該表總行數(shù)的2% 到 4%什么情況下要?jiǎng)?chuàng)建索引什么情況下要?jiǎng)?chuàng)建索引:11 - 39確認(rèn)索引確認(rèn)索引USER_INDEXES數(shù)據(jù)字典包含索引的名稱及其唯一性:USER_IND_COLUMNS數(shù)據(jù)字典包含索引名、表名和列名:SELECT ic.index_name,ic.column_name, ic.column_position col_pos,ix.uniquenessFROM user_indexes ix,user_ind_columns icWHERE ic.index_name=ix.i

23、ndex_nameAND ic.table_name=EMPLOYEES;11 - 40刪除索引刪除索引使用 DROP INDEX 命令可以從數(shù)據(jù)字典中刪除索引:從數(shù)據(jù)字典中刪除 emp_last_name_idx 索引:要?jiǎng)h除索引，必須是索引的所有者或者具有 DROP ANY INDEX 權(quán)限.DROP INDEX emp_last_name_idx;DROP INDEX index;11 - 41同義詞同義詞邏輯上代表一個(gè)或多個(gè)表中數(shù)據(jù)的子集視圖View 數(shù)字值生成器序列Sequence 基本的存儲(chǔ)單元，由行和列組成 表Table給出對(duì)象的替代名稱同義詞Synonym 提高某些查詢的性能索

24、引IndexDescriptionObject11 - 42為一個(gè)對(duì)象創(chuàng)建同義詞為一個(gè)對(duì)象創(chuàng)建同義詞通過(guò)創(chuàng)建同義詞（對(duì)象的另一個(gè)名稱）可以簡(jiǎn)化對(duì)對(duì)象的訪問(wèn)，使用同義詞具有以下優(yōu)點(diǎn):易于引用其它用戶所擁有的表縮短冗長(zhǎng)的對(duì)象名可以為表、視圖、序列、過(guò)程或其它對(duì)象創(chuàng)建同義詞。在語(yǔ)法中：PUBLIC創(chuàng)建一個(gè)所有用戶都可以訪問(wèn)的同義詞synonym 是要?jiǎng)?chuàng)建的同義詞的名稱object標(biāo)識(shí)要為其創(chuàng)建同義詞的對(duì)象準(zhǔn)則：1.該對(duì)象不能包含在程序包中2.私用同義詞必須與同一用戶擁有的所有其它對(duì)象不同名CREATE PUBLIC SYNONYM synonymFOR object;11 - 43創(chuàng)建和刪除同義詞創(chuàng)

25、建和刪除同義詞為DEPT_SUM_VU 視圖創(chuàng)建一個(gè)簡(jiǎn)短的名稱:刪除同義詞:CREATE SYNONYM d_sumFOR dept_sum_vu;DROP SYNONYM d_sum;11 - 44QuizIndexes must be created manually and serve to speed up access to rows in a table.1.True2.False11 - 45SummaryIn this lesson, you should have learned how to:Create, use, and remove viewsAutomatically generate sequence numbers by using a sequence generatorCreate indexes to improve speed of query retrievalUse synonyms to provide alternative names for objec