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

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

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

3、lias指定由視圖的查詢所選擇的表達式的名稱subquery是一個完整的SELECT語句（可以在查詢中使用別名）WITH CHECK OPTION指定只能插入或更新那些視圖可以訪問的行WITH READ ONLY確保不能對視圖執(zhí)行DML操作constraint為兩個WITH選項命名該子查詢可以包含復雜的SELECT 語句.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中員工的詳細信息:像描述表的結(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)建視圖的準則創(chuàng)建視圖的準則 子查詢，可以包含復雜的查詢，包括多表聯(lián)接，組函數(shù)。 如果創(chuàng)建的時候，使用了WITH CHECK OPTION這種約束，我們可以給此約束自定義約束的名稱，或

5、者我們不進行指定，系統(tǒng)將自動分配一個SYS_Cn這種格式的名字給此約束命名. 我們可以使用or replace來重新創(chuàng)建視圖，以實現(xiàn)修改的目的11 - 10創(chuàng)建視圖創(chuàng)建視圖在子查詢中使用列別名來創(chuàng)建視圖:通過給定的別名從此視圖中選擇列.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使用視圖進行使用視圖進行查詢查詢SELECT *FROM salvu50;12311 - 13修改視圖修改視圖使用CREATE OR REPLACE VIEW子句修改EMPVU80視圖，為每個列名添加一個別名: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)建復雜視圖創(chuàng)建復雜視圖創(chuàng)建包含分組函數(shù)的復雜視圖以顯示兩個表中的值: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ī)則可以在簡單的視圖上執(zhí)行DML操作.如果視圖包含以下內(nèi)容，則不能通過視圖刪除行: 分組函數(shù) GROUP BY 子句 DISTINCT 關(guān)鍵字 偽列 ROWNUM 關(guān)鍵字11 - 16在視圖上執(zhí)行在視圖上執(zhí)行DML操作的規(guī)則操作

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

10、不會成功，因為它違反了 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子句指定通過視圖執(zhí)行的INSERT和UPDATE命令不能創(chuàng)建該視圖無法選擇的行，從而允許對插入或更新的數(shù)據(jù)強制執(zhí)行數(shù)據(jù)驗證檢查。如果試圖對視圖未選中的行執(zhí)行D

11、ML操作，就會報錯。上例中，如果部門編號更改為10，該視圖就無法看到該員工了，因此不會更新任何行。所以，使用WITH CHECK OPTION子句，該視圖只能看到部門20的員工，并且不允許通過該視圖更改這些員工的部門編號。UPDATE empvu20 SETdepartment_id=10 WHEREemployee_id=201;11 - 20拒絕拒絕DML操作操作通過在視圖中定義 WITH READ ONLY 選項可以確保不能執(zhí)行DML操作.任何對視圖中的執(zhí)行 DML 的嘗試都會收到一個Oracle server 錯誤.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刪除視圖刪除視圖因為視圖是基于數(shù)據(jù)庫中的基表，所以刪除視圖不會導致丟失數(shù)據(jù).DROP VIEW view;DROP VIEW empvu80;11 - 23序列序列邏輯上代表一個或多個表中數(shù)據(jù)的子集視圖View 數(shù)字值生成器序列Sequence 基本的存儲單元，由行和列組成 表T

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

14、定序列可以生成的最大值NOMAXVALUE指定10的27次冪做為序列的最大值MINVALUE n指定序列的最小值NOMINVALUE指定-（1026）作為遞減序列的最小值CYCLE|NOCYCLE指定達到最大或最小值后，序列是否繼續(xù)生成值，默認NOCACHE n|NOCACHE指定Oracle服務器預先分配并保留內(nèi)存中的值的數(shù)量，默認20個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)建一個名為 DEPT_DEPTID_SEQ 的序列，將它用作表DEPARTMENTS的主鍵.不使用CYCLE選項.CREATE SEQUENCE dept_deptid_seq INCREMENT BY 10 START WITH 120 MAXVALUE 9999 NOCACHE NOCYCLE;11 - 27確認確認序列序列在USER_SEQUENCES數(shù)據(jù)字典中驗證序列值.LAST_NUMBER列顯示下一個可用的序列號（如果指定了NOCACHE）SELECT sequence_name,min_value,max_value, incremen

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

17、子句 UPDATE語句中的SET子句不能在以下情況中使用NEXTVAL和CURRVAL： 視圖的SELECT列表 帶DISTINCT關(guān)鍵字的SELECT語句 帶有GROUP BY、HAVING和ORDER BY子句的SELECT語句 SELECT、DELETE或UPDATE語句中的子查詢 CREATE TABLE或ALTER TABLE語句中的DEFAULT表達式11 - 30使用序列使用序列在地點標識為2500中插入一個名為Support的新部門:查看DEPT_DEPTID_SEQ 序列的當前值: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)存中高速緩存序列值可以更快地訪問緩存的序列值.當發(fā)生以下情況時，序列值會出間斷: 發(fā)生回退 系統(tǒng)崩潰 在其它表中使用了序列如果創(chuàng)建序列時使用了NOCACHE選項，可以通過查詢USER_SEQUENCES表來查看下一個可用的值。11 - 32修改序列修改序列更改增量值、最大值、最小值、循環(huán)選項或高速緩存選項:ALTER SEQUENCE dept

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

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

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

22、較大范圍的值被索引的列將作為表達式的一部分進行引用 表的行數(shù)相當?shù)亩啵谴蠖鄶?shù)查詢檢索的行，不到該表總行數(shù)的2% 到 4%什么情況下要創(chuàng)建索引什么情況下要創(chuàng)建索引:11 - 39確認索引確認索引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 索引:要刪除索引，必須是索引的所有者或者具有 DROP ANY INDEX 權(quán)限.DROP INDEX emp_last_name_idx;DROP INDEX index;11 - 41同義詞同義詞邏輯上代表一個或多個表中數(shù)據(jù)的子集視圖View 數(shù)字值生成器序列Sequence 基本的存儲單元，由行和列組成 表Table給出對象的替代名稱同義詞Synonym 提高某些查詢的性能索

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

25、建和刪除同義詞為DEPT_SUM_VU 視圖創(chuàng)建一個簡短的名稱:刪除同義詞: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