《触发器和内置程序包.ppt》由会员分享，可在线阅读，更多相关《触发器和内置程序包.ppt（25页珍藏版）》请在三一办公上搜索。
1、第九章,触发器和内置程序包,2,回顾,子程序是命名的 PL/SQL 块，存储在数据库中，可带参数并可在需要时随时调用有两种类型的PL/SQL子程序，即过程和函数过程用户执行特定的任务，函数用于执行任务并返回值程序包是对相关类型、变量、常量、游标、异常、过程和函数等对象的封装程序包由两部分组成，即包规范和包主体使用程序包的优点是：模块化、更轻松的程序设计、信息隐藏、新增功能以及性能更佳,3,目标,理解和应用触发器(重点)-了解内置程序包,4,专业英语,Trigger引发,引起,触发,板机Reference提及,涉及,参考,参考书目,5,触发器,触发器介绍 触发器是一种特殊的存储过程，它在插入，删
2、除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力。触发器的功能：允许/限制对表的修改自动生成派生列，比如自增字段强制数据一致性提供审计和日志记录防止无效的事务处理启用复杂的业务逻辑,6,触发器,触发器语法CREATE OR REPLACE TIGGER 触发器名 触发时间 触发事件 ON表名 BEGIN pl/sql语句 END,指明哪些数据库动作会触发此触发器,触发事件:before-表示在数据库动作之前触发器执行;after-表示在数据库动作之后触发器执行。insert：数据库插入会触发此触发器;update：数据库修改会触发此触发器;delete：
3、数据库删除会触发此触发器。,FOR EACH ROW,7,触发器的组成部分 3-1,触发器由三部分组成：触发器语句（事件）定义激活触发器的 DML 事件和 DDL 事件触发器限制执行触发器的条件，该条件必须为真才能激活触发器触发器操作（主体）包含一些 SQL 语句和代码，它们在发出了触发器语句且触发限制的值为真时运行,8,触发器的组成部分 3-2,SQL CREATE OR REPLACE TRIGGER trig_salAFTER UPDATE OF empsal ON salary_records,触发器语句,为 salary_records 表创建 trig-sal 触发器,在更新 em
4、p_sal 列之后激活触发器,触发器限制,SQL FOR EACH ROWWHEN(NEW.empsalOLD.empsal)DECLARESal_diff NUMBER;,只有在WHEN子句中的条件得到满足时，才激活trig_sal 触发器,触发器操作,SQL BEGIN sal_diff:=:NEW.empsal-:OLD.empsal;DBMS_OUTPUT.PUT_LINE(工资差额：sal_diff);END;,如果WHEN子句中的条件得到满足，将执行BEGIN 块中的代码,9,触发器的组成部分 3-3,Oracle 数据库,更新,表,保存更新,激活,触发器,AFTER 触发器的工作
5、原理,BEFORE 触发器的工作原理,更新,表,激活,触发器,保存更新,Oracle 数据库,10,创建触发器,CREATE OR REPLACE TRIGGER aiu_itemfileAFTER INSERTON itemfileFOR EACH ROWBEGIN IF(:NEW.qty_hand=0)THEN DBMS_OUTPUT.PUT_LINE(警告：已插入记录，但数量为零);ELSE DBMS_OUTPUT.PUT_LINE(已插入记录);END IF;END;/,11,触发器事例讲解,需求系统需要在对EMP雇员表进行插入时都记录日志,即插入(insert)一条雇员记录就要触发写
6、日志事件，在日志表中记录日志。记录内容为：人和时间事例表：EMP(雇员表)、EMP_LOG(日志表),1:Create or replace trigger biud_emp After insert or update or delete On emp Begin Insert into emp_log(Who,when)Values(user,sysdate);End;,2:测试update emp set salary=salary*1.1 where empno=7499,3:是哪条语句起到了作用呢？即insert,update,delete触发了触发呢？我们可以在触发器中使用inse
7、rting,updating,deleting条件谓词做判断,即：if inserting then-elsif updating then-elsif deleting then-end if;,12,思考 我现在要记录是谁在什么时间做了什么操作(删除、新增、修改)。更改emp_log的数据结构:Alter table emp_log add(action varchar2(20);接下来，由同学完成这个实例。,Create or replace trigger biud_emp_copy Before insert or update or delete On employees_copy
8、 Declare L_action employees_log.action%type;Begin if inserting then l_action:=Insert;elsif updating then l_action:=Update;elsif deleting then l_action:=Delete;else raise_application_error(-20001,You should never ever get this error.);Insert into employees_log(Who,action,when)Values(user,l_action,sys
9、date);End;,触发器事例讲解,13,启用和禁用触发器删除触发器,启用、禁用和删除触发器,SQL ALTER TRIGGER aiu_itemfile DISABLE;,SQL ALTER TRIGGER aiu_itemfile ENABLE;,SQL DROP TRIGGER aiu_itemfile;,14,查看有关触发器的信息,SQL SELECT TRIGGER_NAME FROM USER_TRIGGERSWHERE TABLE_NAME=EMP;SQL SELECT TRIGGER_TYPE,TRIGGERING_EVENT,WHEN_CLAUSEFROM USER_TRI
10、GGERSWHERE TRIGGER_NAME=BIU_EMP_DEPTNO;,USER_TRIGGERS 数据字典视图包含有关触发器的信息,15,内置程序包 8-1,扩展数据库的功能为 PL/SQL 提供对 SQL 功能的访问用户 SYS 拥有所有程序包是公有同义词可以由任何用户访问,16,内置程序包 8-2,一些内置程序包：,17,内置程序包 8-3,SQL SET SERVEROUTPUT ONSQL BEGIN DBMS_OUTPUT.PUT_LINE(打印三角形);FOR i IN 1.9 LOOP FOR j IN 1.i LOOP DBMS_OUTPUT.PUT(*);END L
11、OOP for_j;DBMS_OUTPUT.NEW_LINE;END LOOP for_i;END;/,DBMS_OUTPUT包显示 PL/SQL 块和子程序的调试信息。,18,内置程序包 8-4,DBMS_LOB 包提供用于处理大型对象的过程和函数DBMS_XMLQUERY 包用于将查询结果转换为 XML 格式,19,内置程序包 8-5,SQL DECLARE result CLOB;xmlstr VARCHAR2(32767);line VARCHAR2(2000);line_no INTEGER:=1;BEGIN result:=DBMS_XMLQuery.getXml(SELECT e
12、mpno,ename FROM employee);xmlstr:=DBMS_LOB.SUBSTR(result,32767);LOOP EXIT WHEN xmlstr IS NULL;line:=SUBSTR(xmlstr,1,INSTR(xmlstr,CHR(10)-1);DBMS_OUTPUT.PUT_LINE(line_no|:|line);xmlstr:=SUBSTR(xmlstr,INSTR(xmlstr,CHR(10)+1);line_no:=line_no+1;END LOOP;END;/,20,内置程序包 8-6,SQL SET SERVEROUTPUT ONSQL DEC
13、LARE l_num NUMBER;counter NUMBER;BEGIN counter:=1;WHILE counter=10 LOOP l_num:=DBMS_RANDOM.RANDOM;DBMS_OUTPUT.PUT_LINE(l_num);counter:=counter+1;END LOOP;END;/,DBMS_RANDOM 包可用来生成随机整数,21,内置程序包 8-7,SQL CREATE DIRECTORY TEST_DIR AS C:DEVELOP;SQL GRANT READ,WRITE ON DIRECTORY TEST_DIR TO SCOTT;,UTL_FILE
14、 包用于读写操作系统文本文件操作文件的一般过程是打开、读或写、关闭UTL_FILE 包指定文件路径依赖于 DIRECTORY 对象,22,内置程序包 8-8-1,declare src clob;xmlfile utl_file.file_type;length integer;buffer varchar2(32767);begin src:=dbms_xmlquery.getxml(select*from emp);length:=dbms_lob.getlength(src);dbms_lob.read(src,length,1,buffer);xmlfile:=utl_file.fop
15、en(TEST_DIR,employees.xml,w);utl_file.put(xmlfile,buffer);utl_file.fclose(xmlfile);end;/,23,内置程序包 8-8-2,SQL SET SERVEROUTPUT ONSQL DECLARE input_file UTL_FILE.FILE_TYPE;input_buffer VARCHAR2(4000);BEGIN input_file:=UTL_FILE.FOPEN(TEST_DIR,employees.xml,r);LOOP UTL_FILE.GET_LINE(input_file,input_buff
16、er);DBMS_OUTPUT.PUT_LINE(input_buffer);END LOOP;UTL_FILE.FCLOSE(input_file);EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(-);END;/,24,总结,触发器是当特定事件出现时自动执行的存储过程 触发器分为 DML 触发器、DDL 触发器和数据库级触发器三种类型DML 触发器的三种类型包括行级触发器、语句级触发器和 INSTEAD OF 触发器一些常用的内置程序包：DBMS_OUTPUT 包输出 PL/SQL 程序的调试信息DBMS_LOB 包提供操作 LOB 数据的子程序DBMS_XMLQUERY 将查询结果转换为 XML 格式DBMS_RANDOM 提供随机数生成器UTL_FILE 用于读写操作系统文本文件,25,作业,本章课后作业第2题,
