过程函数包和触发器精选文档.ppt

过程函数包和触发器本讲稿第一页，共八十五页理解子程序（过程、函数）及包的概念理解子程序（过程、函数）及包的概念 掌握掌握如何创建、执行和删除过程如何创建、执行和删除过程、函数及包的方法、函数及包的方法 了解形参和实参的区别以及不同参数模式的特征了解形参和实参的区别以及不同参数模式的特征灵活使用过程、函数及包书写灵活使用过程、函数及包书写PL/SQLPL/SQL程序程序掌握创建和测试掌握创建和测试DMLDML触发器、触发器、instead-ofinstead-of数据库触发数据库触发器以及系统触发器器以及系统触发器了解数据库触发器的使用时机和限制了解数据库触发器的使用时机和限制本章要点本章要点本讲稿第二页，共八十五页掌握存储子程序与数据字典的交互方式及如何从掌握存储子程序与数据字典的交互方式及如何从SQLSQL语句中调用存储子程序语句中调用存储子程序了解存储子程序和局部子程序的区别了解存储子程序和局部子程序的区别掌握存储子程序的特性。掌握存储子程序的特性。本章子要点本章子要点本讲稿第三页，共八十五页子程序位置子程序位置l子程序和包可以存储在数据字典中（子程序是用命令子程序和包可以存储在数据字典中（子程序是用命令CREATE OR REPLACE创建）创建）l可以从其他可以从其他PL/SQL语句块中调用已创建的子程序。语句块中调用已创建的子程序。l子程序可以在语句块的声明段定义，以这种方式子程序可以在语句块的声明段定义，以这种方式定义的子程序叫做定义的子程序叫做局部子程序局部子程序。l包则必须存储在数据字典中，而不能是局部的。包则必须存储在数据字典中，而不能是局部的。本讲稿第四页，共八十五页存储子程序和数据字典存储子程序和数据字典 l当使用命令当使用命令CREATE OR REPLACE创建创建子程序时，该子程序就被存储在数据字子程序时，该子程序就被存储在数据字典中。典中。l子程序的信息可以通过各种数据字典视子程序的信息可以通过各种数据字典视图来访问。图来访问。本讲稿第五页，共八十五页局部子程序局部子程序l局部子程序局部子程序:是一个在是一个在PL/SQL语句块的声语句块的声明段中声明的子程序。明段中声明的子程序。l子函数子函数只在其声明的语句块中可见，其作只在其声明的语句块中可见，其作用域从声明点开始到该语句块结束为止。用域从声明点开始到该语句块结束为止。其他语句块不能调用该函数，因为该函数其他语句块不能调用该函数，因为该函数对其他语句块来说是不可见的。对其他语句块来说是不可见的。本讲稿第六页，共八十五页局部子程序（续）局部子程序（续）l局部子程序也可以声明为存储子程序声明局部子程序也可以声明为存储子程序声明段的一部分段的一部分l局部子程序的位置：任何局部子程序都必局部子程序的位置：任何局部子程序都必须在声明段的最后进行声明须在声明段的最后进行声明l预先声明预先声明：局部：局部PL/SQL子程序的名称是子程序的名称是标识符，必须在被引用前声明。标识符，必须在被引用前声明。l局部子程序也可以重载局部子程序也可以重载本讲稿第七页，共八十五页存储子程序和局部子程序的比较存储子程序和局部子程序的比较存储子程序局部子程序该类子程序以编译后生成的伪代码形式p-code存储在数据库中。当调用该类子程序时，不需进行编译即可运行局部子程序被编译为包含它的语句块的一部分。如果其所在语句块是匿名块并需要多次运行时，则该子程序就必须每次进行编译存储子程序可以从对该子程序具有EXECUTE特权的用户所提交的任何语句块中调用局部子程序只能从包含子程序的语句块中调用由于存储子程序与调用块的相互隔离，调用块具有代码少，易于理解的特点。除此之外，子程序和调用块还可以分开维护局部子程序和调用块同处于一个块内，所以容易引起混淆。如果修改了调用块的话，则该块调用的子程序作为所属块的一部分也要重新编译可以使用DBMS_SHARED_POOL.KEEP包过程来把编译后p-code代码存储在共享池中。这种方式可以改善程序性能局部子程序自身不能存储在共享池中独立的存储子程序不能进行重载，但同一包内的子程序可以重载同一块中的局部子程序可以重载本讲稿第八页，共八十五页存储子程序和包的注意事项存储子程序和包的注意事项优势：优势：l可以由多个数据库用户共享可以由多个数据库用户共享隐含影响：隐含影响：l存储子程序间的依赖性存储子程序间的依赖性l包状态的处理方法包状态的处理方法l运行存储子程序运行存储子程序l包所需要的特权包所需要的特权本讲稿第九页，共八十五页子程序的依赖性子程序的依赖性 l依赖：数据字典记录了存储过程或函数所引用的所有依赖：数据字典记录了存储过程或函数所引用的所有Oracle对象。该过程或函数就依赖于这些存储的对对象。该过程或函数就依赖于这些存储的对象。象。l自动重编译自动重编译:如果一个依赖对象失效，如果一个依赖对象失效，PL/SQL引擎将在引擎将在该对象再次被调用时对其重新进行编译。该对象再次被调用时对其重新进行编译。l包的依赖性：包体的变化不会导致修改包头。因此，其包的依赖性：包体的变化不会导致修改包头。因此，其他依赖于该包头的对象也不需要进行重新编译。如果该他依赖于该包头的对象也不需要进行重新编译。如果该包头有变化，则会使包体自动失效，这是因为该包头有变化，则会使包体自动失效，这是因为该包体包体依赖于包头依赖于包头。l如何确定无效如何确定无效:通过数据字典在不断地跟踪对象间依赖通过数据字典在不断地跟踪对象间依赖性性本讲稿第十页，共八十五页包运行时状态包运行时状态l当第一次实例化一个包时，将从磁盘中读入该包的当第一次实例化一个包时，将从磁盘中读入该包的伪代码并将其放入系统全局工作区伪代码并将其放入系统全局工作区SGA的共享池中。的共享池中。l包的运行时状态，即打包的变量和游标，则存放在用包的运行时状态，即打包的变量和游标，则存放在用户全局区（户全局区（UGA）的会话存储区中。这就保证了每个）的会话存储区中。这就保证了每个会话都将有其运行时状态的副本。会话都将有其运行时状态的副本。l可串行复用包和非串行复用包：可串行复用包和非串行复用包：PL/SQL 2.3版及更高版版及更高版本允许程序员对包做可串行复用标志。本允许程序员对包做可串行复用标志。本讲稿第十一页，共八十五页包运行时状态包运行时状态可串行复用包非串行复用包运行时状态保存在SGA中，每次数据库调用后都将该运行时状态释放运行时状态保存在UGA中，其生存期与数据库会话相同所用的最大内存与同时使用该包的用户数成正比所用的最大内存与当前登录的用户数成正比本讲稿第十二页，共八十五页特权和存储子程序特权和存储子程序 l存储子程序和包都是数据库字典中的对存储子程序和包都是数据库字典中的对象，因而，它们属于特殊的数据库用户象，因而，它们属于特殊的数据库用户或模式。如果用户被授予了正确的特权，或模式。如果用户被授予了正确的特权，则它们就可以访问这些对象。则它们就可以访问这些对象。l当创建一个存储对象时，考虑到子程序当创建一个存储对象时，考虑到子程序内部的访问可用性，特权和角色也开始内部的访问可用性，特权和角色也开始起作用。起作用。本讲稿第十三页，共八十五页EXECUTE特权特权 l为了能够对表进行访问，必须使用为了能够对表进行访问，必须使用SELECT，INSERT，UPDATE和和DELETE对象特权。对象特权。GRANT语句把这些语句把这些特权赋予数据库用户或角色。对于存储特权赋予数据库用户或角色。对于存储子程序和包来说，相关的特权是子程序和包来说，相关的特权是EXECUTE。本讲稿第十四页，共八十五页本讲稿第十五页，共八十五页本讲稿第十六页，共八十五页存储子程序和角色存储子程序和角色 l子程序使用显式地（而不是通过角色）子程序使用显式地（而不是通过角色）授权其所有者的特权而执行。授权其所有者的特权而执行。l为了使通过角色授权的特权作用在存储为了使通过角色授权的特权作用在存储子程序和触发器内被使用，每次运行过子程序和触发器内被使用，每次运行过程时，必须对该特权进行检查。程时，必须对该特权进行检查。本讲稿第十七页，共八十五页调用者的权限与定义者的权限调用者的权限与定义者的权限 lOracle提供了不同的外部引用解决方案。在调用者提供了不同的外部引用解决方案。在调用者的权限子程序中，外部引用是通过调用者而不是的权限子程序中，外部引用是通过调用者而不是所有者的特权设置而执行的。调用者的权限程序所有者的特权设置而执行的。调用者的权限程序是通过使用是通过使用AUTHID子句而创建，该语句只适用于独子句而创建，该语句只适用于独立子程序、包说明和对象类型说明。立子程序、包说明和对象类型说明。l在包内部或对象类型中的独立子程序必须都是调用者在包内部或对象类型中的独立子程序必须都是调用者子程序或都是定义者子程序，而不能混合。子程序或都是定义者子程序，而不能混合。本讲稿第十八页，共八十五页AUTHIDAUTHID的语法如下：的语法如下：的语法如下：的语法如下：CREATE OR REPLACE FUNCTION function_name parameter_list RETURN return_type AUTHID CURRENT_USER|DEFINER IS|AS function_body;CREATE OR REPLACE PROCEDURE procedure_name parameter_list AUTHID CURRENT_USER|DEFINER IS|AS function_body;CREATE OR REPLACE PACKAGE package_spec_name AUTHID CURRENT_USER|DEFINER IS|AS package_spec;CREATE OR REPLACE TYPE type_name AUTHID CURRENT_USER|DEFINER IS|AS OBJECT type_spec;本讲稿第十九页，共八十五页默认参数默认参数l从过程化语句中调用一个函数时，如果该函从过程化语句中调用一个函数时，如果该函数有形参的话，可以使用其缺省值。然而，数有形参的话，可以使用其缺省值。然而，如果从如果从SQL语句调用函数时，必须指定所有语句调用函数时，必须指定所有参数。参数。l必须使用位置对应法（必须使用位置对应法（Positinal Notation）,而不能使用名称对应法（而不能使用名称对应法（Name Notation）。）。本讲稿第二十页，共八十五页从从Oracle中从中从SQL语句调用函数语句调用函数l从从Oracle起，从起，从DML语句中调用的函数语句中调用的函数既不可以从正在被该既不可以从正在被该DML语句修改的数语句修改的数据库表读取数据，也不能对其进行修改，据库表读取数据，也不能对其进行修改，但该函数可以更新其他表。但该函数可以更新其他表。本讲稿第二十一页，共八十五页包的辅助功能包的辅助功能 l共享池锁定共享池锁定l包体长度的限制包体长度的限制 l优化参数优化参数本讲稿第二十二页，共八十五页共享池锁定共享池锁定 lDBMS_SHARED_POOL包允许程序员把一个对象包允许程序员把一个对象锁定在共享池中。锁定在共享池中。l当该对象被锁定后，除非由程序申请对其清除，当该对象被锁定后，除非由程序申请对其清除，否则无论共享池有多满，也不管是否有程序访问否则无论共享池有多满，也不管是否有程序访问该对象，该对象将常驻在共享池中。该对象，该对象将常驻在共享池中。l这种处理方法有利于提高程序的运行效率，因为从系这种处理方法有利于提高程序的运行效率，因为从系统的磁盘重新装入对象要进行大量读写操作。锁定对统的磁盘重新装入对象要进行大量读写操作。锁定对象还有助于最大限度地减少共享池的碎片。象还有助于最大限度地减少共享池的碎片。本讲稿第二十三页，共八十五页包体长度的限制包体长度的限制 l编译器对包体长度的限制如下编译器对包体长度的限制如下：lDiana树中的节点数。在树中的节点数。在Oracle8i之前的版本中，之前的版本中，Diana节点节点的最大数目是的最大数目是32K，Oracle8i以上版本将包体和类型体的限制以上版本将包体和类型体的限制扩充到了扩充到了64兆字节的容量。兆字节的容量。l编译器生成的临时中间变量的容量最多编译器生成的临时中间变量的容量最多21K字节。字节。l入口点的数量。一个包体最多可以有入口点的数量。一个包体最多可以有32K个入口点，入口点可个入口点，入口点可以是过程或函数。以是过程或函数。l字符串的数量。字符串的数量。PL/SQL对字符串的限制单位是对字符串的限制单位是232。本讲稿第二十四页，共八十五页优化参数优化参数 l使用使用DETERMINISTIC和和PARALLEL_ENABLE关键字，关键字，PL/SQL编译优化器将会对调用编译优化器将会对调用PL/SQL函数进行优化。函数进行优化。l该关键字要放在函数的返回类型和该关键字要放在函数的返回类型和IS或或AS子句之间子句之间。CREATE OR REPLACE FUNCTION function_nameCREATE OR REPLACE FUNCTION function_name parameter_list parameter_listRETURN return_typeRETURN return_typeDETERMINISTICDETERMINISTICPARALLEL_ENABLEPARALLEL_ENABLEIS|ASIS|ASfunction_body;function_body;本讲稿第二十五页，共八十五页小结小结1.通过访问各种数据库字典视图来查询子程序的信息。通过访问各种数据库字典视图来查询子程序的信息。user_objects视视图包含了当前用户拥有的所有对象的信息。图包含了当前用户拥有的所有对象的信息。user_source视图包含视图包含对象的源程序代码。对象的源程序代码。user_errors视图则包含编译错误的信息。视图则包含编译错误的信息。2.预先声明可以解决函数之间相互调用的问题。预先声明可以解决函数之间相互调用的问题。3.子程序引用其他数据库对象时，该子程序依赖于那个对象。如果修改子程序引用其他数据库对象时，该子程序依赖于那个对象。如果修改了被引用对象，那么依赖对象的状态将更改为无效，指出需要重新编了被引用对象，那么依赖对象的状态将更改为无效，指出需要重新编译。译。4.在在PL/SQL中使用的函数可以使用额外的限制，这被称为纯度等中使用的函数可以使用额外的限制，这被称为纯度等级。级。5.共享池是在子程序运行时包含已编译子程序伪代码及其他代码的共享池是在子程序运行时包含已编译子程序伪代码及其他代码的SGA的一部分。的一部分。KEEP过程用来在共享池中锁定对象。过程用来在共享池中锁定对象。UNKEEP过程实现过程实现从共享池中删除锁定对象。从共享池中删除锁定对象。SIZES过程用来把共享池的内容输出到屏过程用来把共享池的内容输出到屏幕。幕。6.PL/SQL编译优化器使用编译优化器使用DETERMINISTIC和和PARALLEL_ENABLE关键字对调用关键字对调用PL/SQL函数进行优化。函数进行优化。本讲稿第二十六页，共八十五页 数据库开发技术数据库开发技术第第14章（下）章（下）数据库触发器数据库触发器福建工程学院福建工程学院本讲稿第二十七页，共八十五页 本章要点本章要点l理解数据库触发器、变异表和约束表的概念及数据库触发器的语法l掌握如何在SQL*PLUS中创建和测试DML触发器、instead-of数据库触发器以及系统触发器l了解数据库触发器的使用时机和限制。本讲稿第二十八页，共八十五页触发器的概念触发器的概念作为数据库对象的触发器是一个与表相关作为数据库对象的触发器是一个与表相关联的被存储的联的被存储的PL/SQL程序。每当一个特定程序。每当一个特定的数据操作语句在指定的表上发生时，就的数据操作语句在指定的表上发生时，就会引发触发器的执行。会引发触发器的执行。与存储子程序的调用方式不同，触发与存储子程序的调用方式不同，触发器的执行是采用触发机制器的执行是采用触发机制本讲稿第二十九页，共八十五页触发器的定义和使用触发器的定义和使用触发器是当特定事件出现时自动执行的存储过程触发器是当特定事件出现时自动执行的存储过程特定事件可以是执行更新的特定事件可以是执行更新的DML语句和语句和DDL语句语句触发器不能被显式调用触发器不能被显式调用触发器的功能：触发器的功能：自动生成数据自动生成数据自定义复杂的安全权限自定义复杂的安全权限提供审计和日志记录提供审计和日志记录启用复杂的业务逻辑启用复杂的业务逻辑本讲稿第三十页，共八十五页触发器的类型触发器的类型 触发器主要有三种类型：触发器主要有三种类型：lDMLlINSTEAD-OF触发器触发器l系统触发器。系统触发器。触发器的应用，主要用于：触发器的应用，主要用于：l维护那些通过创建表时的声明约束无法实现的复杂的完整性约束。维护那些通过创建表时的声明约束无法实现的复杂的完整性约束。l通过记录已进行的改变以及是谁进行了该项改变来检查一个通过记录已进行的改变以及是谁进行了该项改变来检查一个表中的信息。表中的信息。l当一个表发生改变时，自动向其他程序发送需要采取行动的信号。当一个表发生改变时，自动向其他程序发送需要采取行动的信号。l在一个发布在一个发布-预订环境中发布有关各种事件的信息。预订环境中发布有关各种事件的信息。本讲稿第三十一页，共八十五页DML触发器触发器 lDML触发器可以由触发器可以由DML语句激发语句激发l由由DML语句的类型决定语句的类型决定DML触发器的类触发器的类型。型。lDML触发器可以在触发器可以在DML语句操作之前或语句操作之前或之后激发。之后激发。lDML触发器也可以在行或语句操作上激触发器也可以在行或语句操作上激发发。本讲稿第三十二页，共八十五页INSTEAD-OF触发器触发器 lINSTEAD-OF 触发器是行级别的触发器是行级别的,执行执行时，激发它的时，激发它的DML语句不执行。语句不执行。l只能定义在视图上。只能定义在视图上。lOracle8及更高版本才提供。及更高版本才提供。本讲稿第三十三页，共八十五页系统触发器系统触发器 l系统触发器在发生诸如数据库启动或关系统触发器在发生诸如数据库启动或关闭等系统事件时激发，而不是在执行闭等系统事件时激发，而不是在执行DML语句时激发。语句时激发。l系统触发器也可以在系统触发器也可以在DDL操作时，如表操作时，如表的创建时被激发。的创建时被激发。本讲稿第三十四页，共八十五页创建触发器创建触发器 CREATE OR REPLACE TRIGGER trigger_nameBEFORE|AFTER|INSTEAD OF triggering_eventreferencing_clauseWHEN trigger_conditionFOR EACH ROWtrigger_body;本讲稿第三十五页，共八十五页Create or replace trigger emp_t Before insert or update or delete On empBegin If to_char(sysdate,dy)=星期日星期日 thenRaise_application_error(-20000,today is holiday!);End if;-抛出一个异常，但不做异常处理。抛出一个异常，但不做异常处理。End;当执行一条删除语句在星期六对当执行一条删除语句在星期六对emp表进行删除操作时：表进行删除操作时：Delete from emp where eno=eo1;那么将会删那么将会删除成功，如果是在星期日执行这条语句，则触发器的异除成功，如果是在星期日执行这条语句，则触发器的异常导致本应继续执行的触发语句不再执行，删除未成功。常导致本应继续执行的触发语句不再执行，删除未成功。创建触发器创建触发器 本讲稿第三十六页，共八十五页AFTER 子句子句lAFTER：表示触发语句正常执行后，触：表示触发语句正常执行后，触发器被触发。发器被触发。注意：虽然触发器是在触发语句之后触发，注意：虽然触发器是在触发语句之后触发，但是如果触发器代码出现异常时，在此但是如果触发器代码出现异常时，在此之前执行的触发语句无效。之前执行的触发语句无效。例：例：本讲稿第三十七页，共八十五页Create or replace trigger emp_testAfter delete On empBegin Raise_application_error(-20000,test message!);End;当执行一条语句对当执行一条语句对emp表中的数据进行删除时，表中的数据进行删除时，Delete from emp where empno=7369;该语句执行完后，触发器被触发，但触发器抛出一个异常，所该语句执行完后，触发器被触发，但触发器抛出一个异常，所以已执行的删除语句无效，实际上并没有删除以已执行的删除语句无效，实际上并没有删除7369职工记录。职工记录。AFTER 子句子句本讲稿第三十八页，共八十五页触发语句和触发器的相互影响触发语句和触发器的相互影响l系统是将触发语句和触发器的执行作为一个整体来看待的。系统是将触发语句和触发器的执行作为一个整体来看待的。无论是无论是BEFORE还是还是AFTER触发器，触发器有异常，触发器，触发器有异常，触发语句都会撤消；而触发语句出现异常，触发器的触发语句都会撤消；而触发语句出现异常，触发器的工作也会撤消。工作也会撤消。l例：例：Create or replace trigger emp_t1Before insert or update or delete On empBegin Insert into temp values(insert emp!);End;/本讲稿第三十九页，共八十五页l当执行一条插入语句时：当执行一条插入语句时：Insert into emp(empno)values(7369);如果发生主码重复，插入语句错误时如果发生主码重复，插入语句错误时查看查看temp表为空，触发器所做的工作被撤消。表为空，触发器所做的工作被撤消。如果插入合法的话，再查看如果插入合法的话，再查看temp表，则有一表，则有一行行“insert emp!”触发语句和触发器的相互影响触发语句和触发器的相互影响本讲稿第四十页，共八十五页INSTEAD OF 子句子句lINSTEAD OF:表示不执行触发语句，而表示不执行触发语句，而是执行触发器代码。相当于用触发器代是执行触发器代码。相当于用触发器代码替代了触发语句所做的工作。码替代了触发语句所做的工作。l注意：该子句仅用于建立视图上的触发注意：该子句仅用于建立视图上的触发器，而且默认为行触发器。器，而且默认为行触发器。例：编写一个触发器例：编写一个触发器emp_view_t,其功能是其功能是禁止对禁止对emp_view 视图进行所有的操作，视图进行所有的操作，并输出有关提示信息。并输出有关提示信息。本讲稿第四十一页，共八十五页l假设视图定义如下：假设视图定义如下：Create view emp_view asSelect*from emp;触发器代码如下：触发器代码如下：Create or replace trigger emp_view_tInstead of insert or update or delete On emp_viewBegin Insert into temp values(emp_view视图不能修改视图不能修改);End;INSTEAD OF 子句子句本讲稿第四十二页，共八十五页启用及禁用触发器和删除触发器启用及禁用触发器和删除触发器禁用触发器：禁用触发器：Alter trigger emp_t disable;启用触发器：启用触发器：Alter trigger emp_t enable;删除触发器：删除触发器：Drop trigger emp_t;l对数据库数据管理，例如大量数据的导入导出时候，对数据库数据管理，例如大量数据的导入导出时候，一般需要禁止某些触发器，以增加效率。一般需要禁止某些触发器，以增加效率。本讲稿第四十三页，共八十五页FOR EACH ROW子句子句l该子句定义触发器为该子句定义触发器为“行级触发器行级触发器”，而没有指定该子句的触发器是一个而没有指定该子句的触发器是一个“语语句级的触发器句级的触发器”本讲稿第四十四页，共八十五页创建创建DML触发器触发器 本讲稿第四十五页，共八十五页行级触发器行级触发器l前面所讲的都是语句级触发器，触发器只对每条语句触发前面所讲的都是语句级触发器，触发器只对每条语句触发一次。而行级触发器的触发次数与触发语句所处理的记录一次。而行级触发器的触发次数与触发语句所处理的记录有关，对每一条记录都触发一次触发器有关，对每一条记录都触发一次触发器l例：对如下的更新语句（假设已存在触发器）例：对如下的更新语句（假设已存在触发器）Update emp set sal=sal*1.1 where age50 and sal500;假设该条语句更新了假设该条语句更新了5条记录，则对于语句级触发器来说只触条记录，则对于语句级触发器来说只触发一次触发器，而对于行级触发器来说它触发发一次触发器，而对于行级触发器来说它触发5次该触发器。次该触发器。本讲稿第四十六页，共八十五页DML触发器激发顺序触发器激发顺序 l如果存在语句之前的触发器，先执行该如果存在语句之前的触发器，先执行该触发器。触发器。l对于受语句影响每一行：对于受语句影响每一行：l如果存在行之前的触发器，执行该触发器。如果存在行之前的触发器，执行该触发器。l执行该语句本身。执行该语句本身。l如果存在行之后的触发器，执行该触发器。如果存在行之后的触发器，执行该触发器。l如果存在语句之后的触发器，运行该触如果存在语句之后的触发器，运行该触发器。发器。本讲稿第四十七页，共八十五页总结：各触发器的触发顺序如下总结：各触发器的触发顺序如下 Before语句级语句级before行级行级触发语句触发语句after行级行级after语句级语句级举例：假如在举例：假如在emp表上建立了表上建立了4个触发器，它们定义如下个触发器，它们定义如下Create or replace trigger emp_t1Before update on empBegin Insert into tout values(before update 语句触发器语句触发器);End;DML触发器激发顺序触发器激发顺序 本讲稿第四十八页，共八十五页Create or replace trigger emp_t2Before update on empFor each rowBegin Insert into tout values(before update 行触发器行触发器);End;Create or replace trigger emp_t3After update on empFor each rowBegin Insert into tout values(after update 行触发器行触发器);End;DML触发器激发顺序触发器激发顺序 本讲稿第四十九页，共八十五页Create or replace trigger emp_t4After update on empBegin Insert into tout values(after update 语句触发器语句触发器);End;测试这测试这4个触发器触发顺序如下：个触发器触发顺序如下：Update emp set sal=sal*1.1;-假设假设假设假设empemp表现有两条记录表现有两条记录表现有两条记录表现有两条记录DML触发器激发顺序触发器激发顺序 本讲稿第五十页，共八十五页Select *from tout;-查看触发器的触发情况查看触发器的触发情况-Before update语句触发器Before update行触发器After update行触发器Before update行触发器After update行触发器After update语句触发器DML触发器激发顺序触发器激发顺序 重复重复14次次本讲稿第五十一页，共八十五页行级别触发器的相关标识符行级别触发器的相关标识符 l触发语句处理每一行时，行级别触发器触发语句处理每一行时，行级别触发器都激发一次。都激发一次。l在触发器内部，可以通过相关标识符在触发器内部，可以通过相关标识符:old和和:new访问正在处理中的行的数据。访问正在处理中的行的数据。l标识符标识符:old和和:new也被称为伪记录。也被称为伪记录。本讲稿第五十二页，共八十五页lOLD 代表修改前的行，所以修改之前当前各列的值可分别代表修改前的行，所以修改之前当前各列的值可分别用用(如表如表emp)OLD.empno,OLD.ename,OLD.job,OLD.sal,OLD.mgr,OLD.deptno等。等。lNEW代表修改之后当前行各列的值可分别用代表修改之后当前行各列的值可分别用NEW.empno,NEW.ename,NEW.job,NEW.sal,NEW.mgr,NEW.deptno 等。等。l注意：如果是在注意：如果是在PL/SQL块内访问关联名，需要在关联名块内访问关联名，需要在关联名前加冒号，如同访问外部变量一样，而在前加冒号，如同访问外部变量一样，而在PL/SQL块外访块外访问则不需加冒号。问则不需加冒号。行级别触发器的相关标识符行级别触发器的相关标识符 本讲稿第五十三页，共八十五页NEW和和OLD应用举例应用举例l如果用户希望触发器做这样的工作：对修改职工工资如果用户希望触发器做这样的工作：对修改职工工资的操作进行合法性检查。其检查内容如下：的操作进行合法性检查。其检查内容如下：l修改后的工资要大于修改前的工资修改后的工资要大于修改前的工资l工资增量不能超过原工资的工资增量不能超过原工资的10%l目前没有单位的职工不能长工资目前没有单位的职工不能长工资则为了实现上述功能，触发器应该在则为了实现上述功能，触发器应该在UPDATE语句修改语句修改每一个记录时都要使触发器触发一次。每一个记录时都要使触发器触发一次。本讲稿第五十四页，共八十五页Create or replace trigger emp_line_tBefore update of sal On empFor each rowBegin If :new.sal:old.sal*1.1 then Raise_application_error(-20000,:old.empno|工资增长超过工资增长超过10%);Elsif :old.deptno is null then Raise_application_error(-20000,:old.empno|无单位，不能增长无单位，不能增长);End if;End;NEW和和OLD应用举例应用举例本讲稿第五十五页，共八十五页:old和和:new相关标识符相关标识符 注意注意:old标识符对标识符对INSERT语句未定义，而语句未定义，而:new标识符对标识符对DELETE语句未定义。语句未定义。PL/SQL编译器不会对在编译器不会对在INSERT语句中使用的语句中使用的:old和在和在DELETE语句中使用的语句中使用的:new标识标识符报错，编译的结果将使这两者的字段值为符报错，编译的结果将使这两者的字段值为NULL。本讲稿第五十六页，共八十五页WHEN子句子句 语法：WHEN trigger_condition 行触发器顾名思义就是对每一行都触发，但行触发器顾名思义就是对每一行都触发，但有时只需对某些行触发，而另外一些行则有时只需对某些行触发，而另外一些行则不需要触发，我们用不需要触发，我们用WHEN条件子句可以条件子句可以做到这一点。做到这一点。lWHEN子句只适用于行级别触发器。子句只适用于行级别触发器。l如果使用该子句，触发器体将只对满足由如果使用该子句，触发器体将只对满足由WHEN子句指定条件的行而执行。子句指定条件的行而执行。l语法：语法：WHEN trigger_condition本讲稿第五十七页，共八十五页带带WHEN子句的触发器举例子句的触发器举例Create or replace trigger checksalBefore insert or update of sal on empFor each rowWhen(new.sal2000)-在在PL/SQL块外块外NEW不用冒号不用冒号Begin /*Trigger body */End;本讲稿第五十八页，共八十五页触发器谓词触发器谓词 l条件谓词可用在触发器代码中判断触发语句的种类l几个谓词如下：本讲稿第五十九页，共八十五页使用触发器谓词举例触发器谓词举例l该例子功能是当插入或删除的职工记录属于30部门时，记录下操作的时间，语句种类（插入/删除）和涉及的职工号。Create or replace trigger emp_t2 After insert or delete On emp For each row When(old.deptno=30 or new.deptno=30)Declare S varchar2(50);本讲稿第六十页，共八十五页Begin S:=to_char(sysdate,YY_MON_DD HH24:SS);If inserting then Insert into tout Values (s|插入插入30部门一个记录，职工号为：部门一个记录，职工号为：|:new.empno);Elsif deleting then Insert into tout Values (s|删除删除30部门一个记录，职工号为：部门一个记录，职工号为：|:old.empno);End if;End;使用触发器谓词举例触发器谓词举例用触发器跟踪对表的用触发器跟踪对表的DML操作信息，提操作信息，提供审计和日志记录。供审计和日志记录。本讲稿第六十一页，共八十五页创建instead-of触发器 instead-of触发器用于以下两种情况：l允许修改一个本来无法修改的视图。l修改视图中嵌套表列的列。本讲稿第六十二页，共八十五页可更改的与不可更改的视图可更改的与不可更改的视图 可更改视图可更改视图：可以发出DML命令的视图。一般来说，视图如果不包括下列命令中的任何一项，它就是一个可更改视图。1、集合操作符（UNION、UNION ALL、MINUS）2、聚集函数（SUM、AVG等）3、GROUP BY、CONNECT BY或START WITH子句4、DISTINCT操作符5、连接本讲稿第六十三页，共八十五页可更改连接视图的条件 l如果一个视图是不可更改的，则可以在其上编写一个instead-of触发器来执行正确的操作，从而使该视图可更改。l如果需要进行其他处理的话，也可以在可更改视图上编写instead-of触发器。本讲稿第六十四页，共八十五页创建INSTEAD-OF触发器实例假设职工记录分别存储在表假设职工记录分别存储在表emp1(empno,ename,deptno)和表和表emp2(empno,ename,deptno)中。中。emp1存放存放1030部门的职工部门的职工记录，其它部门的职工记录存放在记录，其它部门的职工记录存放在emp2中。中。建立一个视图，包含全部职工记录，该视图定义如下：建立一个视图，包含全部职工记录，该视图定义如下：Create view emp_view as Select *from emp1 Union all Select *from emp2;本讲稿第六十五页，共八十五页l视图建好后，如果向视图插入职工记录，系统很难实现将视图建好后，如果向视图插入职工记录，系统很难实现将不同部门