过程函数包和触发器型

过程函数包和触发器型第一页，共五十三页，编辑于2023年，星期二理解子程序（过程、函数）及包的概念掌握如何创建、执行和删除过程、函数及包的方法了解形参和实参的区别以及不同参数模式的特征灵活使用过程、函数及包书写PL/SQL程序掌握创建和测试DML触发器、instead-of数据库触发器以及系统触发器了解数据库触发器的使用时机和限制本章要点第二页，共五十三页，编辑于2023年，星期二掌握存储子程序与数据字典的交互方式及如何从SQL语句中调用存储子程序了解存储子程序和局部子程序的区别掌握存储子程序在Oracle8i和Oracle9i中的新特性。本章要点第三页，共五十三页，编辑于2023年，星期二子程序位置子程序和包可以存储在数据字典中（子程序是用命令CREATEORREPLACE创建）可以从其他PL/SQL语句块中调用已创建的子程序。子程序可以在语句块的声明段定义，以这种方式定义的子程序叫做局部子程序。包则必须存储在数据字典中，而不能是局部的。第四页，共五十三页，编辑于2023年，星期二存储子程序和数据字典当使用命令CREATEORREPLACE创建子程序时，该子程序就被存储在数据字典中。存储子程序的信息可以通过各种数据字典视图来访问。第五页，共五十三页，编辑于2023年，星期二局部子程序局部子程序:是一个在PL/SQL语句块的声明段中声明的子程序。子函数只在其声明的语句块中可见，其作用域从声明点开始到该语句块结束为止。其他语句块不能调用该函数，因为该函数对其他语句块来说是不可见的。第六页，共五十三页，编辑于2023年，星期二局部子程序（续）局部子程序也可以声明为存储子程序声明段的一部分局部子程序的位置：任何局部子程序都必须在声明段的最后进行声明预先声明：局部PL/SQL子程序的名称是标识符，必须在被引用前声明。局部子程序也可以重载第七页，共五十三页，编辑于2023年，星期二存储子程序和局部子程序的比较存储子程序局部子程序该类子程序以编译后生成的伪代码形式p-code存储在数据库中。当调用该类子程序时，不需进行编译即可运行局部子程序被编译为包含它的语句块的一部分。如果其所在语句块是匿名块并需要多次运行时，则该子程序就必须每次进行编译存储子程序可以从对该子程序具有EXECUTE特权的用户所提交的任何语句块中调用局部子程序只能从包含子程序的语句块中调用由于存储子程序与调用块的相互隔离，调用块具有代码少，易于理解的特点。除此之外，子程序和调用块还可以分开维护局部子程序和调用块同处于一个块内，所以容易引起混淆。如果修改了调用块的话，则该块调用的子程序作为所属块的一部分也要重新编译可以使用DBMS_SHARED_POOL.KEEP包过程来把编译后p-code代码存储在共享池中。这种方式可以改善程序性能局部子程序自身不能存储在共享池中独立的存储子程序不能进行重载，但同一包内的子程序可以重载同一块中的局部子程序可以重载第八页，共五十三页，编辑于2023年，星期二子程序的依赖性依赖：数据字典记录了存储过程或函数所引用的所有Oracle对象。该过程或函数就依赖于这些存储的对象。自动重编译:如果一个依赖对象失效，PL/SQL引擎将在该对象再次被调用时对其重新进行编译。包的依赖性：包体的变化不会导致修改包头。因此，其他依赖于该包头的对象也不需要进行重新编译。如果该包头有变化，则会使包体自动失效，这是因为该包体依赖于包头。如何确定无效:通过数据字典在不断地跟踪对象间依赖性第九页，共五十三页，编辑于2023年，星期二包运行时状态当第一次实例化一个包时，将从磁盘中读入该包的伪代码并将其放入系统全局工作区SGA的共享池中。包的运行时状态，即打包的变量和游标，则存放在用户全局区（UGA）的会话存储区中。这就保证了每个会话都将有其运行时状态的副本。第十页，共五十三页，编辑于2023年，星期二EXECUTE特权

为了能够对表进行访问，必须使用SELECT，INSERT，UPDATE和DELETE对象特权。GRANT语句把这些特权赋予数据库用户或角色。对于存储子程序和包来说，相关的特权是EXECUTE。第十一页，共五十三页，编辑于2023年，星期二默认参数从过程化语句中调用一个函数时，如果该函数有形参的话，可以使用其缺省值。然而，如果从SQL语句调用函数时，必须指定所有参数。必须使用位置对应法（PositinalNotation）,而不能使用名称对应法（NameNotation）。第十二页，共五十三页，编辑于2023年，星期二Oracle8i中从SQL语句调用函数从Oracle8i起，从DML语句中调用的函数既不可以从正在被该DML语句修改的数据库表读取数据，也不能对其进行修改，但该函数可以更新其他表。第十三页，共五十三页，编辑于2023年，星期二小结通过访问各种数据库字典视图来查询子程序的信息。user_objects视图包含了当前用户拥有的所有对象的信息。user_source视图包含对象的源程序代码。user_errors视图则包含编译错误的信息。预先声明可以解决函数之间相互调用的问题。子程序引用其他数据库对象时，该子程序依赖于那个对象。如果修改了被引用对象，那么依赖对象的状态将更改为无效，指出需要重新编译。第十四页，共五十三页，编辑于2023年，星期二

本章要点理解数据库触发器、变异表和约束表的概念及数据库触发器的语法掌握如何在SQL*PLUS中创建和测试DML触发器、instead-of数据库触发器以及系统触发器了解数据库触发器的使用时机和限制。第十五页，共五十三页，编辑于2023年，星期二触发器的概念作为数据库对象的触发器是一个与表相关联的被存储的PL/SQL程序。每当一个特定的数据操作语句在指定的表上发生时，就会引发触发器的执行。与存储子程序的调用方式不同，触发器的执行是采用触发机制第十六页，共五十三页，编辑于2023年，星期二触发器的定义和使用触发器是当特定事件出现时自动执行的存储过程特定事件可以是执行更新的DML语句和DDL语句触发器不能被显式调用触发器的功能：自动生成数据自定义复杂的安全权限提供审计和日志记录启用复杂的业务逻辑第十七页，共五十三页，编辑于2023年，星期二触发器的类型触发器主要有三种类型：DMLINSTEAD-OF触发器系统触发器。触发器的应用，主要用于：维护那些通过创建表时的声明约束无法实现的复杂的完整性约束。通过记录已进行的改变以及是谁进行了该项改变来检查一个表中的信息。当一个表发生改变时，自动向其他程序发送需要采取行动的信号。在一个发布-预订环境中发布有关各种事件的信息。第十八页，共五十三页，编辑于2023年，星期二DML触发器DML触发器可以由DML语句激发由DML语句的类型决定DML触发器的类型。DML触发器可以在DML语句操作之前或之后激发。DML触发器也可以在行或语句操作上激发。第十九页，共五十三页，编辑于2023年，星期二INSTEAD-OF触发器INSTEAD-OF触发器是行级别的,执行时，激发它的DML语句不执行。只能定义在视图上（可以是关系型或是对象）。Oracle8及更高版本才提供。第二十页，共五十三页，编辑于2023年，星期二系统触发器系统触发器在发生诸如数据库启动或关闭等系统事件时激发，而不是在执行DML语句时激发。系统触发器也可以在DDL操作时，如表的创建时被激发。第二十一页，共五十三页，编辑于2023年，星期二创建触发器CREATE[ORREPLACE]TRIGGERtrigger_name{BEFORE|AFTER|INSTEADOF}triggering_eventreferencing_clause[WHENtrigger_condition][FOREACHROW]trigger_body;第二十二页，共五十三页，编辑于2023年，星期二Createtriggeremp_tBeforeinsertorupdateordeleteOnempBeginIfto_char(sysdate,’dy’)=‘星期日’thenRaise_application_error(-20000,’todayisholiday!!!’);Endif;--抛出一个异常，但不做异常处理。End;

当执行一条删除语句在星期六对emp表进行删除操作时：Deletefromempwhereeno=‘eo1’;那么将会删除成功，如果是在星期日执行这条语句，则触发器的异常导致本应继续执行的触发语句不再执行，删除未成功。创建触发器第二十三页，共五十三页，编辑于2023年，星期二AFTER子句AFTER：表示触发语句正常执行后，触发器被触发。注意：虽然触发器是在触发语句之后触发，但是如果触发器代码出现异常时，在此之前执行的触发语句无效。第二十四页，共五十三页，编辑于2023年，星期二Createorreplacetriggeremp_testAfterdeleteOnempBeginRaise_application_error(-20000,'testmessage!');End;当执行一条语句对emp表中的数据进行删除时，Deletefromempwhereempno='7369';该语句执行完后，触发器被触发，但触发器抛出一个异常，所以已执行的删除语句无效，实际上并没有删除7369职工记录。AFTER子句第二十五页，共五十三页，编辑于2023年，星期二触发语句和触发器的相互影响系统是将触发语句和触发器的执行作为一个整体来看待的。无论是BEFORE还是AFTER触发器，触发器有异常，触发语句都会撤消；而触发语句出现异常，触发器的工作也会撤消。例：Createorreplacetriggeremp_t1BeforeinsertorupdateordeleteOnempBeginInsertintotempvalues('insertemp!');End;/第二十六页，共五十三页，编辑于2023年，星期二当执行一条插入语句时：Insertintoemp(empno)values('7369');如果发生主码重复，插入语句错误时查看temp表为空，触发器所做的工作被撤消。如果插入合法的话，再查看temp表，则有一行“insertemp!”触发语句和触发器的相互影响第二十七页，共五十三页，编辑于2023年，星期二INSTEADOF子句INSTEADOF:表示不执行触发语句，而是执行触发器代码。相当于用触发器代码替代了触发语句所做的工作。注意：该子句仅用于建立视图上的触发器，而且默认为行触发器。例：编写一个触发器emp_view_t,其功能是禁止对emp_view视图进行所有的操作，并输出有关提示信息。第二十八页，共五十三页，编辑于2023年，星期二假设视图定义如下：Createviewemp_viewasSelect*fromemp;触发器代码如下：Createorreplacetriggeremp_view_tInsteadofinsertorupdateordeleteOnemp_viewBeginInsertintotempvalues('emp_view视图不能修改');End;INSTEADOF子句第二十九页，共五十三页，编辑于2023年，星期二启用及禁用触发器和删除触发器禁用触发器：Altertriggeremp_tdisable;启用触发器：Altertriggeremp_tenable;删除触发器：Droptriggeremp_t;

对数据库数据管理，例如大量数据的导入导出时候，一般需要禁止某些触发器，以增加效率。第三十页，共五十三页，编辑于2023年，星期二FOREACHROW子句该子句定义触发器为“行级触发器”，而没有指定该子句的触发器是一个“语句级的触发器”第三十一页，共五十三页，编辑于2023年，星期二创建DML触发器第三十二页，共五十三页，编辑于2023年，星期二行级触发器前面所讲的都是语句级触发器，触发器只对每条语句触发一次。而行级触发器的触发次数与触发语句所处理的记录有关，对每一条记录都触发一次触发器例：对如下的更新语句（假设已存在触发器）Updateempsetsal=sal*1.1whereage>50andsal<500;假设该条语句更新了5条记录，则对于语句级触发器来说只触发一次触发器，而对于行级触发器来说它触发5次该触发器。第三十三页，共五十三页，编辑于2023年，星期二DML触发器激发顺序如果存在语句之前的触发器，先执行该触发器。对于受语句影响每一行：如果存在行之前的触发器，执行该触发器。执行该语句本身。如果存在行之后的触发器，执行该触发器。如果存在语句之后的触发器，运行该触发器。第三十四页，共五十三页，编辑于2023年，星期二总结：各触发器的触发顺序如下

Before语句级before行级触发语句after行级after语句级举例：假如在emp表上建立了4个触发器，它们定义如下Createorreplacetriggeremp_t1BeforeupdateonempBeginInsertintotoutvalues('beforeupdate语句触发器');End;DML触发器激发顺序

第三十五页，共五十三页，编辑于2023年，星期二Createorreplacetriggeremp_t2BeforeupdateonempForeachrowBeginInsertintotoutvalues('beforeupdate行触发器');End;Createorreplacetriggeremp_t3AfterupdateonempForeachrowBeginInsertintotoutvalues('afterupdate行触发器');End;DML触发器激发顺序

第三十六页，共五十三页，编辑于2023年，星期二Createorreplacetriggeremp_t4AfterupdateonempBeginInsertintotoutvalues('afterupdate语句触发器');End;测试这4个触发器触发顺序如下：Updateempsetsal=sal*1.1;--假设emp表现有14条记录DML触发器激发顺序

第三十七页，共五十三页，编辑于2023年，星期二Select*fromtout;--查看触发器的触发情况-----------------------------------------------------------Beforeupdate语句触发器Beforeupdate行触发器Afterupdate行触发器Beforeupdate行触发器Afterupdate行触发器……Afterupdate语句触发器DML触发器激发顺序

重复14次第三十八页，共五十三页，编辑于2023年，星期二行级别触发器的相关标识符

触发语句处理每一行时，行级别触发器都激发一次。在触发器内部，可以通过相关标识符:old和:new访问正在处理中的行的数据。标识符:old和:new也被称为伪记录。第三十九页，共五十三页，编辑于2023年，星期二OLD代表修改前的行，所以修改之前当前各列的值可分别用(如表emp)

OLD.empno,OLD.ename,OLD.job,OLD.sal,OLD.mgr,OLD.deptno等。NEW代表修改之后当前行各列的值可分别用

NEW.empno,NEW.ename,NEW.job,NEW.sal,NEW.mgr,NEW.deptno等。注意：如果是在PL/SQL块内访问关联名，需要在关联名前加冒号，如同访问外部变量一样，而在PL/SQL块外访问则不需加冒号。行级别触发器的相关标识符

第四十页，共五十三页，编辑于2023年，星期二NEW和OLD应用举例如果用户希望触发器做这样的工作：对修改职工工资的操作进行合法性检查。其检查内容如下：修改后的工资要大于修改前的工资工资增量不能超过原工资的10%目前没有单位的职工不能长工资则为了实现上述功能，触发器应该在UPDATE语句修改每一个记录时都要使触发器触发一次。第四十一页，共五十三页，编辑于2023年，星期二Createorreplacetriggeremp_line_tBeforeupdateofsalOnempForeachrowBeginIf:new.sal<=:old.salthenRaise_application_error(-20000,:old.empno||'工资没增长');Elsif:new.sal>:old.sal*1.1thenRaise_application_error(-20000,:old.empno||'工资增长超过10%');Elsif:old.deptnoisnullthenRaise_application_error(-20000,:old.empno||'无单位，不能增长');Endif;End;NEW和OLD应用举例第四十二页，共五十三页，编辑于2023年，星期二:old和:new相关标识符注意:old标识符对INSERT语句未定义，而:new标识符对DELETE语句未定义。PL/SQL编译器不会对在INSERT语句中使用的:old和在DELETE语句中使用的:new标识符报错，编译的结果将使这两者的字段值为NULL。第四十三页，共五十三页，编辑于2023年，星期二WHEN子句

语法：WHENtrigger_condition行触发器顾名思义就是对每一行都触发，但有时只需对某些行触发，而另外一些行则不需要触发，我们用WHEN条件子句可以做到这一点。WHEN子句只适用于行级别触发器。如果使用该子句，触发器体将只对满足由WHEN子句指定条件的行而执行。语法：WHENtrigger_condition第四十四页，共五十三页，编辑于2023年，星期二带WHEN子句的触发器举例CreateorreplacetriggerchecksalBeforeinsertorupdateofsalonempForeachrowWhen(new.sal>2000)--在PL/SQL块外NEW不用冒号Begin/*Triggerbody*/End;第四十五页，共五十三页，编辑于2023年，星期二触发器谓词

条件谓词可用在触发器代码中判断触发语句的种类几个谓词如下：第四十六页，共五十三页，编辑于2023年，星期二使用触发器谓词举例该例子功能是当插入或删除的职工记录属于30部门时，记录下操作的时间，语句种类（插入/删除）和涉及的职工号。Createorreplacetriggeremp_t2AfterinsertordeleteOnempForeachrowWhen(old.deptno=‘30’ornew.deptno=‘30’)DeclareSvarchar2(50);第四十七页，共五十三页，编辑于2023年，星期二BeginS:=to_char(sysdate,’YY_MON_DDHH24:SS’);IfinsertingthenInsertintotoutValues(s||’插入30部门一个记录，职工号为：

‘||:new.emp