Oracle數(shù)據(jù)庫培訓 課件

1.OracleSQL基礎Welcome!RainnyZhong138265865711.OracleSQL基礎Welcome!Rainny11.1.OracleSQL簡介Dr.E.F.Coddpublishedthepaper,"ARelationalModelofDataforLargeSharedDataBanks",inJune1970intheAssociationofComputerMachinery(ACM)journal,CommunicationsoftheACM.Codd'smodelisnowacceptedasthedefinitivemodelforrelationaldatabasemanagementsystems(RDBMS).Thelanguage,

StructuredEnglishQueryLanguage

("SEQUEL")wasdevelopedbyIBMCorporation,Inc.,touseCodd'smodel.SEQUELlaterbecameSQL(stillpronounced"sequel").In1979,RelationalSoftware,Inc.(nowOracleCorporation)introducedthefirstcommerciallyavailableimplementationofSQL.Today,SQLisacceptedasthestandardRDBMSlanguage.SQLStandardsOracleCorporationstrivestocomplywithindustry-acceptedstandardsandparticipatesactivelyinSQLstandardscommittees.Industry-acceptedcommitteesaretheAmericanNationalStandardsInstitute(ANSI)andtheInternationalStandardsOrganization(ISO),whichisaffiliatedwiththeInternationalElectrotechnicalCommission(IEC).BothANSIandtheISO/IEChaveacceptedSQLasthestandardlanguageforrelationaldatabases.WhenanewSQLstandardissimultaneouslypublishedbytheseorganizations,thenamesofthestandardsconformtoconventionsusedbytheorganization,butthestandardsaretechnicallyidentical.ThelatestSQLstandardwasadoptedinJuly1999andisoftencalledSQL:99.Theformalnamesofthisstandardare:ANSIX3.135-1999,"DatabaseLanguageSQL",Parts1("Framework"),2("Foundation"),and5("Bindings")ISO/IEC9075:1999,"DatabaseLanguageSQL",Parts1("Framework"),2("Foundation"),and5("Bindings")1.1.OracleSQL簡介Dr.E.F.Codd21.1.1.OracleSQL的處理過程1.1.1.OracleSQL的處理過程31.1.1.OracleSQL的處理過程處理SQL語句處理查詢：

?語法分析：

–搜索同一語句

–檢查語法、對象名和權限

–鎖定語法分析過程中使用的對象

–創(chuàng)建和存儲執(zhí)行計劃

?綁定：獲取變量值

?執(zhí)行：處理語句

?提取：將結果行返回用戶進程處理DML語句：

?語法分析：與處理查詢時的語法分析階段相同。

?綁定：與處理查詢時的綁定階段相同。

?執(zhí)行：

– 如果數(shù)據(jù)庫緩沖區(qū)高速緩存中不存在某些數(shù)據(jù)塊和還原塊，服務器進程就從數(shù)據(jù)文件將它們讀入數(shù)據(jù)庫緩沖區(qū)高速緩存。

–服務器進程鎖定要進行修改的行。還原塊用于存儲數(shù)據(jù)的前像，以便在需要時回退DML語句。

– 數(shù)據(jù)塊記錄數(shù)據(jù)的新值。

– 服務器進程將數(shù)據(jù)的前像記錄到回退塊中，并更新數(shù)據(jù)塊。這兩種更改都是在數(shù)據(jù)庫緩沖區(qū)高速緩存中進行的。數(shù)據(jù)庫緩沖區(qū)高速緩存中所有已更改的塊都標記為灰數(shù)據(jù)緩沖區(qū)，即與磁盤中相應的塊不同的緩沖區(qū)。

–DELETE或INSERT命令的處理使用類似的步驟。DELETE命令的前像包含已刪除行中的列值，而INSERT命令的前像中包含行的位置信息。

處理DDL語句：DDL（數(shù)據(jù)定義語言）語句的執(zhí)行與DML（數(shù)據(jù)操縱語言）語句和查詢的執(zhí)行不盡相同，因為成功執(zhí)行DDL語句需要對數(shù)據(jù)字典具有寫權限。對于這些語句，語法分析階段實際上包括分析、數(shù)據(jù)字典查找和執(zhí)行。事務處理管理SQL語句、會話管理SQL語句和系統(tǒng)管理SQL語句在語法分析和執(zhí)行階段處理。要重新執(zhí)行這些語句，再次進入執(zhí)行階段即可。1.1.1.OracleSQL的處理過程處理SQL語句處41.2.OracleSQL基本元素DatatypesLiteralsFormatModelsNullsPseudocolumns1.2.OracleSQL基本元素Datatypes51.2.1.OracleSQL數(shù)據(jù)類型OracleBuilt-inDatatypesANSI,DB2,andSQL/DSDatatypesUser-DefinedTypesOracle-SuppliedTypes1.2.1.OracleSQL數(shù)據(jù)類型OracleBui.Oracle內(nèi)置數(shù)據(jù)類型.Oracle內(nèi)置數(shù)據(jù)類型.Oracle內(nèi)置的數(shù)據(jù)類型概覽CharacterDatatypes------CHARDatatype------NCHARDatatype------NVARCHAR2Datatype------VARCHAR2DatatypeNUMBERDatatypeLONGDatatypeDatetimeandIntervalDatatypes------DATE------TIMESTAMP------TIMESTAMPWITHTIMEZONE------TIMESTAMPWITHLOCALTIMEZONERAWandLONGRAWDatatypesLOB------BLOB------CLOB------NCLOBROWIDDatatypeBFILEDatatype.Oracle內(nèi)置的數(shù)據(jù)類型概覽Charact81.2.2.OracleSQL偽列CURRVALandNEXTVAL---sequence.CURRVAL---sequence.NEXTVAL

LEVELSELECTemployee_id,last_nameFROMemployeesWHERE(employee_id,LEVEL)IN(SELECTemployee_id,2FROMemployees)STARTWITHemployee_id=2CONNECTBYPRIORemployee_id=manager_id;ROWIDSELECTROWID,last_nameFROMemployeesWHEREdepartment_id=20;

ROWNUMSELECT*FROMemployeesWHEREROWNUM<10;1.2.2.OracleSQL偽列CURRVALand91.3.約束1.3.約束101.3.1.數(shù)據(jù)完整性1.3.1.數(shù)據(jù)完整性111.3.1.數(shù)據(jù)完整性數(shù)據(jù)完整性是指數(shù)據(jù)庫中的數(shù)據(jù)符合業(yè)務規(guī)則。維護數(shù)據(jù)完整性共有三種主要方法：

?應用程序代碼

?數(shù)據(jù)庫觸發(fā)器

?聲明完整性約束

具體使用上述哪種方法映射業(yè)務規(guī)則是設計時應考慮的問題。而數(shù)據(jù)庫管理員主要關心的是實施設計人員選擇的方法，并在完整性需求和性能要求之間取得平衡。應用程序代碼既可作為數(shù)據(jù)庫中的存儲過程實現(xiàn)，也可作為在客戶端上運行的應用程序?qū)崿F(xiàn)。本課著重講述完整性約束的使用。數(shù)據(jù)庫觸發(fā)器：數(shù)據(jù)庫觸發(fā)器是PL/SQL程序，在表上發(fā)生事件（如插入或更新列）時執(zhí)行?？梢詥⒂没蚪糜|發(fā)器，即可以設置觸發(fā)器在事件發(fā)生時執(zhí)行，或者將觸發(fā)器設置為不執(zhí)行（即使已定義）。通常情況下，創(chuàng)建數(shù)據(jù)庫觸發(fā)器只是為了強制應用不能定義為完整性約束的復雜業(yè)務規(guī)則。注：數(shù)據(jù)庫觸發(fā)器在其它Oracle課程中講述。完整性約束：完整性約束是執(zhí)行業(yè)務規(guī)則的首選機制，這是因為它可以：

?改善性能

?易于聲明和修改，不需要進行大量編碼

?集中管理規(guī)則

?使用靈活（啟用或禁用）?在數(shù)據(jù)字典中完全文檔化以下部分解釋完整性約束的行為，并論述Oracle服務器如何執(zhí)行這些完整性約束。

1.3.1.數(shù)據(jù)完整性數(shù)據(jù)完整性是指數(shù)據(jù)庫中的數(shù)據(jù)符合業(yè)務規(guī)121.3.2.約束的類型約束

說明------------------------------------------------------------------------------------------NOTNULL

指示出列不能包含空值UNIQUE

指示一個列或列的組合是唯一的PRIMARYKEY

指示一個列或列的組合作為表的主鍵FOREIGNKEY

指示一個列或列的組合在引用完整性約束中作為外鍵CHECK

指定表中的每一行必須滿足的條件缺省情況下，表中的所有列均可以為空。空意味著沒有值。NOTNULL約束要求表列必須包含值。UNIQUE關鍵字約束要求某列或一組列（關鍵字）中的值必須是唯一的。表中的任何兩行在指定的一列或一組列中不能有重復的值。數(shù)據(jù)庫中的每個表至多有一個PRIMARYKEY約束。PRIMARYKEY約束確保以下兩種情況均為真：?表中的任何兩行在指定列中沒有重復的值。?主鍵列不包含NULL值?某列或一組列上的CHECK完整性約束要求，對于表的每一行，指定的條件必須為真或未知。雖然NOTNULL和CHECK約束并不直接要求DBA關注，但PRIMARYKEY、UNIQUE和FOREIGNKEY約束仍須進行管理，以確保高可用性和性能水平可接受。1.3.2.約束的類型約束131.3.3.約束的狀態(tài)1.3.3.約束的狀態(tài)141.3.3.約束的狀態(tài)可以啟用(ENABLE)或禁用(DISABLE)完整性約束。如果啟用某個約束，則在數(shù)據(jù)庫中輸入或更新數(shù)據(jù)時，就會對數(shù)據(jù)進行檢查。禁止輸入不符合約束規(guī)則的數(shù)據(jù)。如果禁用某個約束，則可以在數(shù)據(jù)庫中輸入不符合約束規(guī)則的數(shù)據(jù)。完整性約束可處于以下狀態(tài)之一：

?DISABLENOVALIDATE

?DISABLEVALIDATE

?ENABLENOVALIDATE

?ENABLEVALIDATE

DISABLENOVALIDATE：不檢查處于DISABLENOVALIDATE狀態(tài)的約束。表中的數(shù)據(jù)（包括輸入或更新的新數(shù)據(jù)）可以不符合約束所定義的規(guī)則。DISABLEVALIDATE：當約束處于此狀態(tài)時，不允許對受約束的列進行任何修改。另外，約束上的索引將被刪除并且禁用約束。注：如果約束可延遲，則不刪除索引。

ENABLENOVALIDATE：如果約束處于此狀態(tài)，則不能輸入違反約束的新數(shù)據(jù)。但是，表可能包含無效的數(shù)據(jù)，即數(shù)據(jù)違反約束。啟用處于NOVALIDATE狀態(tài)的約束對正在上載有效OLTP數(shù)據(jù)的數(shù)據(jù)倉庫配置是非常有用的。ENABLEVALIDATE：如果約束處于此狀態(tài)，則不能將違反約束的行插入到表中。但是，禁用該約束時，可以插入此類行。此類行稱為該約束的例外。如果約束處于ENABLENOVALIDATE狀態(tài)，則在禁用約束時輸入的數(shù)據(jù)所引起的違反情況仍然存在。要將約束置于已驗證狀態(tài)，必須更新或刪除違反約束的行。當某一約束由禁用狀態(tài)更改為ENABLEVALIDATE時，將鎖定表并對表中的所有數(shù)據(jù)進行一致性檢查。這可能會引起DML操作（如等待數(shù)據(jù)加載），因此，建議先從禁用狀態(tài)轉為ENABLENOVALIDATE，然后再轉為ENABLEVALIDATE。這些狀態(tài)之間的轉換須符合以下規(guī)則：?除非指定NOVALIDATE，否則ENABLE表示VALIDATE。?除非指定VALIDATE，否則DISABLE表示NOVALIDATE。?VALIDATE和NOVALIDATE沒有缺省的ENABLE和DISABLE狀態(tài)。?當唯一鍵或主鍵從DISABLE狀態(tài)轉為ENABLE狀態(tài)且沒有現(xiàn)有索引時，將自動創(chuàng)建唯一索引。（如果索引可延遲，則將存在異常。）與此類似，當唯一鍵或主鍵從ENABLE轉為DISABLE且是使用唯一索引啟用時，則刪除該唯一索引。?當任何約束從NOVALIDATE狀態(tài)轉為VALIDATE狀態(tài)時，必須檢查所有的數(shù)據(jù)。但是，從VALIDATE轉為NOVALIDATE時，將忽略數(shù)據(jù)已經(jīng)過檢查這一事實。

?將單個約束從ENABLENOVALIDATE狀態(tài)轉為ENABLEVALIDATE狀態(tài)時，并不禁止使用讀取、寫入或其它DDL語句。1.3.3.約束的狀態(tài)可以啟用(ENABLE)或禁用(DIS151.3.4.約束檢查1.3.4.約束檢查161.3.4.約束檢查約束檢查:可以將約束有效性檢查延遲到事務處理完成時進行。不可延遲的約束或立即約束：不可延遲的約束（也稱立即約束）在每個DML語句結束時執(zhí)行。違反約束將導致語句回退。如果約束導致刪除級聯(lián)等操作，則將該操作作為引起該操作的語句的一部分。不能將定義為不可延遲的約束修改為在事務處理結束時執(zhí)行?？裳舆t的約束：可延遲的約束是僅在提交事務處理時才檢查的約束。如果提交時檢測到違反約束的行，則回退整個事務處理。當同時輸入外鍵關系中的父行和子行時（如在訂單輸入系統(tǒng)中同時輸入訂單和訂單所包含的項目），這些約束非常有用?？梢詫⒍x為可延遲的約束指定為下列模式之一：?“最初為立即”表示，除非另外明確設定，否則在缺省情況下作為立即約束使用。

?“最初為延遲”表示，缺省情況下只在事務處理結束時執(zhí)行約束。1.3.4.約束檢查約束檢查:可以將約束有效性檢查延遲到事務171.3.4.將約束定義為立即或延遲1.3.4.將約束定義為立即或延遲181.3.4.將約束定義為立即或延遲SETCONSTRAINTS語句用于將特定事務處理的約束設置為DEFERRED或IMMEDIATE?？梢允褂么苏Z句設置約束名稱列表或約束的模式。SETCONSTRAINTS模式將一直持續(xù)到事務處理完成或者另一個SETCONSTRAINTS語句重置模式。SETCONSTRAINTS語句不允許在觸發(fā)器內(nèi)部使用。ALTERSESSION語句還包含將約束設置為IMMEDIATE或DEFERRED的子句SETCONSTRAINTS。此命令缺省為設置所有(ALL)可延遲的約束（不能指定約束名稱列表）。ALTERSESSIONSETCONSTRAINTS語句僅適用于當前的會話。ALTERSESSION

SETCONSTRAINT[S]=

{IMMEDIATE|DEFERRED|DEFAULT}

SETCONSTRAINT|CONSTRAINTS

{constraint|ALL}

{IMMEDIATE|DEFERRED}

1.3.4.將約束定義為立即或延遲SETCONSTRAIN191.3.5.執(zhí)行主鍵和唯一鍵約束1.3.5.執(zhí)行主鍵和唯一鍵約束201.3.5.執(zhí)行主鍵和唯一鍵約束主鍵和唯一鍵通過索引執(zhí)行?？煽刂朴脕韴?zhí)行這些約束的索引的位置和類型。Oracle服務器按下列步驟實現(xiàn)唯一鍵和主鍵約束：

? 如果約束被禁用，則不需要索引。

? 如果啟用約束且約束中的列構成索引的主要部分，則無論是否將索引本身創(chuàng)建為唯一還是非唯一索引，都可以使用該索引執(zhí)行約束。

? 如果啟用約束且沒有任何索引將約束列用作索引的主要部分，則按照下列規(guī)則創(chuàng)建一個名稱與約束相同的索引：

– 如果關鍵字為可延遲，則在關鍵字列上創(chuàng)建一個非唯一索引。

– 如果關鍵字為不可延遲，則將創(chuàng)建一個唯一索引。

? 如果可以使用某個索引，并且約束是不可延遲的，則使用現(xiàn)有索引。如果約束是可延遲的，并且索引是非唯一的，則使用現(xiàn)有索引。

1.3.5.執(zhí)行主鍵和唯一鍵約束主鍵和唯一鍵通過索引執(zhí)行?？?11.3.6.外鍵注意事項目標操作

相應解決方法-------------------------------------------------------------------------------------------刪除父表

級聯(lián)約束截斷父表

禁用或刪除外鍵刪除包含父表的表空間

使用CASCADECONSTRAINTS子句在子表上執(zhí)行DML操作

確保包含父鍵的表空間聯(lián)機1.3.6.外鍵注意事項目標操作221.3.6.外鍵注意事項維護外鍵關系中的表時，應該考慮幾個因素。涉及父表的DDL：在刪除父表之前，必須先刪除外鍵?？梢允褂靡韵乱粭l語句同時執(zhí)行這兩個操作：DROPTABLEtableCASCADECONSTRAINTS在未刪除或禁用外鍵之前無法截斷父表。在刪除包含父表的表空間之前，必須先刪除外鍵?？墒褂孟铝忻钔瓿稍摬僮鳎篋ROPTABLESPACEtablespaceINCLUDINGCONTENTS

CASCADECONSTRAINTS

如果從父表中刪除行時沒有使用DELETECASCADE選項，Oracle服務器必須確保子表中的行不包含相應的外鍵。同樣，僅當子行中不包含舊鍵值時，才允許更新父鍵。如果子表的外鍵上沒有索引，則Oracle服務器鎖定子表并禁止更改以確保引用完整性。如果表上有索引，則可通過鎖定索引項并避免子表上有更具限制性的鎖來維護引用完整性。如果必須從不同的事務處理同時更新兩個表，則在外鍵列上創(chuàng)建索引。當在子表中插入數(shù)據(jù)或更新子表中的外鍵列時，Oracle服務器檢查父表上用來執(zhí)行引用關鍵字的索引。因此，僅當包含索引的表空間聯(lián)機時，該操作才能成功。注意，包含父表的表空間在子表上執(zhí)行DML操作時不需要聯(lián)機。Oracle9i在主鍵上執(zhí)行更新或刪除操作時，不再要求在未建索引的外鍵上獲取共享鎖定。它仍然獲取表級共享鎖定，但在獲取后立即釋放該鎖定。如果更新或刪除多個主鍵，則每行獲取和釋放一次鎖定。1.3.6.外鍵注意事項維護外鍵關系中的表時，應該考慮幾個因231.3.7.創(chuàng)建表時定義約束CREATETABLEhr.employee(idNUMBER(7)CONSTRAINTemployee_id_pkPRIMARYKEYDEFERRABLEUSINGINDEXSTORAGE(INITIAL100KNEXT100K)TABLESPACEindx,last_nameVARCHAR2(25)CONSTRAINTemployee_last_name_nnNOTNULL,dept_idNUMBER(7))TABLESPACEusers;1.3.7.創(chuàng)建表時定義約束CREATETABLEhr.241.3.7.創(chuàng)建表時定義約束可以在創(chuàng)建或改變表時定義約束。請使用CREATETABLE或ALTERTABLE語句中的constraint_clause子句來定義約束。要定義完整性約束，必須具有所需的權限。要創(chuàng)建引用完整性約束，父表必須位于您自己的方案中，或者您必須對父表中的引用鍵列擁有REFERENCES權限。column_constraint語法是表定義的一部分。在創(chuàng)建表時，可以使用以下語法定義約束：columndatatype[CONSTRAINTconstraint]{[NOT]NULL|UNIQUE[USINGINDEXindex_clause]|PRIMARYKEY[USINGINDEXindex_clause]|REFERENCES[schema.]table[(column)][ONDELETECASCADE]|CHECK(condition)}constraint_state:==[NOTDEFERRABLE|DEFERRABLE[INITIALLY{IMMEDIATE|DEFERRED}]][DISABLE|ENABLE[VALIDATE|NOVALIDATE]]其中：CONSTRAINT：使用存儲在數(shù)據(jù)字典中的名稱constraint來標識完整性約束USINGINDEX：指定將index-clause中定義的參數(shù)用于Oracle服務器使用的索引，以執(zhí)行唯一鍵約束或主鍵約束（索引的名稱與約束的名稱相同。）DEFERRABLE：表示可使用SETCONSTRAINT命令將約束檢查延遲到事務處理結束時NOTDEFERRABLE：表示在每一DML語句結束時檢查該約束（會話或事務處理不能延遲NOTDEFERRABLE約束。NOTDEFERRABLE是缺省值。）INITIALLYIMMEDIATE：表示在每一事務處理開始時，缺省為在每一DML語句結束時檢查該約束（如果沒有指定子句INITIALLY，則缺省情況下為INITIALLYIMMEDIATE。）INITIALLYDEFERRED：表示該約束為DEFERRABLE，并指定缺省時只在每一事務處理結束時檢查該約束DISABLE：禁用完整性約束（如果禁用完整性約束，則Oracle服務器不執(zhí)行該約束。）1.3.7.創(chuàng)建表時定義約束可以在創(chuàng)建或改變表時定義約束。請251.3.8.約束定義原則1.3.8.約束定義原則261.3.8.約束定義原則定義約束時遵循下列原則十分有用：

?將用于執(zhí)行主鍵約束和唯一性約束的索引與表放在不同的表空間中。這可通過指定USINGINDEX子句或通過創(chuàng)建表、創(chuàng)建索引并改變表以添加或啟用約束來實現(xiàn)。

? 如果經(jīng)常向表中批量加載數(shù)據(jù)，則最好先禁用約束，執(zhí)行完加載后再啟用約束。如果唯一索引用于執(zhí)行主鍵約束或唯一性約束，則在禁用約束時必須刪除該索引。在這種情況下，可以使用非唯一索引執(zhí)行主鍵約束或唯一性約束來改善性能：創(chuàng)建可延遲的鍵，或者在定義或啟用鍵之前創(chuàng)建索引。

? 如果表中包含自引用外鍵，請使用下列方法之一加載數(shù)據(jù)：

– 在初始加載后定義或啟用外鍵。–將約束定義為可延遲的約束。在頻繁加載數(shù)據(jù)的情況下，第二種方法非常有用。

1.3.8.約束定義原則定義約束時遵循下列原則十分有用：271.3.9.啟用約束1.3.9.啟用約束281.3.9.啟用約束可以采用下列兩種方法之一來啟用當前禁用的約束：ENABLENOVALIDATE或ENABLEVALIDATE啟用NOVALIDATE:對于當前已有索引的PRIMARYKEY和UNIQUE約束，啟用NOVALIDATE約束比啟用VALIDATE約束要快得多，這是因為，如果約束是可延遲的，則不檢查現(xiàn)有數(shù)據(jù)是否違反約束。如果使用該選項啟用約束，則不要求鎖定表。這種方法適合表上有許多DML活動的情況，如在OLTP環(huán)境中。下列命令可用于啟用ENABLENOVALIDATE約束：ALTERTABLE[schema.]tableENABLENOVALIDATE{CONSTRAINTconstraint|PRIMARYKEY|UNIQUE(column[,column]...)}[USINGINDEXindex_clause]限制：USINGINDEX子句僅適用于創(chuàng)建為可延遲的主鍵約束或唯一性約束，并且下列條件之一為真的情況：

?約束被創(chuàng)建為禁用。

?約束被禁用且索引已刪除。但是，如果需要創(chuàng)建索引，使用這種啟用約束的方法并不能比ENABLEVALIDATE帶來更多的好處，因為Oracle服務器在建立索引時鎖定表。

1.3.9.啟用約束可以采用下列兩種方法之一來啟用當前禁用的291.3.10.啟用約束21.3.10.啟用約束2301.3.10.啟用約束2啟用VALIDATE約束后將檢查現(xiàn)有數(shù)據(jù)中是否違反約束。這是啟用約束時的缺省操作。若在禁用約束時執(zhí)行，則會產(chǎn)生下列影響：

? 鎖定表，以防在驗證完現(xiàn)有數(shù)據(jù)前對表進行更改。

?如果索引列上不存在索引，Oracle服務器就會創(chuàng)建一個索引。當啟用不可延遲的主鍵約束或唯一性約束時，Oracle服務器將創(chuàng)建一個唯一索引。對于可延遲的主鍵約束或唯一性約束，將建立一個非唯一索引。

如果在執(zhí)行約束時執(zhí)行此命令，則不要求在驗證過程中鎖定任何表。執(zhí)行的約束將保證在驗證期間不會出現(xiàn)違反約束的情況。這有如下好處：

? 所有約束并發(fā)啟用。

? 每一約束在內(nèi)部保持并行。

? 允許表上存在并發(fā)操作。

使用以下命令啟用約束:ENABLEVALIDATE：ALTERTABLE[schema.]tableENABLE[VALIDATE]{CONSTRAINTconstraint|PRIMARYKEY|UNIQUE(column[,column]...)}[USINGINDEXindex_clause][EXCEPTIONSINTO[schema.]table]注：

?VALIDATE選項為缺省設置，不需要在啟用被禁用約束時指定。

?如果表中的數(shù)據(jù)違反約束，則語句回退，約束仍被禁用。

?下面部分將討論EXCEPTIONS子句的使用。1.3.10.啟用約束2啟用VALIDATE約束后將檢查現(xiàn)有311.3.11.使用exception表使用EXCEPTIONS表

?通過運行utlexcpt1.sql腳本來創(chuàng)建EXCEPTIONS表。

?執(zhí)行帶有EXCEPTIONS選項的ALTERTABLE語句。

?使用EXCEPTIONS上的子查詢定位包含無效數(shù)據(jù)的行。

?糾正錯誤。

?重新執(zhí)行ALTERTABLE以啟用約束。如何識別行違反EXCEPTIONS子句標識出任何違反已啟用約束的行。使用下列步驟檢測違反約束的行為，糾正它們并重新啟用約束：如果尚未創(chuàng)建EXCEPTIONS，則運行utlexcpt1.sql腳本：SQL>@?/rdbms/admin/utlexcpt1Statementprocessed.SQL>DESCRIBEexceptions注：utlexcpt1.sql腳本的確切名稱和位置視操作系統(tǒng)而定。有關詳細信息，請參閱專用于該操作系統(tǒng)的Oracle文檔。

1.3.11.使用exception表使用EXCEPTION321.3.12.獲取約束信息1.3.12.獲取約束信息331.4.OracleSQL操作符AboutSQLOperatorsArithmeticOperatorsConcatenationOperatorSetOperatorsUser-DefinedOperators1.4.OracleSQL操作符AboutSQLOpe341.4.1.集合操作符UnionUnionallIntersectMinus1.4.1.集合操作符Union351.4.1.集合操作符--OE/OE--UNIONselectlocation_id,department_name"Department",to_char(null)"Warehouse"fromdepartmentsunionselectlocation_id,to_char(null)"Department",warehouse_namefromwarehouses;--UNIONALLSELECTlocation_idFROMlocationsUNIONALLSELECTlocation_idFROMdepartments;--INTERSECTSELECTproduct_idFROMinventoriesINTERSECTSELECTproduct_idFROMorder_items;--MINUSSELECTproduct_idFROMinventoriesMINUSSELECTproduct_idFROMorder_items;1.4.1.集合操作符--OE/OE361.4.1.集合操作符--OE/OE--UNIONselectlocation_id,department_name"Department",to_char(null)"Warehouse"fromdepartmentsunionselectlocation_id,to_char(null)"Department",warehouse_namefromwarehouses;--UNIONALLSELECTlocation_idFROMlocationsUNIONALLSELECTlocation_idFROMdepartments;--INTERSECTSELECTproduct_idFROMinventoriesINTERSECTSELECTproduct_idFROMorder_items;--MINUSSELECTproduct_idFROMinventoriesMINUSSELECTproduct_idFROMorder_items;1.4.1.集合操作符--OE/OE371.5.表達式AboutSQLExpressionsSimpleExpressionsCompoundExpressionsCASEExpressionsCURSORExpressionsDatetimeExpressionsFunctionExpressionsINTERVALExpressionsObjectAccessExpressionsScalarSubqueryExpressionsTypeConstructorExpressionsVariableExpressionsExpressionLists1.5.表達式AboutSQLExpressions381.6.條件AboutSQLConditionsComparisonConditionsLogicalConditionsMembershipConditionsRangeConditionsNullConditionsEXISTSConditionsLIKEConditions1.6.條件AboutSQLConditions391.7.常用的SQLDDL子句allocate_extent_clauseconstraintsdeallocate_unused_clausefile_specificationlogging_clauseparallel_clausephysical_attributes_clausestorage_clause1.7.常用的SQLDDL子句allocate_exten401.7.1.范圍分配子句使用在:altertable,alterindex1.7.1.范圍分配子句使用在:altertable,al411.7.2.釋放未使用空間的子句使用在:altertable,alterindex1.7.2.釋放未使用空間的子句使用在:altertabl421.7.3.文件詳述子句使用在:createtablespace,createtemporarytablespaceAutoextend_clause:1.7.3.文件詳述子句使用在:createtablesp431.7.4.物理屬性子句使用在:createtable,createindex1.7.4.物理屬性子句使用在:createtable,c441.7.5.存儲子句使用在:createtablespace,createtable,createindex1.7.5.存儲子句使用在:createtablespac451.7.6.創(chuàng)建表空間1.7.6.創(chuàng)建表空間461.7.6.創(chuàng)建表空間Extent_management_clause::=Segment_management_clause::=(只有永久的,本地管理的表空間才有關的)1.7.6.創(chuàng)建表空間Extent_management_c471.7.7.創(chuàng)建臨時表空間create_temporary_tablespace::=temp_tablespace_extent::=1.7.7.創(chuàng)建臨時表空間create_temporary_481.7.8.創(chuàng)建表1.7.8.創(chuàng)建表491.7.9.創(chuàng)建索引Table_index_clause::=1.7.9.創(chuàng)建索引Table_index_clause::501.8.SQL查詢和子查詢AboutQueriesandSubqueriesCreatingSimpleQueriesHierarchicalQueriesTheUNION[ALL],INTERSECT,MINUSOperatorsSortingQueryResultsJoinsUsingSubqueriesUnnestingofNestedSubqueriesSelectingfromtheDUALTableDistributedQueries1.8.SQL查詢和子查詢AboutQueriesan511.8.1.連接A

join

isaquerythatcombinesrowsfromtwoormoretables,views,ormaterializedviews.Oracleperformsajoinwhenevermultipletablesappearinthequery'sFROMclause.Thequery'sselectlistcanselectanycolumnsfromanyofthesetables.Ifanytwoofthesetableshaveacolumnnameincommon,thenyoumustqualifyallreferencestothesecolumnsthroughoutthequerywithtablenamestoavoidambiguity.Joincondition:MostjoinqueriescontainWHEREclauseconditionsthatcomparetwocolumns,eachfromadifferenttable.Suchaconditioniscalledajoincondition.Toexecuteajoin,Oraclecombinespairsofrows,eachcontainingonerowfromeachtable,forwhichthejoinconditionevaluatestoTRUE.Thecolumnsinthejoinconditionsneednotalsoappearintheselectlist.1.8.1.連接Ajoinisaquerythat521.8.1.連接(1)EquijoinsSELECTlast_name,job_id,departments.department_id,department_nameFROMemployees,departmentsWHEREemployees.department_id=departments.department_id;

(2)SelfJoinsSELECTe1.last_name||'worksfor'||e2.last_name"EmployeesandTheirManagers"FROMemployeese1,employeese2WHEREe1.manager_id=e2.employee_idANDe1.last_nameLIKE'R%';(3)CartesianProducts(4)InnerJoins(5)OuterJoinsleftouterjoinSELECTd.department_id,e.last_nameFROMdepartmentsdLEFTOUTERJOINemployeeseONd.department_id=e.department_idORDERBYd.department_id;rightouterjoinSELECTd.department_id,e.last_nameFROMdepartmentsdRIGHTOUTERJOINemployeeseONd.department_id=e.department_idORDERBYd.department_id;fullouterjoinSELECTd.department_idasd_dept_id,e.department_idase_dept_id,e.last_nameFROMdepartmentsdFULLOUTERJOINemployeeseONd.department_id=e.department_idORDERBYd.department_id;1.8.1.連接(1)Equijoins531.8.2.子查詢Asubqueryanswersmultiple-partquestions.Forexample,todeterminewhoworksinTaylor'sdepartment,youcanfirstuseasubquerytodeterminethedepartmentinwhichTaylorworks.YoucanthenanswertheoriginalquestionwiththeparentSELECTstatement.AsubqueryintheFROMclauseofaSELECTstatementisalsocalledan

inlineview.AsubqueryintheWHEREclauseofaSELECTstatementisalsocalleda

nestedsubquery.Oracleperformsa

correlatedsubquery

whenthesubqueryreferencesacolumnfromatablereferredtointheparentstatement.Acorrelatedsubqueryisevaluatedonceforeachrowprocessedbytheparentstatement.TheparentstatementcanbeaSELECT,UPDATE,orDELETEstatement.CorrelatedsubqueryExample:SELECTdepartment_id,last_name,salaryFROMemployeesxWHEREsalary>(SELECTAVG(salary)FROMemployeesWHEREx.department_id=department_id)ORDERBYdepartment_id;

1.8.2.子查詢Asubqueryanswer541.8.3.Select語法1.8.3.Select語法551.8.3.Select語法1.8.3.Select語法561.OracleSQL基礎Welcome!RainnyZhong138265865711.OracleSQL基礎Welcome!Rainny571.1.OracleSQL簡介Dr.E.F.Coddpublishedthepaper,"ARelationalModelofDataforLargeSharedDataBanks",inJune1970intheAssociationofComputerMachinery(ACM)journal,CommunicationsoftheACM.Codd'smodelisnowacceptedasthedefinitivemodelforrelationaldatabasemanagementsystems(RDBMS).Thelanguage,

StructuredEnglishQueryLanguage

("SEQUEL")wasdevelopedbyIBMCorporation,Inc.,touseCodd'smodel.SEQUELlaterbecameSQL(stillpronounced"sequel").In1979,RelationalSoftware,Inc.(nowOracleCorporation)introducedthefirstcommerciallyavailableimplementationofSQL.Today,SQLisacceptedasthestandardRDBMSlanguage.SQLStandardsOracleCorporationstrivestocomplywithindustry-acceptedstandardsandparticipatesactivelyinSQLstandardscommittees.Industry-acceptedcommitteesaretheAmericanNationalStandardsInstitute(ANSI)andtheInternationalStandardsOrganization(ISO),whichisaffiliatedwiththeInternationalElectrotechnicalCommission(IEC).BothANSIandtheISO/IEChaveacceptedSQLasthestandardlanguageforrelationaldatabases.WhenanewSQLstandardissimultaneouslypublishedbytheseorganizations,thenamesofthestandardsconformtoconventionsusedbytheorganization,butthestandardsaretechnicallyidentical.ThelatestSQLstandardwasadoptedinJuly1999andisoftencalledSQL:99.Theformalnamesofthisstandardare:ANSIX3.135-1999,"DatabaseLanguageSQL",Parts1("Framework"),2("Foundation"),and5("Bindings")ISO/IEC9075:1999,"DatabaseLanguageSQL",Parts1("Framework"),2("Foundation"),and5("Bindings")1.1.OracleSQL簡介Dr.E.F.Codd581.1.1.OracleSQL的處理過程1.1.1.OracleSQL的處理過程591.1.1.OracleSQL的處理過程處理SQL語句處理查詢：

?語法分析：

–搜索同一語句

–檢查語法、對象名和權限

–鎖定語法分析過程中使用的對象

–創(chuàng)建和存儲執(zhí)行計劃

?綁定：獲取變量值

?執(zhí)行：處理語句

?提?。簩⒔Y果行返回用戶進程處理DML語句：

?語法分析：與處理查詢時的語法分析階段相同。

?綁定：與處理查詢時的綁定階段相同。

?執(zhí)行：

– 如果數(shù)據(jù)庫緩沖區(qū)高速緩存中不存在某些數(shù)據(jù)塊和還原塊，服務器進程就從數(shù)據(jù)文件將它們讀入數(shù)據(jù)庫緩沖區(qū)高速緩存。

–服務器進程鎖定要進行修改的行。還原塊用于存儲數(shù)據(jù)的前像，以便在需要時回退DML語句。

– 數(shù)據(jù)塊記錄數(shù)據(jù)的新值。

– 服務器進程將數(shù)據(jù)的前像記錄到回退塊中，并更新數(shù)據(jù)塊。這兩種更改都是在數(shù)據(jù)庫緩沖區(qū)高速緩存中進行的。數(shù)據(jù)庫緩沖區(qū)高速緩存中所有已更改的塊都標記為灰數(shù)據(jù)緩沖區(qū)，即與磁盤中相應的塊不同的緩沖區(qū)。

–DELETE或INSERT命令的處理使用類似的步驟。DELETE命令的前像包含已刪除行中的列值，而INSERT命令的前像中包含行的位置信息。

處理DDL語句：DDL（數(shù)據(jù)定義語言）語句的執(zhí)行與DML（數(shù)據(jù)操縱語言）語句和查詢的執(zhí)行不盡相同，因為成功執(zhí)行DDL語句需要對數(shù)據(jù)字典具有寫權限。對于這些語句，語法分析階段實際上包括分析、數(shù)據(jù)字典查找和執(zhí)行。事務處理管理SQL語句、會話管理SQL語句和系統(tǒng)管理SQL語句在語法分析和執(zhí)行階段處理。要重新執(zhí)行這些語句，再次進入執(zhí)行階段即可。1.1.1.OracleSQL的處理過程處理SQL語句處601.2.OracleSQL基本元素DatatypesLiteralsFormatModelsNullsPseudocolumns1.2.OracleSQL基本元素Datatypes611.2.1.OracleSQL數(shù)據(jù)類型OracleBuilt-inDatatypesANSI,DB2,andSQL/DSDatatypesUser-DefinedTypesOracle-SuppliedTypes1.2.1.OracleSQL數(shù)據(jù)類型OracleBui6.Oracle內(nèi)置數(shù)據(jù)類型.Oracle內(nèi)置數(shù)據(jù)類型6.Oracle內(nèi)置的數(shù)據(jù)類型概覽CharacterDatatypes------CHARDatatype------NCHARDatatype------NVARCHAR2Datatype------VARCHAR2DatatypeNUMBERDatatypeLONGDatatypeDatetimeandIntervalDatatypes------DATE------TIMESTAMP------TIMESTAMPWITHTIMEZONE------TIMESTAMPWITHLOCALTIMEZONERAWandLONGRAWDatatypesLOB------BLOB------CLOB------NCLOBROWIDDatatypeBFILEDatatype.Oracle內(nèi)置的數(shù)據(jù)類型概覽Charact641.2.2.OracleSQL偽列CURRVALandNEXTVAL---sequence.CURRVAL---sequence.NEXTVAL

LEVELSELECTemployee_id,last_nameFROMemployeesWHERE(employee_id,LEVEL)IN(SELECTemployee_id,2FROMemployees)STARTWITHemployee_id=2CONNECTBYPRIORemployee_id=manager_id;ROWIDSELECTROWID,last_nameFROMemployeesWHEREdepartment_id=20;

ROWNUMSELECT*FROMemployeesWHEREROWNUM<10;1.2.2.OracleSQL偽列CURRVALand651.3.約束1.3.約束661.3.1.數(shù)據(jù)完整性1.3.1.數(shù)據(jù)完整性671.3.1.數(shù)據(jù)完整性數(shù)據(jù)完整性是指數(shù)據(jù)庫中的數(shù)據(jù)符合業(yè)務規(guī)則。維護數(shù)據(jù)完整性共有三種主要方法：

?應用程序代碼

?數(shù)據(jù)庫觸發(fā)器

?聲明完整性約束

具體使用上述哪種方法映射業(yè)務規(guī)則是設計時應考慮的問題。而數(shù)據(jù)庫管理員主要關心的是實施設計人員選擇的方法，并在完整性需求和性能要求之間取得平衡。應用程序代碼既可作為數(shù)據(jù)庫中的存儲過程實現(xiàn)，也可作為在客戶端上運行的應用程序?qū)崿F(xiàn)。本課著重講述完整性約束的使用。數(shù)據(jù)庫觸發(fā)器：數(shù)據(jù)庫觸發(fā)器是PL/SQL程序，在表上發(fā)生事件（如插入或更新列）時執(zhí)行?？梢詥⒂没蚪糜|發(fā)器，即可以設置觸發(fā)器在事件發(fā)生時執(zhí)行，或者將觸發(fā)器設置為不執(zhí)行（即使已定義）。通常情況下，創(chuàng)建數(shù)據(jù)庫觸發(fā)器只是為了強制應用不能定義為完整性約束的復雜業(yè)務規(guī)則。注：數(shù)據(jù)庫觸發(fā)器在其它Oracle課程中講述。完整性約束：完整性約束是執(zhí)行業(yè)務規(guī)則的首選機制，這是因為它可以：

?改善性能

?易于聲明和修改，不需要進行大量編碼

?集中管理規(guī)則

?使用靈活（啟用或禁用）?在數(shù)據(jù)字典中完全文檔化以下部分解釋完整性約束的行為，并論述Oracle服務器如何執(zhí)行這些完整性約束。

1.3.1.數(shù)據(jù)完整性數(shù)據(jù)完整性是指數(shù)據(jù)庫中的數(shù)據(jù)符合業(yè)務規(guī)681.3.2.約束的類型約束

說明------------------------------------------------------------------------------------------NOTNULL

指示出列不能包含空值UNIQUE

指示一個列或列的組合是唯一的PRIMARYKEY

指示一個列或列的組合作為表的主鍵FOREIGNKEY

指示一個列或列的組合在引用完整性約束中作為外鍵CHECK

指定表中的每一行必須滿足的條件缺省情況下，表中的所有列均可以為空?？找馕吨鴽]有值。NOTNULL約束要求表列必須包含值。UNIQUE關鍵字約束要求某列或一組列（關鍵字）中的值必須是唯一的。表中的任何兩行在指定的一列或一組列中不能有重復的值。數(shù)據(jù)庫中的每個表至多有一個PRIMARYKEY約束。PRIMARYKEY約束確保以下兩種情況均為真：?表中的任何兩行在指定列中沒有重復的值。?主鍵列不包含NULL值?某列或一組列上的CHECK完整性約束要求，對于表的每一行，指定的條件必須為真或未知。雖然NOTNULL和CHECK約束并不直接要求DBA關注，但PRIMARYKEY、UNIQUE和FOREIGNKEY約束仍須進行管理，以確保高可用性和性能水平可接受。1.3.2.約束的類型約束691.3.3.約束的狀態(tài)1.3.3.約束的狀態(tài)701.3.3.約束的狀態(tài)可以啟用(ENABLE)或禁用(DISABLE)完整性約束。如果啟用某個約束，則在數(shù)據(jù)庫中輸入或更新數(shù)據(jù)時，就會對數(shù)據(jù)進行檢查。禁止輸入不符合約束規(guī)則的數(shù)據(jù)。如果禁用某個約束，則可以在數(shù)據(jù)庫中輸入不符合約束規(guī)則的數(shù)據(jù)。完整性約束可處于以下狀態(tài)之一：

?DISABLENOVALIDATE

?DISABLEVALIDATE

?ENABLENOVALIDATE

?ENABLEVALIDATE

DISABLENOVALIDATE：不檢查處于DISABLENOVALIDATE狀態(tài)的約束。表中的數(shù)據(jù)（包括輸入或更新的新數(shù)據(jù)）可以不符合約束所定義的規(guī)則。DISABLEVALIDATE：當約束處于此狀態(tài)時，不允許對受約束的列進行任何修改。另外，約束上的索引將被刪除并且禁用約束。注：如果約束可延遲，則不刪除索引。

ENABLENOVALIDATE：如果約束處于此狀態(tài)，則不能輸入違反約束的新數(shù)據(jù)。但是，表可能包含無效的數(shù)據(jù)，即數(shù)據(jù)違反約束。啟用處于NOVALIDATE狀態(tài)的約束對正在上載有效OLTP數(shù)據(jù)的數(shù)據(jù)倉庫配置是非常有用的。ENABLEVALIDATE：如果約束處于此狀態(tài)，則不能將違反約束的行插入到表中。但是，禁用該約束時，可以插入此類行。此類行稱為該約束的例外。如果約束處于ENABLENOVALIDATE狀態(tài)，則在禁用約束時輸入的數(shù)據(jù)所引起的違反情況仍然存在。要將約束置于已驗證狀態(tài)，必須更新或刪除違反約束的行。當某一約束由禁用狀態(tài)更改為ENABLEVALIDATE時，將鎖定表并對表中的所有數(shù)據(jù)進行一致性檢查。這可能會引起DML操作（如等待數(shù)據(jù)加載），因此，建議先從禁用狀態(tài)轉為ENABLENOVALIDATE，然后再轉為ENABLEVALIDATE。這些狀態(tài)之間的轉換須符合以下規(guī)則：?除非指定NOVALIDATE，否則ENABLE表示VALIDATE。?除非指定VALIDATE，否則DISABLE表示NOVALIDATE。?VALIDATE和NOVALIDATE沒有缺省的ENABLE和DISABLE狀態(tài)。?當唯一鍵或主鍵從DISABLE狀態(tài)轉為ENABLE狀態(tài)且沒有現(xiàn)有索引時，將自動創(chuàng)建唯一索引。（如果索引可延遲，則將存在異常。）與此類似，當唯一鍵或主鍵從ENABLE轉為DISABLE且是使用唯一索引啟用時，則刪除該唯一索引。?當任何約束從NOVALIDATE狀態(tài)轉為VALIDATE狀態(tài)時，必須檢查所有的數(shù)據(jù)。但是，從VALIDATE轉為NOVALIDATE時，將忽略數(shù)據(jù)已經(jīng)過檢查這一事實。

?將單個約束從ENABLENOVALIDATE狀態(tài)轉為ENABLEVALIDATE狀態(tài)時，并不禁止使用讀取、寫入或其它DDL語句。1.3.3.約束的狀態(tài)可以啟用(ENABLE)或禁用(DIS711.3.4.約束檢查1.3.4.約束檢查721.3.4.約束檢查約束檢查:可以將約束有效性檢查延遲到事務處理完成時進行。不可延遲的約束或立即約束：不可延遲的約束（也稱立即約束）在每個DML語句結束時執(zhí)行。違反約束將導致語句回退。如果約束導致刪除級聯(lián)等操作，則將該操作作為引起該操作的語句的一部分。不能將定義為不可延遲的約束修改為在事務處理結束時執(zhí)行?？裳舆t的約束：可延遲的約束是僅在提交事務處理時才檢查的約束。如果提交時檢測到違反約束的行，則回退整個事務處理。當同時輸入外鍵關系中的父行和子行時（如在訂單輸入系統(tǒng)中同時輸入訂單和訂單所包含的項目），這些約束非常有用?？梢詫⒍x為可延遲的約束指定為下列模式之一：?“最初為立即”表示，除非另外明確設定，否則在缺省情況下作為立即約束使用。

?“最初為延遲”表示，缺省情況下只在事務處理結束時執(zhí)行約束。1.3.4.約束檢查約束檢查:可以將約束有效性檢查延遲到事務731.3.4.將約束定義為立即或延遲1.3.4.將約束定義為立即或延遲741.3.4.將約束定義為立即或延遲SETCONSTRAINTS語句用于將特定事務處理的約束設置為DEFERRED或IMMEDIATE。可以使用此語句設置約束名稱列表或約束的模式。SETCONSTRAINTS模式將一直持續(xù)到事務處理完成或者另一個SETCONSTRAINTS語句重置模式。SETCONSTRAINTS語句不允許在觸發(fā)器內(nèi)部使用。ALTERSESSION語句還包含將約束設置為IMMEDIATE或DEFERRED的子句SETCONSTRAINTS。此命令缺省為設置所有(ALL)可延遲的約束（不能指定約束名稱列表）。ALTERSESSIONSETCONSTRAINTS語句僅適用于當前的會話。ALTERSESSION

SETCONSTRAINT[S]=

{IMMEDIATE|DEFERRED|DEFAULT}

SETCONSTRAINT|CONSTRAINTS

{constraint|ALL}

{IMMEDIATE|DEFERRED}

1.3.4.將約束定義為立即或延遲SETCONSTRAIN751.3.5.執(zhí)行主鍵和唯一鍵約束1.3.5.執(zhí)行主鍵和唯一鍵約束761.3.5.執(zhí)行主鍵和唯一鍵約束主鍵和唯一鍵通過索引執(zhí)行?？煽刂朴脕韴?zhí)行這些約束的索引的位置和類型。Oracle服務器按下列步驟實現(xiàn)唯一鍵和主鍵約束：

? 如果約束被禁用，則不需要索引。

? 如果啟用約束且約束中的列構成索引的主要部分，則無論是否將索引本身創(chuàng)建為唯一還是非唯一索引，都可以使用該索引執(zhí)行約束。

? 如果啟用約束且沒有任何索引將約束列用作索引的主要部分，則按照下列規(guī)則創(chuàng)建一個名稱與約束相同的索引：

– 如果關鍵字為可延遲，則在關鍵字列上創(chuàng)建一個非唯一索引。

– 如果關鍵字為不可延遲，則將創(chuàng)建一個唯一索引。

? 如果可以使用某個索引，并且約束是不可延遲的，則使用現(xiàn)有索引。如果約束是可延遲的，并且索引是非唯一的，則使用現(xiàn)有索引。

1.3.5.執(zhí)行主鍵和唯一鍵約束主鍵和唯