第7章存儲過程、觸發器和程序包

存儲過程(重點)函數(重點)觸發器程序包2在很多時候，都需要保存PL/SQL程序塊，以便隨后可以重新使用。這也意味著，程序塊需要一個名稱，這樣需才可以調用或者引用它。命名的PL/SQL程序塊可被獨立編譯并存儲在數據庫中，任何與數據庫相連接的應用程序都可以訪問這些存儲的PL/SQL程序塊。Oracle提供了四種類型的可存儲的程序：過程、函數、觸發器和程序包。37.1存儲過程存儲過程是一種命名的PL/SQL程序塊，它可以接受零個或多個參數作為輸入、輸出，或者既作輸入又作輸出的參數。過程被存儲在數據庫中，并且存儲過程沒有返回值。存儲過程不能由SQL語句直接使用，只能通過EXECUT命令或PL/SQL程序塊內部調用。由于存儲過程是已經編譯好的代碼，所以在調用的時候不必再次進行編譯，從而提高了程序的運行效率。7.1.1創建存儲過程定義存儲過程的語法：4createprocedureprocedure_name[(parameter[,parameter,...])]is[localdeclarations]begin

executestatements[exceptionexceptionhandlers]end[procedure_name]7.1.1創建存儲過程注意：（1）存儲過程與匿名程序塊類似，也包括三部分：聲明部分、執行部分和異常處理部分。（2）不能使用DECLARE關鍵字表示聲明部分，IS關鍵字后聲明的變量為過程體內的局部變量。（3）不能指定參數類型的長度和精度。（4）重新定義存儲過程（同名），必須使用ORREPLACE選項，使新版本覆蓋舊版本。（5）可以在PL/SQL程序塊中調用存儲過程，也可以直接在SQL*PLUS中使用EXECUTE語句調用。57.1.1創建存儲過程6注意：若在創建存儲過程時出現了錯誤，則使用：SHOWERRORS命令顯示創建時產生的錯誤。7.1.2參數Oracle有三種參數模式：IN、OUT和INOUT。IN參數例：將“向scott.emp表中插入記錄”這一功能用過程完成。7——IN參數7.1.2參數在調用存儲過程時，有三種向其傳遞參數的方法：名稱表示法、位置表示法和混合表示法。1）名稱表示法名稱表示法是指為各個參數傳遞參數值時指定傳入數值的參數名。具體格式如下：8——IN參數procedure_name(param_name=>value[,param_name=>value…]);7.1.2參數2）位置表示法當參數比較多時，通過名稱表示法調用過程會非常長，此時可以采用位置表示法。采用位置表示法傳遞參數時，用戶提供的參數值順序必須與過程定義中的參數順序一致。9——IN參數7.1.2參數3）混合表示法名稱表示法和位置表示法各有優缺點，為了彌補這兩者的不足，還可以采用混合表示法，以發揮兩者的優點。10——IN參數注意：當切換為名稱表示法傳遞參數后，后續的參數也必須使用名稱表示法。7.1.2參數OUT類型的參數由存儲過程傳入值，然后由調用者接收參數值。例：通過empno雇員號檢索emp表，并獲得雇員的ename和sal，將這一功能用過程完成。11——OUT參數7.1.2參數在使用SQL*PLUS調用具有OUT參數的過程時，需要使用VARIABLE命令綁定參數值。為了查看執行結果，可以使用PRINT命令顯示變量值，也可以通過SELECT語句檢索綁定的變量值。12——OUT參數7.1.2參數由于過程要通過OUT參數返回值，所以在調用時必須提供能夠接收返回值的變量。即使OUT參數在定義過程時沒有設置返回值，調用時也必須為其提供接收變量。13——OUT參數7.1.2參數INOUT類型的參數同時具有IN參數和OUT參數的特性，在調用過程時既可以向該類型的參數傳入值，也可以從該參數接收值；而在過程的執行過程中，既可以讀取又可以寫入該類型參數。14——INOUT參數7.1.3默認值存儲過程的參數也可以有默認值，這樣當調用該過程時，如果未向參數傳入值，則該參數將使用定義的默認值。例如，下面修改了ADD_EMPLOYEE存儲過程，為其中的參數提供了默認值：注意：只能給IN參數設置默認值。15167.1.4過程中的事務處理當在SQL*Plus中進行操作時，用戶可以使用COMMIT語句將在事務中的所有操作“保存”到數據庫中。如果用戶需要撤銷所有的操作，則可以使用ROLLBACK語句回退事務中未提交的操作，使數據庫返回到事務處理開始前的狀態。在PL/SQL過程中，不僅可以包括插入和更新這類的DML操作，還可以包括事務處理語句COMMIT和ROLLBACK。Oracle支持事務的嵌套，即在事務處理中進行事務處理。在嵌套的事務處理過程中，子事務可以獨立于父事務處理進行提交和回滾。177.1.4過程中的事務處理187.2函數函數與過程非常類似，它也是一種存儲在數據庫中的命名程序塊，并且函數也可以接受零個或多個輸入參數。函數與過程之間的主要區別在于，函數必須有返回值，并且可以作為一個表達式的一部分，函數不能作為一個完整的語句使用。函數返回值的數據類型在創建函數時定義，定義函數的基本語法如下：create[orreplace]function

function_name(parameter[,parameter])

returnedata_typeis[localdeclarations]beginexecutestatements[exceptionexceptionhandlers]end[function_name]197.2函數7.3觸發器觸發器是關系數據庫系統提供的一項技術，觸發器類似過程和函數，它們都包括聲明部分，執行邏輯處理部分和異常處理部分，并且都被存儲在數據庫中。207.3.1觸發器的概述觸發器是與一個表或數據庫事件聯系在一起的，當特定事件出現時將自動執行觸發器的代碼塊。觸發器與過程的區別在于：過程是由用戶或應用程序顯式調用的，而觸發器是不能被直接調用的。在創建觸發器時需要指定觸發器的執行時間和觸發事件。創建觸發器的語法規則如下（P157）21create[orreplace]triggertrigger_name[before|after|insteadof]trigger_eventontable_name[foreachrow[whentrigger_condition]]begintrigger_bodyend[trigger_name];7.3.1觸發器的概述Oracle對觸發器的功能進行了擴展，不僅對表和視圖的DML操作會引起觸發器的運行，而且對Oracle系統的操作也會引發觸發器的運行。根據觸發器的觸發事件和執行情況，可以將Oracle所支持的觸發器分為如下幾種類型：（1）行級觸發器（2）語句級觸發器（3）INSTEADOF觸發器：視圖上（4）系統事件觸發器:系統事件觸發（如啟動與關閉）（5）用戶事件觸發器：與DDL或用戶的登錄/注銷等事件相關22237.3.2語句級觸發器如果在創建觸發器時未使用FOREACHROW子句，則創建的觸發器為語句級觸發器。語句級觸發器在被觸發后只執行一次，而不管這一操作會影響到數據庫中多少行記錄。如何確定是哪個語句（操作類型）激活了觸發器？247.3.2語句級觸發器為了確定觸發事件的類型，可以使用條件謂詞。條件謂詞是由一個關鍵字IF和謂詞INSERTING、UPDATING和DELETING組成。如果值為真，那么就是相應類型的語句觸發了觸發器。此外，還可以在UPDATE觸發器中使用條件謂詞，判斷特定列是否被更新。beginifinsertingthen--insert語句觸發

elsifupdatingthen--update語句觸發

elsifdeletingthen--delete語句觸發

endif;end;ifupdating(job)then--dosomethingendif;257.3.2語句級觸發器267.3.3行級觸發器在創建觸發器時，如果使用了FOREACHROW選項，則創建的該觸發器為行級觸發器。對于行級觸發器而言，當一個DML語句操作影響到數據庫中的多行數據時，行級觸發器會針對于每一行執行一次。277.3.3行級觸發器287.3.3行級觸發器7.3.4INSTEADOF觸發器INSTEADOF觸發器也稱替代觸發器，定義INSTEADOF觸發器后，用戶對表的DML操作將不再被執行，而是執行觸發器主體中的操作。通常情況下，INSTEADOF觸發器是定義在視圖上的，而不是在表上定義的觸發器，它是用來替換所使用實際語句的觸發器。297.3.4INSTEADOF觸發器307.3.5用戶事件觸發器用戶事件觸發器是建立在模式級的操作上的觸發器。激活該類型觸發器的用戶事件包括：CREATE、ALTER、DROP、ANALYZE、ASSOCIATESTATISTICS、DISASSOCIATE、STATISTICS、COMMENT、GRANT、REVOKE、RENAME、TRUNCATE、LOGOFF、SUSPEND和LOGON。317.4程序包程序包其實就是被組合在一起的相關對象的集合，當程序包中任何函數或存儲過程被調用時，程序包就被加載入到內存中，這樣程序包中的任何函數或存儲過程的子程序訪問速度將大大加快。例如，在PL/SQL程序中，為了輸出運行結果，在程序的代碼中使用了DBMS_OUTPT.PUT_LINE語句。事實上，這是調用程序包DBMS_OUTPUT中的PUT_LINE過程。DBMS_OUTPUT程序包的主要功能就是在PL/SQL程序中的輸入和輸出。327.4.1程序包規范對于程序包，規范就像一個說明書，它說明了在程序包中哪些過程或函數可以使用，如何使用。程序包規范必需的，并且必須在程序包主體之前創建。

創建程序包規范的語法形式如下：33create[orreplace]packagepackage_nameis[public_variable_declarations…][public_type_declarations…][public_exception_declarations…][public_cursor_declarations…][function_declarations…][procedure_specifications…]end[package_name]347.4.2主