Oracle数据库系统应用开发实用教程电子课件-第8章-存储过程函数程序包与触发器-

1第8章：子程序、程序包和触发器1第8章：子程序、程序包和触发器1任务分解

任务1：通过过程统计不同部门的员工人数。任务2：通过过程查询雇员信息。任务3：通过函数查询雇员信息。任务4：使用程序包。任务5：通过触发器进行安全性检查。任务6：通过触发器跟踪修改雇员信息的行为。任务7：通过触发器为主键生成自增序列号。任务8：通过触发器在视图中插入数据。任务9：通过触发器跟踪用户新建数据库行为。任务10：通过触发器跟踪用户信息。任务分解任务1：通过过程统计不同部门的员工人数。23任务5任务5.通过触发器进行安全性检查。3任务5任务5.通过触发器进行安全3触发器作用触发器比数据库本身标准的功能有更精细和更复杂的数据控制能力，主要用于维护那些通过创建表的声明约束不可能实现的复杂的完整性约束，并对特定事件进行监控、响应和审计等。触发器作用4触发器示例体验

【例8.26】在emp表上建立触发器。在对emp表插入，更新，删除之前触发，目的是不允许在周末更改表。

触发器示例体验 【例8.26】在emp表上建立触发器。在对e5触发器示例体验触发器示例体验6触发器概述触发器是关系数据库系统提供的一项技术，当特定对象上特定事件出现时，由系统自动触发执行的代码块。数据库触发器能够执行的功能包括以下几方面：①自动生成派生数据。②实现复杂的数据完整性规则。③实施更复杂的安全性检查。④提供审计和日志记录。⑤启用复杂的业务逻辑。触发器概述7CREATE[ORREPLACE]TRIGGER触发器名触发时间触发事件ON对象名[REFERENCING_CLAUSE][FOREACHROW][FOREACHROW[WHENTRIGGER_CONDITION]]PL/SQL语句触发器的基本语法规则CREATE[ORREPLACE]TRIGGER触发8规则说明触发时间：指明触发器何时执行，取值有：{BEFORE|AFTER|INSTEADOF}触发事件：指明哪些数据库动作会触发此触发器，包括 {DML_EVENT|DDL_EVENT|DATABASE_EVENT}，对象名：取值包括{[TABLE|VIEW]|[SCHEMA.]|[DATABASE]}REFERENCING_CLAUSE：用来引用正在处于修改状态下表中对应行的数据。FOREACHROW：触发器对表中的每一行执行一次，即为行级触发器。WHEN子句代表触发器限制条件规则说明触发时间：指明触发器何时执行，取值有：9触发器类型

Oracle具有不同类型的触发器，可以完成不同的任务，按照触发器的触发事件和触发对象的不同，触发器一般分为以下几种：①DML触发器②INSTEADOF触发器③DDL触发器④系统触发器触发器类型10DML触发器建立在基本表上的触发器称为DML触发器。DML触发器建立在基本表上的触发器称为DML触发器。1112DML触发器触发器时机BEFORE触发器AFTER触发器。触发器所依赖的表对象不同语句级触发器行级触发器foreachrow12DML触发器触发器时机12DML触发器BEFORE触发器和AFTER触发器。执行的顺序：BEFORE触发器--约束检查--更新表--AFTER触发器。BEFORE触发器是在约束之前执行的，通常用于：设置或修改被更新或插入的列值。检查复杂的安全规则，如限制时间等。增强商业应用规则。DML触发器BEFORE触发器和AFTER触发器。执行的13DML触发器AFTER触发器是在BEFORE触发器、约束检查以及更新表后才执行的。一般用于：用户信息的审计。导出数据的生成，如果导出数据是存储到其他表中，而不是触发器所依赖的表，则使用AFTER；如果导出数据是存储到当前触发器依赖的表，则触发器必须定义成BEFORE触发器。远程数据的复制。DML触发器AFTER触发器是在BEFORE触发器、约束14语句级触发器触发器的语句中未使用FOREACHROW子句。语句级触发器与INSERT、DELETE、UPDATE或者组合上进行关联。语句触发器都只针对指定语句激活一次。语句级触发器触发器的语句中未使用FOREACHROW子句15实训5①掌握语句级触发器的原理。②语句级触发器的编写方法。③测试语句级触发器是否生效。

完成了任务5，请尝试实训5实训5①掌握语句级触发器的原理。完成了任务5，请尝试实训51617任务6任务6.通过触发器跟踪修改雇员信息的行为17任务6任务6.通过触发器跟踪修改雇员信息的行为17任务6任务618任务6任务61920任务6

思考：上面的例子中，只记录了操作的用户名和时间，如果需要记录下用户的动作，该如何解决？20任务6思考：上面的例子中，只记录了操作的用户名和时20任务6DML触发器有三个可用来确认执行何种操作的逻辑表达式。任务6DML触发器有三个可用来确认执行何种操作的逻辑表达式。21任务6或者任务6或者22任务6任务623任务6任务624实训6①掌握语句级触发器的原理。②练习：建立一触发器，用于审计对HR用户的jobs表的操作行为。

完成了任务6，请尝试实训6实训6①掌握语句级触发器的原理。完成了任务6，请尝试实训62526任务7任务7.通过行级触发器为主键生成自增序列号。

26任务7任务7.通过行级触发器为主键生成自增2627什么是行级触发器

进一步思考：上面的例子中，如何使触发器在执行过程中每行触发一次？工作情境：引入行级触发器。27什么是行级触发器进一步思考：上面的例子中，如何使触27行级触发器行级触发器是指执行DML操作时，每操作一行触发器就执行一次。行级触发器使用WHEN条件，用于控制触发器的执行。WHEN子句的语法为：WHENtrigger_condition说明：trigger_condition是逻辑表达式,该表达式将为每行求值。行级触发器行级触发器是指执行DML操作时，每操作一行触发器就28:old和:new标识符行级触发器中引入了:old和:new两个标识符:old和:new记录可以在trigger_condition内部引用，但不需使用冒号。该冒号只在触发器体内有效。:old和:new标识符行级触发器中引入了:old和:new29每行触发【例8.29】重新修改【例8.28】触发器。每行触发【例8.29】重新修改【例8.28】触发器。30测试测试31任务7：为主键生成自增序列号任务7：为主键生成自增序列号32实训7①掌握行级触发器的原理。②行级触发器的编写方法。练习：创建一行级触发器，为学生信息表的主键生成自增序列号。完成了任务7，请尝试实训7实训7①掌握行级触发器的原理。完成了任务7，请尝试实训73334任务8任务8.通过触发器在视图中插入数据。34任务8任务8.通过触发器在视图中34任务8替代触发器是行级触发器。替代触发器用INSTEADOF来规定,它执行一个替代操作来代替对视图的操作，对视图的操作最终会转换为基本表的操作。问题：对视图view_emp_dept进行插入数据，结果报错。这种情况该如何解决？（视图view_emp_dept的数据来源于emp表的字段empno，ename，job，emp.deptno，条件是emp.deptno=dept.deptno）

（1）创建视图（2）对视图进行插入操作（3）在视图创建INSTEADOF触发器（4）重新对视图进行插入操作任务8替代触发器是行级触发器。替代触发器用INSTEADO35任务8任务836任务8任务837实训8①掌握替代触发器的原理。②创建替代触发器。③替代触发器的测试方法。

完成了任务8，请尝试实训8实训8①掌握替代触发器的原理。完成了任务8，请尝试实训83839任务9任务9.通过触发器跟踪用户新建数据库对象的行为。39任务9任务9.通过触发器跟踪用户39任务9DDL触发器是建立在模式级操作上的触发器。激发DDL操作包括：CREATE、ALTER、DROP等。作用主要是防止DDL操作引起的破坏或提供相应的安全监控。DDL触发器通过事件属性函数获得有关触发事件的信息。任务9DDL触发器是建立在模式级操作上的触发器。激发DDL操40任务9【例8.32】创建DDL触发器，审计记录用户所创建对象的信息（1）创建审计记录表（2）建立DDL触发器logcreation（3）测试DDL触发器任务9【例8.32】创建DDL触发器，审计记录用户所创建41任务9任务942实训9①掌握DDL触发器的原理。②创建DDL触发器。③DDL触发器的测试方法。

完成了任务9，请尝试实训9实训9①掌握DDL触发器的原理。完成了任务9，请尝试实训94344任务10任务10.通过触发器跟踪用户登录信息。44任务10任务10.通过触发器跟踪用户44任务10系统触发器是建立在数据库级别上的触发器，其触发事件包括服务器的启动或关闭，用户的登录或退出，以及服务器错误等。创建系统触发器的语法：任务10系统触发器是建立在数据库级别上的触发器，其触发事件包45任务10database_event_list是一个或多个数据库事件，如下表：任务10database_event_list是一个或多个数46任务10例如：创建当数据库启动时的系统触发器结构如下：任务10例如：创建当数据库启动时的系统触发器结构如下：47任务10【例8.34】创建系统触发器，记录当次启动数据库以来登录的用户。（1）创建一个用户登录的日志记录表（包含登录用户名、登录时间、数据库名字和实例号）（2）授予创建触发器的用户administerdatabasetrigger权限（注意：普通用户需具有此权限才能创建系统触发器）

grantadministerdatabasetriggertoscott;（3）创建系统启动的触发器（4）创建登录系统的触发器（5）测试任务10【例8.34】创建系统触发器，记录当次启动数据库以48任务10系统触发器的数据库事件属性函数获得有关触发事件信息。任务10系统触发器的数据库事件属性函数获得有关触发事件信息。49任务10任务1050任务10

总结：在本例中使用了两个系统触发器，分别为系统启动之后和登录之后触发（afterstartup和afterlogon），登录触发器用于记录用户登录日志，启动触发器用于删除历史登录日志，只记录当此次登录情况。任务10总结：在本例中使用了两个系统触发器，分别为系统启51实训10①掌握DDL触发器的原理。②创建DDL触发器。③DDL触发器的测试方法。

完成了任务10，请尝试实训10实训10①掌握DDL触发器的原理。完成了任务10，请尝试实52管理触发器1.激活和禁用触发器ALTERTRIGGERtriggername{DISABLE|ENABLE};2.修改触发器使用CREATEORREPLACETRIGGER语句直接修改触发器主体内容，如有同名触发器3.重新编译触发器4.查看触发器的有关信息

ALTERTRIGGERtrigger_nameCOMPILE;

5.删除触发器DROPTRIGGERtriggername;管理触发器1.激活和禁用触发器5354作业请登录精品课程-》 2009年国家级精品课程大型数据库管理系统朱亚兴计算机工程技术学院-》 网络课程-》 在线学习找到相应的“学习情境”进行练习54作业请登录54小结过程、函数、程序包和触发器都是命名的PL/SQL块，具有模块化、可重用、易维护性和安全性强等优点。过程能够完成一项或多项特定任务，具有3种类型参数，可以灵活地进行传入值和返回值。函数向调用程序返回一个值，或者借助于PL/SQL赋值语句或条件语句。过程或函数在首次执行时被存储到内存中，然后被其他模块调用。程序包是PL/SQL对象的集合。包括程序包规范和程序包体。触发器是当满足特定事件时自动执行的存储过程。按照触发事件和触发对象的不同，触发器一般分为以下几种：DML触发器、替代触发器、DDL触发器和系统触发器。小结过程、函数、程序包和触发器都是命名的PL/SQL块，具有55谢谢!56谢谢!565657第8章：子程序、程序包和触发器1第8章：子程序、程序包和触发器57任务分解

任务1：通过过程统计不同部门的员工人数。任务2：通过过程查询雇员信息。任务3：通过函数查询雇员信息。任务4：使用程序包。任务5：通过触发器进行安全性检查。任务6：通过触发器跟踪修改雇员信息的行为。任务7：通过触发器为主键生成自增序列号。任务8：通过触发器在视图中插入数据。任务9：通过触发器跟踪用户新建数据库行为。任务10：通过触发器跟踪用户信息。任务分解任务1：通过过程统计不同部门的员工人数。5859任务5任务5.通过触发器进行安全性检查。3任务5任务5.通过触发器进行安全59触发器作用触发器比数据库本身标准的功能有更精细和更复杂的数据控制能力，主要用于维护那些通过创建表的声明约束不可能实现的复杂的完整性约束，并对特定事件进行监控、响应和审计等。触发器作用60触发器示例体验

【例8.26】在emp表上建立触发器。在对emp表插入，更新，删除之前触发，目的是不允许在周末更改表。

触发器示例体验 【例8.26】在emp表上建立触发器。在对e61触发器示例体验触发器示例体验62触发器概述触发器是关系数据库系统提供的一项技术，当特定对象上特定事件出现时，由系统自动触发执行的代码块。数据库触发器能够执行的功能包括以下几方面：①自动生成派生数据。②实现复杂的数据完整性规则。③实施更复杂的安全性检查。④提供审计和日志记录。⑤启用复杂的业务逻辑。触发器概述63CREATE[ORREPLACE]TRIGGER触发器名触发时间触发事件ON对象名[REFERENCING_CLAUSE][FOREACHROW][FOREACHROW[WHENTRIGGER_CONDITION]]PL/SQL语句触发器的基本语法规则CREATE[ORREPLACE]TRIGGER触发64规则说明触发时间：指明触发器何时执行，取值有：{BEFORE|AFTER|INSTEADOF}触发事件：指明哪些数据库动作会触发此触发器，包括 {DML_EVENT|DDL_EVENT|DATABASE_EVENT}，对象名：取值包括{[TABLE|VIEW]|[SCHEMA.]|[DATABASE]}REFERENCING_CLAUSE：用来引用正在处于修改状态下表中对应行的数据。FOREACHROW：触发器对表中的每一行执行一次，即为行级触发器。WHEN子句代表触发器限制条件规则说明触发时间：指明触发器何时执行，取值有：65触发器类型

Oracle具有不同类型的触发器，可以完成不同的任务，按照触发器的触发事件和触发对象的不同，触发器一般分为以下几种：①DML触发器②INSTEADOF触发器③DDL触发器④系统触发器触发器类型66DML触发器建立在基本表上的触发器称为DML触发器。DML触发器建立在基本表上的触发器称为DML触发器。6768DML触发器触发器时机BEFORE触发器AFTER触发器。触发器所依赖的表对象不同语句级触发器行级触发器foreachrow12DML触发器触发器时机68DML触发器BEFORE触发器和AFTER触发器。执行的顺序：BEFORE触发器--约束检查--更新表--AFTER触发器。BEFORE触发器是在约束之前执行的，通常用于：设置或修改被更新或插入的列值。检查复杂的安全规则，如限制时间等。增强商业应用规则。DML触发器BEFORE触发器和AFTER触发器。执行的69DML触发器AFTER触发器是在BEFORE触发器、约束检查以及更新表后才执行的。一般用于：用户信息的审计。导出数据的生成，如果导出数据是存储到其他表中，而不是触发器所依赖的表，则使用AFTER；如果导出数据是存储到当前触发器依赖的表，则触发器必须定义成BEFORE触发器。远程数据的复制。DML触发器AFTER触发器是在BEFORE触发器、约束70语句级触发器触发器的语句中未使用FOREACHROW子句。语句级触发器与INSERT、DELETE、UPDATE或者组合上进行关联。语句触发器都只针对指定语句激活一次。语句级触发器触发器的语句中未使用FOREACHROW子句71实训5①掌握语句级触发器的原理。②语句级触发器的编写方法。③测试语句级触发器是否生效。

完成了任务5，请尝试实训5实训5①掌握语句级触发器的原理。完成了任务5，请尝试实训57273任务6任务6.通过触发器跟踪修改雇员信息的行为17任务6任务6.通过触发器跟踪修改雇员信息的行为73任务6任务674任务6任务67576任务6

思考：上面的例子中，只记录了操作的用户名和时间，如果需要记录下用户的动作，该如何解决？20任务6思考：上面的例子中，只记录了操作的用户名和时76任务6DML触发器有三个可用来确认执行何种操作的逻辑表达式。任务6DML触发器有三个可用来确认执行何种操作的逻辑表达式。77任务6或者任务6或者78任务6任务679任务6任务680实训6①掌握语句级触发器的原理。②练习：建立一触发器，用于审计对HR用户的jobs表的操作行为。

完成了任务6，请尝试实训6实训6①掌握语句级触发器的原理。完成了任务6，请尝试实训68182任务7任务7.通过行级触发器为主键生成自增序列号。

26任务7任务7.通过行级触发器为主键生成自增8283什么是行级触发器

进一步思考：上面的例子中，如何使触发器在执行过程中每行触发一次？工作情境：引入行级触发器。27什么是行级触发器进一步思考：上面的例子中，如何使触83行级触发器行级触发器是指执行DML操作时，每操作一行触发器就执行一次。行级触发器使用WHEN条件，用于控制触发器的执行。WHEN子句的语法为：WHENtrigger_condition说明：trigger_condition是逻辑表达式,该表达式将为每行求值。行级触发器行级触发器是指执行DML操作时，每操作一行触发器就84:old和:new标识符行级触发器中引入了:old和:new两个标识符:old和:new记录可以在trigger_condition内部引用，但不需使用冒号。该冒号只在触发器体内有效。:old和:new标识符行级触发器中引入了:old和:new85每行触发【例8.29】重新修改【例8.28】触发器。每行触发【例8.29】重新修改【例8.28】触发器。86测试测试87任务7：为主键生成自增序列号任务7：为主键生成自增序列号88实训7①掌握行级触发器的原理。②行级触发器的编写方法。练习：创建一行级触发器，为学生信息表的主键生成自增序列号。完成了任务7，请尝试实训7实训7①掌握行级触发器的原理。完成了任务7，请尝试实训78990任务8任务8.通过触发器在视图中插入数据。34任务8任务8.通过触发器在视图中90任务8替代触发器是行级触发器。替代触发器用INSTEADOF来规定,它执行一个替代操作来代替对视图的操作，对视图的操作最终会转换为基本表的操作。问题：对视图view_emp_dept进行插入数据，结果报错。这种情况该如何解决？（视图view_emp_dept的数据来源于emp表的字段empno，ename，job，emp.deptno，条件是emp.deptno=dept.deptno）

（1）创建视图（2）对视图进行插入操作（3）在视图创建INSTEADOF触发器（4）重新对视图进行插入操作任务8替代触发器是行级触发器。替代触发器用INSTEADO91任务8任务892任务8任务893实训8①掌握替代触发器的原理。②创建替代触发器。③替代触发器的测试方法。

完成了任务8，请尝试实训8实训8①掌握替代触发器的原理。完成了任务8，请尝试实训89495任务9任务9.通过触发器跟踪用户新建数据库对象的行为。39任务9任务9.通过触发器跟踪用户95任务9DDL触发器是建立在模式级操作上的触发器。激发DDL操作包括：CREATE、ALTER、DROP等。作用主要是防止DDL操作引起的破坏或提供相应的安全监控。DDL触发器通过事件属性函数获得有关触发事件的信息。任务9DDL触发器是建立在模式级操作上的触发器。激发DDL操96任务9【例8.32】创建DDL触发器，审计记录用户所创建对象的信息（1）创建审计记录表（2）建立DDL触发器logcreation（3）测试DDL触发器任务9【例8.32】创建DDL触发器，审计记录用户所创建97任务9任务998实训9①掌握DDL触发器的原理。②创建DDL触发器。③DDL触发器的测试方法。

完成了任务9，请尝试实训9实训9①掌握DDL触发器的原理。完成了任务9，请尝试实训999100任务10任务10.通过触发器跟踪用户登录信息。44任务10任务10.通过触发器跟踪用户100任务10系统触发器是建立在数据库级别上的触发器，其触发事件包括服务器的启动或关闭，用户的登录或退出，以及服务器错误等。创建系统触发器的语法：任务10系统触发器是建立在数据库级别上的触发器，其触发事件包101任务10database_event_list是一个或多个数据库事件，如下表：任务10database_event_list是一个或多个数102任务10例如：创建当数据库启动时的系统触发器结构如下：任务10例如：创建当数据库启动时的系统触发器结构如下：103任务10【例8.34】创建系统触发器，记录当次启动数据库以来登录的用户。（1）创建一个用户登录的日志记录表（包含登录用户名、登录时间、数据库名字和实例号）（2）授予创建触发器的用户administerdatabasetrigger权限（注意：普通用户需具有此权限才能创建系统触发器）

grantad