第12章 -过程、函数、包和触发器型2

1、数据库开发技术数据库开发技术第12章 使用子程序和包 2数据库开发技术FJUT 理解子程序（过程、函数）及包的概念 掌握如何创建、执行和删除过程、函数及包的方法 了解形参和实参的区别以及不同参数模式的特征 灵活使用过程、函数及包书写PL/SQL程序 掌握创建和测试DML触发器、instead-of数据库触发器以及系统触发器 了解数据库触发器的使用时机和限制本章要点数据库开发技术FJUT 掌握存储子程序与数据字典的交互方式及如何从SQL语句中调用存储子程序 了解存储子程序和局部子程序的区别 掌握存储子程序在Oracle8i和Oracle9i中的新特性。本章要点数据库开发技术FJUT子程序位置子程

2、序和包可以存储在数据字典中（子程序是用命令CREATE OR REPLACE创建）可以从其他PL/SQL语句块中调用已创建的子程序。子程序可以在语句块的声明段定义，以这种方式定义的子程序叫做局部子程序。包则必须存储在数据字典中，而不能是局部的。数据库开发技术FJUT存储子程序和数据字典 当使用命令CREATE OR REPLACE创建子程序时，该子程序就被存储在数据字典中。存储子程序的信息可以通过各种数据字典视图来访问。数据库开发技术FJUT局部子程序局部子程序: 是一个在PL/SQL语句块的声明段中声明的子程序。子函数只在其声明的语句块中可见，其作用域从声明点开始到该语句块结束为止。其他语句

3、块不能调用该函数，因为该函数对其他语句块来说是不可见的。数据库开发技术FJUT局部子程序（续）局部子程序也可以声明为存储子程序声明段的一部分局部子程序的位置：任何局部子程序都必须在声明段的最后进行声明预先声明 ：局部PL/SQL子程序的名称是标识符，必须在被引用前声明。局部子程序也可以重载数据库开发技术FJUT存储子程序和局部子程序的比较存储子程序局部子程序该类子程序以编译后生成的伪代码形式p-code存储在数据库中。当调用该类子程序时，不需进行编译即可运行局部子程序被编译为包含它的语句块的一部分。如果其所在语句块是匿名块并需要多次运行时，则该子程序就必须每次进行编译存储子程序可以从对该子程序

4、具有EXECUTE特权的用户所提交的任何语句块中调用局部子程序只能从包含子程序的语句块中调用由于存储子程序与调用块的相互隔离，调用块具有代码少，易于理解的特点。除此之外，子程序和调用块还可以分开维护局部子程序和调用块同处于一个块内，所以容易引起混淆。如果修改了调用块的话，则该块调用的子程序作为所属块的一部分也要重新编译可以使用DBMS_SHARED_POOL.KEEP包过程来把编译后p-code代码存储在共享池中。这种方式可以改善程序性能局部子程序自身不能存储在共享池中独立的存储子程序不能进行重载，但同一包内的子程序可以重载同一块中的局部子程序可以重载数据库开发技术FJUT子程序的依赖性 依赖

5、：数据字典记录了存储过程或函数所引用的所有Oracle对象。该过程或函数就依赖于这些存储的对象。自动重编译:如果一个依赖对象失效，PL/SQL引擎将在该对象再次被调用时对其重新进行编译。 包的依赖性：包体的变化不会导致修改包头。因此，其他依赖于该包头的对象也不需要进行重新编译。如果该包头有变化，则会使包体自动失效，这是因为该包体依赖于包头 。如何确定无效 :通过数据字典在不断地跟踪对象间依赖性数据库开发技术FJUT包运行时状态当第一次实例化一个包时，将从磁盘中读入该包的伪代码并将其放入系统全局工作区SGA的共享池中。包的运行时状态，即打包的变量和游标，则存放在用户全局区（UGA）的会话存储区中

6、。这就保证了每个会话都将有其运行时状态的副本。数据库开发技术FJUTEXECUTE特权 为了能够对表进行访问，必须使用SELECT，INSERT，UPDATE和DELETE对象特权。GRANT语句把这些特权赋予数据库用户或角色。对于存储子程序和包来说，相关的特权是EXECUTE。 数据库开发技术FJUT默认参数从过程化语句中调用一个函数时，如果该函数有形参的话，可以使用其缺省值。然而，如果从SQL语句调用函数时，必须指定所有参数。必须使用位置对应法（Positinal Notation）,而不能使用名称对应法（Name Notation）。 数据库开发技术FJUTOracle8i中从SQL语句

7、调用函数从Oracle8i起，从DML语句中调用的函数既不可以从正在被该DML语句修改的数据库表读取数据，也不能对其进行修改，但该函数可以更新其他表。 数据库开发技术FJUT小结 通过访问各种数据库字典视图来查询子程序的信息。user_objects视图包含了当前用户拥有的所有对象的信息。user_source视图包含对象的源程序代码。user_errors视图则包含编译错误的信息。 预先声明可以解决函数之间相互调用的问题。 子程序引用其他数据库对象时，该子程序依赖于那个对象。如果修改了被引用对象，那么依赖对象的状态将更改为无效，指出需要重新编译。数据库开发技术FJUT 本章要点理解数据库触发

8、器、变异表和约束表的概念及数理解数据库触发器、变异表和约束表的概念及数据库触发器的语法据库触发器的语法掌握如何在掌握如何在SQLSQL* *PLUSPLUS中创建和测试中创建和测试DMLDML触发触发器、器、instead-ofinstead-of数据库触发器以及系统触发器数据库触发器以及系统触发器了解数据库触发器的使用时机和限制。了解数据库触发器的使用时机和限制。数据库开发技术FJUT触发器的概念作为数据库对象的触发器是一个与表相关联的被存储的PL/SQL程序。每当一个特定的数据操作语句在指定的表上发生时，就会引发触发器的执行。 与存储子程序的调用方式不同，触发器的执行是采用触发机制数据库开

9、发技术FJUT触发器的定义和使用触发器是当特定事件出现时自动执行的存储过程特定事件可以是执行更新的DML语句和DDL语句触发器不能被显式调用触发器的功能：自动生成数据自定义复杂的安全权限提供审计和日志记录启用复杂的业务逻辑数据库开发技术FJUT触发器的类型 触发器主要有三种类型：DMLINSTEAD-OF触发器系统触发器。触发器的应用，主要用于：维护那些通过创建表时的声明约束无法实现的复杂的完整性约束。通过记录已进行的改变以及是谁进行了该项改变来检查一个表中的信息。当一个表发生改变时，自动向其他程序发送需要采取行动的信号。在一个发布-预订环境中发布有关各种事件的信息。 数据库开发技术FJUTD

10、ML触发器 DML触发器可以由DML语句激发由DML语句的类型决定DML触发器的类型。DML触发器可以在DML语句操作之前或之后激发。DML触发器也可以在行或语句操作上激发 。数据库开发技术FJUTINSTEAD-OF触发器 INSTEAD-OF 触发器是行级别的, 执行时，激发它的DML语句不执行。 只能定义在视图上（可以是关系型或是对象）。 Oracle8及更高版本才提供。数据库开发技术FJUT系统触发器 系统触发器在发生诸如数据库启动或关闭等系统事件时激发，而不是在执行DML语句时激发。系统触发器也可以在DDL操作时，如表的创建时被激发。 数据库开发技术FJUT创建触发器 CREATE

11、OR REPLACE TRIGGER trigger_nameBEFORE | AFTER | INSTEAD OF triggering_eventreferencing_clauseWHEN trigger_conditionFOR EACH ROWtrigger_body;数据库开发技术FJUTCreate trigger emp_t Before insert or update or delete On empBegin If to_char(sysdate,dy)=星期日星期日 thenRaise_application_error(-20000,today is holiday!

12、); End if; End; 当执行一条删除语句在星期六对当执行一条删除语句在星期六对emp表进行删除操作时：表进行删除操作时：Delete from emp where eno=eo1; 那么将会删除成功那么将会删除成功，如果是在星期日执行这条语句，则触发器的异常导致本应继，如果是在星期日执行这条语句，则触发器的异常导致本应继续执行的触发语句不再执行，删除未成功。续执行的触发语句不再执行，删除未成功。创建触发器 数据库开发技术FJUTAFTER 子句AFTER：表示触发语句正常执行后，触发器被触发。注意：虽然触发器是在触发语句之后触发，但是如果触发器代码出现异常时，在此之前执行的触发语句无

13、效。数据库开发技术FJUTCreate or replace trigger emp_testAfter delete On empBegin Raise_application_error(-20000,test message !);End;当执行一条语句对emp表中的数据进行删除时，Delete from emp where empno=7369;该语句执行完后，触发器被触发，但触发器抛出一个异常，所以已执行的删除语句无效，实际上并没有删除7369职工记录。AFTER 子句数据库开发技术FJUT触发语句和触发器的相互影响系统是将触发语句和触发器的执行作为一个整体来看待的。无论是BEFOR

14、E还是AFTER触发器，触发器有异常，触发语句都会撤消；而触发语句出现异常，触发器的工作也会撤消。例：Create or replace trigger emp_t1Before insert or update or delete On empBegin Insert into temp values(insert emp! );End;/数据库开发技术FJUT当执行一条插入语句时：Insert into emp(empno) values(7369); 如果发生主码重复，插入语句错误时查看temp表为空，触发器所做的工作被撤消。如果插入合法的话，再查看temp表，则有一行“insert e

15、mp!”触发语句和触发器的相互影响数据库开发技术FJUTINSTEAD OF 子句INSTEAD OF:表示不执行触发语句，而是执行触发器代码。相当于用触发器代码替代了触发语句所做的工作。注意：该子句仅用于建立视图上的触发器，而且默认为行触发器。例：编写一个触发器emp_view_t,其功能是禁止对emp_view 视图进行所有的操作，并输出有关提示信息。数据库开发技术FJUT假设视图定义如下：Create view emp_view asSelect * from emp;触发器代码如下：Create or replace trigger emp_view_tInstead of inser

16、t or update or delete On emp_viewBegin Insert into temp values(emp_view视图不能修改);End;INSTEAD OF 子句数据库开发技术FJUT启用及禁用触发器和删除触发器禁用触发器：Alter trigger emp_t disable;启用触发器：Alter trigger emp_t enable;删除触发器：Drop trigger emp_t; l对数据库数据管理，例如大量数据的导入导出对数据库数据管理，例如大量数据的导入导出时候，一般需要禁止某些触发器，以增加效率时候，一般需要禁止某些触发器，以增加效率。数据库开

17、发技术FJUTFOR EACH ROW子句该子句定义触发器为“行级触发器”，而没有指定该子句的触发器是一个“语句级的触发器”数据库开发技术FJUT创建DML触发器 数据库开发技术FJUT行级触发器前面所讲的都是语句级触发器，触发器只对每条语句触发一次。而行级触发器的触发次数与触发语句所处理的记录有关，对每一条记录都触发一次触发器例：对如下的更新语句（假设已存在触发器）Update emp set sal=sal*1.1 where age50 and sal500;假设该条语句更新了5条记录，则对于语句级触发器来说只触发一次触发器，而对于行级触发器来说它触发5次该触发器。数据库开发技术FJUT

18、DML触发器激发顺序 如果存在语句之前的触发器，先执行该触发器。对于受语句影响每一行：如果存在行之前的触发器，执行该触发器。执行该语句本身。如果存在行之后的触发器，执行该触发器。如果存在语句之后的触发器，运行该触发器。数据库开发技术FJUT总结：各触发器的触发顺序如下 Before语句级before行级触发语句after行级after语句级举例：假如在emp表上建立了4个触发器，它们定义如下Create or replace trigger emp_t1Before update on empBegin Insert into tout values(before update 语句触发器);

19、End;DML触发器激发顺序 数据库开发技术FJUTCreate 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触发器激发顺序 数据库开发技术FJUTCreate or replace

20、trigger emp_t4After update on empBegin Insert into tout values(after update 语句触发器);End;测试这4个触发器触发顺序如下：Update emp set sal=sal*1.1; DML触发器激发顺序 数据库开发技术FJUTSelect * from tout; -Before update语句触发器语句触发器Before update行触发器行触发器After update行触发器行触发器Before update行触发器行触发器After update行触发器行触发器After update语句触发器语句触发器

21、DML触发器激发顺序 数据库开发技术FJUT行级别触发器的相关标识符 触发语句处理每一行时，行级别触发器都激发一次。 在触发器内部，可以通过相关标识符:old和:new访问正在处理中的行的数据。标识符:old和:new也被称为伪记录。 数据库开发技术FJUTOLD 代表修改前的行，所以修改之前当前各列的值可分别用(如表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.d

22、eptno 等。注意：如果是在PL/SQL块内访问关联名，需要在关联名前加冒号，如同访问外部变量一样，而在PL/SQL块外访问则不需加冒号。行级别触发器的相关标识符 数据库开发技术FJUTNEW和OLD应用举例如果用户希望触发器做这样的工作：对修改职工工资的操作进行合法性检查。其检查内容如下：修改后的工资要大于修改前的工资工资增量不能超过原工资的10%目前没有单位的职工不能长工资则为了实现上述功能，触发器应该在UPDATE语句修改每一个记录时都要使触发器触发一次。数据库开发技术FJUTCreate or replace trigger emp_line_tBefore update of sa

23、l 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应用举例数据库开发技术FJUT: old和:new相关标识符 注意注意:old标识符对标识符对INSERT语句未定义，而语句未定义，而:new标识标识符对符对DELETE语句未定

24、义。语句未定义。PL/SQL编译器不会对在编译器不会对在INSERT语句中使用的语句中使用的:old和在和在DELETE语句中使用语句中使用的的:new标识符报错，编译的结果将使这两者的字段标识符报错，编译的结果将使这两者的字段值为值为NULL。数据库开发技术FJUTWHEN子句 语法：WHEN trigger_condition 行触发器顾名思义就是对每一行都触发，但有时只需对某些行触发，而另外一些行则不需要触发，我们用WHEN条件子句可以做到这一点。WHEN子句只适用于行级别触发器。如果使用该子句，触发器体将只对满足由WHEN子句指定条件的行而执行。语法：WHEN trigger_cond

25、ition数据库开发技术FJUT带WHEN子句的触发器举例Create or replace trigger checksalBefore insert or update of sal on empFor each rowWhen (new.sal2000) -在PL/SQL块外NEW不用冒号Begin /*Trigger body */End;数据库开发技术FJUT触发器谓词 条件谓词可用在触发器代码中判断触发语句的种类条件谓词可用在触发器代码中判断触发语句的种类几个谓词如下：几个谓词如下：数据库开发技术FJUT使用使用触发器谓词举例该例子功能是当插入或删除的职工记录属于该例子功能是当插入

26、或删除的职工记录属于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);数据库开发技术FJUTBegin S:=to_char(sysdate,YY_MON_DD HH24:SS); If inserting then Insert into tou

27、t Values (s|插入30部门一个记录，职工号为： |:new.empno); Elsif deleting then Insert into tout Values (s|删除30部门一个记录，职工号为： |:old.empno); End if;End;使用使用触发器谓词举例用触发器跟踪对表的用触发器跟踪对表的DML操作信操作信息，提供审计和日志记录。息，提供审计和日志记录。 数据库开发技术FJUT其他触发器问题 触发器名称的命名空间（触发器名称的命名空间（Name-space）使用触发器的各种限制和不同种类的触发器体。使用触发器的各种限制和不同种类的触发器体。与触发器有关的权限问题

28、与触发器有关的权限问题 数据库开发技术FJUT触发器名称 命名空间：是一组合法的可供对象作为名字使用的标识符。命名空间：是一组合法的可供对象作为名字使用的标识符。过程、包和表都共享同一个命名空间，在一个数据库模式范过程、包和表都共享同一个命名空间，在一个数据库模式范围内，同一命名空间内的所有的对象必须具有唯一的名称。围内，同一命名空间内的所有的对象必须具有唯一的名称。触发器隶属于一个独立的命名空间。也就是说，触发器可以触发器隶属于一个独立的命名空间。也就是说，触发器可以有与表和过程相同的名称。然而，在一个模式范围内，给定有与表和过程相同的名称。然而，在一个模式范围内，给定的名称只能用于一个触发

29、器。的名称只能用于一个触发器。 数据库开发技术FJUT对触发器的限制 触发器不能发出任何事务控制语句，如触发器不能发出任何事务控制语句，如COMMIT、ROLLBACK、SAVEPOINT或或SET TRANSACTION。由触发器体调用的任何过程或函数都不能发出任何事务控制语句（除非在由触发器体调用的任何过程或函数都不能发出任何事务控制语句（除非在Oracle8i及更高版本中把它们声明为自治的）。及更高版本中把它们声明为自治的）。触发器体不能声明任何触发器体不能声明任何LONG或或LONG RAW变量。而且，变量。而且，:new和和:old也也不能指向为之定义的表的不能指向为之定义的表的LONG或或LONG RAW类型的列。类型的列。在在Oracle8及更高版本中，触发器体中的代码可以引用和使用及更高版本中，触发器体中的代码可以引用和使用LOB（大型（大型对象）列，但不能修改该列的值。这个限制也适用于对象列。对象）列，但不能修改该列的值。这个限制也适用于对象列。 数据库开发技术FJUT触发器体 在在Oracle8i之前的版本中，触发器体必须是之前的版