第八章理论课触发器和内置程序包

《Oracle数据库应用》理论课
触发器和内置程序包
⏹本章技能目标
◆理解和应用触发器
◆了解内置程序包
1.触发器
触发器是特定事件出现的时候，自动执行的代码块。

类似于存储过程，触发器与存储过程的区别在于:存储过程是由用户或应用程序显式调用的,而触发器是不能被直接调用的。

触发器可以用于加载Oracle的默认功能，提供高度可定制的数据库，触发器能够执行的功能有：
1、允许/限制对表的修改
2、自动生成派生列，比如自增字段
3、强制数据一致性
4、提供审计和日志记录
5、防止无效的事务处理
6、启用复杂的业务逻辑
触发器与特定的表或视图相关联，用于检查对表或视图所做的数据修改。

无论正在执行操作的用户身份如何，触发器都能够在它们执行操作时生效。

当INSERT，DELETE，UPADTE等事件发生在表或视图中时，就会激活触发器的代码。

before和after：指在事件发生之前或之后激活触发器。

instead of：如果使用此子句，表示可以执行触发器代码来代替导致触发器调用的事件。

insert、delete和update：指定构成触发器事件的数据操纵类型，update还可以制定列的列表。

referencing：指定新行（即将更新）和旧行（更新前）的其他名称，默认为new和old。

table_or_view_name：指要创建触发器的表或视图的名称。

for each row：指定是否对受影响的每行都执行触发器，即行级触发器，如果不使用此子句，则为语句级触发器。

when：限制执行触发器的条件，该条件可以包括新旧数据值得检查。

declare---end：是一个标准的PL/SQL块。

例1演示如何在SCOTT模式的EMP表上创建触发器。

例1：
SHOW ERRORS。

1.1触发器的组成部分
触发器由三部分组成：
●触发器语句（事件）
⏹定义激活触发器的DML 事件和DDL 事件
●触发器限制
⏹执行触发器的条件，该条件必须为真才能激活触发器
●触发器操作（主体）
⏹包含一些SQL 语句和代码，它们在发出了触发器语句且触发限制的值为真时运
行
触发器语句
触发器语句是那些可以导致触发器的事件，即在表或视图上执行的INSERT，DELETE，UPADTE 之类的DML语句，在模式对象上执行的DDL语句或数据库事件。

EMP表的deptno列进行UPADTE时。

而且触发器将在受到影响的每一行上执行一次。

但是触发器是否真正的执行还要检查触发器的条件，只有满足限制条件才会执行。

触发器限制
触发器限制条件包含一个布尔表达式，该值必须为“真”才能激活触发器。

如果该值为“假”
或“未知”，将不运行触发器操作。

这就是说，如果列deptno的新值不等于40，触发器将会执行。

触发器操作
触发器操作是触发器的主体，包含一些SQL语句和代码。

这些代码在执行触发器语句且触发器限制条件的值为“真”时运行。

行级触发器允许触发操作中的语句访问行的列值。

例1中的触发器操作如下面的代码所示
以上这段代码将列comm新增设置为0。

现在，来测试此触发器产生的效果。

例2演示了如何在EMP表中插入记录。

例2：
上述语句在EMP表中插入了两条记录。

并为每行记录指定了COMM列的值。

用户在表中插入了值，这将激活前面创建的触发器。

可以看到，插入第一行数据时指定了COMM列的值为600。

但是DEPTNO的值不等于40，所以会激活触发器，COMM列的值被设置为0。

插入第二行记录时，由于DEPTNO的值等于40，不满足触发器限制条件，所以触发器不执行，用户指定的COMM列的值插入到数据库中。

1.2触发器类型
Oracle具有不同类型的触发器，可以实现不同的任务。

这些触发器类型包括：
语句触发器，行触发器，INSTEAD OF 触发器，模式触发器，数据库级触发器
分类如下图：
DDL 触发器：在模式中执行DDL 语句时执行
数据库级触发器：在发生打开、关闭、登录和退出数据库等系统事件时执行
DML 触发器：在对表或视图执行DML语句时执行
语句级触发器：无论受影响的行数是多少，都只执行一次
行级触发器：对DML语句修改的每个行执行一次
INSTEAD OF 触发器：用于用户不能直接使用DML 语句修改的视图
具体叙述如下：
1.行级触发器
行级触发器对DML语句影响的每个行执行一次，例如：UPADTE语句影响多行，就会对每行都激活一次触发器。

行触发器是触发器中最常用的一种。

通常用于数据库审计和实现复杂的业务逻辑。

可以在CREATE TRIGGER命令中指定FOR EACH ROW子句创建行级触发器。

由于触发器是事件驱动的，因此可以设置触发器在这些事件之前或之后执行，即在执行DML 语句之前或之后执行。

在触发器中，可以引用DML语句中涉及的旧值和新值。

“新”是指由DML语句创建的数据值（如插入记录中的列）。

执行DML语句之前的值的默认名称是:old ,之后的值是:new。

insert 操作只有:new
delete 操作只有:old
update 操作两者都有
如果需要通过触发器在插入行中设置一个列值，就应该使用BEFORE INSERT 触发器访问新值。

使用AFTER INSERT不允许设置插入值，因为该行已经插入表中。

在审计应用程序中经常使用AFTER行级触发器，直到行被修改才会触发它们。

行的成功修改表明此行已经通过该表定义的完整性约束。

例3演示了行级触发器的应用。

例3：
例3先创建一个测试表TEST_TRG，接着创建一个序列SEQ_TEST，然后创建一个触发器自动生成ID列的值，并禁止UPADTE表的ID列。

最后，向测试表中插入3条记录。

现在，使用以
上述语句的输出结果是：
可以看出，表的ID列的值是由触发器自动产生的，而不是用户在插入命令中指定值。

提示：如果一个触发器由多种语句的触发，可以使用INSERTING，UPDATING，DELETING 条件谓词来检查，如果值为“真”，那么就是相应得语句类型激活了该触发器。

条件谓词只能在
2.语句级触发器
语句级触发器对每个DML语句执行一次。

例如，如果一条INSERT语句在表中插入200行，那么这个表上的INSERT语句级触发器只执行一次。

语句级触发器不常用于与数据库相关的活动，通常用于强制实施在表上执行操作的额外安全性措施。

语句级触发器是CREATE TRIGGER命令创建的触发器的默认类型。

例4演示了如何创建语法级触发器。

例4-1需要对在表上进行DML操作的用户进行安全检查，看是否具有合适的特权。

3.INSTEAD OF 触发器
INSTEAD OF 触发器是在视图上而不是在表上定义的触发器，它是用来替换所使用实际语句的触发器。

这样的触发器可以用于克服Oracle在任何视图上设置的限制。

允许用户修改不能直接使用DML语句修改的视图。

以下是对INSTEAD OF 触发器的一些限制。

它们只能在行级使用，而不能在语句级使用。

它们只能应用于视图，而不能应用于表。

如果有需要同时向两个表插入值的情况，可以通过使用INSTEAD OF 触发器来实现。

例5演示了在表order_master和order_detail上创建视图以完成同时向两个表中插入值的操作。

例5：
上述语句创建了两个表order_master,和order_detail上的联接视图ord_view。

在创建视图之后，
入了行。

4.模式触发器
可以在模式级的操作上建立触发器，如：CREATE，ALTER，DROP，GRANT，REVOKE和TRUNCA TE等DDL语句。

用户可以创建触发器来防止删除自己创建的表。

模式触发器提供的主要功能是阻止DDL操作以及发生DDL操作时提供额外的安全监控。

当在表，视图，过程，函数，索引，程序包，序列和同义词等模式对象执行CREATE，DROP 和ALTER命令时，会激活DDL触发器。

例6演示如何对用户所删除的所有对象进行日志记录。

5.数据库触发器
可以创建在数据库事件上的触发器。

包括启动，关闭，服务器错误，登录和注销等。

这些事件都是实例范围的。

不与特定的表和视图关联。

可以使用这种类型的触发器自动进行数据库维护和统计。

下面给出一个简单的示例。

1.3启用、禁用触发器
触发器一旦创建就会立即生效，有时可能需要临时禁用触发器，最常见的原因就是涉及数据加载。

用于启动和禁用触发器的语法如下：
1.4删除触发器
下面演示如何删除触发器biu_emp_deptno。

1.5查看有关触发器的信息
要查看有关触发器的信息，可使用USER_TRIGGERS 数据字典视图，此视图包含有关触发器
下面的案例演示如何查看有关触发器的信息。

例8：
2.内置程序包
Oracle提供了许多内置程序包，它们用于扩展数据库的功能。

在开发应用程序时，可以利用这些程序包。

数据库用户SYS拥有Orcale提供的所有程序包。

它们被定义为公有同义词，并将执行权限授予了PUBLIC用户组，任何用户都可以访问它们。

Oracle提供的程序包的部分列表见下表。

一些内置程序包
下面简单介绍几个常用的内置程序包。

2.1DBMS_OUTPUT
DBMS_OUTPUT程序包允许显示PL/SQL块和子程序的输出结果。

这样便于测试和调试它们。

PUT和PUT_LINE过程将信息输出到SGA中的缓存区。

通过调用过程GET_LINE或通过SET SERVEROUTPUT ON。

可以显示缓存区的信息。

默认缓存区大小是2000字节。

最小值是2000，最大值是1,000,000。

DBMS_OUTPUT包有以下常用过程。

ENABLE
ENABLE过程用来启用对PUT，PUT_LINE和NEW_LINE等过程的调用，它只有一个输入参数，即缓存区大小（BUFFER_SIZE）。

缓存区大小的输入值用于设置默认缓存的信息量。

调用ENABLE将清除任何已废弃会话中缓存的数据。

DISABLE
DISABLE没有输入和输出。

DISABLE用于禁用对PUT，PUT_LINE和NEW_LINE的所有调用。

调用DISABLE过程还可清除缓存区中的任何剩余信息。

PUT
PUT有一个输入参数。

此参数被重载以接受VRACHAR2，NUMBER和DATE值。

PUT用于在缓存区中存储一条信息。

TO_CHAR将使用默认格式设置格式化这些项。

PUT_LINE
PUT_LINE有一个输入参数。

此参数被重载以接受VRACHAR2，NUMBER和DATE值。

如果这些值是混合使用,则必须使用TO_CHAR函数.PUT_LINE过程用于在缓存区中存储一条信息。

后接一个行结束标记。

NEW_LINE
NEW_LINE没有参数。

它用于向缓存区中添加换行符, 换行符充当结束标记,对PUT_LINE或NEW_LINE的每次调用都将产生一行。

例9演示了DBMS_OUTPUT包的用法。

上述语句的输出结果是：
2.2DBMS_LOB
DBMS_LOB程序包包含用于处理大型对象的过程和函数.在Orcale中,LOB分为以下几种类型:BLOB(二进制大对象),CLOB(字符大对象),BFILE(外部存储的二进制文件).大部分LOB操作由此程序包提供.
DBMS_LOB包有如下的一些过程和函数.
APPEND:此过程用于将在src_lob参数中指定的BLOB或CLOB追加到在dest_lob参数中指定的BLOB或CLOB.
COPY:此过程用于从指定的偏移量值开始将源BLOB或CLOB复制到目的BLOB或CLOB. ERASE:此过程用于删除lob_loc指定的BLOB或CLOB中指定偏移量(offset)处开始的指定量(amount)的部分.
GETLENGTH:此函数返回指定的BLOB,CLOB或BFILE的长度,并使用适合于该类型的单位.
INSTR:此函数从LOB数据中查找子串.
READ:此过程从LOB数据中读取指定长度数据到缓存.
SUBSTR:. :此函数从LOB数据中取子串
WHITE:此过程用于将指定数量的数据写入LOB.
以下是一些专门操作BFILE类型文件以进行读取.
FILEOPEN:此过程用于打开BFILE类型文件以进行读取.
FILEGETNAME:此过程用于接受文件定位器值并返回文件目录别名和文件名.
FILECLOSE:此过程用于关闭指定的BFILE.
FILEISOPEN:该函数用于判断BFILE文件是否已打开.
DBMS_LOB程序的使用参见第五章的相关案例)
2.3DBMS_XMLQUERY
DBMS_XMLQUERY包用于将查询结果转换为XML格式。

例10演示了DBMS_XMLQUERY包的用法。

上例声明了一个CLOB数据类型的result变量，以及V ARCHAR2类型的xmlstr和line两个变量。

其中getxml函数用于将查询结果转换为XML格式，SUBSTR函数检索子字符串，32767值指定要从CLOB数据类型读取的最大字节数。

检索到的字符串存储在xmlstr变量中。

LOOP 块循环执行，每次从xmlstr中读取一行。

INSERT函数返回一个字符串在另一个字符串中首次出现的位置。

在此，该函数返回换行符在字符串中的位置，直到遇到下一行。

上述语句的输出结果如下所示：
2.4DBMS_RANDOM
DBMS_RANDOM包可以用来生成随机数，该包的RANDOM函数返回一个8位的随机整数，该整数的正负也是随机的。

例11演示了产生10个1到100得随机数。

例12：
DBMS_RANDON.STRING（参数一，参数二）;
其中：
参数二是指生成随机数的长度。

参数一有以下几种形式：
1）’u’生成的是大写字母
2) ’l’生成的是小写字母
3）’a’生成的是大小写混合
4）’x’生成的是数字和大写字母混合
5）’p’任何形式（连特殊符号都行）;
2.5UTL_FILE
UTL_FILE 包用于从PL/SQL程序中读写操作系统文本文件，如XML文件。

UTL_FILE包对文件的读写过程与编程语言（如C语言）的流操作类似，一般的过程是打开，读或写，关闭。

UTL_FILE包要求文件所在位置是基于ORACLE的DIRECTORY对象指定的目录。

不能直接使用文件的绝对路径。

要使用UTL_FILE包必须先将创建目录对象与操作系统得目录关联起来。

创建目录对象需要有相关的权限，以SYSTEM用户执行如下命令。

上例首先创建了目录对象TEST_DIR关联到服务器上的'C:\DEVELOP文件夹，在使用此目录对象之前必须保证操作系统中已存在此文件夹。

然后，将操作此目录对象的授权SCOTT用户。

以SCOTT用户执行以下命令。

例15通过UTL_FILE将DBMS_XMLQUERY包的查询结果保存到文件中。

例16演示如何从XML文件中读取文本文件。

例16：
总结
触发器是当特定事件出现时自动执行的存储过程
触发器分为DML 触发器、DDL 触发器和数据库级触发器三种类型
DML 触发器的三种类型包括行级触发器、语句级触发器和INSTEAD OF 触发器 一些常用的内置程序包：
⏹DBMS_OUTPUT 包输出PL/SQL 程序的调试信息
⏹DBMS_LOB 包提供操作LOB 数据的子程序
⏹DBMS_XMLQUERY 将查询结果转换为XML 格式
⏹DBMS_RANDOM 提供随机数生成器
⏹UTL_FILE 用于读写操作系统文本文件
一、练习
1使用（）命令可查看在创建触发器时发生的编译错误。

A VIEW ERRORS
B SHOW ERRORS
C DISPLAY ERRORS
D CHECK ERRORS
2（）包用于显示PL/SQL块和存储过程中的调试信息。

A DBMS-OUTPUT
B DBMS-STANDARD
C DBMS-INPUT
D DBMS-SESSION
3（）触发器允许触发操作中的语句访问行的列值。

A 行级
B 语句级
C 模式
D 数据库级
4要审计用户执行的CREA TE、DROP和ALTER等DDL语句，应该创建（）触发器。

A 行级
B 语句级
C INSTEA
D OF
D 模式
E 数据库级
5 Oracle的内置程序包由（）用户所有。

A SYS
B SYSTEM
C SCOTT
D PUBLIC
6（）程序包用于读写操作系统文本文件。

A DBMS-OUTPUT
B DBMS-LOB
C DBMS-RANDOM
D UTL-FILE
二、作业
1创建一个触发器，无论用户插入新记录，还是修改EMP表的JOB列，都将用户指定的JOB 列的值转换成大写。

2 创建一个触发器，禁止用户删除DEPT表中的记录。

提示：创建语句触发器。