Oracle第章LSQL程序設(shè)計

1第15章PL/SQL程序設(shè)計2本章內(nèi)容存儲過程函數(shù)局部子程序包觸發(fā)器3本章要求掌握PL/SQL功能模塊的應(yīng)用存儲過程、函數(shù)、包、觸發(fā)器的創(chuàng)建存儲過程、函數(shù)、包、觸發(fā)器的維護415.1

存儲子程序存儲過程函數(shù)局部子程序15.1.1存儲過程存儲過程的創(chuàng)建存儲過程的調(diào)用存儲過程的管理56（1）存儲過程的創(chuàng)建基本語法CREATE[ORREPLACE]PROCEDUREprocedure_name(parameter1_name[mode]datatype[DEFAULT|:=value][,parameter2_name[mode]datatype[DEFAULT|:=value],…])AS|IS/*Declarativesectionishere*/BEGIN/*Executablesectionishere*/EXCEPTION/*Exceptionsectionishere*/END[procedure_name];7參數(shù)說明參數(shù)的模式

IN（默認參數(shù)模式）表示當(dāng)過程被調(diào)用時，實參值被傳遞給形參；在過程內(nèi)，形參起常量作用，只能讀該參數(shù)，而不能修改該參數(shù)；當(dāng)子程序調(diào)用結(jié)束返回調(diào)用環(huán)境時，實參沒有被改變。IN模式參數(shù)可以是常量或表達式。OUT表示當(dāng)過程被調(diào)用時，實參值被忽略；在過程內(nèi)，形參起未初始化的PL/SQL變量的作用，初始值為NULL，可以進行讀/寫操作；當(dāng)子程序調(diào)用結(jié)束后返回調(diào)用環(huán)境時，形參值被賦給實參。OUT模式參數(shù)只能是變量，不能是常量或表達式。INOUT表示當(dāng)過程被調(diào)用時，實參值被傳遞給形參；在過程內(nèi)，形參起已初始化的PL/SQL變量的作用，可讀可寫；當(dāng)子程序調(diào)用結(jié)束返回調(diào)用環(huán)境時，形參值被賦給實參。INOUT模式參數(shù)只能是變量，不能是常量或表達式。

8參數(shù)的限制在聲明形參時，不能定義形參的長度或精度、刻度，它們是作為參數(shù)傳遞機制的一部分被傳遞的，是由實參決定的。參數(shù)傳遞方式當(dāng)子程序被調(diào)用時，實參與形參之間值的傳遞方式取決于參數(shù)的模式。IN參數(shù)為引用傳遞，即實參的指針被傳遞給形參；OUT，INOUT參數(shù)為值傳遞，即實參的值被復(fù)制給形參。參數(shù)默認值可以為參數(shù)設(shè)置默認值，這樣存儲過程被調(diào)用時如果沒有給該參數(shù)傳遞值，則采用默認值。需要注意，有默認值的參數(shù)應(yīng)該放在參數(shù)列表的最后。

9創(chuàng)建一個存儲過程，以部門號為參數(shù)，查詢該部門的平均工資，并輸出該部門中比平均工資高的員工號、員工名。CREATEORREPLACEPROCEDUREshow_emp(p_deptnoemp.deptno%TYPE)ASv_salemp.sal%TYPE;BEGINSELECTavg(sal)INTOv_salFROMempWHEREdeptno=p_deptno;DBMS_OUTPUT.PUT_LINE(p_deptno||''||'averagesalaryis:'||v_sal);FORv_empIN(SELECT*FROMempWHEREdeptno=p_deptnoANDsal>v_sal)LOOPDBMS_OUTPUT.PUT_LINE(v_emp.empno||''||v_emp.ename);ENDLOOP;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thedepartmentdoesn’’texists!');ENDshow_emp;10通常，存儲過程不需要返回值，如果需要返回一個值可以通過函數(shù)調(diào)用實現(xiàn)。但是，如果希望返回多個值，可以使用OUT或INOUT模式參數(shù)來實現(xiàn)。11創(chuàng)建建一一個個存存儲儲過過程程，，以以部部門門號號為為參參數(shù)數(shù)，，返返回回該該部部門門的的人人數(shù)數(shù)和和最最高高工工資資。。CREATEORREPLACEPROCEDUREreturn_deptinfo(p_deptnoemp.deptno%TYPE,p_avgsalOUTemp.sal%TYPE,p_countOUTemp.sal%TYPE)ASBEGINSELECTavg(sal),count(*)INTOp_avgsal,p_countFROMempWHEREdeptno=p_deptno;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thedepartmentdon’’’texists!');ENDreturn_deptinfo;12（2）存存儲儲過過程程的的調(diào)調(diào)用用在SQL*PLUS中調(diào)用用EXECprocedure_name(parameter_list)EXECUTEshow_emp(10)在PL/SQL塊中調(diào)調(diào)用BEGINprocedure_name(parameter_list);END；注意在PL/SQL程序中中，存存儲過過程可可以作作為一一個獨獨立的的表達達式被被調(diào)用用。13DECLAREv_avgsalemp.sal%TYPE;v_countNUMBER;BEGINshow_emp(20);return_deptinfo(10,v_avgsal,v_count);DBMS_OUTPUT.PUT_LINE(v_avgsal||''||v_count);END;14（3）存儲儲過程程的管管理修改存存儲過過程CREATEORREPLACEPROCEDUREprocedure_name查看看存存儲儲過過程程及及其其源源代代碼碼查詢詢數(shù)數(shù)據(jù)據(jù)字字典典視視圖圖USER_SOURCESELECTname,textFROMuser_sourceWHEREtype='PROCEDURE';重新新編編譯譯存存儲儲過過程程ALTERPROCEDURE……COMPILEALTERPROCEDUREshow_empCOMPILE;刪除除存存儲儲過過程程DROPPROCEDUREDROPPROCEDUREshow_emp;1515.1.2函數(shù)數(shù)函數(shù)數(shù)的的創(chuàng)創(chuàng)建建函數(shù)數(shù)的的調(diào)調(diào)用用函數(shù)數(shù)的的管管理理16（1）函數(shù)的創(chuàng)建建基本語法為CREATE[ORREPLACE]FUNCTIONfunction_name(parameter1_name[mode]datatype[DEFAULT|:=value][,parameter2_name[mode]datatype[DEFAULT|:=value],…])RETURNreturn_datatypeAS|IS/*Declarativesectionishere*/BEGIN/*Executablesectionishere*/EXCEPTION/*Exceptionsectionishere*/END[function_name];17注意在函數(shù)定義的的頭部，參數(shù)數(shù)列表之后，，必須包含一一個RETURN語句來指明明函數(shù)返回回值的類型型，但不能能約束返回回值的長度度、精度、、刻度等。。如果使用用%TYPE，則則可可以以隱隱含含地地包包括括長長度度、、精精度度、、刻刻度度等等約約束束信信息息；；在函函數(shù)數(shù)體體的的定定義義中中，，必必須須至至少少包包含含一一個個RETURN語句句，，來來指指明明函函數(shù)數(shù)返返回回值值。。也也可可以以有有多多個個RETURN語句句，，但但最最終終只只有有一一個個RETURN語句句被被執(zhí)執(zhí)行行。。18創(chuàng)建建一一個個以以部部門門號號為為參參數(shù)數(shù)，，返返回回該該部部門門最最高高工工資資的的函函數(shù)數(shù)。。CREATEORREPLACEFUNCTIONreturn_maxsal(p_deptnoemp.deptno%TYPE)RETURNemp.sal%TYPEASv_maxsalemp.sal%TYPE;BEGINSELECTmax(sal)INTOv_maxsalFROMempWHEREdeptno=p_deptno;RETURNv_maxsal;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thedeptnoisinvalid!');ENDreturn_maxsal;19如果需需要函函數(shù)返返回多多個值值，可可以使使用OUT或INOUT模式參參數(shù)。。20創(chuàng)建一一個函函數(shù)，，以部部門號號為參參數(shù)，，返回回部門門名、、部門門人數(shù)數(shù)及部部門平平均工工資。CREATEORREPLACEFUNCTIONret_deptinfo(p_deptnodept.deptno%TYPE,p_numOUTNUMBER,p_avgOUTNUMBER)RETURNdept.dname%TYPEASv_dnamedept.dname%TYPE;BEGINSELECTdnameINTOv_dnameFROMdeptWHEREdeptno=p_deptno;SELECTcount(*),avg(sal)INTOp_num,p_avgFROMempWHEREdeptno=p_deptno;RETURNv_dname;ENDret_maxsal;21（2）函數(shù)數(shù)的調(diào)調(diào)用在SQL語句中中調(diào)用用函數(shù)數(shù)在PL/SQL中調(diào)用用函數(shù)數(shù)注意函數(shù)只只能作作為表表達式式的一一部分分被調(diào)調(diào)用。。示例通過return_maxsal函數(shù)的的調(diào)用用，輸輸出各各個部部門的的最高高工資資；通通過ret_deptinfo函數(shù)調(diào)調(diào)用，，輸出出各個個部門門名、、部門門人數(shù)數(shù)及平平均工工資。。22DECLAREv_maxsalemp.sal%TYPE;v_avgsalemp.sal%TYPE;v_numNUMBER;v_dnamedept.dname%TYPE;BEGINFORv_deptIN(SELECTDISTINCTdeptnoFROMemp)LOOPv_maxsal:=ret_maxsal(v_dept.deptno);v_dname:=ret_deptinfo(v_dept.deptno,v_num,v_avgsal);DBMS_OUTPUT.PUT_LINE(v_dname||''||v_maxsal||''||v_avgsal||''||v_num);ENDLOOP;END;23函數(shù)可可以在在SQL語句的的以下下部分分調(diào)用用：SELECT語句的的目標(biāo)標(biāo)列；；WHERE和HAVING子句；；CONNECTBY，STARTWITH，ORDERBY，GROUPBY子句；；INSERT語句的的VALUES子句中中；UPDATE語句的的SET子句中中。24如果要要在SQL中調(diào)用用函數(shù)數(shù)，那那么函函數(shù)必必須符符合下下列限限制和和要求求：在SELECT語句中中的函函數(shù)不不能修修改（（INSERT，UPDATE，DELETE）調(diào)用用函數(shù)數(shù)的SQL語句中中使用用的表表；函數(shù)在在一個個遠程程或并并行操操作中中使用用時，，不能能讀/寫封裝裝變量量；函數(shù)必須須是一個個存儲數(shù)數(shù)據(jù)庫對對象（或或存儲在在包中））；函數(shù)的參參數(shù)只能能使用IN模式；形式參數(shù)數(shù)類型必必須使用用數(shù)據(jù)庫庫數(shù)據(jù)類類型；返回的數(shù)數(shù)據(jù)類型型必須是是數(shù)據(jù)庫庫數(shù)據(jù)類類型；25（3）函數(shù)的的管理函數(shù)的修修改CREATEORREPLACEFUNCTIONfunction_name查看函數(shù)數(shù)及其源源代碼查詢數(shù)據(jù)據(jù)字典視視圖USER_SOURCESELECTname,textFROMuser_sourceWHEREtype='FUNCTION';函數(shù)重編編譯ALTERFUNCTION…COMPILEALTERFUNCTIONret_maxsalCOMPILE;刪除函數(shù)數(shù)DROPFUNCTIONDROPFUNCTIONret_maxsal;2615.1.3局部子程程序局部子程程序嵌套在其其他PL/SQL塊中的子子程序。。只能在其其定義的的塊內(nèi)部部被調(diào)用用，而不不能在其其父塊外外被調(diào)用用。使用局部部子程序序時需要要注意：：局部子程程序只在在當(dāng)前語語句塊內(nèi)內(nèi)有效；；局部子程程序必須須在PL/SQL塊聲明部部分的最最后進行行定義；；局部子程程序必須須在使用用之前聲聲明，如如果是子子程序間間相互引引用，則則需要采采用預(yù)先先聲明；；局部子程程序可以以重載。。27在一個塊塊內(nèi)部定定義一個個函數(shù)和和一個過過程。函函數(shù)以部部門號為為參數(shù)返返回該部部門的平平均工資資；過程程以部門門號為參參數(shù)，輸輸出該部部門中工工資低于于部門平平均工資資的員工工的員工工號、員員工名。。28DECLAREv_deptnoemp.deptno%TYPE;v_avgsalemp.sal%TYPE;FUNCTIONreturn_avgsal(p_deptnoemp.deptno%TYPE)RETURNemp.sal%TYPEASv_salemp.sal%TYPE;BEGINSELECTavg(sal)INTOv_salFROMempWHEREdeptno=p_deptno;RETURNv_sal;ENDreturn_avgsal;29PROCEDUREshow_emp(p_deptnoemp.deptno%TYPE)ASCURSORc_empISSELECT*FROMempWHEREdeptno=p_deptno;BEGINFORv_empINc_empLOOPIFv_emp.sal<return_avgsal(v_emp.deptno)THENDBMS_OUTPUT.PUT_LINE(v_emp.empno||''||v_emp.ename);ENDIF;ENDLOOP;ENDshow_emp;BEGINv_deptno:=&x;v_avgsal:=return_avgsal(v_deptno);show_emp(v_deptno);END;30存儲子程程序與局局部子程程序區(qū)別別在于：：存儲子程程序己經(jīng)經(jīng)編譯好好放在數(shù)數(shù)據(jù)庫服服務(wù)器端端，可以以直接調(diào)調(diào)用，而而局部子子程序存存在于定定義它的的語句塊塊中，在在運行時時先進行行編譯；；存儲子程程序不能能重載，，而局部部子程序序可以進進行重載載；存儲子程程序可以以被任意意的PL/SQL塊調(diào)用，，而局部部子程序序只能在在定義它它的塊中中被調(diào)用用。31在一個PL/SQL塊中重載載兩個過過程，一一個以員員工號為為參數(shù)，，輸出該該員工信信息；另另一個以以員工名名為參數(shù)數(shù)，輸出出員工信信息。利利用這兩兩個過程程分別查查詢員工工號為7902，7934，以及員工工名為SMITH，F(xiàn)ORD的員工信息息。32DECLAREPROCEDUREshow_empinfo(p_empnoemp.empno%TYPE)ASv_empemp%ROWTYPE;BEGINSELECT*INTOv_empFROMempWHEREempno=p_empno;DBMS_OUTPUT.PUT_LINE(v_emp.ename||''||v_emp.deptno);EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thereisnotsuchanemployee!');ENDshow_empinfo;33PROCEDUREshow_empinfo(p_enameemp.ename%TYPE)ASv_empemp%ROWTYPE;BEGINSELECT*INTOv_empFROMempWHEREename=p_ename;DBMS_OUTPUT.PUT_LINE(v_emp.empno||''||v_emp.deptno);EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thereisnotsuchanemployee!');WHENTOO_MANY_ROWSTHENDBMS_OUTPUT.PUT_LINE('Therearemorethanoneemployee!');ENDshow_empinfo;34BEGINshow_empinfo(7902);show_empinfo(7934);show_empinfo('SMITH');show_empinfo('FORD');END;3515.2包包概述包的創(chuàng)建包的調(diào)用包重載載包的初初始化化包的管管理36包概述述包是包包含一一個或或多個個子程程序單單元（（過程程、函函數(shù)等等）的的容器器。包是一一種全全局結(jié)結(jié)構(gòu)。。包類型型數(shù)據(jù)庫庫內(nèi)置置包用戶創(chuàng)創(chuàng)建的的包包構(gòu)成成包規(guī)范范包體兩兩37包規(guī)范范聲明明了軟軟件包包中所所有內(nèi)內(nèi)容，，如過過程、、函數(shù)數(shù)、游游標(biāo)、、類型型、異異常和和變量量等，，其中中過程程和函函數(shù)只只包括括原型型信息息，不不包含含任何何子程程序代代碼。。包體中中包含含了在在包頭頭中的的過程程和函函數(shù)的的實現(xiàn)現(xiàn)代碼碼。包包體中中還可可以包包括在在規(guī)范范中沒沒有聲聲明的的變量量、游游標(biāo)、、類型型、異異常、、過程程和函函數(shù)，，但是是它們們是私私有元元素，，只能能由同同一包包體中中其他他過程程和函函數(shù)使使用。。3815.2.1包的創(chuàng)創(chuàng)建創(chuàng)建包包規(guī)范范創(chuàng)建包包體39（1）創(chuàng)建建包規(guī)規(guī)范語法CREATEORREPLACEPACKAGEpackage_nameIS|AS[PRAGMASERIALLY_RESUABLE]type_definition|variable_declaration|exception_declaration|cursor_declaration|procedure_declaration|function_declarationEND[package_name];40注意：元素聲明明的順序序可以是是任意的的，但必必須先聲聲明后使使用；所有元素素是可選選的；過程和函函數(shù)的聲聲明只包包括原型型，不包包括具體體實現(xiàn)。。41創(chuàng)建一個個軟件包包，包括括2個變量、、2個過程和和1個異常。。CREATEORREPLACEPACKAGEpkg_empASminsalNUMBER;maxsalNUMBER;e_beyondboundEXCEPTION;PROCEDUREupdate_sal(p_empnoNUMBER,p_salNUMBER);PROCEDUREadd_employee(p_empnoNUMBER,p_salNUMBER);ENDpkg_emp;42語法CREATEORREPLACEPACKAGEBODYpackage_nameIS|AS[PRAGMASERIALLY_RESUABLE]type_definition|variable_declaration|exception_declaration|cursor_declaration|procedure_definition|function_definitionEND[package_name];（2）創(chuàng)建包體43注意：包體中函數(shù)和和過程的原型型必須與包規(guī)規(guī)范中的聲明明完全一致；；只有在包規(guī)范范已經(jīng)創(chuàng)建的的條件下，才才可以創(chuàng)建包包體；如果包規(guī)范中中不包含任何何函數(shù)或過程程，則可以不不創(chuàng)建包體。。44CREATEORREPLACEPACKAGEBODYpkg_empASPROCEDUREupdate_sal(p_empnoNUMBER,p_salNUMBER)ASBEGINSELECTmin(sal),max(sal)INTOminsal,maxsalFROMemp;IFp_salBETWEENminsalANDmaxsalTHENUPDATEempSETsal=p_salWHEREempno=p_empno;IFSQL%NOTFOUNDTHENRAISE_APPLICATION_ERROR(-20000,'Theemployeedoesn''texist');ENDIF;ELSERAISEe_beyondbound;ENDIF;EXCEPTIONWHENe_beyondboundTHENDBMS_OUTPUT.PUT_LINE('Thesalaryisbeyondbound!');ENDupdate_sal;45PROCEDUREadd_employee(p_empnoNUMBER,p_salNUMBER)ASBEGINSELECTmin(sal),max(sal)INTOminsal,maxsalFROMemp;IFp_salBETWEENminsalANDmaxsalTHENINSERTINTOemp(empno,sal)VALUES(p_empno,p_sal);ELSERAISEe_beyondbound;ENDIF;EXCEPTIONWHENe_beyondboundTHENDBMS_OUTPUT.PUT_LINE('Thesalaryisbeyondbound!');ENDadd_employee;ENDpkg_emp;4615.2.2包的調(diào)用在包規(guī)范聲明明的任何元素素是公有的，，在包外都是是可見的包外：通過package.element形式調(diào)用；包內(nèi)：直接通通過元素名進進行調(diào)用。在包體中定義義而沒有在包包頭中聲明的的元素是私有有的，只能在在包體中引用用47調(diào)用軟件包pkg_emp中的過程update_sal，修改7844員工工資為3000。調(diào)用add_employee添加一個員工工號為1357，工資為4000的員工。BEGINpkg_emp.update_sal(7844,3000);pkg_emp.add_employee(1357,4000);END;4815.2.3包重載重載子程序必必須同名不同同參，即名稱稱相同，參數(shù)數(shù)不同。參數(shù)數(shù)不同體現(xiàn)為為參數(shù)的個數(shù)數(shù)、順序、類類型等不同。。如果兩個子程程序參數(shù)只是是名稱和模式式不同，則不不能重載。PROCEDUREoverloadme(parameter1INNUMBER);PROCEDUREoverloadme(parameter2OUTNUMBER);不能根據(jù)兩個個函數(shù)返回類類型不同而對對它們進行重重載。FUNCTIONoverloadmeRETURNDATE;FUNCTIONoverloadmeRETURNNUMBER;重載子程序參參數(shù)必須在類類型系列方面面有所不同。。PROCEDUREoverloadchar(parameterINCHAR);PROCEDUREoverloadchar(parameterINVARCHAR2);49在一個包中重重載兩個過程程，分別以部部門號和部門門名稱為參數(shù)數(shù)，查詢相應(yīng)應(yīng)部門員工名名、員工號信信息。50CREATEORREPLACEPACKAGEpkg_overloadASPROCEDUREshow_emp(p_deptnoNUMBER);PROCEDUREshow_emp(p_dnameVARCHAR2);ENDpkg_overload;51CREATEORREPLACEPACKAGEBODYpkg_overloadASPROCEDUREshow_emp(p_deptnoNUMBER)ASBEGINFORv_empIN(SELECT*FROMempWHEREdeptno=p_deptno)LOOPDBMS_OUTPUT.PUT_LINE(v_emp.empno||''||v_emp.ename);ENDLOOP;ENDshow_emp;52PROCEDUREshow_emp(p_dnameVARCHAR2)ASv_deptnoNUMBER;BEGINSELECTdeptnoINTOv_deptnoFROMdeptWHEREdname=p_dname;FORv_empIN(SELECT*FROMempWHEREdeptno=v_deptno)LOOPDBMS_OUTPUT.PUT_LINE(v_emp.empno||''||v_emp.ename);ENDLOOP;ENDshow_emp;ENDpkg_overload;5315.2.4包的初始始化包在第一一次被調(diào)調(diào)用時從從磁盤讀讀取到共共享池，，并在整整個會話話的持續(xù)續(xù)期間保保持。在在此過程程中，可可以自動動執(zhí)行一一個初始始化過程程，對軟軟件包進進行實例例化。包的初始始化過程程只在包包第一次次被調(diào)用用時執(zhí)行行，因此此也稱為為一次性性過程，，它是一一個匿名名的PL/SQL塊，在包包體結(jié)構(gòu)構(gòu)的最后后，以BEGIN開始。示例在pkg_emp包中，在在包初始始化時給給minsal和maxsal兩個變量量賦值，，在子程程序中直直接引用用這兩個個變量。。54CREATEORREPLACEPACKAGEpkg_empASminsalNUMBER;maxsalNUMBER;e_beyondboundEXCEPTION;PROCEDUREupdate_sal(p_empnoNUMBER,p_salNUMBER);PROCEDUREadd_employee(p_empnoNUMBER,p_salNUMBER);ENDpkg_emp;55CREATEORREPLACEPACKAGEBODYpkg_empASPROCEDUREupdate_sal(p_empnoNUMBER,p_salNUMBER)ASBEGINIFp_salBETWEENminsalANDmaxsalTHENUPDATEempSETsal=p_salWHEREempno=p_empno;IFSQL%NOTFOUNDTHENRAISE_APPLICATION_ERROR(-20000,'Theemployeedoesn''texist');ENDIF;ELSERAISEe_beyondbound;ENDIF;EXCEPTIONWHENe_beyondboundTHENDBMS_OUTPUT.PUT_LINE('Thesalaryisbeyondbound!');ENDupdate_sal;56PROCEDUREadd_employee(p_empnoNUMBER,p_salNUMBER)ASBEGINIFp_salBETWEENminsalANDmaxsalTHENINSERTINTOemp(empno,sal)VALUES(p_empno,p_sal);ELSERAISEe_beyondbound;ENDIF;EXCEPTIONWHENe_beyondboundTHENDBMS_OUTPUT.PUT_LINE('Thesalaryisbeyondbound!');ENDadd_employee;BEGINSELECTmin(sal),max(sal)INTOminsal,maxsalFROMemp;ENDpkg_emp;5715.2.5包的管理包的修改CREATEORREPLACEPACKAGEpackage_name查看包及其其源代碼查詢數(shù)據(jù)字字典視圖USER_SOURCESELECTname,textFROMuser_sourceWHEREtype='PACKAGE';SELECTname,textFROMuser_sourceWHEREtype='PACKAGEBODY';重新編譯包包ALTERPACKAGE…COMPILE（包規(guī)范和和包體）ALTERPACKAGE…COMPILESPECIFICATION（包規(guī)范）ALTERPACKAGE…COMPILEBODY（包體）58ALTERPACKAGEpkg_empCOMPILE;ALTERPACKAGEpkg_empCOMPILESPECIFICATION;ALTERPACKAGEpkg_empCOMPILEBODY;刪除包DROPPACKAGE（包規(guī)范和包包體）DROPPACKAGEBODY（包體）DROPPACKAGEBODYpkg_emp;DROPPACKAGEpkg_emp;5915.3觸發(fā)器觸發(fā)器概述DML觸發(fā)器INSTEAD-OF觸發(fā)器系統(tǒng)觸發(fā)器變異表觸發(fā)器器觸發(fā)器的管理理6015.3.1觸發(fā)器概述觸發(fā)器的概念念與作用觸發(fā)器的類型型觸發(fā)器組成61（1）觸發(fā)器的概概念與作用觸發(fā)器是一種種特殊類型的的存儲過程，，編譯后存儲儲在數(shù)據(jù)庫服服務(wù)器中。當(dāng)特定事件發(fā)發(fā)生時，由系系統(tǒng)自動調(diào)用用執(zhí)行，而不不能由應(yīng)用程程序顯式地調(diào)調(diào)用執(zhí)行。觸發(fā)器不接受受任何參數(shù)。。觸發(fā)器主要用用于維護那些些通過創(chuàng)建表表時的聲明約約束不可能實實現(xiàn)的復(fù)雜的的完整性約束束，并對數(shù)據(jù)據(jù)庫中特定事事件進行監(jiān)控控和響應(yīng)。62（2）觸發(fā)器的類類型DML觸發(fā)器建立在基本表表上的觸發(fā)器器，響應(yīng)基本本表的INSERT，UPDATE，DELETE操作。INSTEADOF觸發(fā)器建立在視圖上上的觸發(fā)器，，響應(yīng)視圖上上的INSERT，UPDATE，DELETE操作。系統(tǒng)觸發(fā)器建立在系統(tǒng)或或模式上的觸觸發(fā)器，響應(yīng)應(yīng)系統(tǒng)事件和和DDL（CREATE，ALTER，DROP）操作。63（3）觸發(fā)器組成成觸發(fā)器由觸發(fā)發(fā)器頭部和觸觸發(fā)器體兩個個部分組成，，主要包括：：作用對象：觸觸發(fā)器作用的的對象包括表表、視圖、數(shù)數(shù)據(jù)庫和模式式。觸發(fā)事件：激激發(fā)觸發(fā)器執(zhí)執(zhí)行的事件。。如DML、DDL、數(shù)據(jù)庫系統(tǒng)統(tǒng)事件等。觸發(fā)時間：用用于指定觸發(fā)發(fā)器在觸發(fā)事事件完成之前前還是之后執(zhí)執(zhí)行。如果指指定為AFTER，則表示先執(zhí)執(zhí)行觸發(fā)事件件，然后再執(zhí)執(zhí)行觸發(fā)器；；如果指定為為BEFORE，則表示先執(zhí)執(zhí)行觸發(fā)器，，然后再執(zhí)行行觸發(fā)事件。。64觸發(fā)級別：觸觸發(fā)級別用于于指定觸發(fā)器器響應(yīng)觸發(fā)事事件的方式。。默認為語句句級觸發(fā)器，，即觸發(fā)事件件發(fā)生后，觸觸發(fā)器只執(zhí)行行一次。如果果指定為FOREACHROW，即為行級觸觸發(fā)器，則觸觸發(fā)事件每作作用于一個記記錄，觸發(fā)器器就會執(zhí)行一一次。觸發(fā)條件：由由WHEN子句指定一個個邏輯表達式式，當(dāng)觸發(fā)事事件發(fā)生，而而且WHEN條件為TRUE時，觸發(fā)器才才會執(zhí)行。觸發(fā)操作：觸觸發(fā)器執(zhí)行時時所進行的操操作。6515.3.2DML觸發(fā)器DML觸發(fā)器的種類類及執(zhí)行順序序創(chuàng)建DML觸發(fā)器66（1）DML觸發(fā)器的種類類及執(zhí)行順序序DML觸發(fā)器的種類類語句級前觸發(fā)發(fā)器語句級后觸發(fā)發(fā)器行級前觸發(fā)器器行級后觸發(fā)器器67DML觸發(fā)器的執(zhí)行行順序如果存在，則則執(zhí)行語句級級前觸發(fā)器。。對于受觸發(fā)事事件影響的每每一個記錄：：如果存在，則則執(zhí)行行級前前觸發(fā)器；執(zhí)行行當(dāng)當(dāng)前前記記錄錄的的DML操作作（（觸觸發(fā)發(fā)事事件件））；；如果果存存在在，，則則執(zhí)執(zhí)行行行行級級后后觸觸發(fā)發(fā)器器。。如果果存存在在，，則則執(zhí)執(zhí)行行語語句句級級后后觸觸發(fā)發(fā)器器。。68（2）創(chuàng)創(chuàng)建建DML觸發(fā)發(fā)器器語法法CREATE[ORREPLACE]TRIGGERtrigger_nameBEFORE|AFTERtriggering_event[OFcolumn_name]ONtable_name][FOREACHROW][WHENtrigger_condition]DECLARE/*Declarativesectionishere*/BEGIN/*Exccutablesectionsihere*/EXCEPTION/*Exceptionsectionishere*/END[trigger_name];Trigger_body69語句級級觸發(fā)發(fā)器在默認認情況況下創(chuàng)創(chuàng)建的的DML觸發(fā)器器為語語句級級觸發(fā)發(fā)器，，即觸觸發(fā)事事件發(fā)發(fā)生后后，觸觸發(fā)器器只執(zhí)執(zhí)行一一次。。70創(chuàng)建一一個觸觸發(fā)器器，禁禁止在在休息息日改改變雇雇員信信息CREATEORREPLACETRIGGERtrg_emp_weekendBEFOREINSERTORUPDATEORDELETEONempBEGINIFTO_CHAR(SYSDATE,'DY','nls_date_language=american')IN('SAT','SUN')THENraise_application_error(-20000,'Can''toperateinweekend.');ENDIF;ENDtrg_emp_weekend;71如果觸發(fā)器器響應(yīng)多個個DML事件，而且且需要根據(jù)據(jù)事件的不不同進行不不同的操作作，則可以以在觸發(fā)器器體中使用用3個條件謂詞詞。謂詞行為INSERTING如果觸發(fā)語句是INSERT，則為TRUE；否則為FALSEUPDATING如果觸發(fā)語句是UPDATE，則為TRUE；否則為FALSEDELETING如果觸發(fā)語句是DELETE，則為TRUE；否則為FALSE72為emp表創(chuàng)建一個個觸發(fā)器，，當(dāng)執(zhí)行插插入操作時時，統(tǒng)計操操作后員工工人數(shù)；當(dāng)當(dāng)執(zhí)行更新新工資操作作時，統(tǒng)計計更新后員員工平均工工資；當(dāng)執(zhí)執(zhí)行刪除操操作時，統(tǒng)統(tǒng)計刪除后后各個部門門的人數(shù)。。73CREATEORREPLACETRIGGERtrg_emp_dmlAFTERINSERTORUPDATEORDELETEONempDECLAREv_countNUMBER;v_salNUMBER(6,2);BEGINIFINSERTINGTHENSELECTcount(*)INTOv_countFROMemp;DBMS_OUTPUT.PUT_LINE(v_count);ELSIFUPDATINGTHENSELECTavg(sal)INTOv_salFROMemp;DBMS_OUTPUT.PUT_LINE(v_sal);ELSEFORv_deptIN(SELECTdeptno,count(*)numFROMempGROUPBYdeptno)LOOPDBMS_OUTPUT.PUT_LINE(v_dept.deptno||''||v_dept.num);ENDLOOP;ENDIF;ENDtrg_emp_dml;74行級觸發(fā)器器行級觸發(fā)器器是指執(zhí)行行DML操作時，每每操作一個個記錄，觸觸發(fā)器就執(zhí)執(zhí)行一次，，一個DML操作涉及多多少個記錄錄，觸發(fā)器器就執(zhí)行多多少次。在行級觸發(fā)發(fā)器中可以以使用WHEN條件，進一一步控制觸觸發(fā)器的執(zhí)執(zhí)行。在行級觸觸發(fā)器中中引入了了:old和:new兩個標(biāo)識識符，來來訪問和和操作當(dāng)當(dāng)前被處處理記錄錄中的數(shù)數(shù)據(jù)。75標(biāo)識符:old和:new作為triggering_table%ROWTYPE類型的兩兩個變量量在不同觸觸發(fā)事件件中，:old和:new的意義不不同觸發(fā)事件:old:newINSERT未定義，所有字段都為NULL當(dāng)語句完成時，被插入的記錄UPDATE更新前原始記錄當(dāng)語句完成時，更新后的記錄DELETE記錄被刪除前的原始值未定義，所有字段都為NULL76引用方式式：:old.field和:new.field（執(zhí)行部部分）old.field和new.field(WHEN條件中)注意事項項：是偽記錄錄，不能能作為整整個記錄錄進行賦賦值或引引用不能傳遞遞給帶triggering_table%ROWTYPE參數(shù)的過過程和函函數(shù)如果觸發(fā)發(fā)器是建建立在嵌嵌套表上上，;old和;new都執(zhí)行嵌嵌套表的的行，:parent指向父表表中的當(dāng)當(dāng)前行。。77為emp表創(chuàng)建一一個觸發(fā)發(fā)器，當(dāng)當(dāng)插入新新員工時時顯示新新員工的的員工號號、員工工名；當(dāng)當(dāng)更新員員工工資資時，顯顯示修改改前后員員工工資資；當(dāng)刪刪除員工工時，顯顯示被刪刪除的員員工號、、員工名名。78CREATEORREPLACETRIGGERtrg_emp_dml_rowBEFOREINSERTORUPDATEORDELETEONempFOREACHROWBEGINIFINSERTINGTHENDBMS_OUTPUT.PUT_LINE(:new.empno||''||:new.ename);ELSIFUPDATINGTHENDBMS_OUTPUT.PUT_LINE(:old.sal||''||:new.sal);ELSEDBMS_OUTPUT.PUT_LINE(:old.empno||''||:old.ename);ENDIF;ENDtrg_emp_dml_row;79在行級觸觸發(fā)器中中，可以以使用WHEN子句進一一步控制制觸發(fā)器器的執(zhí)行行。例如，修修改員工工工資時時，保證證修改后后的工資資高于修修改前的的工資。。CREATEORREPLACETRIGGERtrg_emp_update_rowBEFOREUPDATEOFsalONempFOREACHROWWHEN(new.sal<=old.sal)BEGINRAISE_APPLICATION_ERROR(-20001,'Thesalaryislower!');ENDtrg_emp_update_row;8015.3.3INSTEADOF觸發(fā)器特點只能定義義在視圖圖上Instead-of觸發(fā)器是是行級觸觸發(fā)器Instead-of觸發(fā)器由由DML操作激發(fā)發(fā)，而DML操作本身身并不執(zhí)執(zhí)行作用修改一個個本來不不可以修修改的視視圖修改視圖圖中某嵌嵌套表列列的列81如果視圖圖中包含含下列任任何一項項，則該該視圖不不可修改改集合操作作符（UNION，UNIONALL，MINUS，INTERSECT）；聚集函數(shù)數(shù)（SUM，AVG等）；GROUPBY，CONNECTBY或STARTWITH子句；DISTINCT操作符；；涉及多個個表的連連接操作作。82創(chuàng)建INSTEADOF觸發(fā)器的的基本語語法CREATE[ORREPLACE]TRIGGERtrigger_nameINSTEADOFtriggering_event[OFcolumn_name]ONview_nameFOREACHROW[WHENtrigger_condition]DECLARE/*Declarativesectionishere*/BEGIN/*Exccutablesectionsihere*/EXCEPTION/*Exceptionsectionishere*/END[trigger_name];83創(chuàng)建一個包括括員工及其所所在部門信息息的視圖empdept，然后向視圖圖中插入一條條記錄（2345,’TOM’,3000,’SALES’）。CREATEORREPLACEVIEWempdeptASSELECTempno,ename,sal,dnameFROMemp,deptWHEREemp.deptno=dept.deptnoWITHCHECKOPTION;INSERTINTOempdeptVALUES(2345,'TOM',3000,'SALES');*ERROR位于第1行:ORA-01733:此處不允許虛虛擬列84CREATEORREPLACETRIGGERtrig_viewINSTEADOFINSERTONempdeptFOREACHROWDECLAREv_deptnodept.deptno%type;BEGINSELECTdeptnoINTOv_deptnoFROMdeptWHEREdname=:new.dname;INSERTINTOemp(empno,ename,sal,deptno)VALUES(:new.empno,:new.ename,v_deptno,:new.sal);ENDtrig_view;8515.3.4系統(tǒng)觸觸發(fā)器器觸發(fā)器器事件件創(chuàng)建系系統(tǒng)觸觸發(fā)器器86（1）觸發(fā)發(fā)事件件DDL事件CREATE，ALTER，DROP，RENAME，GRANT，REVOKE，AUDIT，NOAUDIT，COMMENT，TRUNCATE，ANALYZE，ASSOCIATESTATISTICS，DISASSOCIATESTATISTICS等。。觸發(fā)發(fā)時時間間可可以以是是BEFORE，也也可可以以是是AFTER。數(shù)據(jù)據(jù)庫庫事事件件STARTUP，SHUTDOWN，SERVERERROR，LOGON，LOGOFF等。觸發(fā)時間間由具體體事件決決定，87事件允許計時描述STARTUPAFTER當(dāng)實例開始時激發(fā)SHUTDOWNBEFORE當(dāng)實例關(guān)閉時激發(fā)SERVERERRORAFTER只要錯誤發(fā)生就激發(fā)LOGONAFTER在一個用戶成功連接數(shù)據(jù)庫時觸發(fā)LOGOFFBEFORE在用戶注銷時開始激發(fā)數(shù)據(jù)庫事事件的觸觸發(fā)時間間88語法CREATE[ORREPLACE]TRIGGERtrigger_nameBEFORE|AFTERddl_event_list|database_event_listONDATABASE|SCHEMA[WHENtrigger_condition]DECLARE/*Declarativesectionishere*/BEGIN/*Executablesectionishere*/EXCEPTION/*Exceptionsectionishere*/END[trigger_name];（2）創(chuàng)建系系統(tǒng)觸發(fā)發(fā)器89說明基于數(shù)據(jù)據(jù)庫（DATABASE）的觸發(fā)發(fā)器，只只要系統(tǒng)統(tǒng)中該觸觸發(fā)事件件發(fā)生，，且滿足足觸發(fā)條條件，則則觸發(fā)器器執(zhí)行；；對于基于于模式（（SCHEMAN）的觸發(fā)發(fā)器，只只有當(dāng)特特定模式式中的觸觸發(fā)事件件發(fā)生時時，觸發(fā)發(fā)器才執(zhí)執(zhí)行。注意STARTUP和SHUTDOWN事件只能能激發(fā)基基于數(shù)據(jù)據(jù)庫的觸觸發(fā)器。。90將每個用用戶的登登錄信息息寫入temp_table表中。CREATEORREPLACETRIGGERlog_user_connectionAFTERLOGONONDATABASEBEGININSERTINTOscott.temp_tableVALUES(user,sysdate);ENDlog_user_connection;91在系統(tǒng)觸觸發(fā)器內(nèi)內(nèi)部可以以使用一一些事件件屬性函函數(shù)以獲獲得觸發(fā)發(fā)事件的的信息。。由于系統(tǒng)統(tǒng)沒有為為這些事事件屬性性函數(shù)指指定同義義詞，因因此在調(diào)調(diào)用時必必須在其其前加上上“SYS.”前綴。示例當(dāng)數(shù)據(jù)庫庫中執(zhí)行行CREATE操作時，，將創(chuàng)建建的對象象信息記記錄到ddl_creations表中。92CREATETABLEddl_creations(user_idVARCHAR2(30),object_typeVARCHAR2(20),object_nameVARCHAR2(30),object_ownerVARCHAR2(30),creation_dateDATE);CREATEORREPLACETRIGGERlog_creationsAFTERCREATEONDATABASEBEGININSERTINTOddl_creationsVALUES(ora_login_user,ora_dict_obj_type,ora_dict_obj_name,ora_dict_obj_owner,sysdate);ENDlog_creations;9315.3.5變異表表觸發(fā)發(fā)器概念變異表表是指指激發(fā)發(fā)觸發(fā)發(fā)器的的DML語句所所操作作的表表，即即觸發(fā)發(fā)器為為之定定義的的表，，或者者由于于DELETECASCADE操作而而需要要修改改的表表，即即當(dāng)前前表的的子表表。約束表表是指指由于于引用用完整整性約約束而而需要要從中中讀取取或修修改數(shù)數(shù)據(jù)的的表，，即當(dāng)當(dāng)前表表的父父表。。94當(dāng)對一一個表表創(chuàng)建建行級級觸發(fā)發(fā)器，，或創(chuàng)創(chuàng)建由由DELETECASCADE操作而而激發(fā)發(fā)的語語句級級觸發(fā)發(fā)器時時，有有下列列兩條條限制制：不能讀讀取或或修改改任何何觸發(fā)發(fā)