Oracle11g基础学习---------（6） 游标和触发器

 学习Oracle从哪里入手呢，在这里，我将带你走进Oracle的大门，一天一章，7天入门不是梦

1. 游标
SQL语言是面向集合的，其结果一般是集合量（多条记录），而PL/SQL语言的变量一般是标量，其一组变量一次只能存放一条记录 
因为查询结果的记录数是不确定的，事先无法确定需要声明多少个变量，所以仅使用变量并不能完全满足SQL语句向应用输出数据的要求 
为此，PL/SQL中引入了游标（cursor）的概念，使用游标来协调这两种不同的处理方式 
当在PL/SQL块中执行查询语句（SELECT）和数据操纵语句（DML）时，Oracle会为其分配上下文区（Context Area） 
游标是指向上下文区的指针，它为应用提供了一种对具有多行数据查询结果集中的每一行数据分别进行单独处理的方法

1.1 显式游标
游标分为显式游标和隐含游标两种 
隐含游标用于处理SELECT INTO和DML语句 
显式游标则用于处理SELECT语句返回的多行数据
使用显示游标 
定义游标
CURSOR cursor_name IS select_statement;
打开游标 
OPEN cursor_name;
提取数据 
FETCH cursor_name INTO variable1,variable2,...;
关闭游标 
CLOSE cursor_name;  
显式游标
显示游标属性
显式游标属性用于返回显示游标的执行信息 
当使用显示游标属性时，必须在显示游标属性之前添加显式游标名作为前缀，其格式为：游标名.属性名

%ISOPEN 
%ISOPEN属性用于确定游标是否已经打开。如果游标已经打开，则返回值为TRUE；否则返回FALSE 
%FOUND 
%FOUND属性用于检查是否从结果集中提取到数据。如果提取到数据，则返回值为TRUE；否则返回FALSE 
%NOTFOUND 
%NOTFOUND属性与%FOUND属性恰好相反，如果提取到数据，则返回值为FALSE；否则返回TRUE 
%ROWCOUNT 
%ROWCOUNT属性用于返回到当前行为止已经提取到的实际行数 

显式游标显示游标使用示例在显示游标中使用FETCH...INTO语句使用游标属性基于游标定义记录变量在显示游标中，使用FETCH...BULK COLLECT INTO语句提取所有数据

1.2 参数游标 
参数游标是指带有参数的游标 
在定义了参数游标之后，当使用了不同参数值多次打开游标时，可以生成不同的结果集
CURSOR cursor_name(parameter_name datatype) IS select_statement;

定义参数游标时，游标参数只能指定数据类型，而不能指定长度。另外，定义参数游标时，一定要在游标子查询的WHERE子句中引用该参数，否则就失去了定义参数游标的意义。

1.3 使用游标更新或删除数据
通过使用显式游标，不仅可以逐行处理SELECT语句的结果，而且还可以更新或删除当前游标行的数据 
如果需要通过游标更新或删除数据，则必须在定义游标时带有FOR UPDATE子句
为了更新或删除当前游标行数据，必须在UPDATE或DELETE语句中引用WHERE CURRENT OF子句

CURSOR cursor_name(parameter_name datatype) IS select_statement
FOR UPDATE [NOWAIT];
  
使用游标更新或删除数据
使用NOWAIT子句
使用FOR UPDATE子句会对被操作的行加锁，但如果其他会话已经在被操作的行或表上加了锁，则在默认情况下当前会话需要一直等待对方释放锁 
如果在FOR UPDATE子句后加上NOWAIT选项，则可以避免长时间等待对方释放锁，此时当前会话会立即显示错误信息，并退出PL/SQL块 

1.4 游标FOR循环
游标FOR循环是在PL/SQL块中使用游标的最简单方式，简化了对游标的处理
当使用游标FOR循环时，Oracle会隐含地打开游标，提取游标数据并关闭游标

FOR record_name IN cursor_name LOOP
     ...
END LOOP;

1.5  使用游标变量当使用显式游标时，需要在定义部分指定其所对应的静态SELECT语句 而当使用游标变量时，开发人员可以在打开游标变量时指定其所对应的SELECT语句
TYPE ref_type_name IS REF CURSOR;cursor_variable ref_type_name;

1.6 使用游标变量开发返回结果集的子程序游标变量的另一强大功能就是可以为子程序提供一种返回结果集的方法 Oracle为了简化REF CURSOR游标类型的使用，在系统中预先定义了SYS_REFCURSOR的数据类型可以通过SYS_REFCURSOR类型来开发返回结果集的子程序

1.7 隐含游标
隐含游标由PL/SQL控制。当执行一条DML语句或者SELECT...INTO语句时，都会创建一个隐含游标 
隐含游标的名称是SQL，不能对SQL游标显式地执行OPEN、FETCH和CLOSE语句。Oracle隐式地打开、提取，并总是自动地关闭SQL游标 
隐式游标属性包括SQL%FOUND、SQL%NOTFOUND、SQL%ROWCOUNT、SQL%ISOPEN等
SQL%FOUND 
只有DML语句影响一行或多行时，SQL%FOUND属性才返回TRUE  
SQL%ROWCOUNT 
SQL%ROWCOUNT属性返回DML语句影响的行数。如果DML语句没有影响任何行，则SQL%ROWCOUNT属性将返回0 
SQL%ISOPEN 
SQL%ISOPEN属性用于判断SQL游标是否已经打开 
在执行SQL语句之后，Oracle自动地关闭SQL游标，所以隐式游标的SQL%ISOPEN属性始终为FALSE

2. 触发器
2.1 触发器概述
触发器是指被隐含执行的存储过程，它可以使用PL/SQL、Java和C语言进行开发 
当发生特定事件（如修改表、创建对象、登录到数据库）时，Oracle会自动执行触发器的相应代码 
触发器由触发事件、触发条件和触发操作3个部分组成 
触发事件
触发事件是指引起触发器被触发的SQL语句、数据库事件或用户事件 
触发条件
触发条件是指使用WHERE子句指定一个BOOLEAN表达式，当布尔表达式返回值为TRUE时，会自动地执行行触发器相应代码，否则不会执行触发操作 
触发操作 
触发操作是指包含SQL语句和其他执行代码的PL/SQL块，不仅可以使用PL/SQL进行开发，还可使用Java和C语言进行开发 
编写触发器执行代码时，需要注意以下限制 
触发器代码的大小不能超过32K。如果确实需要使用大量的代码创建触发器，则应该首先创建过程，然后在触发器中使用CALL语句调用过程 
触发器代码只能包含SELECT、INSERT、UPDATE和DELETE语句，而不能包含DDL语句（CREATE、ALTER和DROP）和事务控制语句（COMMIT、ROLLBACK和SAVEPOINT）

2.2 创建DML触发器语句触发器 
语句触发器是指当执行DML语句时被隐含执行的触发器 
如果在表上针对某种DML操作创建了语句触发器，则当执行DML操作时会自动地执行触发器的相应代码 
为了审计DML操作，或者确保DML操作安全执行时，可以使用语句触发器 
CREATET [OR REPLACE] TRIGGER trigger_name
timing event1 [OR event2 OR event3]
ON table_name
PL/SQL block;
参数	说明
trigger_name 	指定触发器名 
timing 		指定触发时机（BEFORE或AFTER） 
event 		指定触发事件（INSERT、UPDATTE和DELETE） 
table_name 	指定DML操作所对应的表名 

创建BEFORE语句触发器 
如果指定了BEFORE关键字，则表示在执行DML操作之前触发触发器
使用条件谓词 
当在触发器中同时包含多个触发事件时，为了在触发器代码中区分具体的触发事件，可以使用以下3个条件谓词 
INSERTING：当触发事件是INSERT操作时，该条件谓词返回值为TRUE，否则返回FALSE
UPDATING：当触发事件是UPDATE操作时，该条件谓词返回值为TRUE，否则返回FALSE
DELETING：当触发事件是DELETE操作时，该条件谓词返回值为TRUE，否则返回FALSE
也可以使用“UPDATING('列名')”的语法进一步判断某个列是否被更新创建BEFORE语句触发器 
如果指定了BEFORE关键字，则表示在执行DML操作之前触发触发器使用条件谓词 
当在触发器中同时包含多个触发事件时，为了在触发器代码中区分具体的触发事件，可以使用以下3个条件谓词 
INSERTING：当触发事件是INSERT操作时，该条件谓词返回值为TRUE，否则返回FALSE
UPDATING：当触发事件是UPDATE操作时，该条件谓词返回值为TRUE，否则返回FALSE
DELETING：当触发事件是DELETE操作时，该条件谓词返回值为TRUE，否则返回FALSE
了。例如，以下代码表示如果表emp的sal列更新了，则执行某种处理：
IF UPDATING('sal')   THEN
       ...
END IF;

创建AFTER语句触发器  
如果指定了AFTER关键字，则表示在执行DML操作之后触发触发器
为了审计DML操作，或者在DML操作之后执行汇总运算，可以使用AFTER语句触发器 

行触发器  
行触发器是指执行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_name 	指定触发器名 
timing 		指定触发时机（BEFORE或AFTER） 
event 		指定触发事件（INSERT、UPDATTE和DELETE） 
table_name 	指定DML操作所对应的表名 
REFERENCING 	指定引用新、旧数据的方式，默认情况下使用old修饰符引用旧数据，使用new修饰符引用新数据 
FOR EACH ROW 	创建行级触发器 
WHERE 		指定触发条件 

创建BEFORE行触发器  
在开发数据库应用时，为了确保数据符合商业逻辑或企业规则，应该使用约束对输入数据加以限制 
但某些情况下，使用约束可能无法实现复杂的商业逻辑或企业规则，此时可以考虑使用BEFORE行触发器 
创建AFTER行触发器   
为了审计数据变化，则应该使用AFTER行触发器
创建数据变化审计表
为了审计所有雇员的工资变化创建ALTER行触发器
 执行DML操作并显示审计表结果 
限制行触发器

2.3 创建INSTEAD OF 触发器
为了在不能执行DML操作的复杂视图上执行DML操作，必须基于视图创建INSTEAD OF触发器
创建INSTEAD OF触发器时需要注意以下几点 
INSTEAD OF选项只适用于视图
当基于视图创建触发器时，不能指定BEFORE和AFTER选项
当创建INSTEAD OF触发器时，必须指定FOR EACH ROW选项
只能在视图上创建INSTEAD OF触发器，而不能创建其他类型的触发器

2.4 创建系统事件触发器
系统事件触发器是指基于Oracle系统事件所创建的触发器 
创建系统事件触发器时，应用开发人员经常需要使用事件属性函数

事件属性函数	功能
ora_client_ip_address	返回客户端的IP地址
ora_database_name	返回当前数据库名
ora_dict_obj_name 	返回DDL操作所对应的数据库对象名
ora_dict_obj_owner	返回DDL操作所对应的对象的所有者名
ora_dict_obj_type	返回DDL操作对应的数据库对象的类型
ora_instance_num	返回例程号
ora_is_alter_column(column_name IN VARCHAR2)	检测特定列是否被修改
ora_is_drop_column(column_name IN VARCHAR2)	检测特定列是否被删除
ora_login_user		返回登录用户名
ora_sysevent		返回触发触发器的系统事件名

创建例程启动和关闭触发器 
为了跟踪例程启动和关闭事件，可以分别创建例程启动触发器和例程关闭触发器
创建事件表
创建例程启动和关闭触发器
打开或关闭数据库时执行对应触发器的相应代码

2.5 管理触发器
显示触发器信息 
通过查询数据字典视图USER_TRIGGERS，可以显示当前用户所包含的所有触发器信息
禁用或启用触发器
重新编译触发器
当使用ALTER TABLE命令修改表的结构时，会使触发器变为无效状态
为了使触发器继续生效，需要重新编译触发器    
删除触发器

 总结
游标是指向上下文区的指针，它为应用提供了一种对具有多行数据查询结果集中的每一行数据分别进行单独处理的方法
游标分为显式游标和隐含游标两种。其中，隐含游标用于处理SELECT INTO和DML语句，而显式游标则用于处理SELECT语句返回的多行数据
为了简化显式游标的数据处理，建议开发人员使用记录变量存放游标数据
参数游标是指带有参数的游标，在定义了参数游标之后，当使用了不同参数值多次打开游标时，可以生成不同的结果集
使用游标变量时，开发人员可以在打开游标变量时指定其所对应的SELECT语句
触发器由触发事件、触发条件和触发操作3个部分组成
如果在表上针对某种DML操作创建了语句触发器，则当执行DML操作时会自动地执行触发器的相应代码
行触发器是指执行DML操作时，每作用一行就触发一次的触发器
为了在复杂视图上执行DML操作，必须基于视图创建INSTEAD OF触发器
系统事件触发器是指基于Oracle系统事件所创建的触发器