1、第6章 游和触发器理论01第6章游标和触发器本章学习内容1. 游标2. 触发器本章学习目标1. 理解和应用游标2. 理解和应用触发器本章简介在第5章中，我们已经学习了过程、函数与程序包的知识。并使用函数实现获取汉字拼音首字母，使用过程实现商品出库操作。但在实际开发过程中，有一些复杂的业务逻辑必须对表中每一条记录分别进行单独处理，这个时候必须使用游标，游标提供了一种用于实现更加复杂业务逻辑的途径，但使用游标会降低SQL语句的处理效率。为了实现一些简单的业务需求，提高数据库的完整性，可以使用Oracle内置的约束，例如主键约束、外键约束等，但是要实现复杂的业务约束，这些内置的约束就显得力不从心了，
2、必须使用触发器，触发器为我们提供了一种实现复杂数据库完整性的一种途径。本章将学习游标与触发器。游标是指向上下文区的指针，它为应用提供了一种对具有多行数据查询结果集中的每一行数据分别进行单独处理的方法。触发器是指被隐含执行的存储过程，它可以使用PL/SQL、Java和C语言进行开发。当发生特定事件（如修改表、创建对象、登录到数据库）时，Oracle会自动执行触发器的相应代码。6.1 游标SQL语言是面向集合的，其结果一般是集合量（多条记录），而PL/SQL语言的变量一般是标量，其一组变量一次只能存放一条记录。因为查询结果的记录数是不确定的，事先无法确定需要声明多少个变量，所以仅使用变量并不能完全
3、满足SQL语句向应用输出数据的要求。为此，PL/SQL中引入了游标（cursor）的概念，使用游标来协调这两种不同的处理方式。当在PL/SQL块中执行查询语句（SELECT）和数据操纵语句（DML）时，Oracle会为其分配上下文区（Context Area）。游标是指向上下文区的指针，它为应用提供了一种对具有多行数据查询结果集中的每一行数据分别进行单独处理的方法。6.1.1 显式游标游标分为显式游标和隐含游标两种。其中，隐含游标用于处理SELECT INTO和DML语句，而显式游标则用于处理SELECT语句返回的多行数据。1. 使用显示游标为了处理SELECT语句返回的多行数据，开发人员可以
4、使用显式游标。使用显式游标包括定义游标、打开游标、提取数据和关闭游标4个阶段，如图1.6.1所示。图1.6.1 使用显式游标的4个阶段（1）定义游标。在使用显式游标之前，必须首先在定义部分定义游标，用于指定游标所对应的SELECT语句。语法：CURSOR cursor_name IS select_statement;在语法中，cursor_name用于指定游标名称，select_statement用于指定游标所对应的SELECT语句。（2）打开游标。当打开游标时，Oracle会执行游标所对应的SELECT语句，并将SELECT语句的结果暂时存放在结果集中。语法：OPEN cursor_nam
5、e;在语法中，游标名cursor_name必须是在定义部分已经被定义的游标。（3）提取数据。在打开游标之后，SELECT语句的结果被临时存放在游标结果集中。为了处理结果集合中的数据，需要使用FETCH语句提取游标数据。在Oracle 9i之前，使用FETCH语句一次只能提取一行数据。从Oracle 9i开始，通过使用FETCH.BULK COLLECT INTO语句，一次可以提取多行数据。语法：FETCH cursor_name INTO variable1,variable2,.;或者：FETCH cursor_name BULK COLLECT INTO collect1,collect2
6、,.;在语法中，variable用于指定接收游标数据的变量，collect用于指定接收游标结果的集合变量。（4）关闭游标。在提取并处理了结果集的所有数据之后，就可以关闭游标并释放其结果集。语法：CLOSE cursor_name;2. 显示游标属性显式游标属性用于返回显示游标的执行信息，包括%ISOPEN、%FOUND、%NOTFOUND和%ROWCOUNT等属性。当使用显示游标属性时，必须在显示游标属性之前添加显式游标名作为前缀，其格式为：游标名.属性名。（1）%ISOPEN%ISOPEN属性用于确定游标是否已经打开。如果游标已经打开，则返回值为TRUE；否则返回FALSE。示例6.1IF
7、c1%ISOPEN THEN -如果游标打开，则执行相应的操作.ELSE -如果游标未打开，则打开游标OPEN c1;END IF;（2）%FOUND%FOUND属性用于检查是否从结果集中提取到数据。如果提取到数据，则返回值为TRUE；否则返回FALSE。示例6.2LOOPFETCH c1 INTO var1,var2; -提取数据到变量中IF c1%FOUND THEN -如果提取到数据，则进行处理.ELSE EXIT; -如果未提取到数据，则退出循环END IF;END LOOP;（3）%NOTFOUND%NOTFOUND属性与%FOUND属性恰好相反，如果提取到数据，则返回值为FALSE
8、；否则返回TRUE。示例6.3LOOPFETCH c1 INTO var1,var2; -提取数据到变量中EXIT WHEN c1%NOTFOUND; -如果未提取到数据，则退出循环.END LOOP;（4）%ROWCOUNT%ROWCOUNT属性用于返回到当前行为止已经提取到的实际行数。示例6.4LOOPFETCH c1 INTO var1,var2; -提取数据到变量中IF c1%ROWCOUNTn THEN -当提取的数据行数大于某个标准时，进行实际处理.END IF;EXIT WHEN c1%NOTFOUND; -如果未提取到数据，则退出循环END LOOP;3. 显示游标使用示例（1
9、）在显示游标中使用FETCH.INTO语句。FETCH.INTO语句每次只能处理一行数据，为了处理结果集中的多行数据，必须使用循环语句进行处理。以下通过在PL/SQL块中显示部门30的所有雇员名及其工资为例，来说明在显示游标中使用FETCH.INTO语句的方法，如图1.6.2所示。图1.6.2 在显示游标中使用FETCH.INTO语句（2）在显示游标中，使用FETCH.BULK COLLECT INTO语句提取所有数据。从Oracle 9i开始，通过使用FETCH.BULK COLLECT INTO语句，一次性可提取结果集的所有数据。下面以显示部门30的所有雇员名和工资为例，说明使用FETCH
10、.BULK COLLECT INTO语句提取所有数据的方法，如图1.6.3所示。图1.6.3 使用FETCH.BULK COLLECT INTO语句提取所有数据（3）使用游标属性。当使用显式游标时，为了取得显式游标的执行信息，需要使用显式游标属性。以下通过使用显式游标属性%ISOPEN和%ROWCOUNT为例，来说明在PL/SQL块中使用显式游标属性的方法，如图1.6.4所示。图1.6.4 使用游标属性（4）基于游标定义记录变量。使用%ROWTYPE属性不仅可以基于表和视图定义记录变量，还可以基于游标定义记录变量。当基于游标定义记录变量时，记录成员名实际就是SELECT语句的列名或列别名。以下
11、通过显示所有雇员名及其工资为例，来说明在处理显式游标时使用记录变量的方法，如图1.6.5所示。图1.6.5 基于游标定义记录变量注意6.1.2 参数游标参数游标是指带有参数的游标。在定义了参数游标之后，当使用了不同参数值多次打开游标时，可以生成不同的结果集。语法：CURSOR cursor_name(parameter_name datatype) IS select_statement;在语法中，当定义参数游标时，需要指定参数名及其数据类型。以下通过显示特定部门所有雇员名为例，来说明定义和使用参数游标的方法，如图1.6.6所示。图1.6.6 参数游标的使用注意6.1.3 使用游标更新或删除数
12、据通过使用显式游标，不仅可以逐行处理SELECT语句的结果，而且还可以更新或删除当前游标行的数据。注意，如果需要通过游标更新或删除数据，则必须在定义游标时带有FOR UPDATE子句。语法：CURSOR cursor_name(parameter_name datatype) IS select_statementFOR UPDATE NOWAIT;在语法中，FOR UPDATE子句用于在游标结果集数据上加行共享锁，以防止其他用户在相应行上执行DML操作；当SELECT语句引用多张表时，使用OF子句可以确定哪些表需要加锁，如果没有OF子句，则会在SELECT语句所引用的全部表上加锁；NOWAI
13、T子句用于指定不等待锁。在提取了游标数据之后，为了更新或删除当前游标行数据，必须在UPDATE或DELETE语句中引用WHERE CURRENT OF子句。1. 使用游标更新数据以下通过给工资低于2000的雇员增加100元工资为例，来说明使用显式游标更新数据的方法，如图1.6.7所示。图1.6.7 使用游标更新数据2. 使用游标删除数据以下通过解雇部门30的所有雇员为例，来说明使用显式游标删除数据的方法，如图1.6.8所示。图1.6.8 使用游标删除数据3. 使用NOWAIT子句使用FOR UPDATE子句会对被操作的行加锁，但如果其他会话已经在被操作的行或表上加了锁，则在默认情况下当前会话需
14、要一直等待对方释放锁。如果在FOR UPDATE子句后加上NOWAIT选项，则可以避免长时间等待对方释放锁，此时当前会话会立即显示错误信息，并退出PL/SQL块，如图1.6.9所示。图 1.6.9 使用NOWAIT子句避免长时间等待对方释放锁6.1.4 游标FOR循环游标FOR循环是在PL/SQL块中使用游标的最简单方式，简化了对游标的处理。当使用游标FOR循环时，Oracle会隐含地打开游标，提取游标数据并关闭游标。语法：FOR record_name IN cursor_name LOOP .END LOOP;在语法中，cursor_name是已经定义的游标名，record_name是Or
15、acle隐含定义的记录变量名。当使用游标FOR循环时，在执行循环体内容之前，Oracle会隐含地打开游标，并且每循环一次提取一次数据，在提取了所有数据之后会自动退出循环并隐含地关闭游标。1. 使用游标FOR循环当使用游标开发PL/SQL应用时，为了简化程序代码，推荐使用游标FOR循环。以下通过顺序显示emp表的所有雇员为例，来说明使用游标FOR循环的方法，如图1.6.10所示。图1.6.10 使用游标FOR循环2. 在游标FOR循环中直接使用子查询当使用游标FOR循环时，习惯的做法是首先在定义部分定义游标，然后在游标FOR循环中引用该游标。如果在使用游标FOR循环时不需要使用任何游标属性，则可
16、以直接在游标FOR循环中使用子查询。以下通过显示emp表的所有雇员名为例，来说明在游标FOR循环中直接使用子查询的方法，如图1.6.11所示。图1.6.11 在游标FOR循环中直接使用子查询6.1.5 使用游标变量当使用显式游标时，需要在定义部分指定其所对应的静态SELECT语句；而当使用游标变量时，开发人员可以在打开游标变量时指定其所对应的SELECT语句。开发人员可以在应用中直接使用PL/SQL游标变量。在PL/SQL块中使用游标变量包括定义游标变量、打开游标、提取游标数据、关闭游标等4个阶段，具体步骤如下：1. 定义REF CURSOR类型和游标变量为了在PL/SQL块中定义游标变量，必
17、须首先定义REF CURSOR类型，然后才能定义游标变量。语法：TYPE ref_type_name IS REF CURSOR;cursor_variable ref_type_name;在语法中，ref_type_name用于指定自定义类型名，RETURN子句用于指定REF CURSOR返回结果的数据类型，cursor_variable用于指定游标变量名。注意，当指定RETURN子句时，其数据类型必须是记录类型。另外，不能在包内定义游标变量。2. 游标变量使用示例以下通过顺序地显示部门30的所有雇员名为例，来说明使用游标变量的方法，如图1.6.12所示。图1.6.12 游标变量使用示例游标
18、变量不支持游标FOR循环。注意6.1.6 使用游标变量开发返回结果集的子程序游标变量的另一强大功能就是可以为子程序提供一种返回结果集的方法。Oracle为了简化REF CURSOR游标类型的使用，在系统中预先定义了SYS_REFCURSOR的数据类型，可以通过SYS_REFCURSOR类型来开发返回结果集的子程序。1. 开发返回结果集的过程以下通过创建一个用于根据部门编号返回该部门雇员信息的子程序为例，来说明开发返回结果集的过程的方法，如图1.6.13所示。图1.6.13 开发返回结果集的过程创建了proc_getEmpsByDeptno过程之后，即可在PL/SQL块中测试此过程，如图1.6.
19、14所示。图1.6.14 测试proc_getEmpsByDeptno过程2. 开发返回结果集的函数以下通过创建一个用于根据雇员雇用日期的年份返回指定年份所有雇员信息的函数为例，来说明开发返回结果集的函数的方法，如图1.6.15所示。图1.6.15 开发返回结果集的函数创建了fun_getEmpsByHireDateYear函数之后，即可在PL/SQL块中测试此函数，如图1.6.16所示。图1.6.16 测试开发返回结果集的函数6.1.7 隐含游标隐含游标由PL/SQL控制。当执行一条DML语句或者SELECT.INTO语句时，都会创建一个隐含游标。隐含游标的名称是SQL，不能对SQL游标显式
20、地执行OPEN、FETCH和CLOSE语句。Oracle隐式地打开、提取，并总是自动地关闭SQL游标。隐式游标属性包括SQL%found、SQL%notfound、SQL%rowcount、SQL%isopen等。1. SQL%found只有DML语句影响一行或多行时，SQL%found属性才返回TRUE。图1.6.17中的代码用于演示SQL%found属性的使用方法。图1.6.17 SQL%found属性的使用2. SQL%NOTFOUNDSQL%notfound属性的作用正好与SQL%found属性的相反。如果DML语句没有影响任何行数，则SQL%notfound属性返回true，否则返回
21、FALSE。3. SQL%rowcountSQL%rowcount属性返回DML语句影响的行数。如果DML语句没有影响任何行，则SQL%rowcount属性将返回0。图1.6.18中的代码用于演示SQL%rowcount属性的使用方法。图1.6.18 SQL%rowcount属性示例演示4. SQL%isopenSQL%isopen属性用于判断SQL游标是否已经打开。在执行SQL语句之后，Oracle自动地关闭SQL游标，所以隐式游标的SQL%isopen属性始终为false。6.2 触发器6.2.1 触发器概述触发器是指被隐含执行的存储过程，它可以使用PL/SQL、Java和C语言进行开发。
22、当发生特定事件（如修改表、创建对象、登录到数据库）时，Oracle会自动执行触发器的相应代码。触发器由触发事件、触发条件和触发操作3个部分组成。1. 触发事件触发事件是指引起触发器被触发的SQL语句、数据库事件或用户事件。在Oracle 8i之前，触发事件只能是DML操作。而从Oracle9i开始，不仅支持原有的DML事件，而且还增加了其他触发事件，具体的触发事件如下：（1）启动和关闭例程。（2）Oracle错误消息。（3）用户登录和断开会话。（4）特定表或视图的DML操作。（5）在任何方案上的DDL语句。2. 触发条件触发条件是指使用WHERE子句指定一个BOOLEAN表达式，当布尔表达式返
23、回值为TRUE时，会自动地执行行触发器相应代码，否则不会执行触发操作。3. 触发操作触发操作是指包含SQL语句和其他执行代码的PL/SQL块，不仅可以使用PL/SQL进行开发，还可使用Java和C语言进行开发。当触发条件为TRUE时，会自动地执行触发操作的相应代码。但编写触发器执行代码时，需要注意以下限制：（1）触发器代码的大小不能超过32K。如果确实需要使用大量的代码创建触发器，则应该首先创建过程，然后在触发器中使用CALL语句调用过程。（2）触发器代码只能包含SELECT、INSERT、UPDATE和DELETE语句，而不能包含DDL语句（CREATE、ALTER和DROP）和事务控制语句
24、（COMMIT、ROLLBACK和SAVEPOINT）。6.2.2 创建DML触发器创建MDL触发器时，需要指定触发时机（BEFORE或AFTER）、触发事件（INSERT、UPDATE或DELETE）、表名、触发类型、触发条件和触发操作。1. 语句触发器语句触发器是指当执行DML语句时被隐含执行的触发器。如果在表上针对某种DML操作创建了语句触发器，则当执行DML操作时会自动地执行触发器的相应代码。为了审计DML操作，或者确保DML操作安全执行时，可以使用语句触发器。注意，使用语句触发器不能记录列的数据变化。语法：CREATET OR REPLACE TRIGGER trigger_name
25、 timing event1 OR event2 OR event3 ON table_name PL/SQL block;在语法中，trigger_name用于指定触发器名，timing用于指定触发时机（BEFORE或AFTER），event用于指定触发事件（INSERT、UPDATTE和DELETE），table_name用于指定DML操作所对应的表名。以下通过示例来说明如何创建语句触发器。（1）创建BEFORE语句触发器。如果指定了BEFORE关键字，则表示在执行DML操作之前触发触发器。为了确保DML操作在正常情况下执行，可以基于DML操作创建BEFORE语句触发器。例如，为了禁止工作
26、人员在休息日改变雇员信息，开发人员可以创建BEFORE语句触发器，以实现数据的完全保护，如图1.6.19所示。图1.6.19 创建BEFORE语句触发器创建了触发器tr_sec_emp之后，如果是星期六或星期日在emp表上执行DML操作，则会显示错误信息，并阻止DML操作，如图1.6.20所示。图1.6.20 在休息日操作emp表出错（2）使用条件谓词。当在触发器中同时包含多个触发事件时，为了在触发器代码中区分具体的触发事件，可以使用以下3个条件谓词：1）INSERTING：当触发事件是INSERT操作时，该条件谓词返回值为TRUE，否则返回FALSE。2）UPDATING：当触发事件是UPD
27、ATE操作时，该条件谓词返回值为TRUE，否则返回FALSE。3）DELETING：当触发事件是DELETE操作时，该条件谓词返回值为TRUE，否则返回FALSE。以下举例说明在触发器中使用这3个条件谓词的方法，如图1.6.21所示。图1.6.21 使用条件谓词在创建了触发器tr_sec_emp之后，如果是星期六或星期日在emp表上执行DML操作，则会根据不同操作显示不同的错误号和错误消息，如图1.6.22所示。图1.6.22 使用条件谓词显示不同错误消息也可以使用“UPDATING(列名)”的语法进一步判断某个列是否被更新了。例如，以下代码表示如果表emp的sal列更新了，则执行某种处理：I
28、F UPDATING(sal) THEN . END IF;注意（3）创建AFTER语句触发器。如果指定了AFTER关键字，则表示在执行DML操作之后触发触发器。为了审计DML操作，或者在DML操作之后执行汇总运算，可以使用AFTER语句触发器。在创建AFTER触发器之前，首先创建审计表audit_table，如图1.6.23所示。图1.6.23 创建审计表为了审计在emp表上DML操作的执行次数、最早执行时间和最近执行时间，需要创建ALTER语句触发器，如图1.6.24所示。图1.6.24 创建AFTER语句触发器创建了触发器tr_sec_emp之后，在emp表上执行DML操作时，会将DML
29、操作次数以及时间记录在审计表audit_table中，如图1.6.25所示。图1.6.25 执行DML操作并显示审计表结果2. 行触发器行触发器是指执行DML操作时，每作用一行就触发一次的触发器。为了审计数据变化，可以使用行触发器。语法：CREATET OR REPLACE TRIGGER trigger_name timing event1 OR event2 OR event3 ON table_name REFERENCING OLD AS old | NEW AS new FOR EACH ROW WHEN condition PL/SQL block;在语法中，trigger_nam
30、e用于指定触发器名；timing用于指定触发时机（BEFORE或AFTER）；event用于指定触发事件（INSERT、UPDATTE和DELETE）；REFERENCING子句用于指定引用新、旧数据的方式，默认情况下使用old修饰符引用旧数据，使用new修饰符引用新数据；table_name用于指定DML操作所对应的表名；FOR EACH ROW表示创建行级触发器；WHERE子句用于指定触发条件。以下通过示例说明如何创建行触发器。（1）创建BEFORE行触发器。在开发数据库应用时，为了确保数据符合商业逻辑或企业规则，应该使用约束对输入数据加以限制。但某些情况下，使用约束可能无法实现复杂的商业
31、逻辑或企业规则，此时可以考虑使用BEFORE行触发器。以下通过确保雇员工资不能低于其原有工资为例，来说明创建BEFORE行触发器的方法，如图1.6.26所示。图1.6.26 创建BEFORE行触发器在创建了触发器tr_emp_sal之后，如果雇员新工资低于其原有工资，则会提示错误信息，如图1.6.27所示。图1.6.27 雇员新工资低于其原有工资时显示错误信息注意（2）创建AFTER行触发器为了审计DML操作，可以使用语句触发器或Oracle系统提供的审计功能。为了审计数据变化，则应该使用AFTER行触发器。以下通过审计雇员工资变化为例，来说明使用AFTER行触发器的方法。在创建AFTER触发器之前，首先创建审计表audit_sal_change，如图1.6.28所示。图1.6.28 创建数据变化审计表为了审计所有
