第10章 其他數(shù)據(jù)庫對(duì)象

第十章其他數(shù)據(jù)庫對(duì)象東軟IT人才實(shí)訓(xùn)中心Copyright2014ByNeusoftGroup.Allrightsreserved學(xué)時(shí)：4學(xué)時(shí)教學(xué)方法：講授ppt＋上機(jī)練習(xí)＋點(diǎn)評(píng)目標(biāo):本章旨在向?qū)W員介紹其他數(shù)據(jù)庫對(duì)象的應(yīng)用，通過本課的學(xué)習(xí)，學(xué)員應(yīng)該掌握如下知識(shí)：1)視圖2)序列3)索引4)同義詞本章要點(diǎn)視圖序列索引同義詞視圖視圖是虛表。是一個(gè)命名的查詢，用于改變基表數(shù)據(jù)的顯示，簡化查詢。視圖的訪問方式與表的訪問方式相同。視圖的好處：可以限制對(duì)基表數(shù)據(jù)的訪問，只允許用戶通過視圖看到表中的一部分?jǐn)?shù)據(jù)可以使復(fù)雜的查詢變的簡單提供了數(shù)據(jù)的獨(dú)立性，用戶并不知道數(shù)據(jù)來自于何處提供了對(duì)相同數(shù)據(jù)的不同顯示簡單視圖和復(fù)雜視圖簡單視圖：只涉及到一個(gè)表，而且SELECT子句中不包含函數(shù)表達(dá)式列（包括單行函數(shù)和分組函數(shù)）。復(fù)雜視圖：涉及到一個(gè)或多個(gè)表，SELECT子句中包含函數(shù)表達(dá)式列（單行函數(shù)或負(fù)責(zé)函數(shù)）。創(chuàng)建視圖在子查詢中可以加入復(fù)雜的SELECT.CREATE[ORREPLACE]VIEWview[(alias[,alias]...)]ASsubquery[WITHCHECKOPTION[CONSTRAINTconstraint]][WITHREADONLY[CONSTRAINTconstraint]];創(chuàng)建視圖示例例10-3查詢50部門的員工的年薪的視圖CREATEORREPLACEVIEWsalvu50ASSELECTemployee_idID_NUMBER,last_nameNAME,salary*12ANN_SALARYFROMemployeesWHEREdepartment_id=50;DESCsalvu50；SELECT*FROMsalvu50；通過視圖執(zhí)行DML操作創(chuàng)建一個(gè)測試用表EMP_DML創(chuàng)建視圖v_emp1，是個(gè)簡單視圖。通過視圖進(jìn)行DML操作。CREATETABLEemp_dmlASSELECTemployee_id,last_name,salaryFROMemployeesWHEREdepartment_id=50;CREATEORREPLACEVIEWv_emp1ASSELECTemployee_id,salaryFROMemp_dml;UPDATEv_emp1SETsalary=salary+100;不能通過視圖刪除記錄的條件視圖中包含分組函數(shù)視圖中含有GROUPBY子句視圖中含有DISTINCT關(guān)鍵字視圖中包含偽列ROWNUM不能通過視圖修改記錄的條件視圖中包含分組函數(shù)視圖中含有GROUPBY子句視圖中含有DISTINCT關(guān)鍵字視圖中包含偽列ROWNUM視圖中要修改的列包含表達(dá)式不能通過視圖添加記錄的條件視圖中包含分組函數(shù)視圖中含有GROUPBY子句視圖中含有DISTINCT關(guān)鍵字視圖中包含偽列ROWNUM視圖中要修改的列包含表達(dá)式視圖中沒有表的NOTNULL列。WITHCHECKOPTIONWITHCHECKOPTION實(shí)質(zhì)是給視圖加一個(gè)“CHECK”約束，該CHECK約束的條件就是視圖中的子查詢的WHERE條件，以后如果想通過該視圖執(zhí)行DML操作，不允許違反該CHECK約束。WITHCHECKOPTION示例例10-6WITHCHECKOPTION例子該約束的條件為視圖中WHERE條件，即“employee_id=141”，如視圖v_emp3想執(zhí)行DML操作，不能把記錄的employee_id字段值改成其他編號(hào)（只能是141），如果違反了，執(zhí)行出錯(cuò)，會(huì)出現(xiàn)錯(cuò)誤提示。CREATEORREPLACEVIEWv_emp3ASSELECTemployee_id,salaryFROMemp_dmlWHEREemployee_id=141WITHCHECKOPTIONCONSTRAINTv_emp3_ck;WITHREADONLYWITHREADONLY的視圖是只讀的，不允許通過該視圖執(zhí)行DML語句。例10-7WITHREADONLY例子,創(chuàng)建一個(gè)視圖v_emp4通過該視圖進(jìn)行更新操作UPDATEv_emp4SETsalary=salary+100;CREATEORREPLACEVIEWv_emp4ASSELECTemployee_id,salaryFROMemp_dmlWITHREADONLY；刪除視圖刪除視圖的語法DROPVIEWview；內(nèi)聯(lián)視圖內(nèi)聯(lián)視圖（InlineView）,是一個(gè)在SQL語句內(nèi)可以使用的子查詢的別名。是一個(gè)命名的SQL語句，但不是真正的數(shù)據(jù)庫的視圖對(duì)象。最常見的內(nèi)聯(lián)視圖的例子就是主查詢中的FROM子句中，包含的是一個(gè)命名的子查詢。例10-8內(nèi)聯(lián)視圖的例子SELECTlast_name,department_nameFROMDEPARTMENTSa,(SELECTlast_name,department_idFROMEMPLOYEES)bWHEREa.department_id=b.department_id；

Top-N分析Top-N查詢主要是實(shí)現(xiàn)查找表中最大或最小的N條記錄功能。Top-N分析語法：SELECT[列名],ROWNUMFROM(SELECT[列名]FROM表名

ORDERBYTop-N操作的列)WHEREROWNUM<=N;Top-N分析（續(xù)）ROWNUM是一個(gè)偽列。功能是在每次查詢時(shí)，返回結(jié)果集的順序數(shù)。第一行顯示為1，第二行為2，以此類推。偽列是使用上類似于表中的列，而實(shí)際并沒有存儲(chǔ)在表中的特殊對(duì)象。對(duì)ROWNUM只能執(zhí)行<、<=運(yùn)算，不能執(zhí)行>、>=或一個(gè)區(qū)間運(yùn)算Between..And等。

序列序列是一種用于產(chǎn)生唯一數(shù)字列值的數(shù)據(jù)庫對(duì)象。一般使用序列自動(dòng)地生成主碼值或唯一鍵值。序列可以是升序或降序。序列特點(diǎn)：可以為表中的記錄自動(dòng)產(chǎn)生唯一序列值。由用戶創(chuàng)建并且可被多個(gè)用戶共享。典型應(yīng)用是生成主鍵值，用于標(biāo)識(shí)記錄的唯一性。允許同時(shí)生成多個(gè)序列號(hào)，而每一個(gè)序列號(hào)是唯一的?？商娲鷳?yīng)用程序中使用到的序列號(hào)。使用緩存加速序列的訪問速度。創(chuàng)建序列創(chuàng)建序列的語法：創(chuàng)建序列，必須有CREATESEQUENCE或CREATEANYSEQUENCE權(quán)限。序列被創(chuàng)建后，可以通過查詢數(shù)據(jù)字典視圖USER_SEQUENCES查看序列信息。CREATESEQUENCE[schema.]序列名[INCREMENTBYn][STARTWITHn][MAXVALUEn|NOMAXVALUE][MINVALUEn|NOMINVALUE][CYCLE|NOCYCLE][CACHEn|NOCACHE];序列語法說明當(dāng)全部缺省時(shí)，則該序列為上升序列，由1開始，增量為1，沒有上限，緩存中序列值個(gè)數(shù)為20。INCREMENTBYn：n可為正的或負(fù)的整數(shù)，但不可為0。默認(rèn)值為1。STARTWITHn：指定生成的第一個(gè)序列號(hào)，默認(rèn)為1。對(duì)于升序，默認(rèn)為序列的最小值。對(duì)于降序，默認(rèn)為序列的最大值。MAXVALUEn：指定n為序列可生成的最大值。NOMAXVALUE：為默認(rèn)情況。指定升序最大值為1027，指定降序指定最大值為-1。序列語法說明（續(xù)）MINVALUEn：指定n為序列的最小值。NOMINVALUE：為默認(rèn)情況。指定升序默認(rèn)最小值為1。指定降序默認(rèn)最小值為-1026。CYCLE：指定序列使用循環(huán)。即序列達(dá)到了最大值，則返回最小值重新開始。默認(rèn)為NOCYCLE。CACHEn：定義n個(gè)序列值保存在緩存中，默認(rèn)值為20個(gè)。創(chuàng)建序列例10-12 創(chuàng)建序列test_seq。

CREATESEQUENCEtest_seqSTARTWITH10——序列從10開始INCREMENTBY2——序列每次增加2MAXVALUE100——序列最大值100MINVALUE9——序列最小值9CYCLE——序列循環(huán)。每次增加2，一直到100后回 到9從新開始CACHE10；——緩存中序列值個(gè)數(shù)為10偽列可用語句sequence_name.CURRVAL和sequence_name.NEXTVAL來訪問序列。CURRVAL當(dāng)前序列正被分配的序列值。NEXTVAL在序列中增加新值并返回此值。CURRVAL和NEXTVAL都返回NUMBER值。下列語句可使用NEXTVAL和CURRVAL偽列：SELECT語句的非子查詢的目標(biāo)列名列表中。INSERT語句中的子查詢的SELECT目標(biāo)列名列表中。INSERT語句的VALUES子句中。UPDATE語句的SET子句中。偽列（續(xù)）下列語句不允許使用NEXTVAL和CURRVAL偽列：在對(duì)視圖查詢的SELECT目標(biāo)列名列表中。使用了DISTINCT命令的SELECT語句中。SELECT語句中使用了GROUPBY、HAVING或ORDERBY子句時(shí)。在SELECT、DELETE或UPDATE語句的子查詢中。在CREATETABLE或ALTERTABLE語句中的默認(rèn)值表達(dá)式中。序列與偽列的應(yīng)用示例創(chuàng)建序列student_seq：使用序列student_seq生成student表中sid列插入值：查看student_seq序列當(dāng)前值：SELECTstudent_seq.CURRVALFROMdual;INSERTINTOstudentVALUES(student_seq.NEXTVAL,'Scott','ComputerScience',11);CREATESEQUENCEstudent_seqSTARTWITH10000INCREMENTBY1;修改序列修改序列的語法如下：必須是序列的所有者，或者有ALTERANYSEQUENCE權(quán)限才能修改序列。修改序列的語法除沒有STARTWITH子句外。ALTERSEQUENCE[schema.]序列名

[INCREMENTBYn]

[MAXVALUEn|NOMAXVALUE][MINVALUEn|NOMINVALUE][CYCLE|NOCYCLE][CACHEn|NOCACHE];修改序列示例正確修改錯(cuò)誤修改ALTERSEQUENCEtest_seqINCREMENTBY4MAXVALUE100——最大值100小于已經(jīng)分配的序列值200NOCACHE；ALTERSEQUENCEtest_seqINCREMENTBY4——序列每次增加4MAXVALUE1000——序列最大值1000NOCACHE；——不設(shè)定緩存刪除序列對(duì)序列的刪除必須是序列的所有者或者具有DROPANYSEQUENCE權(quán)限的用戶才可以完成。刪除序列的語法如下：例10-15 刪除序列student_seq刪除序列student_seqDROPSEQUENCEstudent_seq;DROPSEQUENCE[schema.]

序列名；索引索引是:方案（schema）中的一個(gè)數(shù)據(jù)庫對(duì)象在Oracle數(shù)據(jù)庫中用來加速對(duì)表的查詢速度通過使用快速路徑訪問方法快速定位數(shù)據(jù),減少了磁盤的I/O與表獨(dú)立存放，但需要依附于表，是在表的基礎(chǔ)上創(chuàng)建的由Oracle數(shù)據(jù)庫自動(dòng)維護(hù)索引創(chuàng)建索引創(chuàng)建索引有兩種方式：自動(dòng)或者手動(dòng)自動(dòng):當(dāng)在表上定義一個(gè)PRIMARYKEY或者UNIQUE約束條件時(shí),Oracle數(shù)據(jù)庫自動(dòng)創(chuàng)建一個(gè)對(duì)應(yīng)的唯一索引.手動(dòng):用戶可以創(chuàng)建索引以加速查詢，在需要?jiǎng)?chuàng)建索引的字段上創(chuàng)建需要的索引。創(chuàng)建一個(gè)索引在一列或者多列上創(chuàng)建索引.下面的索引將會(huì)提高對(duì)EMPLOYEES表基于LAST_NAME字段的查詢速度.CREATEINDEXindexnameONtable(column[,column]...);CREATEINDEX emp_last_name_idxONemployees(last_name);Indexcreated.測試索引創(chuàng)建測試環(huán)境表。測試無索引檢索時(shí)間。CREATETABLEe1ASSELECT*FROMemployees;INSERTINTOe1SELECT*FROMe1;--多次運(yùn)行UPDATEe1SETemployee_id=ROWNUM;--更新所有記錄的employee_id，以使其數(shù)值唯一commit;--提交settimingon;--設(shè)置環(huán)境變量timingSELECTlast_name,salaryFROMe1WHEREemployee_id=210000;--沒有索引的情況下做測試已用時(shí)間:00:00:06.05–沒有索引的時(shí)候用時(shí)6秒測試索引（續(xù)）測試建索引后檢索時(shí)間。CREATEINDEXe1_idONe1(employee_id);--創(chuàng)建索引SELECTlast_name,salaryFROMe1WHEREemployee_id=210000;--再次在有索引的情況下做測試已用時(shí)間:00:00:00.00–測試結(jié)果，幾乎沒有消耗什么時(shí)間settimingoff;--取消時(shí)間統(tǒng)計(jì)適合創(chuàng)建索引情況查詢列的數(shù)據(jù)范圍很廣泛查詢列中包含大量的NULL值WHERE條件中的列或者多表連接的列適合創(chuàng)建索引欲查詢的表數(shù)據(jù)量很大，而且大多數(shù)的查詢得到結(jié)果集的數(shù)量占總記錄量的2％～4％不適合創(chuàng)建索引的情況很小數(shù)據(jù)量的表在查詢中不常用來作為查詢條件的列查詢最終得到的結(jié)果集很大頻繁更新的表（索引對(duì)于DML操作是有部分負(fù)面影響的）索引列作為表達(dá)式的一部分被使用時(shí)（比如常查詢的條件是SALARY*12，此時(shí)在SALARY列上創(chuàng)建索引是沒有效果的）刪除索引刪除索引的語法刪除索引后，索引中的數(shù)據(jù)及定義被刪除，索引所占的數(shù)據(jù)空間被釋放，但表中的數(shù)據(jù)仍然存在。常用與索引相關(guān)的數(shù)據(jù)字典視圖有：USER_INDEXES：用戶的索引對(duì)象的定義，包含索引的名字和索引的一些相關(guān)屬性（比如唯一性等）USER_IND_COLUMNS：用戶索引對(duì)象的列的定義信息，會(huì)列出索引名字，表名字，和列的名字等。DROPINDEXindex;同義詞同義詞（Synonyms）是指向數(shù)據(jù)庫對(duì)象（如：表、視圖