Oracle數(shù)據(jù)庫-存儲(chǔ)過程、函數(shù)、觸發(fā)器和包

1、第10章章存存儲(chǔ)過程程、函數(shù)數(shù)、觸發(fā)發(fā)器和包包本章要點(diǎn)點(diǎn)：掌握存儲(chǔ)儲(chǔ)過程的的創(chuàng)建。熟練掌握握帶參數(shù)數(shù)的存儲(chǔ)儲(chǔ)過程的的使用。掌握存儲(chǔ)儲(chǔ)過程的的管理。掌握函數(shù)數(shù)的創(chuàng)建建與使用用。了解觸發(fā)發(fā)器的類類型。理解觸發(fā)發(fā)器的作作用。熟練掌握握各種類類型的觸觸發(fā)器。了解程序序包的創(chuàng)創(chuàng)建與使使用。210.1存存 儲(chǔ)過過程程存儲(chǔ)過程程是一組組為了完完成特定定功能的的SQL語句集集，它大大大提高高了SQL語句句的功能能和靈活活性。存儲(chǔ)過程程經(jīng)編譯譯后存儲(chǔ)儲(chǔ)在數(shù)據(jù)據(jù)庫中，所以執(zhí)執(zhí)行存儲(chǔ)儲(chǔ)過程要要比執(zhí)行行存儲(chǔ)過過程中封封裝的SQL語語句更有有效率。310.1.1創(chuàng)創(chuàng)建建與調(diào)用用存儲(chǔ)過過程創(chuàng)建存儲(chǔ)儲(chǔ)過程需需要使用用CRE

2、ATE PROCEDURE語句句，其語語法如下下：CREATEORREPLACE PROCEDURE procedure_name ( parameter IN |OUT |INOUT data_type ) ,. IS |AS declaration_section;BEGINprocedure_body;ENDprocedure_name ;410.1.1創(chuàng)創(chuàng)建建與調(diào)用用存儲(chǔ)過過程【例10.1】創(chuàng)建一個(gè)個(gè)簡(jiǎn)單的的存儲(chǔ)過過程update_emp，該過程程用于將將emp表中empno為6500的員工工的姓名名修改為為CANDY，如下：SQL CREATEPROCEDUREupdate_empA

3、S2BEGIN3UPDATEempSET ename =CANDYWHEREempno= 6500;4ENDupdate_emp;SQL /過程已創(chuàng)創(chuàng)建?！纠?0.2】使用EXECUTE語語句與CALL語句調(diào)調(diào)用存儲(chǔ)儲(chǔ)過程update_emp，分別別如下：SQL EXECUTEupdate_emp;PL/SQL過過程已已成功完完成。510.1.2帶帶參參數(shù)的存存儲(chǔ)過程程1IN參數(shù)的的使用IN參數(shù)數(shù)是指輸輸入?yún)?shù)數(shù)，由存存儲(chǔ)過程程的調(diào)用用者為其其賦值(也可以以使用默默認(rèn)值)。如果果不為參參數(shù)指定定模式，則其模模式默認(rèn)認(rèn)為IN。在調(diào)用上上述存儲(chǔ)儲(chǔ)過程update_emp2時(shí)，就需要要為該過過程的兩

4、兩個(gè)輸入入?yún)?shù)賦賦值，賦賦值的形形式主要要有如下下兩種。(1)不不指定定參數(shù)名名(2)指指定參參數(shù)名2OUT參數(shù)數(shù)的使用用OUT參參數(shù)是指指輸出參參數(shù)，由由存儲(chǔ)過過程中的的語句為為其賦值值，并返返回給用用戶。使使用這種種模式的的參數(shù)，必須在在參數(shù)后后面添加加OUT關(guān)鍵字字。3IN OUT參數(shù)數(shù)的使用用INOUT參參數(shù)同時(shí)時(shí)擁有IN與OUT參參數(shù)的特特性，它它既接受受用戶的的傳值，又允許許在過程程體中修修改其值值，并可可以將值值返回。使用這這種模式式的參數(shù)數(shù)需要在在參數(shù)后后面添加加INOUT關(guān)鍵字字。不過過，IN OUT參數(shù)數(shù)不接受受常量值值，只能能使用變變量為其其傳值。610.1.2帶帶參參數(shù)

5、的存存儲(chǔ)過程程【例10.3】創(chuàng)建帶IN參數(shù)數(shù)的存儲(chǔ)儲(chǔ)過程update_emp2，為為該過程程設(shè)置兩兩個(gè)IN參數(shù)，分別用用于接受受用戶提提供的empno與ename值，如下：SQL CREATEPROCEDUREupdate_emp22( emp_numINNUMBER ,emp_nameINVARCHAR2)AS3BEGIN4UPDATEempSET ename =emp_name5WHERE empno =emp_num;6ENDupdate_emp2;7/過程已創(chuàng)創(chuàng)建。710.1.2帶帶參參數(shù)的存存儲(chǔ)過程程【例10.4】調(diào)用update_emp2過程，通過該該過程將將empno為為6500

6、的員員工的ename修改改為XIAOQI，如如下：SQL EXECupdate_emp2(6500,XIAOQI);PL/SQL過過程已已成功完完成?！纠?0.5】使用指定定參數(shù)名名的形式式調(diào)用update_emp2過程程，如下下：SQL EXECupdate_emp2(emp_name = XIAOQI ,emp_num=6500);PL/SQL過過程已已成功完完成。810.1.2帶帶參參數(shù)的存存儲(chǔ)過程程【例10.6】創(chuàng)建存儲(chǔ)儲(chǔ)過程select_emp，為該該過程設(shè)設(shè)置一個(gè)個(gè)IN參參數(shù)和一一個(gè)OUT參數(shù)數(shù)，其中中IN參參數(shù)接受受用戶提提供的empno值，然后在在過程體體中將該該empno對(duì)對(duì)

7、應(yīng)的ename值傳傳遞給OUT參參數(shù)，如如下：SQL CREATEPROCEDUREselect_emp2( emp_numINNUMBER ,emp_nameOUT VARCHAR2 )AS3BEGIN4SELECTenameINTO emp_name5FROMemp WHERE empno =emp_num;6ENDselect_emp;7/過程已創(chuàng)創(chuàng)建。910.1.2帶帶參參數(shù)的存存儲(chǔ)過程程【例10.7】調(diào)用存儲(chǔ)儲(chǔ)過程select_emp，為其IN參數(shù)數(shù)賦值為為6500，并并聲明變變量employee_name接受與與輸出其其OUT參數(shù)的的返回值值，如下下：SQL VARIABLE em

8、ployee_nameVARCHAR2(10) ;SQL EXECselect_emp(6500, :employee_name);PL/SQL過過程已已成功完完成。然后，需需要使用用PRINT命命令查看看變量employee_name中的值值，如下下：SQL PRINT employee_name;EMPLOYEE_NAME-XIAOQI也可以使使用SELECT語句句查看變變量employee_name中中的值，語句如如下：SQL SELECT:employee_nameFROM dual;1010.1.2帶帶參參數(shù)的存存儲(chǔ)過程程【例10.8】創(chuàng)建存儲(chǔ)儲(chǔ)過程exchange_value，通通

9、過該過過程交換換兩個(gè)變變量中的的值，過過程創(chuàng)建建如下：參見教材材P227調(diào)用exchange_value過程，調(diào)用前前聲明為為INOUT參數(shù)賦賦值的變變量，調(diào)調(diào)用后使使用SELECT語句句輸出交交換值后后的結(jié)果果，如下下：參見教材材P2271110.1.3修修改改與刪除除存儲(chǔ)過過程修改存儲(chǔ)儲(chǔ)過程是是在CREATEPROCEDURE語語句中添添加OR REPLACE關(guān)關(guān)鍵字，其他內(nèi)內(nèi)容與創(chuàng)創(chuàng)建存儲(chǔ)儲(chǔ)過程一一樣，其其實(shí)質(zhì)是是刪除原原有過程程，然后后創(chuàng)建一一個(gè)全新新的過程程，只不不過前后后兩個(gè)過過程的名名稱相同同而已。刪除存儲(chǔ)儲(chǔ)過程需需要使用用DROPPROCEDURE語語句，其其語法形形式如下下：

10、DROP PROCEDUREprocedure_name;1210.1.4查查詢?cè)兇鎯?chǔ)過過程的定定義信息息對(duì)于創(chuàng)建建好的存存儲(chǔ)過程程，如果果想要了了解其定定義信息息，可以以查詢數(shù)數(shù)據(jù)字典典user_source?！纠?0.9】通過數(shù)據(jù)據(jù)字典user_source查詢?cè)兇鎯?chǔ)過過程select_emp的的定義信信息，如如下：參見教材材P228其中，name表示對(duì)對(duì)象名稱稱；type表表示對(duì)象象類型；line表示示定義信信息中文文本所在在的行數(shù)數(shù)；text表表示對(duì)應(yīng)應(yīng)行的文文本信息息。1310.2函函數(shù)數(shù)創(chuàng)建函數(shù)數(shù)需要使使用CREATEFUNCTION語句句，其語語法如下下：CREATEORREPL

11、ACE FUNCTIONfunction_name ( parameter IN |OUT |INOUT data_type ) ,. RETURNdata_type IS |AS declaration_section;BEGINfunction_body ;ENDfunction_name ;1410.3實(shí)驗(yàn)指導(dǎo)導(dǎo)使使用存儲(chǔ)儲(chǔ)過程與與函數(shù)查查詢圖書書信息實(shí)驗(yàn)指導(dǎo)導(dǎo)10-1：使使用存儲(chǔ)儲(chǔ)過程和和函數(shù)查查詢圖書書信息1創(chuàng)建建函數(shù)get_prompt首先創(chuàng)建建函數(shù)get_prompt，如下下：參見教材材P2302創(chuàng)建建存儲(chǔ)過過程get_book_information3調(diào)用用過程存儲(chǔ)過程程和函數(shù)

12、數(shù)都已經(jīng)經(jīng)創(chuàng)建好好了，需需要查詢?cè)兡硤D書書的信息息時(shí)就可可以直接接調(diào)用get_book_information存儲(chǔ)儲(chǔ)過程。例如獲獲取bookid為2的圖書書的信息息，如下下：參見教材材P2311510.4觸觸發(fā)發(fā)器器觸發(fā)器是是一種特特殊的存存儲(chǔ)過程程，它在在發(fā)生某某種數(shù)據(jù)據(jù)庫事件件時(shí)由Oracle系系統(tǒng)自動(dòng)動(dòng)觸發(fā)。觸發(fā)器通通常用于于加強(qiáng)數(shù)數(shù)據(jù)的完完整性約約束和業(yè)業(yè)務(wù)規(guī)則則等，對(duì)對(duì)于表來來說，觸觸發(fā)器可可以實(shí)現(xiàn)現(xiàn)比CHECK約束更更為復(fù)雜雜的約束束。1610.4.1觸觸發(fā)發(fā)器的類類型DML觸觸發(fā)器：DML觸發(fā)器器由DML語句句觸發(fā)，例如INSERT、UPDATE和DELETE語句句。INSTEA

13、D OF觸發(fā)器器：INSTEADOF觸觸發(fā)器又又稱替代代觸發(fā)器器，用于于執(zhí)行一一個(gè)替代代操作來來代替觸觸發(fā)事件件的操作作。系統(tǒng)事件件觸發(fā)器器：系統(tǒng)統(tǒng)事件觸觸發(fā)器在在發(fā)生如如數(shù)據(jù)庫庫啟動(dòng)或或關(guān)閉等等系統(tǒng)事事件時(shí)觸觸發(fā)。DDL觸觸發(fā)器：DDL觸發(fā)器器由DDL語句句觸發(fā)，例如CREATE、ALTER和和DROP語句句。DDL觸發(fā)發(fā)器同樣樣可以分分為BEFORE觸發(fā)發(fā)器與AFTER觸發(fā)發(fā)器。1710.4.2創(chuàng)創(chuàng)建建觸發(fā)器器創(chuàng)建觸發(fā)發(fā)器需要要使用CREATETRIGGER語句句，其語語法如下下：CREATEORREPLACE TRIGGERtrigger_name BEFORE| AFTER |INST

14、EADOF trigger_event ON table_name|view_name|DATABASE FOREACH ROW ENABLE| DISABLE WHENtrigger_condition DECLAREdeclaration_statements; BEGINtrigger_body;ENDtrigger_name;1810.4.3DML觸發(fā)發(fā)器DML觸觸發(fā)器由由DML語句觸觸發(fā)，其其對(duì)應(yīng)的的trigger_event具體體內(nèi)容如如下： INSERT| DELETE| UPDATE OF column ,. 關(guān)于DML觸發(fā)發(fā)器的說說明如下下：DML操操作主要要包括INSERT

15、、DELETE和UPDATE操作作，通常常根據(jù)觸觸發(fā)器所所針對(duì)的的具體事事件將DML觸觸發(fā)器分分為INSERT觸發(fā)發(fā)器、UPDATE觸觸發(fā)器和和DELETE觸發(fā)器器?？梢詫ML操操作細(xì)化化到列，即針對(duì)對(duì)某列進(jìn)進(jìn)行DML操作作時(shí)激活活觸發(fā)器器。任何DML觸發(fā)發(fā)器都可可以按觸觸發(fā)時(shí)間間分為BEFORE觸觸發(fā)器與與AFTER觸觸發(fā)器。在行級(jí)觸觸發(fā)器中中，為了了獲取某某列在DML操操作前后后的數(shù)據(jù)據(jù)，Oracle提供供了兩種種特殊的的標(biāo)識(shí)符符:OLD和:NEW，通過:OLD.column_name的形式式可以獲獲取該列列的舊數(shù)數(shù)據(jù)，而而通過:NEW.column_name則可以以獲取該該列的新新數(shù)據(jù)

16、。1910.4.3DML觸發(fā)發(fā)器【例10.11】為了演示示觸發(fā)器器的效果果，下面面首先創(chuàng)創(chuàng)建兩個(gè)個(gè)簡(jiǎn)單的的示例表表：student(學(xué)生表表)和record(記錄表表)，并并向student表表中添加加幾條記記錄，如如下：參見教材材P233創(chuàng)建AFTER UPDATE觸發(fā)發(fā)器，要要求在修修改student表表中的某某行數(shù)據(jù)據(jù)后，在在record表中記記錄修改改操作，并保存存修改前前的行數(shù)數(shù)據(jù)。創(chuàng)創(chuàng)建觸發(fā)發(fā)器的語語句如下下：參見教材材P2342010.4.4INSTEADOF觸觸發(fā)器INSTEAD OF觸發(fā)器器用于執(zhí)執(zhí)行一個(gè)個(gè)替代操操作來代代替觸發(fā)發(fā)事件的的操作，而觸發(fā)發(fā)事件本本身最終終不會(huì)被被

17、執(zhí)行。如果是DML觸觸發(fā)器，則無論論是BEFORE觸發(fā)發(fā)器還是是AFTER觸觸發(fā)器，觸發(fā)事事件最終終都會(huì)被被執(zhí)行。不過，Oracle中中的INSTEADOF觸觸發(fā)器不不能針對(duì)對(duì)表，而而只能針針對(duì)視圖圖。2110.4.4INSTEADOF觸觸發(fā)器【例10.12】首先基于于student表創(chuàng)創(chuàng)建視圖圖student_view，該視視圖檢索索student表中中的所有有數(shù)據(jù)，但將student表中的的sage列加加1。視視圖創(chuàng)建建如下：SQL CREATEVIEW student_view2AS3SELECTsid ,sname,sage +1new_age4FROM student5WITH CH

18、ECK OPTION;視圖已創(chuàng)創(chuàng)建。2210.4.5系系統(tǒng)統(tǒng)事件觸觸發(fā)器系統(tǒng)事件件觸發(fā)器器是指由由數(shù)據(jù)庫庫系統(tǒng)事事件觸發(fā)發(fā)的觸發(fā)發(fā)器，其其所支持持的系統(tǒng)統(tǒng)事件如如表10-1所所示。系統(tǒng)事件說 明LOGOFF用戶從數(shù)據(jù)庫注銷LOGON用戶登錄數(shù)據(jù)庫SERVERERROR服務(wù)器發(fā)生錯(cuò)誤SHUTDOWN關(guān)閉數(shù)據(jù)庫實(shí)例STARTUP打開數(shù)據(jù)庫實(shí)例2310.4.5系系統(tǒng)統(tǒng)事件觸觸發(fā)器【例10.13】在system用戶下下創(chuàng)建一一個(gè)系統(tǒng)統(tǒng)事件觸觸發(fā)器，該觸發(fā)發(fā)器由LOGON事件件觸發(fā)，記錄登登錄用戶戶的用戶戶名(USER)與登登錄時(shí)間間，如下下：SQL CONNECTsystem/admin已連接。SQL

19、 CREATETRIGGER logon_trigger2AFTERLOGON3ONDATABASE4BEGIN5INSERTINTO logon_logVALUES(USER ,SYSDATE) ;6ENDlogon_trigger;7/觸發(fā)器已已創(chuàng)建2410.4.6DDL觸發(fā)發(fā)器DDL觸觸發(fā)器由由DDL語句觸觸發(fā)，按按觸發(fā)時(shí)時(shí)間可以以分為BEFORE觸觸發(fā)器與與AFTER觸觸發(fā)器，其所針針對(duì)的事事件包括括CREATE、ALTER、DROP、ANALYZE、GRANT、COMMENT、REVOKE、RENAME、TRUNCATE、AUDIT、NOTAUDIT、ASSOCIATE STATIS

20、TICS和和DISASSOCIATE STATISTICS。創(chuàng)建DDL觸發(fā)發(fā)器需要要用戶具具有DBA權(quán)限限。2510.4.7禁禁用用與啟用用觸發(fā)器器在創(chuàng)建觸觸發(fā)器時(shí)時(shí)，可以以使用ENABLE與與DISABLE關(guān)鍵鍵字指定定觸發(fā)器器的初始始狀態(tài)為為啟用或或禁用，默認(rèn)情情況下為為ENABLE。在需要的的時(shí)候，也可以以使用ALTERTRIGGER語句修修改觸發(fā)發(fā)器的狀狀態(tài)，其其語法如如下：ALTERTRIGGER trigger_nameENABLE|DISABLE ;如果需要要修改某某個(gè)表上上的所有有觸發(fā)器器的狀態(tài)態(tài)，還可可以使用用如下形形式：ALTERTABLEtable_name ENABLE

21、| DISABLEALL TRIGGERS ;2610.4.8修修改改與刪除除觸發(fā)器器修改觸發(fā)發(fā)器只需需要在CREATETRIGGER語句句中添加加ORREPLACE關(guān)鍵鍵字。刪除觸發(fā)發(fā)器需要要使用DROP TRIGGER語語句，其其語法如如下：DROP TRIGGERtrigger_name ;2710.5程程序序包包使用程序序包主要要是為了了實(shí)現(xiàn)程程序模塊塊化，程程序包可可以將相相關(guān)的存存儲(chǔ)過程程、函數(shù)數(shù)、變量量、常量量和游標(biāo)標(biāo)等PL/SQL程序序組合在在一起，通過這這種方式式可以構(gòu)構(gòu)建供程程序人員員重用的的代碼庫庫。另外，當(dāng)當(dāng)首次調(diào)調(diào)用程序序包中的的存儲(chǔ)過過程或函函數(shù)等元元素時(shí)，Orac

22、le會(huì)將整整個(gè)程序序包調(diào)入入內(nèi)存，在下次次調(diào)用包包中的元元素時(shí)，Oracle就可以以直接從從內(nèi)存中中讀取，從而提提高程序序的運(yùn)行行效率。2810.5.1創(chuàng)創(chuàng)建建程序包包1創(chuàng)建建包規(guī)范范創(chuàng)建包規(guī)規(guī)范需要要使用CREATEPACKAGE語句句，其簡(jiǎn)簡(jiǎn)要語法法如下：CREATEORREPLACE PACKAGEpackage_name IS |ASpackage_specification;ENDpackage_name ;語法說明明如下。package_name：創(chuàng)建的的包名。package_specification：用于列列出用戶戶可以使使用的公公共存儲(chǔ)儲(chǔ)過程、函數(shù)、類型和和對(duì)象。2910.5

23、.1創(chuàng)創(chuàng)建建程序包包2創(chuàng)建建包體創(chuàng)建包體體需要使使用CREATEPACKAGE BODY語語句，并并且在創(chuàng)創(chuàng)建時(shí)需需要指定定已創(chuàng)建建的包，其簡(jiǎn)要要語法如如下：CREATEORREPLACE PACKAGEBODY package_name IS |ASpackage_body;ENDpackage_name ;3010.5.2調(diào)調(diào)用用程序包包中的元元素DBMS_OUTPUT是系系統(tǒng)定義義的包，而PUT_LINE是該包包中的存存儲(chǔ)過程程?？梢娨娬{(diào)用程程序包中中的元素素時(shí)，是是使用如如下形式式：package_name.element_name;其中，element_name表表示元素素名稱，可以是是