Oracle第15章PLSQL程序設計

1、1第15章 PL/SQL程序設計2本章內(nèi)容容存儲過程程函數(shù)局部子程程序包觸發(fā)器3本章要求求掌握PL/SQL功能模塊塊的應用用存儲過程程、函數(shù)數(shù)、包、觸發(fā)器器的創(chuàng)建建存儲過程程、函數(shù)數(shù)、包、觸發(fā)器器的維護護415.1存儲子程序存儲過程程函數(shù)局部子程程序15.1.1存儲過程程存儲過程程的創(chuàng)建建存儲過程程的調(diào)用用存儲過程程的管理理56（1）存儲過過程的創(chuàng)創(chuàng)建基本語法法CREATEOR REPLACE PROCEDUREprocedure_name(parameter1_name modedatatypeDEFAULT|:=value,parameter2_namemode datatypeDEFAU

2、LT|:=value,)AS|IS/*Declarative sectionishere*/BEGIN/*Executablesectionishere*/EXCEPTION/*Exceptionsection is here*/ENDprocedure_name;7參數(shù)說明明參數(shù)的模模式IN（默認參參數(shù)模式式）表示示當過程程被調(diào)用用時，實實參值被被傳遞給給形參；在過程程內(nèi)，形形參起常常量作用用，只能能讀該參參數(shù)，而而不能修修改該參參數(shù)；當當子程序序調(diào)用結結束返回回調(diào)用環(huán)環(huán)境時，實參沒沒有被改改變。IN模式參數(shù)數(shù)可以是是常量或或表達式式。OUT表示當過過程被調(diào)調(diào)用時，實參值值被忽略略；在過過程

3、內(nèi)，形參起起未初始始化的PL/SQL變量的作作用，初初始值為為NULL，可以進進行讀/寫操作；當子程程序調(diào)用用結束后后返回調(diào)調(diào)用環(huán)境境時，形形參值被被賦給實實參。OUT模式參數(shù)數(shù)只能是是變量，不能是是常量或或表達式式。INOUT表示當過過程被調(diào)調(diào)用時，實參值值被傳遞遞給形參參；在過過程內(nèi)，形參起起已初始始化的PL/SQL變量的作作用，可可讀可寫寫；當子子程序調(diào)調(diào)用結束束返回調(diào)調(diào)用環(huán)境境時，形形參值被被賦給實實參。INOUT模式參數(shù)數(shù)只能是是變量，不能是是常量或或表達式式。8參數(shù)的限限制在聲明形形參時，不能定定義形參參的長度度或精度度、刻度度，它們們是作為為參數(shù)傳傳遞機制制的一部部分被傳傳遞的，

4、是由實實參決定定的。參數(shù)傳遞遞方式當子程序序被調(diào)用用時，實實參與形形參之間間值的傳傳遞方式式取決于于參數(shù)的的模式。IN參數(shù)為引引用傳遞遞，即實實參的指指針被傳傳遞給形形參；OUT，INOUT參數(shù)為值值傳遞，即實參參的值被被復制給給形參。參數(shù)默認認值可以為參參數(shù)設置置默認值值，這樣樣存儲過過程被調(diào)調(diào)用時如如果沒有有給該參參數(shù)傳遞遞值，則則采用默默認值。需要注注意，有有默認值值的參數(shù)數(shù)應該放放在參數(shù)數(shù)列表的的最后。9創(chuàng)建一個個存儲過過程，以以部門號號為參數(shù)數(shù)，查詢詢該部門門的平均均工資，并輸出出該部門門中比平平均工資資高的員員工號、員工名名。CREATEORREPLACEPROCEDUREshow

5、_emp(p_deptnoemp.deptno%TYPE)ASv_salemp.sal%TYPE;BEGINSELECTavg(sal)INTOv_salFROM empWHEREdeptno=p_deptno;DBMS_OUTPUT.PUT_LINE(p_deptno|averagesalaryis:|v_sal);FORv_empIN(SELECT* FROMemp WHEREdeptno=p_deptnoAND salv_sal)LOOPDBMS_OUTPUT.PUT_LINE(v_emp.empno|v_emp.ename);ENDLOOP;EXCEPTIONWHEN NO_DATA_

6、FOUNDTHENDBMS_OUTPUT.PUT_LINE(The departmentdoesnt exists!);ENDshow_emp;10通常，存存儲過程程不需要要返回值值，如果果需要返返回一個個值可以以通過函函數(shù)調(diào)用用實現(xiàn)。但是，如果希希望返回回多個值值，可以以使用OUT或INOUT模式參數(shù)數(shù)來實現(xiàn)現(xiàn)。11創(chuàng)建一個個存儲過過程，以以部門號號為參數(shù)數(shù)，返回回該部門門的人數(shù)數(shù)和最高高工資。CREATEORREPLACEPROCEDUREreturn_deptinfo(p_deptnoemp.deptno%TYPE,p_avgsalOUTemp.sal%TYPE,p_countOUTem

7、p.sal%TYPE)ASBEGINSELECTavg(sal),count(*)INTOp_avgsal,p_countFROM empWHEREdeptno=p_deptno;EXCEPTIONWHEN NO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE(The departmentdont exists!);ENDreturn_deptinfo;12（2）存儲過過程的調(diào)調(diào)用在SQL*PLUS中調(diào)用EXECprocedure_name(parameter_list)EXECUTE show_emp(10)在PL/SQL塊中調(diào)用用BEGINprocedure_name

8、(parameter_list);END；注意在PL/SQL程序中，存儲過過程可以以作為一一個獨立立的表達達式被調(diào)調(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ù)據(jù)據(jù)字典視視圖USER_SOURCESELE

9、CTname,text FROMuser_sourceWHEREtype=PROCEDURE;重新編譯譯存儲過過程ALTERPROCEDURECOMPILEALTERPROCEDUREshow_empCOMPILE;刪除存儲儲過程DROP PROCEDUREDROP PROCEDUREshow_emp;1515.1.2函數(shù)函數(shù)的創(chuàng)創(chuàng)建函數(shù)的調(diào)調(diào)用函數(shù)的管管理16（1）函數(shù)的的創(chuàng)建基本語法法為CREATEOR REPLACE FUNCTION function_name(parameter1_name modedatatypeDEFAULT|:=value,parameter2_namemode

10、 datatypeDEFAULT|:=value,)RETURNreturn_datatypeAS|IS/*Declarative sectionishere*/BEGIN/*Executablesectionishere*/EXCEPTION/*Exceptionsection is here*/ENDfunction_name;17注意在函數(shù)定定義的頭頭部，參參數(shù)列表表之后，必須包包含一個個RETURN語句來指指明函數(shù)數(shù)返回值值的類型型，但不不能約束束返回值值的長度度、精度度、刻度度等。如如果使用用%TYPE，則可以以隱含地地包括長長度、精精度、刻刻度等約約束信息息；在函數(shù)體體的定義義中，

11、必必須至少少包含一一個RETURN語句，來來指明函函數(shù)返回回值。也也可以有有多個RETURN語句，但但最終只只有一個個RETURN語句被執(zhí)執(zhí)行。18創(chuàng)建一個個以部門門號為參參數(shù)，返返回該部部門最高高工資的的函數(shù)。CREATEORREPLACEFUNCTIONreturn_maxsal(p_deptnoemp.deptno%TYPE)RETURNemp.sal%TYPEASv_maxsalemp.sal%TYPE;BEGINSELECTmax(sal)INTOv_maxsalFROM empWHEREdeptno=p_deptno;RETURNv_maxsal;EXCEPTIONWHEN NO_

12、DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE(The deptnoisinvalid!);ENDreturn_maxsal;19如果需要要函數(shù)返返回多個個值，可可以使用用OUT或INOUT模式參數(shù)數(shù)。20創(chuàng)建一個個函數(shù)，以部門門號為參參數(shù)，返返回部門門名、部部門人數(shù)數(shù)及部門門平均工工資。CREATEORREPLACEFUNCTIONret_deptinfo(p_deptnodept.deptno%TYPE,p_numOUTNUMBER,p_avgOUTNUMBER)RETURNdept.dname%TYPEASv_dname dept.dname%TYPE;BEGINS

13、ELECTdnameINTOv_dname FROMdeptWHEREdeptno=p_deptno;SELECTcount(*),avg(sal)INTOp_num,p_avgFROM empWHEREdeptno=p_deptno;RETURNv_dname;ENDret_maxsal;21（2）函數(shù)的的調(diào)用在SQL語句中調(diào)調(diào)用函數(shù)數(shù)在PL/SQL中調(diào)用函函數(shù)注意函數(shù)只能能作為表表達式的的一部分分被調(diào)用用。示例通過return_maxsal函數(shù)的調(diào)調(diào)用，輸輸出各個個部門的的最高工工資；通通過ret_deptinfo函數(shù)調(diào)用用，輸出出各個部部門名、部門人人數(shù)及平平均工資資。22DECLARE

14、v_maxsalemp.sal%TYPE;v_avgsalemp.sal%TYPE;v_numNUMBER;v_dnamedept.dname%TYPE;BEGINFORv_dept IN (SELECTDISTINCTdeptno FROMemp)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語句

15、的以以下部分分調(diào)用：SELECT語句的目目標列；WHERE和HAVING子句；CONNECT BY，STARTWITH，ORDERBY，GROUPBY子句；INSERT語句的VALUES子句中；UPDATE語句的SET子句中。24如果要在在SQL中調(diào)用函函數(shù)，那那么函數(shù)數(shù)必須符符合下列列限制和和要求：在SELECT語句中的的函數(shù)不不能修改改（INSERT，UPDATE，DELETE）調(diào)用函函數(shù)的SQL語句中使使用的表表；函數(shù)在一一個遠程程或并行行操作中中使用時時，不能能讀/寫封裝變變量；函數(shù)必須須是一個個存儲數(shù)數(shù)據(jù)庫對對象（或或存儲在在包中）；函數(shù)的參參數(shù)只能能使用IN模式；形式參數(shù)數(shù)類型必必

16、須使用用數(shù)據(jù)庫庫數(shù)據(jù)類類型；返回的數(shù)數(shù)據(jù)類型型必須是是數(shù)據(jù)庫庫數(shù)據(jù)類類型；25（3）函數(shù)的的管理函數(shù)的修修改CREATEORREPLACEFUNCTIONfunction_name查看函數(shù)數(shù)及其源源代碼查詢數(shù)據(jù)據(jù)字典視視圖USER_SOURCESELECTname,text FROMuser_sourceWHEREtype=FUNCTION;函數(shù)重編編譯ALTERFUNCTIONCOMPILEALTERFUNCTIONret_maxsal COMPILE;刪除函數(shù)數(shù)DROP FUNCTIONDROP FUNCTION ret_maxsal;2615.1.3局部子程程序局部子程程序嵌套在其其他P

17、L/SQL塊中的子子程序。只能在其其定義的的塊內(nèi)部部被調(diào)用用，而不不能在其其父塊外外被調(diào)用用。使用局部部子程序序時需要要注意：局部子程程序只在在當前語語句塊內(nèi)內(nèi)有效；局部子程程序必須須在PL/SQL塊聲明部部分的最最后進行行定義；局部子程程序必須須在使用用之前聲聲明，如如果是子子程序間間相互引引用，則則需要采采用預先先聲明；局部子程程序可以以重載。27在一個塊塊內(nèi)部定定義一個個函數(shù)和和一個過過程。函函數(shù)以部部門號為為參數(shù)返返回該部部門的平平均工資資；過程程以部門門號為參參數(shù)，輸輸出該部部門中工工資低于于部門平平均工資資的員工工的員工工號、員員工名。28DECLAREv_deptnoemp.de

18、ptno%TYPE;v_avgsalemp.sal%TYPE;FUNCTIONreturn_avgsal(p_deptno emp.deptno%TYPE)RETURNemp.sal%TYPEASv_salemp.sal%TYPE;BEGINSELECTavg(sal)INTOv_salFROM empWHEREdeptno=p_deptno;RETURNv_sal;ENDreturn_avgsal; 29PROCEDUREshow_emp(p_deptnoemp.deptno%TYPE)ASCURSORc_empISSELECT*FROM empWHEREdeptno=p_deptno;BE

19、GINFORv_empINc_empLOOPIFv_emp.salreturn_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ù)庫服服務器端端，可以以直接調(diào)調(diào)用，而而局部子子程序存存在于定定義它的的語句塊塊中，在在運行時時先

20、進行行編譯；存儲子程程序不能能重載，而局部部子程序序可以進進行重載載；存儲子程程序可以以被任意意的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*INTO v_e

21、mp FROMempWHEREempno=p_empno;DBMS_OUTPUT.PUT_LINE(v_emp.ename|v_emp.deptno);EXCEPTIONWHEN NO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE(Thereisnot suchanemployee!);ENDshow_empinfo; 33PROCEDUREshow_empinfo(p_enameemp.ename%TYPE)ASv_empemp%ROWTYPE;BEGINSELECT*INTO v_emp FROMempWHEREename=p_ename;DBMS_OUTPUT.P

22、UT_LINE(v_emp.empno|v_emp.deptno);EXCEPTIONWHEN NO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE(Thereisnot suchanemployee!);WHEN TOO_MANY_ROWSTHENDBMS_OUTPUT.PUT_LINE(Thereare morethanoneemployee!);ENDshow_empinfo;34BEGINshow_empinfo(7902);show_empinfo(7934);show_empinfo(SMITH);show_empinfo(FORD);END;3515.2包包

23、概述包的創(chuàng)建建包的調(diào)用用包重載包的初始始化包的管理理36包概述包是包含含一個或或多個子子程序單單元（過過程、函函數(shù)等）的容器器。包是一種種全局結結構。包類型數(shù)據(jù)庫內(nèi)內(nèi)置包用戶創(chuàng)建建的包包構成包規(guī)范包體兩37包規(guī)范聲聲明了軟軟件包中中所有內(nèi)內(nèi)容，如如過程、函數(shù)、游標、類型、異常和和變量等等，其中中過程和和函數(shù)只只包括原原型信息息，不包包含任何何子程序序代碼。包體中包包含了在在包頭中中的過程程和函數(shù)數(shù)的實現(xiàn)現(xiàn)代碼。包體中中還可以以包括在在規(guī)范中中沒有聲聲明的變變量、游游標、類類型、異異常、過過程和函函數(shù)，但但是它們們是私有有元素，只能由由同一包包體中其其他過程程和函數(shù)數(shù)使用。3815.2.1包的創(chuàng)

24、建建創(chuàng)建包規(guī)規(guī)范創(chuàng)建包體體39（1）創(chuàng)建包包規(guī)范語法CREATEORREPLACEPACKAGE package_nameIS|ASPRAGMA SERIALLY_RESUABLEtype_definition|variable_declaration|exception_declaration|cursor_declaration|procedure_ declaration|function_declarationENDpackage_name;40注意：元素聲明明的順序序可以是是任意的的，但必必須先聲聲明后使使用；所有元素素是可選選的；過程和函函數(shù)的聲聲明只包包括原型型，不包包括具體體實

25、現(xiàn)。41創(chuàng)建一個個軟件包包，包括括2個變量、2個過程和和1個異常。CREATEORREPLACEPACKAGE pkg_empASminsalNUMBER;maxsalNUMBER;e_beyondboundEXCEPTION;PROCEDUREupdate_sal(p_empno NUMBER,p_salNUMBER);PROCEDUREadd_employee(p_empno NUMBER,p_salNUMBER);ENDpkg_emp;42語法CREATEORREPLACEPACKAGE BODYpackage_nameIS|ASPRAGMA SERIALLY_RESUABLEtype_

26、definition|variable_declaration|exception_declaration|cursor_declaration|procedure_definition|function_definitionENDpackage_name;（2）創(chuàng)建包包體43注意：包體中函函數(shù)和過過程的原原型必須須與包規(guī)規(guī)范中的的聲明完完全一致致；只有在包包規(guī)范已已經(jīng)創(chuàng)建建的條件件下，才才可以創(chuàng)創(chuàng)建包體體；如果包規(guī)規(guī)范中不不包含任任何函數(shù)數(shù)或過程程，則可可以不創(chuàng)創(chuàng)建包體體。44CREATEORREPLACEPACKAGE BODYpkg_empASPROCEDUREupdate_sal(p_

27、empno NUMBER,p_salNUMBER)ASBEGINSELECTmin(sal), max(sal) INTOminsal,maxsalFROM emp;IFp_salBETWEEN minsalANDmaxsal THENUPDATEemp SETsal=p_salWHEREempno=p_empno;IFSQL%NOTFOUNDTHENRAISE_APPLICATION_ERROR(-20000,Theemployeedoesntexist);ENDIF;ELSERAISEe_beyondbound;ENDIF;EXCEPTIONWHEN e_beyondboundTHENDB

28、MS_OUTPUT.PUT_LINE(The salaryisbeyondbound! );ENDupdate_sal;45PROCEDUREadd_employee(p_empnoNUMBER,p_sal NUMBER)ASBEGINSELECTmin(sal), max(sal) INTOminsal,maxsalFROM emp;IFp_salBETWEEN minsalANDmaxsal THENINSERTINTOemp(empno,sal)VALUES(p_empno,p_sal);ELSERAISEe_beyondbound;ENDIF;EXCEPTIONWHEN e_beyon

29、dboundTHENDBMS_OUTPUT.PUT_LINE(The salaryisbeyondbound! );ENDadd_employee;ENDpkg_emp;4615.2.2包的調(diào)用用在包規(guī)范范聲明的的任何元元素是公公有的，在包外外都是可可見的包外：通通過package.element形式調(diào)用用；包內(nèi)：直直接通過過元素名名進行調(diào)調(diào)用。在包體中中定義而而沒有在在包頭中中聲明的的元素是是私有的的，只能能在包體體中引用用47調(diào)用軟件件包pkg_emp中的過程程update_sal，修改7844員工工資資為3000。調(diào)用add_employee添加一個個員工號號為1357，工資為為4000

30、的員工。BEGINpkg_emp.update_sal(7844,3000);pkg_emp.add_employee(1357,4000);END;4815.2.3包重載重載子程程序必須須同名不不同參，即名稱稱相同，參數(shù)不不同。參參數(shù)不同同體現(xiàn)為為參數(shù)的的個數(shù)、順序、類型等等不同。如果兩個個子程序序參數(shù)只只是名稱稱和模式式不同，則不能能重載。PROCEDUREoverloadme(parameter1 IN NUMBER);PROCEDUREoverloadme(parameter2 OUTNUMBER);不能根據(jù)據(jù)兩個函函數(shù)返回回類型不不同而對對它們進進行重載載。FUNCTIONoverl

31、oadme RETURNDATE;FUNCTIONoverloadme RETURNNUMBER;重載子程程序參數(shù)數(shù)必須在在類型系系列方面面有所不不同。PROCEDUREoverloadchar(parameterINCHAR);PROCEDUREoverloadchar(parameterINVARCHAR2);49在一個包包中重載載兩個過過程，分分別以部部門號和和部門名名稱為參參數(shù)，查查詢相應應部門員員工名、員工號號信息。50CREATEORREPLACEPACKAGE pkg_overloadASPROCEDUREshow_emp(p_deptnoNUMBER);PROCEDUREsho

32、w_emp(p_dnameVARCHAR2);ENDpkg_overload;51CREATEORREPLACEPACKAGE BODYpkg_overloadASPROCEDUREshow_emp(p_deptnoNUMBER)ASBEGINFORv_empIN(SELECT* FROMemp WHERE deptno=p_deptno)LOOPDBMS_OUTPUT.PUT_LINE(v_emp.empno|v_emp.ename);ENDLOOP;ENDshow_emp;52PROCEDUREshow_emp(p_dnameVARCHAR2)ASv_deptnoNUMBER;BEGINS

33、ELECTdeptno INTOv_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)

34、用用時執(zhí)行行，因此此也稱為為一次性性過程，它是一一個匿名名的PL/SQL塊，在包包體結構構的最后后，以BEGIN開始。示例在pkg_emp包中，在在包初始始化時給給minsal和maxsal兩個變量量賦值，在子程程序中直直接引用用這兩個個變量。54CREATEORREPLACEPACKAGE pkg_empASminsalNUMBER;maxsalNUMBER;e_beyondboundEXCEPTION;PROCEDUREupdate_sal(p_empno NUMBER,p_salNUMBER);PROCEDUREadd_employee(p_empno NUMBER,p_salNUMBE

35、R);ENDpkg_emp;55CREATEORREPLACEPACKAGE BODYpkg_empASPROCEDUREupdate_sal(p_empno NUMBER,p_salNUMBER)ASBEGINIFp_salBETWEEN minsalANDmaxsal THENUPDATEemp SETsal=p_salWHEREempno=p_empno;IFSQL%NOTFOUNDTHENRAISE_APPLICATION_ERROR(-20000,Theemployeedoesntexist);ENDIF;ELSERAISEe_beyondbound;ENDIF;EXCEPTIONW

36、HEN e_beyondboundTHENDBMS_OUTPUT.PUT_LINE(The salaryisbeyondbound!);ENDupdate_sal;56PROCEDUREadd_employee(p_empnoNUMBER,p_sal NUMBER)ASBEGINIFp_salBETWEEN minsalANDmaxsal THENINSERTINTOemp(empno,sal)VALUES(p_empno,p_sal);ELSERAISEe_beyondbound;ENDIF;EXCEPTIONWHEN e_beyondboundTHENDBMS_OUTPUT.PUT_LIN

37、E(The salaryisbeyondbound!);ENDadd_employee;BEGINSELECTmin(sal), max(sal) INTOminsal,maxsalFROM emp;ENDpkg_emp;5715.2.5包的管理理包的修改改CREATEORREPLACEPACKAGE package_name查看包及及其源代代碼查詢數(shù)據(jù)據(jù)字典視視圖USER_SOURCESELECTname,text FROMuser_sourceWHEREtype=PACKAGE;SELECTname,text FROMuser_sourceWHEREtype=PACKAGEBODY;重新編

38、譯譯包ALTERPACKAGECOMPILE（包規(guī)范范和包體體）ALTERPACKAGECOMPILE SPECIFICATION（包規(guī)范范）ALTERPACKAGECOMPILE BODY（包體）58ALTERPACKAGE pkg_empCOMPILE;ALTERPACKAGEpkg_empCOMPILE SPECIFICATION;ALTERPACKAGE pkg_empCOMPILEBODY;刪除包DROP PACKAGE（包規(guī)范范和包體體）DROP PACKAGEBODY（包體）DROP PACKAGEBODYpkg_emp;DROP PACKAGEpkg_emp;5915.3觸發(fā)器

39、觸發(fā)器概概述DML觸發(fā)器INSTEAD-OF觸發(fā)器系統(tǒng)觸發(fā)發(fā)器變異表觸觸發(fā)器觸發(fā)器的的管理6015.3.1觸發(fā)器概概述觸發(fā)器的的概念與與作用觸發(fā)器的的類型觸發(fā)器組組成61（1）觸發(fā)器器的概念念與作用用觸發(fā)器是是一種特特殊類型型的存儲儲過程，編譯后后存儲在在數(shù)據(jù)庫庫服務器器中。當特定事事件發(fā)生生時，由由系統(tǒng)自自動調(diào)用用執(zhí)行，而不能能由應用用程序顯顯式地調(diào)調(diào)用執(zhí)行行。觸發(fā)器不不接受任任何參數(shù)數(shù)。觸發(fā)器主主要用于于維護那那些通過過創(chuàng)建表表時的聲聲明約束束不可能能實現(xiàn)的的復雜的的完整性性約束，并對數(shù)數(shù)據(jù)庫中中特定事事件進行行監(jiān)控和和響應。62（2）觸發(fā)器器的類型型DML觸發(fā)器建立在基基本表上上的觸發(fā)發(fā)

40、器，響響應基本本表的INSERT，UPDATE，DELETE操作。INSTEAD OF觸發(fā)器建立在視視圖上的的觸發(fā)器器，響應應視圖上上的INSERT，UPDATE，DELETE操作。系統(tǒng)觸發(fā)發(fā)器建立在系系統(tǒng)或模模式上的的觸發(fā)器器，響應應系統(tǒng)事事件和DDL（CREATE，ALTER，DROP）操作。63（3）觸發(fā)器器組成觸發(fā)器由由觸發(fā)器器頭部和和觸發(fā)器器體兩個個部分組組成，主主要包括括：作用對象象：觸發(fā)發(fā)器作用用的對象象包括表表、視圖圖、數(shù)據(jù)據(jù)庫和模模式。觸發(fā)事件件：激發(fā)發(fā)觸發(fā)器器執(zhí)行的的事件。如DML、DDL、數(shù)據(jù)庫庫系統(tǒng)事事件等。觸發(fā)時間間：用于于指定觸觸發(fā)器在在觸發(fā)事事件完成成之前還還是之

41、后后執(zhí)行。如果指指定為AFTER，則表示示先執(zhí)行行觸發(fā)事事件，然然后再執(zhí)執(zhí)行觸發(fā)發(fā)器；如如果指定定為BEFORE，則表示示先執(zhí)行行觸發(fā)器器，然后后再執(zhí)行行觸發(fā)事事件。64觸發(fā)級別別：觸發(fā)發(fā)級別用用于指定定觸發(fā)器器響應觸觸發(fā)事件件的方式式。默認認為語句句級觸發(fā)發(fā)器，即即觸發(fā)事事件發(fā)生生后，觸觸發(fā)器只只執(zhí)行一一次。如如果指定定為FOREACHROW，即為行行級觸發(fā)發(fā)器，則則觸發(fā)事事件每作作用于一一個記錄錄，觸發(fā)發(fā)器就會會執(zhí)行一一次。觸發(fā)條件件：由WHEN子句指定定一個邏邏輯表達達式，當當觸發(fā)事事件發(fā)生生，而且且WHEN條件為TRUE時，觸發(fā)發(fā)器才會會執(zhí)行。觸發(fā)操作作：觸發(fā)發(fā)器執(zhí)行行時所進進行的操操

42、作。6515.3.2DML觸發(fā)器DML觸發(fā)器的的種類及及執(zhí)行順順序創(chuàng)建DML觸發(fā)器66（1）DML觸發(fā)器的的種類及及執(zhí)行順順序DML觸發(fā)器的的種類語句級前前觸發(fā)器器語句級后后觸發(fā)器器行級前觸觸發(fā)器行級后觸觸發(fā)器67DML觸發(fā)器的的執(zhí)行順順序如果存在在，則執(zhí)執(zhí)行語句句級前觸觸發(fā)器。對于受觸觸發(fā)事件件影響的的每一個個記錄：如果存在在，則執(zhí)執(zhí)行行級級前觸發(fā)發(fā)器；執(zhí)行當前前記錄的的DML操作（觸觸發(fā)事件件）；如果存在在，則執(zhí)執(zhí)行行級級后觸發(fā)發(fā)器。如果存在在，則執(zhí)執(zhí)行語句句級后觸觸發(fā)器。68（2）創(chuàng)建DML觸發(fā)器語法 CREATEOR REPLACE TRIGGERtrigger_nameBEFORE|

43、AFTERtriggering_eventOFcolumn_nameONtable_nameFOR EACHROWWHENtrigger_conditionDECLARE/*Declarative sectionishere*/BEGIN/*Exccutablesectionsihere*/EXCEPTION/*Exceptionsection is here*/ENDtrigger_name;Trigger_body69語句級觸觸發(fā)器在默認情情況下創(chuàng)創(chuàng)建的DML觸發(fā)器為為語句級級觸發(fā)器器，即觸觸發(fā)事件件發(fā)生后后，觸發(fā)發(fā)器只執(zhí)執(zhí)行一次次。70創(chuàng)建一個個觸發(fā)器器，禁止止在休息息日改變變雇員信信息

44、CREATEORREPLACETRIGGER trg_emp_weekendBEFOREINSERT OR UPDATEORDELETEONempBEGINIFTO_CHAR(SYSDATE, DY, nls_date_language=american) IN(SAT,SUN)THENraise_application_error(-20000, Cantoperateinweekend.);ENDIF;ENDtrg_emp_weekend; 71如果觸發(fā)發(fā)器響應應多個DML事件，而而且需要要根據(jù)事事件的不不同進行行不同的的操作，則可以以在觸發(fā)發(fā)器體中中使用3個條件謂謂詞。謂詞行為INSER

45、TING如果觸發(fā)語句是INSERT，則為TRUE；否則為FALSEUPDATING如果觸發(fā)語句是UPDATE，則為TRUE；否則為FALSEDELETING如果觸發(fā)語句是DELETE，則為TRUE；否則為FALSE72為emp表創(chuàng)建一一個觸發(fā)發(fā)器，當當執(zhí)行插插入操作作時，統(tǒng)統(tǒng)計操作作后員工工人數(shù)；當執(zhí)行行更新工工資操作作時，統(tǒng)統(tǒng)計更新新后員工工平均工工資；當當執(zhí)行刪刪除操作作時，統(tǒng)統(tǒng)計刪除除后各個個部門的的人數(shù)。73CREATEORREPLACETRIGGER trg_emp_dmlAFTERINSERTORUPDATE OR DELETEONempDECLAREv_count NUMBER;

46、v_salNUMBER(6,2);BEGINIFINSERTINGTHENSELECTcount(*)INTOv_count FROMemp;DBMS_OUTPUT.PUT_LINE(v_count);ELSIFUPDATINGTHENSELECTavg(sal)INTOv_salFROM emp;DBMS_OUTPUT.PUT_LINE(v_sal);ELSEFORv_dept IN (SELECTdeptno,count(*) numFROM empGROUPBYdeptno) LOOPDBMS_OUTPUT.PUT_LINE(v_dept.deptno| |v_dept.num);END

47、LOOP;ENDIF;ENDtrg_emp_dml;74行級觸發(fā)發(fā)器行級觸發(fā)發(fā)器是指指執(zhí)行DML操作時，每操作作一個記記錄，觸觸發(fā)器就就執(zhí)行一一次，一一個DML操作涉及及多少個個記錄，觸發(fā)器器就執(zhí)行行多少次次。在行級觸觸發(fā)器中中可以使使用WHEN條件，進進一步控控制觸發(fā)發(fā)器的執(zhí)執(zhí)行。在行級觸觸發(fā)器中中引入了了:old和:new兩個標識識符，來來訪問和和操作當當前被處處理記錄錄中的數(shù)數(shù)據(jù)。75標識符:old和:new作為triggering_table%ROWTYPE類型的兩兩個變量量在不同觸觸發(fā)事件件中，:old和:new的意義不不同觸發(fā)事件:old:newINSERT未定義，所有字段都為NU

48、LL當語句完成時，被插入的記錄UPDATE更新前原始記錄當語句完成時，更新后的記錄DELETE記錄被刪除前的原始值未定義，所有字段都為NULL76引用方式式：:old.field和:new.field（執(zhí)行部部分）old.field和new.field(WHEN條件中)注意事項項：是偽記錄錄，不能能作為整整個記錄錄進行賦賦值或引引用不能傳遞遞給帶triggering_table%ROWTYPE參數(shù)的過過程和函函數(shù)如果觸發(fā)發(fā)器是建建立在嵌嵌套表上上，;old和;new都執(zhí)行嵌嵌套表的的行，:parent指向父表表中的當當前行。77為emp表創(chuàng)建一一個觸發(fā)發(fā)器，當當插入新新員工時時顯示新新員工的的員工號號、員工工名；當當更新員員工工資資時，顯顯示修改改前后員員工