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

第10章存儲(chǔ)過程、函數(shù)、觸發(fā)器和包本章要點(diǎn)：掌握存儲(chǔ)過程的創(chuàng)建。熟練掌握帶參數(shù)的存儲(chǔ)過程的使用。掌握存儲(chǔ)過程的管理。掌握函數(shù)的創(chuàng)建與使用。了解觸發(fā)器的類型。理解觸發(fā)器的作用。熟練掌握各種類型的觸發(fā)器。了解程序包的創(chuàng)建與使用。210.1存儲(chǔ)過程存儲(chǔ)過程是一組為了完成特定功能的SQL語(yǔ)句集，它大大提高了SQL語(yǔ)句的功能和靈活性。存儲(chǔ)過程經(jīng)編譯后存儲(chǔ)在數(shù)據(jù)庫(kù)中，所以執(zhí)行存儲(chǔ)過程要比執(zhí)行存儲(chǔ)過程中封裝的SQL語(yǔ)句更有效率。310.1.1創(chuàng)建與調(diào)用存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程需要使用CREATEPROCEDURE語(yǔ)句，其語(yǔ)法如下：CREATE[ORREPLACE]PROCEDUREprocedure_name[ (parameter[IN|OUT|INOUT]data_type) [,...]]{IS|AS} [declaration_section;]BEGIN procedure_body;END[procedure_name];410.1.1創(chuàng)建與調(diào)用存儲(chǔ)過程【例10.1】創(chuàng)建一個(gè)簡(jiǎn)單的存儲(chǔ)過程update_emp，該過程用于將emp表中empno為6500的員工的姓名修改為CANDY，如下：SQL>CREATEPROCEDUREupdate_empAS2BEGIN3UPDATEempSETename='CANDY'WHEREempno=6500;4ENDupdate_emp;SQL>/過程已創(chuàng)建。【例10.2】使用EXECUTE語(yǔ)句與CALL語(yǔ)句調(diào)用存儲(chǔ)過程update_emp，分別如下：SQL>EXECUTEupdate_emp;PL/SQL過程已成功完成。510.1.2帶參數(shù)的存儲(chǔ)過程1．IN參數(shù)的使用IN參數(shù)是指輸入?yún)?shù)，由存儲(chǔ)過程的調(diào)用者為其賦值(也可以使用默認(rèn)值)。如果不為參數(shù)指定模式，則其模式默認(rèn)為IN。在調(diào)用上述存儲(chǔ)過程update_emp2時(shí)，就需要為該過程的兩個(gè)輸入?yún)?shù)賦值，賦值的形式主要有如下兩種。(1) 不指定參數(shù)名(2) 指定參數(shù)名2．OUT參數(shù)的使用OUT參數(shù)是指輸出參數(shù)，由存儲(chǔ)過程中的語(yǔ)句為其賦值，并返回給用戶。使用這種模式的參數(shù)，必須在參數(shù)后面添加OUT關(guān)鍵字。3．INOUT參數(shù)的使用INOUT參數(shù)同時(shí)擁有IN與OUT參數(shù)的特性，它既接受用戶的傳值，又允許在過程體中修改其值，并可以將值返回。使用這種模式的參數(shù)需要在參數(shù)后面添加INOUT關(guān)鍵字。不過，INOUT參數(shù)不接受常量值，只能使用變量為其傳值。610.1.2帶參數(shù)的存儲(chǔ)過程【例10.3】創(chuàng)建帶IN參數(shù)的存儲(chǔ)過程update_emp2，為該過程設(shè)置兩個(gè)IN參數(shù)，分別用于接受用戶提供的empno與ename值，如下：SQL>CREATEPROCEDUREupdate_emp22(emp_numINNUMBER,emp_nameINVARCHAR2)AS3BEGIN4UPDATEempSETename=emp_name5WHEREempno=emp_num;6ENDupdate_emp2;7/過程已創(chuàng)建。710.1.2帶參數(shù)的存儲(chǔ)過程【例10.4】調(diào)用update_emp2過程，通過該過程將empno為6500的員工的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ǔ)過程select_emp，為該過程設(shè)置一個(gè)IN參數(shù)和一個(gè)OUT參數(shù)，其中IN參數(shù)接受用戶提供的empno值，然后在過程體中將該empno對(duì)應(yīng)的ename值傳遞給OUT參數(shù)，如下：SQL>CREATEPROCEDUREselect_emp2(emp_numINNUMBER,emp_nameOUTVARCHAR2)AS3BEGIN4SELECTenameINTOemp_name5FROMempWHEREempno=emp_num;6ENDselect_emp;7/過程已創(chuàng)建。910.1.2帶參數(shù)的存儲(chǔ)過程【例10.7】調(diào)用存儲(chǔ)過程select_emp，為其IN參數(shù)賦值為6500，并聲明變量employee_name接受與輸出其OUT參數(shù)的返回值，如下：SQL>VARIABLEemployee_nameVARCHAR2(10);SQL>EXECselect_emp(6500,:employee_name);PL/SQL過程已成功完成。然后，需要使用PRINT命令查看變量employee_name中的值，如下：SQL>PRINTemployee_name;EMPLOYEE_NAME--------------------------------XIAOQI也可以使用SELECT語(yǔ)句查看變量employee_name中的值，語(yǔ)句如下：SQL>SELECT:employee_nameFROMdual;1010.1.2帶帶參數(shù)數(shù)的存存儲(chǔ)過過程【例10.8】】創(chuàng)建存存儲(chǔ)過過程exchange_value，，通過過該過過程交交換兩兩個(gè)變變量中中的值值，過過程創(chuàng)創(chuàng)建如如下：：參見教教材P227調(diào)用exchange_value過過程，，調(diào)用用前聲聲明為為INOUT參數(shù)數(shù)賦值值的變變量，，調(diào)用用后使使用SELECT語(yǔ)語(yǔ)句輸輸出交交換值值后的的結(jié)果果，如如下：：參見教教材P2271110.1.3修修改與與刪除除存儲(chǔ)儲(chǔ)過程程修改存存儲(chǔ)過過程是是在CREATEPROCEDURE語(yǔ)語(yǔ)句中中添加加ORREPLACE關(guān)鍵鍵字，，其他他內(nèi)容容與創(chuàng)創(chuàng)建存存儲(chǔ)過過程一一樣，，其實(shí)實(shí)質(zhì)是是刪除除原有有過程程，然然后創(chuàng)創(chuàng)建一一個(gè)全全新的的過程程，只只不過過前后后兩個(gè)個(gè)過程程的名名稱相相同而而已。。刪除存存儲(chǔ)過過程需需要使使用DROPPROCEDURE語(yǔ)語(yǔ)句，，其語(yǔ)語(yǔ)法形形式如如下：：DROPPROCEDUREprocedure_name;1210.1.4查查詢存存儲(chǔ)過過程的的定義義信息息對(duì)于創(chuàng)創(chuàng)建好好的存存儲(chǔ)過過程，，如果果想要要了解解其定定義信信息，，可以以查詢?cè)償?shù)據(jù)據(jù)字典典user_source。?！纠?0.9】】通過數(shù)數(shù)據(jù)字字典user_source查詢?cè)兇鎯?chǔ)儲(chǔ)過程程select_emp的定定義信信息，，如下下：參見教教材P228其中，，name表示示對(duì)象象名稱稱；type表表示對(duì)對(duì)象類類型；；line表示示定義義信息息中文文本所所在的的行數(shù)數(shù)；text表表示對(duì)對(duì)應(yīng)行行的文文本信信息。。1310.2函函數(shù)數(shù)創(chuàng)建函函數(shù)需需要使使用CREATEFUNCTION語(yǔ)句句，其其語(yǔ)法法如下下：CREATE[ORREPLACE]FUNCTIONfunction_name[(parameter[IN|OUT|INOUT]data_type)[,...]]RETURNdata_type{IS|AS}[declaration_section;]BEGINfunction_body;END[function_name];1410.3實(shí)驗(yàn)指指導(dǎo)———使使用存存儲(chǔ)過過程與與函數(shù)數(shù)查詢?cè)儓D書書信息息實(shí)驗(yàn)指指導(dǎo)10-1：：使用用存儲(chǔ)儲(chǔ)過程程和函函數(shù)查查詢圖圖書信信息1．創(chuàng)創(chuàng)建函函數(shù)get_prompt首先創(chuàng)創(chuàng)建函函數(shù)get_prompt，，如下下：參見教教材P2302．創(chuàng)創(chuàng)建存存儲(chǔ)過過程get_book_information3．調(diào)調(diào)用過過程存儲(chǔ)過過程和和函數(shù)數(shù)都已已經(jīng)創(chuàng)創(chuàng)建好好了，，需要要查詢?cè)兡硤D圖書的的信息息時(shí)就就可以以直接接調(diào)用用get_book_information存儲(chǔ)儲(chǔ)過程程。例例如獲獲取bookid為為2的的圖書書的信信息，，如下下：參見教教材P2311510.4觸觸發(fā)發(fā)器器觸發(fā)器器是一一種特特殊的的存儲(chǔ)儲(chǔ)過程程，它它在發(fā)發(fā)生某某種數(shù)數(shù)據(jù)庫(kù)庫(kù)事件件時(shí)由由Oracle系統(tǒng)統(tǒng)自動(dòng)動(dòng)觸發(fā)發(fā)。觸發(fā)器器通常常用于于加強(qiáng)強(qiáng)數(shù)據(jù)據(jù)的完完整性性約束束和業(yè)業(yè)務(wù)規(guī)規(guī)則等等，對(duì)對(duì)于表表來說說，觸觸發(fā)器器可以以實(shí)現(xiàn)現(xiàn)比CHECK約束束更為為復(fù)雜雜的約約束。。1610.4.1觸觸發(fā)器器的類類型DML觸發(fā)發(fā)器：：DML觸觸發(fā)器器由DML語(yǔ)句句觸發(fā)發(fā)，例例如INSERT、、UPDATE和DELETE語(yǔ)語(yǔ)句。。INSTEADOF觸觸發(fā)器器：INSTEADOF觸觸發(fā)器器又稱稱替代代觸發(fā)發(fā)器，，用于于執(zhí)行行一個(gè)個(gè)替代代操作作來代代替觸觸發(fā)事事件的的操作作。系統(tǒng)事事件觸觸發(fā)器器：系系統(tǒng)事事件觸觸發(fā)器器在發(fā)發(fā)生如如數(shù)據(jù)據(jù)庫(kù)啟啟動(dòng)或或關(guān)閉閉等系系統(tǒng)事事件時(shí)時(shí)觸發(fā)發(fā)。DDL觸發(fā)發(fā)器：：DDL觸觸發(fā)器器由DDL語(yǔ)句句觸發(fā)發(fā)，例例如CREATE、、ALTER和和DROP語(yǔ)句句。DDL觸發(fā)發(fā)器同同樣可可以分分為BEFORE觸觸發(fā)器器與AFTER觸發(fā)發(fā)器。。1710.4.2創(chuàng)建觸觸發(fā)器創(chuàng)建觸發(fā)器需需要使用CREATETRIGGER語(yǔ)句，，其語(yǔ)法如下下：CREATE[ORREPLACE]TRIGGERtrigger_name[BEFORE|AFTER|INSTEADOF]trigger_event{ONtable_name|view_name|DATABASE}[FOREACHROW][ENABLE|DISABLE][WHENtrigger_condition][DECLAREdeclaration_statements;]BEGINtrigger_body;END[trigger_name];1810.4.3DML觸發(fā)器DML觸發(fā)器器由DML語(yǔ)語(yǔ)句觸發(fā)，其其對(duì)應(yīng)的trigger_event具體內(nèi)容容如下：{INSERT|DELETE|UPDATE[OFcolumn[,...]]}關(guān)于DML觸觸發(fā)器的說明明如下：DML操作主主要包括INSERT、、DELETE和UPDATE操作作，通常根據(jù)據(jù)觸發(fā)器所針針對(duì)的具體事事件將DML觸發(fā)器分為為INSERT觸發(fā)器、、UPDATE觸發(fā)器和和DELETE觸發(fā)器。?？梢詫ML操作細(xì)化到到列，即針對(duì)對(duì)某列進(jìn)行DML操作時(shí)時(shí)激活觸發(fā)器器。任何DML觸觸發(fā)器都可以以按觸發(fā)時(shí)間間分為BEFORE觸發(fā)發(fā)器與AFTER觸發(fā)器器。在行級(jí)觸發(fā)器器中，為了獲獲取某列在DML操作前前后的數(shù)據(jù)，，Oracle提供了兩兩種特殊的標(biāo)標(biāo)識(shí)符——:OLD和:NEW，通通過:OLD.column_name的形式式可以獲取該該列的舊數(shù)據(jù)據(jù)，而通過:NEW.column_name則可以獲取取該列的新數(shù)數(shù)據(jù)。1910.4.3DML觸發(fā)器【例10.11】為了演示觸發(fā)發(fā)器的效果，，下面首先創(chuàng)創(chuàng)建兩個(gè)簡(jiǎn)單單的示例表：：student(學(xué)生生表)和record(記錄表)，，并向student表表中添加幾條條記錄，如下下：參見教材P233創(chuàng)建AFTERUPDATE觸發(fā)發(fā)器，要求在在修改student表表中的某行數(shù)數(shù)據(jù)后，在record表中記錄修修改操作，并并保存修改前前的行數(shù)據(jù)。。創(chuàng)建觸發(fā)器器的語(yǔ)句如下下：參見教材P2342010.4.4INSTEADOF觸發(fā)器器INSTEADOF觸觸發(fā)器用于執(zhí)執(zhí)行一個(gè)替代代操作來代替替觸發(fā)事件的的操作，而觸觸發(fā)事件本身身最終不會(huì)被被執(zhí)行。如果是DML觸發(fā)器，則則無(wú)論是BEFORE觸觸發(fā)器還是AFTER觸觸發(fā)器，觸發(fā)發(fā)事件最終都都會(huì)被執(zhí)行。。不過，Oracle中的的INSTEADOF觸發(fā)器不能能針對(duì)表，而而只能針對(duì)視視圖。2110.4.4INSTEADOF觸發(fā)器器【例10.12】首先基于student表創(chuàng)建視圖圖student_view，該視視圖檢索student表中的所有有數(shù)據(jù)，但將將student表中的的sage列列加1。視圖圖創(chuàng)建如下：：SQL>CREATEVIEWstudent_view2AS3SELECTsid,sname,sage+1new_age4FROMstudent5WITHCHECKOPTION;視圖已創(chuàng)建。。2210.4.5系統(tǒng)事事件觸發(fā)器系統(tǒng)事件觸發(fā)發(fā)器是指由數(shù)數(shù)據(jù)庫(kù)系統(tǒng)事事件觸發(fā)的觸觸發(fā)器，其所所支持的系統(tǒng)統(tǒng)事件如表10-1所示示。系統(tǒng)事件說

明LOGOFF用戶從數(shù)據(jù)庫(kù)注銷LOGON用戶登錄數(shù)據(jù)庫(kù)SERVERERROR服務(wù)器發(fā)生錯(cuò)誤SHUTDOWN關(guān)閉數(shù)據(jù)庫(kù)實(shí)例STARTUP打開數(shù)據(jù)庫(kù)實(shí)例2310.4.5系統(tǒng)事事件觸發(fā)器【例10.13】在system用戶下創(chuàng)創(chuàng)建一個(gè)系統(tǒng)統(tǒng)事件觸發(fā)器器，該觸發(fā)器器由LOGON事件觸發(fā)發(fā)，記錄登錄錄用戶的用戶戶名(USER)與登錄錄時(shí)間，如下下：SQL>CONNECTsystem/admin已連接。SQL>CREATETRIGGERlogon_trigger2AFTERLOGON3ONDATABASE4BEGIN5INSERTINTOlogon_logVALUES(USER,SYSDATE);6ENDlogon_trigger;7/觸發(fā)器已創(chuàng)建建2410.4.6DDL觸發(fā)器DDL觸發(fā)器器由DDL語(yǔ)語(yǔ)句觸發(fā)，按按觸發(fā)時(shí)間可可以分為BEFORE觸觸發(fā)器與AFTER觸發(fā)發(fā)器，其所針針對(duì)的事件包包括CREATE、ALTER、DROP、ANALYZE、GRANT、COMMENT、REVOKE、RENAME、、TRUNCATE、AUDIT、、NOTAUDIT、ASSOCIATESTATISTICS和和DISASSOCIATESTATISTICS。創(chuàng)建DDL觸觸發(fā)器需要用用戶具有DBA權(quán)限。2510.4.7禁用與與啟用觸發(fā)器器在創(chuàng)建觸發(fā)器器時(shí)，可以使使用ENABLE與DISABLE關(guān)鍵字指定定觸發(fā)器的初初始狀態(tài)為啟啟用或禁用，，默認(rèn)情況下下為ENABLE。在需要的時(shí)候候，也可以使使用ALTERTRIGGER語(yǔ)語(yǔ)句修改觸發(fā)發(fā)器的狀態(tài)，，其語(yǔ)法如下下：ALTERTRIGGERtrigger_nameENABLE|DISABLE;如果果需需要要修修改改某某個(gè)個(gè)表表上上的的所所有有觸觸發(fā)發(fā)器器的的狀狀態(tài)態(tài)，，還還可可以以使使用用如如下下形形式式：：ALTERTABLEtable_nameENABLE|DISABLEALLTRIGGERS;2610.4.8修修改改與與刪刪除除觸觸發(fā)發(fā)器器修改改觸觸發(fā)發(fā)器器只只需需要要在在CREATETRIGGER語(yǔ)語(yǔ)句句中中添添加加ORREPLACE關(guān)關(guān)鍵鍵字字。。刪除除觸觸發(fā)發(fā)器器需需要要使使用用DROPTRIGGER語(yǔ)語(yǔ)句句，，其其語(yǔ)語(yǔ)法法如如下下：：DROPTRIGGERtrigger_name;2710.5程程序序包包使用用程程序序包包主主要要是是為為了了實(shí)實(shí)現(xiàn)現(xiàn)程程序序模模塊塊化化，，程程序序包包可可以以將將相相關(guān)關(guān)的的存存儲(chǔ)儲(chǔ)過過程程、、函函數(shù)數(shù)、、變變量量、、常常量量和和游游標(biāo)標(biāo)等等PL/SQL程程序序組組合合在在一一起起，，通通過過這這種種方方式式可可以以構(gòu)構(gòu)建建供供程程序序人人員員重重用用的的代代碼碼庫(kù)庫(kù)。。另外外，，當(dāng)當(dāng)首首次次調(diào)調(diào)用用程程序序包包中中的的存存儲(chǔ)儲(chǔ)過過程程或或函函數(shù)數(shù)等等元元素素時(shí)時(shí)，，Oracle會(huì)會(huì)將將整整個(gè)個(gè)程程序序包包調(diào)調(diào)入入內(nèi)內(nèi)存存，，在在下下次次調(diào)調(diào)用用包包中中的的元元素素時(shí)時(shí)，，Oracle就就可可以以直直接接從從內(nèi)內(nèi)存存中中讀讀取取，，從從而而提提高高程程序序的的運(yùn)運(yùn)行行效效率率。。2810.5.1創(chuàng)創(chuàng)建建程程序序包包1．．創(chuàng)創(chuàng)建建包包規(guī)規(guī)范范創(chuàng)建建包包規(guī)規(guī)范范需需要要使使用用CREATEPACKAGE語(yǔ)語(yǔ)句句，，其其簡(jiǎn)簡(jiǎn)要要語(yǔ)語(yǔ)法法如如下下：：CREATE[ORREPLACE]PACKAGEpackage_name{IS|AS}package_specification;ENDpackage_name;語(yǔ)法法說說明明如如下下。。package_name：：創(chuàng)創(chuàng)建建的的包包名名。。package_specification：：用用于于列列出出用用戶戶可可以以使使用用的的公公共共存存儲(chǔ)儲(chǔ)過過程程、、函函數(shù)數(shù)、、類類型型和和對(duì)對(duì)象象。。2910.5.1創(chuàng)創(chuàng)建建程程序序包包2．創(chuàng)創(chuàng)建包包體創(chuàng)建包包體需需要使使用CREATEPACKAGEBODY語(yǔ)句句，并并且在在創(chuàng)建建時(shí)需需要指指定已已創(chuàng)建建的包包，其其簡(jiǎn)要要語(yǔ)法法如下下：CREATE[ORREPLACE]PACKAGEBODYpackage_name{IS|AS}package_body;ENDpackage_name;3010.5.2調(diào)調(diào)用程程序包包中的的元素素DBMS_OUTPUT是系系統(tǒng)定定義的的包，，而PUT_LINE是是該包包中的的存儲(chǔ)儲(chǔ)過程程?？煽梢娬{(diào)調(diào)用程程序包包中的的元素素時(shí)，，是使使用如如下形形式：：package_name.[element_name];其中，，element_name表示示元素素名稱稱，可可以是是存儲(chǔ)儲(chǔ)過程程名、、函數(shù)數(shù)名、、變量量名和和常量量名等等。3110.5.2調(diào)調(diào)用程程序包包中的的元素素【例10.17】調(diào)用student_package包中中的add_student_procedure過程程，向向student表表中添添加一一條記記錄，，如下下：