Oracle第章LSQL程序设计

1第15章PL/SQL程序设计2本章内容存储过程函数局部子程序包触发器3本章要求掌握PL/SQL功能模块的应用存储过程、函数、包、触发器的创建存储过程、函数、包、触发器的维护415.1

存储子程序存储过程函数局部子程序15.1.1存储过程存储过程的创建存储过程的调用存储过程的管理56（1）存储过程的创建基本语法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参数说明参数的模式

IN（默认参数模式）表示当过程被调用时，实参值被传递给形参；在过程内，形参起常量作用，只能读该参数，而不能修改该参数；当子程序调用结束返回调用环境时，实参没有被改变。IN模式参数可以是常量或表达式。OUT表示当过程被调用时，实参值被忽略；在过程内，形参起未初始化的PL/SQL变量的作用，初始值为NULL，可以进行读/写操作；当子程序调用结束后返回调用环境时，形参值被赋给实参。OUT模式参数只能是变量，不能是常量或表达式。INOUT表示当过程被调用时，实参值被传递给形参；在过程内，形参起已初始化的PL/SQL变量的作用，可读可写；当子程序调用结束返回调用环境时，形参值被赋给实参。INOUT模式参数只能是变量，不能是常量或表达式。

8参数的限制在声明形参时，不能定义形参的长度或精度、刻度，它们是作为参数传递机制的一部分被传递的，是由实参决定的。参数传递方式当子程序被调用时，实参与形参之间值的传递方式取决于参数的模式。IN参数为引用传递，即实参的指针被传递给形参；OUT，INOUT参数为值传递，即实参的值被复制给形参。参数默认值可以为参数设置默认值，这样存储过程被调用时如果没有给该参数传递值，则采用默认值。需要注意，有默认值的参数应该放在参数列表的最后。

9创建一个存储过程，以部门号为参数，查询该部门的平均工资，并输出该部门中比平均工资高的员工号、员工名。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通常，存储过程不需要返回值，如果需要返回一个值可以通过函数调用实现。但是，如果希望返回多个值，可以使用OUT或INOUT模式参数来实现。11创建建一一个个存存储储过过程程，，以以部部门门号号为为参参数数，，返返回回该该部部门门的的人人数数和和最最高高工工资资。。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）存存储储过过程程的的调调用用在SQL*PLUS中调用用EXECprocedure_name(parameter_list)EXECUTEshow_emp(10)在PL/SQL块中调调用BEGINprocedure_name(parameter_list);END；注意在PL/SQL程序中中，存存储过过程可可以作作为一一个独独立的的表达达式被被调用用。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查看看存存储储过过程程及及其其源源代代码码查询询数数据据字字典典视视图图USER_SOURCESELECTname,textFROMuser_sourceWHEREtype='PROCEDURE';重新新编编译译存存储储过过程程ALTERPROCEDURE……COMPILEALTERPROCEDUREshow_empCOMPILE;删除除存存储储过过程程DROPPROCEDUREDROPPROCEDUREshow_emp;1515.1.2函数数函数数的的创创建建函数数的的调调用用函数数的的管管理理16（1）函数的创建建基本语法为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注意在函数定义的的头部，参数数列表之后，，必须包含一一个RETURN语句来指明明函数返回回值的类型型，但不能能约束返回回值的长度度、精度、、刻度等。。如果使用用%TYPE，则则可可以以隐隐含含地地包包括括长长度度、、精精度度、、刻刻度度等等约约束束信信息息；；在函函数数体体的的定定义义中中，，必必须须至至少少包包含含一一个个RETURN语句句，，来来指指明明函函数数返返回回值值。。也也可可以以有有多多个个RETURN语句句，，但但最最终终只只有有一一个个RETURN语句句被被执执行行。。18创建建一一个个以以部部门门号号为为参参数数，，返返回回该该部部门门最最高高工工资资的的函函数数。。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如果需需要函函数返返回多多个值值，可可以使使用OUT或INOUT模式参参数。。20创建一一个函函数，，以部部门号号为参参数，，返回回部门门名、、部门门人数数及部部门平平均工工资。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）函数数的调调用在SQL语句中中调用用函数数在PL/SQL中调用用函数数注意函数只只能作作为表表达式式的一一部分分被调调用。。示例通过return_maxsal函数的的调用用，输输出各各个部部门的的最高高工资资；通通过ret_deptinfo函数调调用，，输出出各个个部门门名、、部门门人数数及平平均工工资。。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函数可可以在在SQL语句的的以下下部分分调用用：SELECT语句的的目标标列；；WHERE和HAVING子句；；CONNECTBY，STARTWITH，ORDERBY，GROUPBY子句；；INSERT语句的的VALUES子句中中；UPDATE语句的的SET子句中中。24如果要要在SQL中调用用函数数，那那么函函数必必须符符合下下列限限制和和要求求：在SELECT语句中中的函函数不不能修修改（（INSERT，UPDATE，DELETE）调用用函数数的SQL语句中中使用用的表表；函数在在一个个远程程或并并行操操作中中使用用时，，不能能读/写封装装变量量；函数必须须是一个个存储数数据库对对象（或或存储在在包中））；函数的参参数只能能使用IN模式；形式参数数类型必必须使用用数据库库数据类类型；返回的数数据类型型必须是是数据库库数据类类型；25（3）函数的的管理函数的修修改CREATEORREPLACEFUNCTIONfunction_name查看函数数及其源源代码查询数据据字典视视图USER_SOURCESELECTname,textFROMuser_sourceWHEREtype='FUNCTION';函数重编编译ALTERFUNCTION…COMPILEALTERFUNCTIONret_maxsalCOMPILE;删除函数数DROPFUNCTIONDROPFUNCTIONret_maxsal;2615.1.3局部子程程序局部子程程序嵌套在其其他PL/SQL块中的子子程序。。只能在其其定义的的块内部部被调用用，而不不能在其其父块外外被调用用。使用局部部子程序序时需要要注意：：局部子程程序只在在当前语语句块内内有效；；局部子程程序必须须在PL/SQL块声明部部分的最最后进行行定义；；局部子程程序必须须在使用用之前声声明，如如果是子子程序间间相互引引用，则则需要采采用预先先声明；；局部子程程序可以以重载。。27在一个块块内部定定义一个个函数和和一个过过程。函函数以部部门号为为参数返返回该部部门的平平均工资资；过程程以部门门号为参参数，输输出该部部门中工工资低于于部门平平均工资资的员工工的员工工号、员员工名。。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存储子程程序与局局部子程程序区别别在于：：存储子程程序己经经编译好好放在数数据库服服务器端端，可以以直接调调用，而而局部子子程序存存在于定定义它的的语句块块中，在在运行时时先进行行编译；；存储子程程序不能能重载，，而局部部子程序序可以进进行重载载；存储子程程序可以以被任意意的PL/SQL块调用，，而局部部子程序序只能在在定义它它的块中中被调用用。31在一个PL/SQL块中重载载两个过过程，一一个以员员工号为为参数，，输出该该员工信信息；另另一个以以员工名名为参数数，输出出员工信信息。利利用这两两个过程程分别查查询员工工号为7902，7934，以及员工工名为SMITH，FORD的员工信息息。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包包概述包的创建包的调用包重载载包的初初始化化包的管管理36包概述述包是包包含一一个或或多个个子程程序单单元（（过程程、函函数等等）的的容器器。包是一一种全全局结结构。。包类型型数据库库内置置包用户创创建的的包包构成成包规范范包体两两37包规范范声明明了软软件包包中所所有内内容，，如过过程、、函数数、游游标、、类型型、异异常和和变量量等，，其中中过程程和函函数只只包括括原型型信息息，不不包含含任何何子程程序代代码。。包体中中包含含了在在包头头中的的过程程和函函数的的实现现代码码。包包体中中还可可以包包括在在规范范中没没有声声明的的变量量、游游标、、类型型、异异常、、过程程和函函数，，但是是它们们是私私有元元素，，只能能由同同一包包体中中其他他过程程和函函数使使用。。3815.2.1包的创创建创建包包规范范创建包包体39（1）创建建包规规范语法CREATEORREPLACEPACKAGEpackage_nameIS|AS[PRAGMASERIALLY_RESUABLE]type_definition|variable_declaration|exception_declaration|cursor_declaration|procedure_declaration|function_declarationEND[package_name];40注意：元素声明明的顺序序可以是是任意的的，但必必须先声声明后使使用；所有元素素是可选选的；过程和函函数的声声明只包包括原型型，不包包括具体体实现。。41创建一个个软件包包，包括括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）创建包体43注意：包体中函数和和过程的原型型必须与包规规范中的声明明完全一致；；只有在包规范范已经创建的的条件下，才才可以创建包包体；如果包规范中中不包含任何何函数或过程程，则可以不不创建包体。。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包的调用在包规范声明明的任何元素素是公有的，，在包外都是是可见的包外：通过package.element形式调用；包内：直接通通过元素名进进行调用。在包体中定义义而没有在包包头中声明的的元素是私有有的，只能在在包体中引用用47调用软件包pkg_emp中的过程update_sal，修改7844员工工资为3000。调用add_employee添加一个员工工号为1357，工资为4000的员工。BEGINpkg_emp.update_sal(7844,3000);pkg_emp.add_employee(1357,4000);END;4815.2.3包重载重载子程序必必须同名不同同参，即名称称相同，参数数不同。参数数不同体现为为参数的个数数、顺序、类类型等不同。。如果两个子程程序参数只是是名称和模式式不同，则不不能重载。PROCEDUREoverloadme(parameter1INNUMBER);PROCEDUREoverloadme(parameter2OUTNUMBER);不能根据两个个函数返回类类型不同而对对它们进行重重载。FUNCTIONoverloadmeRETURNDATE;FUNCTIONoverloadmeRETURNNUMBER;重载子程序参参数必须在类类型系列方面面有所不同。。PROCEDUREoverloadchar(parameterINCHAR);PROCEDUREoverloadchar(parameterINVARCHAR2);49在一个包中重重载两个过程程，分别以部部门号和部门门名称为参数数，查询相应应部门员工名名、员工号信信息。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包的初始始化包在第一一次被调调用时从从磁盘读读取到共共享池，，并在整整个会话话的持续续期间保保持。在在此过程程中，可可以自动动执行一一个初始始化过程程，对软软件包进进行实例例化。包的初始始化过程程只在包包第一次次被调用用时执行行，因此此也称为为一次性性过程，，它是一一个匿名名的PL/SQL块，在包包体结构构的最后后，以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查看包及其其源代码查询数据字字典视图USER_SOURCESELECTname,textFROMuser_sourceWHEREtype='PACKAGE';SELECTname,textFROMuser_sourceWHEREtype='PACKAGEBODY';重新编译包包ALTERPACKAGE…COMPILE（包规范和和包体）ALTERPACKAGE…COMPILESPECIFICATION（包规范）ALTERPACKAGE…COMPILEBODY（包体）58ALTERPACKAGEpkg_empCOMPILE;ALTERPACKAGEpkg_empCOMPILESPECIFICATION;ALTERPACKAGEpkg_empCOMPILEBODY;删除包DROPPACKAGE（包规范和包包体）DROPPACKAGEBODY（包体）DROPPACKAGEBODYpkg_emp;DROPPACKAGEpkg_emp;5915.3触发器触发器概述DML触发器INSTEAD-OF触发器系统触发器变异表触发器器触发器的管理理6015.3.1触发器概述触发器的概念念与作用触发器的类型型触发器组成61（1）触发器的概概念与作用触发器是一种种特殊类型的的存储过程，，编译后存储储在数据库服服务器中。当特定事件发发生时，由系系统自动调用用执行，而不不能由应用程程序显式地调调用执行。触发器不接受受任何参数。。触发器主要用用于维护那些些通过创建表表时的声明约约束不可能实实现的复杂的的完整性约束束，并对数据据库中特定事事件进行监控控和响应。62（2）触发器的类类型DML触发器建立在基本表表上的触发器器，响应基本本表的INSERT，UPDATE，DELETE操作。INSTEADOF触发器建立在视图上上的触发器，，响应视图上上的INSERT，UPDATE，DELETE操作。系统触发器建立在系统或或模式上的触触发器，响应应系统事件和和DDL（CREATE，ALTER，DROP）操作。63（3）触发器组成成触发器由触发发器头部和触触发器体两个个部分组成，，主要包括：：作用对象：触触发器作用的的对象包括表表、视图、数数据库和模式式。触发事件：激激发触发器执执行的事件。。如DML、DDL、数据库系统统事件等。触发时间：用用于指定触发发器在触发事事件完成之前前还是之后执执行。如果指指定为AFTER，则表示先执执行触发事件件，然后再执执行触发器；；如果指定为为BEFORE，则表示先执执行触发器，，然后再执行行触发事件。。64触发级别：触触发级别用于于指定触发器器响应触发事事件的方式。。默认为语句句级触发器，，即触发事件件发生后，触触发器只执行行一次。如果果指定为FOREACHROW，即为行级触触发器，则触触发事件每作作用于一个记记录，触发器器就会执行一一次。触发条件：由由WHEN子句指定一个个逻辑表达式式，当触发事事件发生，而而且WHEN条件为TRUE时，触发器才才会执行。触发操作：触触发器执行时时所进行的操操作。6515.3.2DML触发器DML触发器的种类类及执行顺序序创建DML触发器66（1）DML触发器的种类类及执行顺序序DML触发器的种类类语句级前触发发器语句级后触发发器行级前触发器器行级后触发器器67DML触发器的执行行顺序如果存在，则则执行语句级级前触发器。。对于受触发事事件影响的每每一个记录：：如果存在，则则执行行级前前触发器；执行行当当前前记记录录的的DML操作作（（触触发发事事件件））；；如果果存存在在，，则则执执行行行行级级后后触触发发器器。。如果果存存在在，，则则执执行行语语句句级级后后触触发发器器。。68（2）创创建建DML触发发器器语法法CREATE[ORREPLACE]TRIGGERtrigger_nameBEFORE|AFTERtriggering_event[OFcolumn_name]ONtable_name][FOREACHROW][WHENtrigger_condition]DECLARE/*Declarativesectionishere*/BEGIN/*Exccutablesectionsihere*/EXCEPTION/*Exceptionsectionishere*/END[trigger_name];Trigger_body69语句级级触发发器在默认认情况况下创创建的的DML触发器器为语语句级级触发发器，，即触触发事事件发发生后后，触触发器器只执执行一一次。。70创建一一个触触发器器，禁禁止在在休息息日改改变雇雇员信信息CREATEORREPLACETRIGGERtrg_emp_weekendBEFOREINSERTORUPDATEORDELETEONempBEGINIFTO_CHAR(SYSDATE,'DY','nls_date_language=american')IN('SAT','SUN')THENraise_application_error(-20000,'Can''toperateinweekend.');ENDIF;ENDtrg_emp_weekend;71如果触发器器响应多个个DML事件，而且且需要根据据事件的不不同进行不不同的操作作，则可以以在触发器器体中使用用3个条件谓词词。谓词行为INSERTING如果触发语句是INSERT，则为TRUE；否则为FALSEUPDATING如果触发语句是UPDATE，则为TRUE；否则为FALSEDELETING如果触发语句是DELETE，则为TRUE；否则为FALSE72为emp表创建一个个触发器，，当执行插插入操作时时，统计操操作后员工工人数；当当执行更新新工资操作作时，统计计更新后员员工平均工工资；当执执行删除操操作时，统统计删除后后各个部门门的人数。。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行级触发器器行级触发器器是指执行行DML操作时，每每操作一个个记录，触触发器就执执行一次，，一个DML操作涉及多多少个记录录，触发器器就执行多多少次。在行级触发发器中可以以使用WHEN条件，进一一步控制触触发器的执执行。在行级触触发器中中引入了了:old和:new两个标识识符，来来访问和和操作当当前被处处理记录录中的数数据。75标识符:old和:new作为triggering_table%ROWTYPE类型的两两个变量量在不同触触发事件件中，:old和:new的意义不不同触发事件:old:newINSERT未定义，所有字段都为NULL当语句完成时，被插入的记录UPDATE更新前原始记录当语句完成时，更新后的记录DELETE记录被删除前的原始值未定义，所有字段都为NULL76引用方式式：:old.field和:new.field（执行部部分）old.field和new.field(WHEN条件中)注意事项项：是伪记录录，不能能作为整整个记录录进行赋赋值或引引用不能传递递给带triggering_table%ROWTYPE参数的过过程和函函数如果触发发器是建建立在嵌嵌套表上上，;old和;new都执行嵌嵌套表的的行，:parent指向父表表中的当当前行。。77为emp表创建一一个触发发器，当当插入新新员工时时显示新新员工的的员工号号、员工工名；当当更新员员工工资资时，显显示修改改前后员员工工资资；当删删除员工工时，显显示被删删除的员员工号、、员工名名。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在行级触触发器中中，可以以使用WHEN子句进一一步控制制触发器器的执行行。例如，修修改员工工工资时时，保证证修改后后的工资资高于修修改前的的工资。。CREATEORREPLACETRIGGERtrg_emp_update_rowBEFOREUPDATEOFsalONempFOREACHROWWHEN(new.sal<=old.sal)BEGINRAISE_APPLICATION_ERROR(-20001,'Thesalaryislower!');ENDtrg_emp_update_row;8015.3.3INSTEADOF触发器特点只能定义义在视图图上Instead-of触发器是是行级触触发器Instead-of触发器由由DML操作激发发，而DML操作本身身并不执执行作用修改一个个本来不不可以修修改的视视图修改视图图中某嵌嵌套表列列的列81如果视图图中包含含下列任任何一项项，则该该视图不不可修改改集合操作作符（UNION，UNIONALL，MINUS，INTERSECT）；聚集函数数（SUM，AVG等）；GROUPBY，CONNECTBY或STARTWITH子句；DISTINCT操作符；；涉及多个个表的连连接操作作。82创建INSTEADOF触发器的的基本语语法CREATE[ORREPLACE]TRIGGERtrigger_nameINSTEADOFtriggering_event[OFcolumn_name]ONview_nameFOREACHROW[WHENtrigger_condition]DECLARE/*Declarativesectionishere*/BEGIN/*Exccutablesectionsihere*/EXCEPTION/*Exceptionsectionishere*/END[trigger_name];83创建一个包括括员工及其所所在部门信息息的视图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系统触触发器器触发器器事件件创建系系统触触发器器86（1）触发发事件件DDL事件CREATE，ALTER，DROP，RENAME，GRANT，REVOKE，AUDIT，NOAUDIT，COMMENT，TRUNCATE，ANALYZE，ASSOCIATESTATISTICS，DISASSOCIATESTATISTICS等。。触发发时时间间可可以以是是BEFORE，也也可可以以是是AFTER。数据据库库事事件件STARTUP，SHUTDOWN，SERVERERROR，LOGON，LOGOFF等。触发时间间由具体体事件决决定，87事件允许计时描述STARTUPAFTER当实例开始时激发SHUTDOWNBEFORE当实例关闭时激发SERVERERRORAFTER只要错误发生就激发LOGONAFTER在一个用户成功连接数据库时触发LOGOFFBEFORE在用户注销时开始激发数据库事事件的触触发时间间88语法CREATE[ORREPLACE]TRIGGERtrigger_nameBEFORE|AFTERddl_event_list|database_event_listONDATABASE|SCHEMA[WHENtrigger_condition]DECLARE/*Declarativesectionishere*/BEGIN/*Executablesectionishere*/EXCEPTION/*Exceptionsectionishere*/END[trigger_name];（2）创建系系统触发发器89说明基于数据据库（DATABASE）的触发发器，只只要系统统中该触触发事件件发生，，且满足足触发条条件，则则触发器器执行；；对于基于于模式（（SCHEMAN）的触发发器，只只有当特特定模式式中的触触发事件件发生时时，触发发器才执执行。注意STARTUP和SHUTDOWN事件只能能激发基基于数据据库的触触发器。。90将每个用用户的登登录信息息写入temp_table表中。CREATEORREPLACETRIGGERlog_user_connectionAFTERLOGONONDATABASEBEGININSERTINTOscott.temp_tableVALUES(user,sysdate);ENDlog_user_connection;91在系统触触发器内内部可以以使用一一些事件件属性函函数以获获得触发发事件的的信息。。由于系统统没有为为这些事事件属性性函数指指定同义义词，因因此在调调用时必必须在其其前加上上“SYS.”前缀。示例当数据库库中执行行CREATE操作时，，将创建建的对象象信息记记录到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变异表表触发发器概念变异表表是指指激发发触发发器的的DML语句所所操作作的表表，即即触发发器为为之定定义的的表，，或者者由于于DELETECASCADE操作而而需要要修改改的表表，即即当前前表的的子表表。约束表表是指指由于于引用用完整整性约约束而而需要要从中中读取取或修修改数数据的的表，，即当当前表表的父父表。。94当对一一个表表创建建行级级触发发器，，或创创建由由DELETECASCADE操作而而激发发的语语句级级触发发器时时，有有下列列两条条限制制：不能读读取或或修改改任何何触发发