Oracle數據庫管理.ppt

《Oracle數據庫管理.ppt》由會員分享，可在線閱讀，更多相關《Oracle數據庫管理.ppt（69頁珍藏版）》請在裝配圖網上搜索。

Oracle數據庫管理,本章學習目標Oracle數據庫是一個復雜的大型關系數據庫，數據庫管理的內容很多，包括管理實例、創(chuàng)建數據庫、管理控制文件、日志文件、表空間和數據文件、管理臨時段、數據庫對象、數據完整性、用戶，使用管理工具等。,第五章Oracle數據庫管理,本章內容安排,5.1Oracle數據庫管理簡介,5.2創(chuàng)建、啟動和關閉Oracle數據庫,4.3存儲過程,4.4觸發(fā)器,4.5游標,4.6包,4.7函數,,4.1PL/SQL簡介,4.1.1PL/SQL特點,4.1.2開發(fā)及運行環(huán)境,1.PL/SQL的優(yōu)點（1）PL/SQL是一種高性能的基于事務處理的語言，能運行在任何Oracle環(huán)境中，支持所有數據處理命令。通過使用PL/SQL程序單元處理SQL的數據定義和數據控制元素。（2）PL/SQL支持所有SQL數據類型和所有SQL函數，同時支持所有Oracle對象類型。（3）PL/SQL塊可以被命名和存儲在Oracle服務器中，同時也能被其他的PL/SQL程序或SQL命令調用，任何客戶/服務器工具都能訪問PL/SQL程序，具有很好的可重用性。（4）可以使用Oracle數據工具管理存儲在服務器中的PL/SQL程序的安全性。可以授權或撤銷數據庫其他用戶訪問PL/SQL程序的能力。（5）PL/SQL代碼可以使用任何ASCII文本編輯器編寫，所以對任何Oracle能夠運行的操作系統(tǒng)都是非常便利的。,4.1.1PL/SQL特點,2.PL/SQL的缺點對于SQL，Oracle必須在同一時間處理每一條SQL語句，在網絡環(huán)境下這就意味作每一個獨立的調用都必須被oracle服務器處理，這就占用大量的服務器時間，同時導致網絡擁擠。而PL/SQL是以整個語句塊發(fā)給服務器，這就降低了網絡擁擠。,服務器端PL/SQL不需要顯式的安裝或許可。它是Oracle9i數據庫的隱式部分，此處有相應的文檔。PL/SQL編譯器和解釋器也嵌入到OracleDeveloper中，為開發(fā)者在客戶端和服務器端提供一致的、可利用的開發(fā)模型。,4.1.2開發(fā)及運行環(huán)境,4.2.2常量與變量,4.2.3數據類型,4.2.4運算符,4.2.1PL/SQL塊,4.2.5流程控制,4.2.6異常處理,4.2PL/SQL語法,,1．塊結構,2、塊的命名和匿名,3、塊的執(zhí)行,4.2.1PL/SQL塊,,1.塊結構PL/SQL是一種塊結構的語言，組成PL/SQL程序的單元是邏輯塊，一個PL/SQL程序包含了一個或多個邏輯塊，每個塊都可以劃分為三個部分。（1）塊的三個部分①聲明部分（Declarationsection）聲明部分包含了變量和常量的數據類型和初始值。這個部分是由關鍵字DECLARE開始，如果不需要聲明變量或常量，那么可以忽略這一部分。②執(zhí)行部分（Executablesection）執(zhí)行部分是PL/SQL塊中的指令部分，由關鍵字BEGIN開始，所有的可執(zhí)行語句都放在這一部分，其他的PL/SQL塊也可以放在這一部分。③異常處理部分（Exceptionsection）這一部分是可選的，在這一部分中處理異?；蝈e誤，對異常處理的詳細討論在后面進行。,,（2）PL/SQL塊語法PL/SQL塊語法結構如下：[DECLARE]declarationstatementsBEGINexecutablestatements[EXCEPTION]exceptionstatementsENDPL/SQL塊中的每一條語句都必須以分號結束，SQL語句可以多行，但分號表示該語句的結束。一行中可以有多條SQL語句，他們之間以分號分隔。每一個PL/SQL塊由BEGIN或DECLARE開始，以END結束。注釋由--標示。,,,2．塊的命名和匿名PL/SQL程序塊可以是一個命名的程序塊也可以是一個匿名程序塊，匿名程序塊可以用在服務器端也可以用在客戶端。執(zhí)行部分包含了所有的語句和表達式，執(zhí)行部分以關鍵字BEGIN開始，以關鍵字EXCEPTION結束，如果EXCEPTION不存在，那么將以關鍵字END結束。分號分隔每一條語句，使用賦值操作符:=或SELECTINTO或FETCHINTO給每個變量賦值，執(zhí)行部分的錯誤將在異常處理部分解決，在執(zhí)行部分中可以使用另一個PL/SQL程序塊，這種程序塊被稱為嵌套塊。所有的SQL數據操作語句都可以用于執(zhí)行部分，PL/SQL塊不能在屏幕上顯示SELECT語句的輸出。SELECT語句必須包括一個INTO子串或者是游標的一部分，執(zhí)行部分使用的變量和常量必須首先在聲明部分聲明，執(zhí)行部分必須至少包括一條可執(zhí)行語句，NULL是一條合法的可執(zhí)行語句，事物控制語句COMMIT和ROLLBACK可以在執(zhí)行部分使用，數據定義語言(DataDefinitionlanguage)不能在執(zhí)行部分中使用，DDL語句與EXECUTEIMMEDIATE一起使用或者是DBMS_SQL調用。,,3．塊的執(zhí)行SQL*PLUS中匿名的PL/SQL塊的執(zhí)行是在PL/SQL塊后輸入/來執(zhí)行。命名的程序與匿名程序的執(zhí)行不同，執(zhí)行命名的程序塊必須使用execute關鍵字:。如果在另一個命名程序塊或匿名程序塊中執(zhí)行這個程序，那么就不需要EXECUTE關鍵字。,,1．變量,2、常量,3、有效字符集,4.2.2常量與變量,,,1、變量（1）聲明變量聲明變量的語句格式如下：Variable_name[CONSTANT]databyte[NOTNULL][:=|DEFAULTexpression]注意：可以在聲明變量的同時給變量強制性的加上NOTNULL約束條件，此時變量在初始化時必須賦值。（2）給變量賦值給變量賦值有兩種方式：①直接給變量賦值②通過SQLSELECTINTO或FETCHINTO給變量賦值,,,2、常量常量與變量相似，但常量的值在程序內部不能改變，常量的值在定義時賦予，聲明方式與變量相似，但必須包括關鍵字CONSTANT。常量和變量都可被定義為SQL和用戶定義的數據類型。為了減少這部分程序的修改，編程時使用%TYPE、%ROWTYPE方式聲明變量，使變量聲明的類型與表中的保持同步，隨表的變化而變化，這樣的程序在一定程度上具有更強的通用性。,,,3、有效字符集（1）所有的大寫和小寫英文字母；（2）數字0-9；（3）符號：0＋一*／＜＞＝！一；:.‘＠％，“‘?！埃Γ撸?？[]PL/SQL標識符的最大長度是30個字符，并且不區(qū)分字母的大小寫。但是適當地使用大小寫，可以提高程序的可讀性。,,4.2.3數據類型,例如：定義如下若干類型變量，常量。DECLAREORDER_NONUMBER(3);CUST_NAMEVARCHAR2(20);ORDER_DATEDATE;EMP_NOINTEGER:=25;PICONSTANTNUMBER:=3.1416;BEGINNULL;END;,,4.2.4運算符,與其他程序設計語言相同，PL/SQL有一系列操作符。主要有：算術操作符關系操作符邏輯操作符,,1．條件結構,2、循環(huán)控制,3、GOTO語句,4.2.5流程控制,4、嵌套,1、條件結構1）If條件判斷邏輯結構If條件判斷邏輯結構有三種表達方式。①表達式一：ifconditionthenStatementendif該表達式的功能為：若條件為真，執(zhí)行then后的語句；否則，跳出條件語句執(zhí)行endif后的語句。②表達式二：ifconditionthenStatements_1elseStatements_2endif該表達式的功能為：如果條件為真執(zhí)行then后的語句，否則執(zhí)行else后的語句。③表達式三：ifcondition1thenStatements_1elseifcondition2thenStatements_2elseStatements_3endif該表達式的功能為：如果if后的條件成立，執(zhí)行then后面的語句，否則判斷elseif后面的條件，條件成立執(zhí)行第二個then后面的語句，否則執(zhí)行else后的語句。這是條件語句嵌套。IF可以嵌套，可以在IF或IF..ELSE語句中使用IF或IF…ELSE語句。,2）Case表達式Case語句的基本格式如下：Case變量WHEN表達式1then值1WHEN表達式2then值2WHEN表達式3then值3WHEN表達式4then值4ELSE值5END;Case語句的功能：首先設定變量的值作為條件，然后順序檢查表達式，一旦從中找到與條件匹配的表達式值，就停止case語句的處理。,2、循環(huán)控制（1）loop…exit…end循環(huán)控制語句LOOP循環(huán)語句是其中最基本的一種。LOOP語句的格式如下：LOOPstatementsENDLOOP;這種循環(huán)語句是沒有終止的，如果不人為控制的話，其中的statements將會無限地執(zhí)行。一般可以通過加入EXIT語句來終結該循環(huán)。（2）WHILE…..LOOP循環(huán)控制語句WHILE…..LOOP有一個條件與循環(huán)相聯系，如果條件為TRUE，則執(zhí)行循環(huán)體內的語句，如果結果為FALSE，則結束循環(huán)。,（3）FOR..LOOP循環(huán)控制語句FOR..LOOP循環(huán)控制語句的格式如下：FORcounterIN[REVERSE]start_range..end_rangeLOOPstatements;ENDLOOP;LOOP和WHILE循環(huán)的循環(huán)次數都是不確定的，FOR循環(huán)的循環(huán)次數是固定的，counter是一個隱式聲明的變量，初始值是start_range，第二個值是start_range+1，直到end_range，如果start_range等于end_range，那么循環(huán)將執(zhí)行一次。如果使用了REVERSE關鍵字，那么范圍將是一個降序。,3、GOTO語句GOTO語句的格式如下：GOTOLABEL;執(zhí)行GOTO語句時，控制會立即轉到由標簽標記的語句。PL/SQL中對GOTO語句有一些限制，對于塊、循環(huán)、IF語句而言，從外層跳轉到內層是非法的。,4、嵌套程序塊的內部可以有另一個程序塊這種情況稱為嵌套。嵌套要注意的是變量，定義在最外部程序塊中的變量可以在所有子塊中使用，如果在子塊中定義了與外部程序塊變量相同的變量名，在執(zhí)行子塊時將使用子塊中定義的變量。子塊中定義的變量不能被父塊引用。同樣GOTO語句不能由父塊跳轉道子塊中，反之則是合法的。,,1．PL/SQL的異常,2、預定義異常,3、自定義異常,4.2.6異常處理,,,1、PL/SQL的異常異常處理塊中包含了與異常相關的錯誤發(fā)生以及當錯誤發(fā)生時要進行執(zhí)行和處理的代碼。異常部分的語法一般如下：BEGINEXCEPTIONWHENexcep_name1THEN…WHENexcep_name2THEN…WHENOTHERSTHEN…END;,,,2、預定義異常表4-3常見異常情況表,,,3、自定義異常異常不一定必須是oracle返回的系統(tǒng)錯誤，用戶可以在自己的應用程序中創(chuàng)建可觸發(fā)及可處理的自定義異常，調用異常處理需要使用Raise語句。異常情態(tài)的傳播指的是當在程序塊的聲明、執(zhí)行、異常部分分別出現異常情態(tài)時，或在本塊中沒有相應的異常處理器時會將這個異常情態(tài)傳播到哪里，會去激發(fā)那個塊中的處理器。傳播規(guī)則是這樣的:當一個異常情態(tài)是在塊的執(zhí)行部分引發(fā)的(最常見的)，PL/SQL使用下面的規(guī)則確定激活哪個異常處理器。（1）若當前塊對該異常情態(tài)設置了處理器，則執(zhí)行它并成功完成該塊的執(zhí)行，然后控制轉給包含塊。（2）若當前塊沒有該處理器，則通過在包含塊中引發(fā)它來傳播異常情態(tài)。然后對包含塊執(zhí)行PL/SQL的異常操作。另外，無論是在聲明部分引發(fā)了一個異常情態(tài)，還是在異常處理部分引發(fā)，則該異常情態(tài)將立即傳播給包含塊。在包含塊引用上述規(guī)則進行異常情態(tài)的處理，即使在當前塊設置了OTHERS處理器也不會被執(zhí)行。,4.3.2存儲過程的調用,4.3.3存儲過程的釋放,4.3.4實例,4.3.1存儲過程的創(chuàng)建,4.3存儲過程,創(chuàng)建存儲過程的語句如下：CREATE[ORREPLACE]PROCEDURE＜過程名＞＜參數1>，「方式l]＜數據類型1>，＜參數2>，[方式2]＜數據類型2>，……)IS|ASis＿或as完全等價BEGINPL/SQL過程體END＜過程名＞,4.3.1存儲過程的創(chuàng)建,過程參數有以下三種類型：（1）in參數類型：表示輸入給過程的參數。（2）out參數類型：表示參數在過程中將被賦值，可以傳給過程體的外部。（3）inout參數類型：表示該類參數既可以向過程體傳值，也可以在過程體中賦值，以便向過程體外傳值。,存儲過程可以直接用EXECUT命令調用或PL/SQL程序塊內部調用。用EXECUT命令調用存儲過程的格式如下：SQL>executeproc_name(par1,par2…);,4.3.2存儲過程的調用,存儲過程也可以被另外的PL/SQL塊調用，調用的語句是：declarepar1,par2;beginproc_name(par1,par2…);end;在調用前要聲明變量par1，par2,當某個存儲過程不再需要時，應將其從內存中刪除，以釋放它占用的內存資源。釋放過程的語句格式如下：SQL>dropprocedureproc_name;proc_name為過程名。,4.3.3存儲過程的釋放,編寫存儲過程，顯示所指定雇員名所在的部門名和位置。CREATEORREPLACEPROCEDUREDeptMesg(penameemp.ename%TYPE,pdnameOUTdept.dname%TYPE,plocOUTdept.loc%TYPE)ASBEGINSELECTdname,locINTOpdname,plocFROMemp,deptWHEREemp.deptno=dept.deptnoANDemp.ename=pename;END;/VARIABLEvdnameVARCHAR2(14);VARIABLEvlocVARCHAR2(13);EXECUTEDeptMesg(SMITH,:vdname，:vloc);PRINTvdnamevloc;,4.3.4實例,4.4.2觸發(fā)器的類型,4.4.3觸發(fā)器的創(chuàng)建,4.4.4觸發(fā)器的修改和刪除,4.4.1觸發(fā)器的設計規(guī)則,4.4觸發(fā)器,4.4.5實例,（1）作用范圍清晰（2）不要讓觸發(fā)器去完成Oracle后臺已經能夠完成的功能（3）限制觸發(fā)器代碼的行數（4）不要創(chuàng)建遞歸的觸發(fā)器（5）觸發(fā)器僅在被觸發(fā)語句觸發(fā)時進行集中的，全局的操作，同用戶和數據庫應用無關。,4.4.1觸發(fā)器的設計規(guī)則,可以創(chuàng)建被如下語句所觸發(fā)的觸發(fā)器：（1）DML語句（DELETE，INSERT，UPDATE）；（2）DDL語句（CREATE，ALTER，DROP）；（3）數據庫操作（SERVERERROR，LOGON，LOGOFF，STARTUP，SHUTDOWN）。,4.4.2觸發(fā)器的類型,1．使用CREATETRIGGER語句創(chuàng)建觸發(fā)器使用CREATETRIGGER語句創(chuàng)建觸發(fā)器的語句格式如下：CREATE[ORREPLACE]TRIGGERname{BEFORE|AFTER}{event[OR...]}ONtable[FOR[EACH]{ROW|STATEMENT}][WHEN(condition)]plsqlblock|callprocedures_statement,4.4.3觸發(fā)器的創(chuàng)建,2．注意事項（1）觸發(fā)器可以聲明為在對記錄進行操作之前，在之前(檢查約束之前和INSERT，UPDATE或DELETE執(zhí)行前)或之后(在檢查約束之后和完成INSERT，UPDATE或DELETE操作)觸發(fā).。（2）一個FOREACHROW執(zhí)行指定操作的觸發(fā)器為操作修改的每一行都調用一次。（3）SELECT并不更改任何行，因此不能創(chuàng)建SELECT觸發(fā)器.這種場合下規(guī)則和視圖更適合。（4）觸發(fā)器和某一指定的表格有關，當該表格備刪除時，任何與該表有關的觸發(fā)器同樣會被刪除。（5）在一個表上的每一個動作只能有一個觸發(fā)器與之關聯。（6）在一個單獨的表上，最多只能創(chuàng)建三個觸發(fā)器與之關聯，一個INSERT觸發(fā)器，一個DELETE觸發(fā)器和一個UPDATE觸發(fā)器。,刪除觸發(fā)器的語句格式為：DROPTRIGGERnameONtable;一個觸發(fā)器由三部分組成:觸發(fā)事件或語句、觸發(fā)限制和觸發(fā)器動作。觸發(fā)事件或語句是指引起激發(fā)觸發(fā)器的SQL語句，可為對一指定表的INSERT、UNPDATE或DELETE語句。觸發(fā)限制是指定一個布爾表達式，當觸發(fā)器激發(fā)時該布爾表達式是必須為真。觸發(fā)器作為過程，是PL/SQL塊，當觸發(fā)語句發(fā)出、觸發(fā)限制計算為真時該過程被執(zhí)行。,4.4.4觸發(fā)器的修改和刪除,編寫一個數據庫觸發(fā)器，當任何時候某個部門從dept表中刪除時，該觸發(fā)器將從emp表中刪除該部門的所有雇員。CREATEORREPLACETRIGGERdel_emp_deptnoBEFOREDELETEONdeptFOREACHROWBEGINDELETEFROMempWHEREdeptno=:OLD.deptno;END;,4.4.5實例,4.5.2隱式游標,4.5.3實例,4.5.1顯式游標,4.5游標,Oracle游標是一種用于輕松的處理多行數據的機制，沒有游標，Oracle開發(fā)人員必須單獨地、顯式地取回并管理游標查詢選擇的每一條記錄。游標的另一項功能是，它包含一個跟蹤當前訪問的記錄的指針，這使程序能夠一次處理多條記錄。,,1．聲明游標,2、打開游標,3、從游標中取回數據,4.5.1顯式游標,4、關閉游標,,1.聲明游標聲明游標的語句格式如下：DECLAREcursor_nameISSELECTstatement聲明游標完成了下面兩個目的：（1）給游標命名；（2）將一個查詢與游標關聯起來。,,,2．打開游標打開游標的語句格式如下：OPENcursor_name;打開游標將激活查詢并識別活動集，可是在執(zhí)行游標取回命令之前，并沒有真正取回記錄。OPEN命令還初始化了游標指針，使其指向活動集的第一條記錄。游標被打開后，直到關閉之前，取回到活動集的所有數據都是靜態(tài)的。換句話說，游標忽略所有在游標打開之后，對數據執(zhí)行的SQLDML命令(INSERT、UPDATE、DELETE和SELECT)，因此只有在需要時才打開它，要刷新活動集，只需關閉并重新打開游標即可。,,,3．從游標中取回數據FETCH命令以每次一條記錄的方式取回活動集中的記錄。通常將FETCH命令和某種迭代處理結合起來使用，在迭代處理中，FETCH命令每執(zhí)行一次，游標前進到活動集的下一條記錄。FETCH命令的語句格式如下：FETCHcursor_nameINTOrecord_list;執(zhí)行FETCH命令后，活動集中的結果被取回到PL/SQL變量中，以便在PL/SQL塊中使用。每取回一條記錄，游標的指針就移向活動集的下一條記錄。,,,4．關閉游標CLOSE語句關閉以前打開的游標，使得活動集不確定。CLOSE語句的格式：CLOSEcursor_name;,,,隱式游標也可以叫做SQL游標。和顯式的游標不同，不能對一個SQL游標顯式的執(zhí)行OPEN，CLOSE和FETCH語句。Oracle隱式的打開SQL游標、處理SQL游標、然后再關閉該游標。Oracle提供隱式游標的主要目的就是利用這些游標的屬性來確定SQL語句運行的情況。,4.5.2隱式游標,,,以上游標應用的完整程序代碼如下：DECLARECURSORC1ISSELECTVIEW_NAMEFROMALL_VIEWSWHEREROWNUMIS變量、常量及數據類型定義；游標定義；函數、過程定義和參數列表及返回類型；END＜包名＞；,,2.包主體部分創(chuàng)建包主體部分的語句格式如下：CREATEPACKAGEBODY＜包名＞AS游標、函數、過程的具體定義；END＜包名＞；,,1．描述部分,2、包體部分,4.6.2實例,,1.描述部分CREATEPACKAGEznz_package//創(chuàng)建包頭ISman_numNUMBER;//定義變量woman_numNUMBER;CURSOR學生;//定義游標CREATEFUNCTIONf_count(insexIN學生.sex%TYPE)//定義函數RETURNNUMBER;//定義返回值類型CREATEPROCEDUREp_count//定義過程(in_sexIN學生.sex%TYPE，//過程參數out_numOUTNUMBER);ENDznz_package;//包頭結束,,2.包體部分CREATEPACKAGEBODYznz_package//創(chuàng)建包體ASCURSOR學生IS//游標具體定義SELECT學號，姓名FROM學生WHERE學號executeman_num:=count_num(‘女’),,3.函數的釋放當函數不再使用時，要用drop命令將其從內存中刪除，例如：SQL>dropfunctioncount_num;,,4.實例編寫一個函數以顯示該雇員在此組織中的工作天數。CREATEORREPLACEFUNCTIONHire_Day(noemp.empno%TYPE)RETURNNUMBERASvhiredateemp.hiredate%TYPE;vdayNUMBER;BEGINSELECThiredateINTOvhiredateFROMempWHEREempno=no;vday:=CEIL(SYSDATE-vhiredate);RETURNvday;END;,