触发器

内置程序包
• DBMS_LOB 包提供用于处理大型对象的过程和 函数
• DBMS_XMLQUERY 包用于将查询结果转换为 XML 格式
DBMS_LOB
--创建表 create table lob_table ( key_value int primary key, f_lob Bfile );
变异表
• 变异表是触发器触发源正在次修改的表或被修改者的级联表， 如果行级触发器的执行语句修改的表是变异表中的记录，会 导致一个死循环，系统会自动检测并抛出异常
• 例：如果修改学生性别时，就修改姓名
create or replace trigger stuinfo_trig before update of stusex –当修改列stusex触发 on stuinfo for each row begin
BEGIN result := DBMS_XMLQuery.getXml(‘SELECT empno, ename FROM emp’);--将查询结果转为XML格式，返回结果为长文本类型 xmlstr := DBMS_LOB.SUBSTR(result,32767);--对长文本截取为字符串 LOOP EXIT WHEN xmlstr IS NULL;
• DBMS_RANDOM .string(‘x’ , 10);
• DBMS_RANDOM.random();
DBMS_RANDOM
- -产生20个随机数 declare
num int; str varchar2(20); begin for i in 1..20 loop
str:=dbms_random.string('x', 10); num:=dbms_random.random( ); dbms_output.put_line(str || ',‘ || num); end loop; end; /
或（）语句时触发的存储过程。（选一项） • A delete • B drop • C create • D truncate
答案：da
替代触发器
• 替代触发器被激发来代替执行修改视图的DML语句，不执行 语句本身，只执行触发器的内容
• 视图如下(三表联接):
• 如果向视图插入数据，会怎样？
insert into info_mark_view values(5, ‘星星’, ’女’, ’11-11月-1981’, 4, 'J2EE', 90);
内置程序包 8-1
• 扩展数据库的功能 • 为 PL/SQL 提供对 SQL 功能的访问 • 用户 SYS 拥有所有程序包 • 是公有同义词 • 可以由任何用户访问
内置程序包 8-2
• 一些内置程序包：
程序包名称
说明
DBMS_OUTPUT DBMS_LOB DBMS_XMLQUERY DBMS_RANDOM UTL_FILE
计、信息隐藏、新增功能以及性能更佳
触发器
• 触发器是当特定事件出现时自动执行的存储过程 • 触发器不能被显式调用（潜台词：只能通过被指定
事件触发然后被隐式调用）
触发器的分类
• 触发器主要分为数据库（DDL）触发器和DML触发器
– 数据库触发器：对数据库对象（如表或试图）进行创建、 删除和修改的时候触发，例如drop table create table alter table等。
触发事：被什么事件触发的？如增删改等DML操作
触发时机：前置(before)或后置(after)或代替(instead of) 如：是执行增删改操作之前触发，还是执行之后触发，还是 代替触发（不让该操作执行，而执行别的动作，通常涉及 到多表时候才使用）
触发动作：触发器被触发时所执行的动作
认证考试习题
insert into stuinfo values(:new.stuid, :new.stuname, :new.stusex, :new.stuBirth);
insert into subinfo values(:new.subid,:new.subname); insert into markinfo values(:new.stuid,:new.subid,:new.score); end; /
update stuinfo set stuname='qq'; end; /
Update stuinfo set stusex=‘女’ Where stuid = 1
触发器的管理
• 将单个触发器禁用或启用 ALTER TRIGGER 触发器名称 DISABLE | ENABLE • 将一个表上的所有触发器禁用或启用 ALTER TABLE 表名 DISABLE | ENABLE ALL TRIGGER
• 可以使用替代触发器解决
前置和后置触发器
create or replace trigger info_mark_trig instead of - -替代触发器，只执行触发器，不执行语句本身 insert or update on info_mark_view for each row begin
length:=dbms_lob.getlength(nfile); dbms_output.put_line('长度是:'||length); else dbms_output.put_line('文件不存在!'); end if; end; /
DBMS_XMLQUERY
DECLARE result CLOB; xmlstr VARCHAR2(32767); line VARCHAR2(2000); line_no INTEGER := 1;
处理PL/SQL块和子程序输出调试信息 提供对 LOB数据类型进行操作的功能 提供将数据转换为 XML 类型的功能 提供随机数生成器 用 PL/SQL 程序来读写操作系统文本文件
内置程序包 8-3
• DBMS_OUTPUT包显示 PL/SQL 块和子程序的调试信息。 • 示例：打印9行的等腰直角三角形
答案：DC
认证考试习题
• 有关ORACLE的INSTEAD OF触发器说法正确的是（选一项） • A 替代触发器的触发源可以是表 • B 替代触发器可以后置触发 • C 替代触发器就是用触发器执行部分替换DML语句的操作 • D 替代触发器仍然会执行DML语句 • 在Oracle中，INSTEAD OF触发器主要用于（选一项） • A表 • B 表和视图 • C 基于单个表的视图 • D 基于多个表的视图
xmlstr := SUBSTR(xmlstr,INSTR(xmlstr,CHR(10))+1); line_no := line_no + 1; END LOOP; END; /
DBMS_RANDOM
• DBMS_RANDOM 包可用来生成随机整数
函数名 Initialize( Integer )
函数说明 设定随机数生成器的种子并重新初始化
Seed( Integer )
设定随机数生成器的种子
Random( )
生成随机的整数
String(char, len )
返回一个随机生成的字符串
‘a’, ‘A’ 随机字符串大写 ‘l’, ’L’ 随机字符串小写 ‘u’, ‘U’ 随机大写字符串 ‘x’, ‘X’ 任意字符串
SQL> SET SERVEROUTPUT ON SQL> BEGIN
DBMS_OUTPUT.PUT_LINE('打印三角形'); FOR i IN 1..9 LOOP
FOR j IN 1..i LOOP DBMS_OUTPUT.PUT('*');
END LOOP for_j; DBMS_OUTPUT.NEW_LINE(); END LOOP for_i; END; /
--对xmlstr进行截取，截取的长度为第一个换行符的位置减1
line := SUBSTR(xmlstr,1,INSTR(xmlstr,CHR(10))-1); DBMS_OUTPUT.PUT_LINE(line_no || ':' || line);
--将第一个换行符后面的字符串截取下来赋给xmlstr
答案：cd
认证考试习题
• 在Oracle中，关于触发器的描述正确的是（选一项） • A 触发器可以删除，但不能禁用 • B 触发器只能用于表 • C 触发器可以分为行级和语句级 • D 触发器是一个对关联表发出insert、update或delete语句时触发的存
储过程 • 在Oracle中，数据库中的触发器是一个对关联表发出insert、update
UTL_FILE
SQL> SET SERVEROUTPUT ON SQL> DECLARE
– DML触发器：对数据进行操作时进行触发，主要是插入、 修改、删除操作。
DML触发器的分类
• DML触发器分类：
行级触发器(修改了几行的数据就触发几次，只能用于修 改)
语句触发器(不管修改几行数据都只触发一次)(默认就时语 句，即如果不加关键字就是语句级)
触发器的基本要素
触发源：是什么对象被触发？例如表或视图
答案：AC
认证考试习题
• 要审计用户执行的create,drop,和alter等ddl语句，应创建( )触发器。 （选一项）
• A 行级 • B 语句级 • C 模式 • D 数据库 • 有关ORACLE触发器的说法正确的是（选一项） • A 可以通过手工的方式激发触发器 • B 停用触发器的唯一方法是删除触发器 • C 行级触发器是在修改每条记录时都触发一次 • D 在行级触发器中可以使用deleted和inserted
DBMS_LOB
--判断文件是否存在并读取文件的长度 declare
nfile bfile; length number; begin select f_lob into nfile from lob_table where key_value=1; if dbms_lob.fileexists(nfile)=1 then
第八章
触发器
回顾
• 子程序是命名的 PL/SQL 块，存储在数据库中， 可带参数并可在需要时随时调用
• 有两种类型的PL/SQL子程序，即过程和函数 • 过程用户执行特定的任务，函数用于执行任务并
返回值 • 程序包是对相关类型、变量、常量、游标、异常、
过程和函数等对象的封装 • 程序包由两部分组成，即包规范和包主体 • 使用程序包的优点是：模块化、更轻松的程序设
UTL_FILE
• UTL_FILE 包用于读写操作系统文本文件 • 操作文件的一般过程是打开、读或写、关闭 • UTL_FILE 包指定文件路径依赖于 DIRECTORY
对象
SQL> CREATE DIRECTORY TEST_DIR AS 'C:\DEVELOP'; SQL> GRANT READ, WRITE ON DIRECTORY TEST_DIR TO SCOTT;
• ( )触发器允许触发操作的语句访问行的列值。（选一项） • A 行级 • B 语句级 • C 模式 • D 数据库级 • 如果希望执行某操作时，该操作不执行，而是执行另一个操作，那么
可是使用什么方式来完成（选一项） • A before触发器 • B after触发器 • C instead of触发器 • D undo触发器
---创建目录，目录名称必须大写 CREATE DIRECTORY TEST_DIR AS 'd:\test';
--插入数据 insert into lob_table values(1,bfilename('TEST_DIR','person.xml')); insert into lob_table values(2,bfilename('TEST_DIR','pic.gif'));
• USER_TRIGGERS 数据字典视图包含有关触发器的信息
SQL> SELECT TRIGGER_NAME FROM USER_TRIGGERS WHERE TABLE_NAME='EMP';
SQL> SELECT TRIGGER_TYPE, TRIGGERING_EVENT, WHEN_CLAUSE FROM USER_TRIGGERS WHERE TRIGGER_NAME = 'BIU_EMP_DEPTNO';