1、第7章 存儲(chǔ)過程、觸發(fā)器和程序包,南京信息工程大學(xué) 計(jì)算機(jī)與軟件學(xué)院,教學(xué)目的與要求,（1）熟練游標(biāo)的使用； （2）掌握過程和函數(shù)的創(chuàng)建、調(diào)用過程； （3）理解存儲(chǔ)過程中各種形式的參數(shù)； （4）掌握觸發(fā)器的概念、分類及特點(diǎn)； （5）熟悉程序包規(guī)范和主體。,7.1 游標(biāo) 7.2 存儲(chǔ)過程和函數(shù) 7.3 觸發(fā)器 7.4 程序包,本章主要內(nèi)容,教學(xué)重點(diǎn)與難點(diǎn),（1）游標(biāo)的使用 （2）存儲(chǔ)過程、函數(shù)的創(chuàng)建 （3）各種形式的參數(shù) （4）各種觸發(fā)器的特點(diǎn)及使用,7.1 游標(biāo),游標(biāo)的作用就相當(dāng)于指針，通過游標(biāo)程序設(shè)計(jì)語言就可以一次處理查詢結(jié)果集中的一行。在Oracle中，游標(biāo)可以分為兩大類：靜態(tài)游標(biāo)和REF

2、游標(biāo)。REF游標(biāo)是一種引用類型，類似于指針。而靜態(tài)游標(biāo)又可以分為隱式游標(biāo)和顯式游標(biāo)。,7.1.1 隱式游標(biāo),在執(zhí)行一個(gè)SQL語句時(shí)，Oracle會(huì)自動(dòng)創(chuàng)建一個(gè)隱式游標(biāo)。這個(gè)游標(biāo)是內(nèi)存中處理該語句的工作區(qū)域，在其中存儲(chǔ)了執(zhí)行SQL語句的結(jié)果。通過游標(biāo)的屬性可獲知SQL語句的執(zhí)行結(jié)果，以及游標(biāo)的狀態(tài)信息。,%FOUND:布爾型，如果SQL語句至少影響到一行數(shù)據(jù)，則該屬性為TRUE，否則為FALSE。 %NOTFOUND:布爾型，與%FOUND相反。 %ISOPEN:布爾型，當(dāng)游標(biāo)已經(jīng)打開時(shí)返回TRUE，否則為FALSE。 %ROWCOUNT:數(shù)值型，返回受SQL語句影響的行數(shù)。,游標(biāo)的屬性信息反映

3、的是最新的SQL語句處理結(jié)果。在一個(gè)程序這出現(xiàn)多個(gè)SQL語句時(shí)，需要及時(shí)檢查屬性值。,處理SQL語句返回的結(jié)果集時(shí)，需要使用CURSOR FOR LOOP語句通過隱式游標(biāo)進(jìn)行處理。,7.1.2 顯式游標(biāo),在PL/SQL程序中處理結(jié)果集時(shí)，用戶也可以通過顯式定義游標(biāo)，然后手動(dòng)操作該游標(biāo)處理結(jié)果集。使用顯式游標(biāo)處理數(shù)據(jù)需要的四個(gè)步驟：定義游標(biāo)、打開游標(biāo)、提取游標(biāo)數(shù)據(jù)和關(guān)閉游標(biāo)。,（1）定義游標(biāo),游標(biāo)由游標(biāo)名稱和對(duì)應(yīng)的SELECT結(jié)果集組成。與聲明變量一樣，定義游標(biāo)也放在PL/SQL程序塊的聲明部分。,CURSOR cursor_name(parameter, parameter) RETURN r

4、eturn_type IS select_statement;,PARAMETER參數(shù)的格式： parameter_name in datatype :=|default expression,注意：在指定參數(shù)數(shù)據(jù)類型時(shí)，不能使用長度約束。,（2）打開游標(biāo),OPEN cursor_name(value, value);,打開游標(biāo)的過程包括兩個(gè)步驟： 將符合條件的記錄送入內(nèi)存； 將指針指向第一條記錄。,（3）提取游標(biāo)數(shù)據(jù),提取游標(biāo)中的數(shù)據(jù)就是將檢索到的結(jié)果數(shù)據(jù)保存到變量中，以便在程序中進(jìn)行處理。 可以使用FETCH語句從游標(biāo)中提取數(shù)據(jù)。,FETCH cursor_name INTO (varia

5、ble_list | record_variable);,在游標(biāo)中包含了一個(gè)指針，最初打開游標(biāo)時(shí)，指針指向結(jié)果集中的第一行。當(dāng)使用FETCH語句提取數(shù)據(jù)時(shí)，游標(biāo)中的指針會(huì)自動(dòng)指向下一行。這樣，可以在循環(huán)中使用FETCH語句提取數(shù)據(jù)，使得每一次循環(huán)都會(huì)從結(jié)果集中讀取一行數(shù)據(jù)。直到結(jié)果集中沒有剩余的記錄（%FOUND屬性值為FALSE）。,（4）關(guān)閉游標(biāo),使用完游標(biāo)后，用戶必須顯式關(guān)閉游標(biāo)，以釋放SELECT語句的查詢結(jié)果。,CLOSE cursor_name;,7.1.3 游標(biāo)FOR循環(huán),游標(biāo)通常與循環(huán)聯(lián)合使用。PL/SQL還提供了一種將兩者綜合在一起的語句，即游標(biāo)FOR循環(huán)語句。游標(biāo)FOR循環(huán)

6、是顯式游標(biāo)的一種快捷使用方式，它使用FOR循環(huán)依次讀取結(jié)果集中的數(shù)據(jù)。當(dāng)FOR循環(huán)開始時(shí)，游標(biāo)會(huì)自動(dòng)打開（不需要使用OPEN方法），每循環(huán)一次系統(tǒng)自動(dòng)讀取游標(biāo)當(dāng)前行的數(shù)據(jù)（不需要使用FETCH），當(dāng)退出FOR循環(huán)時(shí)，游標(biāo)被自動(dòng)關(guān)閉（不需要使用CLOSE）。,for cursor_record in cursor_name loop statements; end loop;,7.2 存儲(chǔ)過程和函數(shù),在很多時(shí)候，都需要保存PL/SQL程序塊，以便隨后可以重新使用。命名的PL/SQL程序塊可被獨(dú)立編譯并存儲(chǔ)在數(shù)據(jù)庫中，任何與數(shù)據(jù)庫相連接的應(yīng)用程序都可以訪問這些存儲(chǔ)的PL/SQL程序塊。Oracle

7、提供了四種類型的可存儲(chǔ)的程序：過程、函數(shù)、觸發(fā)器和程序包。,7.2.1 存儲(chǔ)過程,存儲(chǔ)過程是一種命名的PL/SQL程序塊，它可以接受零個(gè)或多個(gè)作為輸入、輸出或者既作輸入又作輸出的參數(shù)。過程被存儲(chǔ)在數(shù)據(jù)庫中，并且存儲(chǔ)過程沒有返回值，存儲(chǔ)過程不能由SQL語句直接使用，只能通過EXECUT命令或PL/SQL程序塊內(nèi)部調(diào)用。由于存儲(chǔ)過程是已經(jīng)編譯好的代碼，所以在調(diào)用的時(shí)候不必再次進(jìn)行編譯，從而提高了程序的運(yùn)行效率。,（1）創(chuàng)建存儲(chǔ)過程,定義存儲(chǔ)過程的語法如下：,create or replace procedure proc_name (param,param,.) is local declarat

8、ions begin execute statements exception exception handlers end procedure _name,注意： （1）存儲(chǔ)過程與匿名程序塊類似，也包括三部分：聲明部分、執(zhí)行部分和異常處理部分。 （2）不能使用DECLARE關(guān)鍵字表示聲明部分，IS關(guān)鍵字后聲明的變量為過程體內(nèi)的局部變量。 （3）不能指定參數(shù)類型的長度和精度。 （4）重新定義存儲(chǔ)過程（同名），必須使用OR REPLACE選項(xiàng)，使新版本覆蓋舊版本。 （5）可以在PL/SQL程序塊中調(diào)用存儲(chǔ)過程，也可以直接在SQL*PLUS中使用EXECUTE語句調(diào)用。,（2）參數(shù),Oracle有

9、三種參數(shù)模式：IN、OUT和IN OUT。,IN參數(shù),該類型的參數(shù)值由調(diào)用者傳入，并且只能被存儲(chǔ)過程讀取。這種參數(shù)模式是最常用的，也是默認(rèn)的參數(shù)模式。,在調(diào)用存儲(chǔ)過程時(shí)，有三種向其傳遞參數(shù)的方法：名稱表示法、位置表示法和混合表示法。,1）名稱表示法,名稱表示法是指為各個(gè)參數(shù)傳遞參數(shù)值時(shí)指定傳入數(shù)值的參數(shù)名。具體格式如下：,procedure_name(param_name=value, param_name=value);,優(yōu)點(diǎn)：明確指定了向各個(gè)參數(shù)傳遞的值，在調(diào)用過程時(shí)就不需要再考慮創(chuàng)建過程時(shí)定義的參數(shù)順序。,2）位置表示法,當(dāng)參數(shù)比較多時(shí)，通過名稱表示法調(diào)用過程會(huì)非常長，此時(shí)可以采用位置表

10、示法。采用位置表示法傳遞參數(shù)時(shí)，用戶提供的參數(shù)值順序必須與過程定義中的參數(shù)順序一致。,3）混合表示法,名稱表示法和位置表示法各有優(yōu)缺點(diǎn)，為了彌補(bǔ)這兩者的不足，還可以采用混合表示法，以發(fā)揮兩者的優(yōu)點(diǎn)。,注意：當(dāng)切換為名稱表示法傳遞參數(shù)后，后續(xù)的參數(shù)也必須使用名稱表示法。,OUT參數(shù),OUT類型的參數(shù)由存儲(chǔ)過程傳入值，然后由調(diào)用者接收參數(shù)值。,由于過程要通過OUT參數(shù)返回值，所以在調(diào)用時(shí)必須提供能夠接收返回值的變量。即使OUT參數(shù)在定義過程時(shí)沒有設(shè)置返回值，調(diào)用時(shí)也必須為其提供接收變量。,在使用SQL*PLUS調(diào)用具有OUT參數(shù)的過程時(shí)，需要使用VARIABLE命令綁定參數(shù)值。,為了查看執(zhí)行結(jié)果，

11、可以使用PRINT命令顯示變量值，也可以通過SELECT語句檢索綁定的變量值。,IN OUT參數(shù),IN OUT類型的參數(shù)同時(shí)具有IN參數(shù)和OUT參數(shù)的特性，在調(diào)用過程時(shí)既可以向該類型的參數(shù)傳入值，也可以從該參數(shù)接收值；而在過程的執(zhí)行過程中，既可以讀取又可以寫入該類型參數(shù)。,（3）默認(rèn)值,存儲(chǔ)過程的參數(shù)也可以有默認(rèn)值，這樣當(dāng)調(diào)用該過程時(shí)，如果未向參數(shù)傳入值，則該參數(shù)將使用定義的默認(rèn)值。 注意：只能給IN參數(shù)設(shè)置默認(rèn)值。,（4）過程中的事務(wù)處理,當(dāng)在SQL*Plus中進(jìn)行操作時(shí)，用戶可以使用COMMIT語句將在事務(wù)中的所有操作“保存”到數(shù)據(jù)庫中。如果用戶需要撤銷所有的操作，則可以使用ROLLBAC

12、K語句回退事務(wù)中未提交的操作，使數(shù)據(jù)庫返回到事務(wù)處理開始前的狀態(tài)。在PL/SQL過程中，不僅可以包括插入和更新這類的DML操作，還可以包括事務(wù)處理語句COMMIT和ROLLBACK。 Oracle支持事務(wù)的嵌套，即在事務(wù)處理中進(jìn)行事務(wù)處理。在嵌套的事務(wù)處理過程中，子事務(wù)可以獨(dú)立于父事務(wù)處理進(jìn)行提交和回滾。,7.2.2 函數(shù),函數(shù)與過程非常類似，它也是一種存儲(chǔ)在數(shù)據(jù)庫中的命名程序塊，并且函數(shù)也可以接受零個(gè)或多個(gè)輸入?yún)?shù)。函數(shù)與過程之間的主要區(qū)別在于，函數(shù)必須有返回值，并且可以作為一個(gè)表達(dá)式的一部分，函數(shù)不能作為一個(gè)完整的語句使用。函數(shù)返回值的數(shù)據(jù)類型在創(chuàng)建函數(shù)時(shí)定義。,create or rep

13、lace function func_name (param,param) return data_type is local declarations begin execute statements exception exception handlers end func_name,7.3 觸發(fā)器,觸發(fā)器是關(guān)系數(shù)據(jù)庫系統(tǒng)提供的一項(xiàng)技術(shù)，觸發(fā)器類似過程和函數(shù)，它們都包括聲明部分，執(zhí)行邏輯處理部分和異常處理部分，并且都被存儲(chǔ)在數(shù)據(jù)庫中。,7.3.1 觸發(fā)器概述,觸發(fā)器是與一個(gè)表或數(shù)據(jù)庫事件聯(lián)系在一起的，當(dāng)特定事件出現(xiàn)時(shí)將自動(dòng)執(zhí)行觸發(fā)器的代碼塊。 觸發(fā)器與過程（或函數(shù)）的區(qū)別在于： （1）過程

14、是由用戶或應(yīng)用程序顯式調(diào)用的，而觸發(fā)器是不能被直接調(diào)用的。 （2）在創(chuàng)建觸發(fā)器時(shí)需要指定觸發(fā)器的執(zhí)行時(shí)間和觸發(fā)事件。,create or replace trigger trigger_name before | after | instead of trigger_event on table_name for each row when trigger_condition begin trigger_body end trigger_name;,注意：各主要參數(shù)的含義。,Oracle對(duì)觸發(fā)器的功能進(jìn)行了擴(kuò)展，不僅對(duì)表和視圖的DML操作會(huì)引起觸發(fā)器的運(yùn)行，而且對(duì)Oracle系統(tǒng)的操作也會(huì)引發(fā)

15、觸發(fā)器的運(yùn)行。 根據(jù)觸發(fā)器的觸發(fā)事件和執(zhí)行情況，可以將Oracle所支持的觸發(fā)器分為如下幾種類型： （1）行級(jí)觸發(fā)器 （2）語句級(jí)觸發(fā)器 （3）INSTEAD OF觸發(fā)器：視圖上 （4）系統(tǒng)事件觸發(fā)器:系統(tǒng)事件觸發(fā)（如啟動(dòng)與關(guān)閉） （5）用戶事件觸發(fā)器：與DDL或用戶的登錄/注銷等事件相關(guān),7.3.2 語句級(jí)觸發(fā)器,如果在創(chuàng)建觸發(fā)器時(shí)未使用FOR EACH ROW子句，則創(chuàng)建的觸發(fā)器為語句級(jí)觸發(fā)器。語句級(jí)觸發(fā)器在被觸發(fā)后只執(zhí)行一次，而不管這一操作會(huì)影響到數(shù)據(jù)庫中多少行記錄。,如何確定是哪個(gè)語句（操作類型）激活了觸發(fā)器？,為了確定觸發(fā)事件的類型，可以使用條件謂詞。條件謂詞是由一個(gè)關(guān)鍵字IF和謂詞

16、INSERTING、UPDATING和DELETING組成。如果值為真，那么就是相應(yīng)類型的語句觸發(fā)了觸發(fā)器。,此外，還可以在UPDATE觸發(fā)器中使用條件謂詞，判斷特定列是否被更新。,begin if inserting then -insert語句觸發(fā) elsif updating then -update語句觸發(fā) elsif deleting then -delete語句觸發(fā) end if; end;,if updating(job) then -do something end if;,7.3.3 行級(jí)觸發(fā)器,如果在創(chuàng)建觸發(fā)器時(shí)使用了FOR EACH ROW選項(xiàng)，則創(chuàng)建的觸發(fā)器為行級(jí)觸發(fā)器

17、。對(duì)于行級(jí)觸發(fā)器而言，當(dāng)一個(gè)DML語句操作影響到數(shù)據(jù)庫中的多行數(shù)據(jù)時(shí)，行級(jí)觸發(fā)器會(huì)針對(duì)每一行執(zhí)行一次。,7.3.4 INSTEAD OF 觸發(fā)器,INSTEAD OF 觸發(fā)器也稱替代觸發(fā)器，定義INSTEAD OF觸發(fā)器后，用戶對(duì)表的DML操作將不再被執(zhí)行，而是執(zhí)行觸發(fā)器主體中的操作。通常情況下，INSTEAD OF觸發(fā)器是定義在視圖上的，而不是在表上定義的觸發(fā)器，它是用來替換所使用實(shí)際語句的觸發(fā)器。,7.3.5 用戶事件觸發(fā)器,用戶事件觸發(fā)器是建立在模式級(jí)的操作上的觸發(fā)器。激活該類型觸發(fā)器的用戶事件包括：CREATE、ALTER、DROP、ANALYZE、ASSOCIATE STATISTI

18、CS、DISASSOCIATE 、STATISTICS、COMMENT、GRANT、REVOKE、RENAME、TRUNCATE、LOGOFF、SUSPEND和LOGON。,7.4 程序包,程序包其實(shí)就是被組合在一起的相關(guān)對(duì)象的集合，當(dāng)程序包中任何函數(shù)或存儲(chǔ)過程被調(diào)用時(shí)，程序包就被加載入到內(nèi)存中，這樣程序包中的任何函數(shù)或存儲(chǔ)過程的子程序訪問速度將大大加快。 例如，在PL/SQL程序中，為了輸出運(yùn)行結(jié)果，在程序的代碼中使用了DBMS_OUTPT.PUT_LINE語句。事實(shí)上，這是調(diào)用程序包DBMS_OUTPUT中的PUT_LINE過程。 程序包由兩部分組成：規(guī)范和包主體。在規(guī)范中描述程序包所使用的變量、常量、游標(biāo)和子程序；程序包主體則完成定義子程序和游標(biāo)。,7.4.1 程序包規(guī)范,對(duì)于