触发器和内置程序包.ppt

第九章,触发器和内置程序包,2,回顾,子程序是命名的 PL/SQL 块，存储在数据库中，可带参数并可在需要时随时调用 有两种类型的PL/SQL子程序，即过程和函数 过程用户执行特定的任务，函数用于执行任务并返回值 程序包是对相关类型、变量、常量、游标、异常、过程和函数等对象的封装 程序包由两部分组成，即包规范和包主体 使用程序包的优点是：模块化、更轻松的程序设计、信息隐藏、新增功能以及性能更佳,3,目标,理解和应用触发器(重点) - 了解内置程序包,4,专业英语,Trigger 引发, 引起, 触发,板机 Reference 提及, 涉及, 参考, 参考书目,5,触发器,触发器介绍 触发器是一种特殊的存储过程，它在插入，删除或修改特定 表中的数据时触发执行，它比数据库本身标准的功能有更精细 和更复杂的数据控制能力。 触发器的功能： 允许/限制对表的修改 自动生成派生列，比如自增字段 强制数据一致性 提供审计和日志记录 防止无效的事务处理 启用复杂的业务逻辑,6,触发器,触发器语法 CREATE OR REPLACE TIGGER 触发器名 触发时间 触发事件 ON表名 BEGIN pl/sql语句 END,指明哪些数据库动作会触发此触发器,触发事件: before-表示在数据库动作之前触发器执行; after-表示在数据库动作之后触发器执行。 insert：数据库插入会触发此触发器; update：数据库修改会触发此触发器; delete：数据库删除会触发此触发器。,对表的每一行触发器执行一次.如果没有这一选项 ,则只对整个表执行一次,FOR EACH ROW,7,触发器的组成部分 3-1,触发器由三部分组成： 触发器语句（事件） 定义激活触发器的 DML 事件和 DDL 事件 触发器限制 执行触发器的条件，该条件必须为真才能激活触发器 触发器操作（主体） 包含一些 SQL 语句和代码，它们在发出了触发器语句且触发限制的值为真时运行,8,触发器的组成部分 3-2,SQL CREATE OR REPLACE TRIGGER trig_sal AFTER UPDATE OF empsal ON salary_records ,触发器语句,为 salary_records 表创建 trig-sal 触发器,在更新 emp_sal 列之后激活触发器,触发器限制,SQL FOR EACH ROW WHEN (NEW.empsalOLD.empsal) DECLARE Sal_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 触发器的工作原理,BEFORE 触发器的工作原理,更新,表,激活,触发器,保存更新,Oracle 数据库,10,创建触发器,CREATE OR REPLACE TRIGGER aiu_itemfile AFTER INSERT ON itemfile FOR EACH ROW BEGIN IF (:NEW.qty_hand = 0) THEN DBMS_OUTPUT.PUT_LINE(警告：已插入记录，但数量为零); ELSE DBMS_OUTPUT.PUT_LINE(已插入记录); END IF; END; /,11,触发器事例讲解,需求 系统需要在对EMP雇员表进行插入时都记录日志, 即插入(insert)一条雇员记录就要触发写日志事件 ，在日志表中记录日志。记录内容为：人和时间 事例表：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触发了触发呢？我们可以在触发器中使用inserting,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 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,sysdate); 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_TRIGGERS WHERE TABLE_NAME=EMP; SQL SELECT TRIGGER_TYPE, TRIGGERING_EVENT, WHEN_CLAUSE FROM USER_TRIGGERS WHERE TRIGGER_NAME = BIU_EMP_DEPTNO;,USER_TRIGGERS 数据字典视图包含有关触发器的信息,15,内置程序包 8-1,扩展数据库的功能 为 PL/SQL 提供对 SQL 功能的访问 用户 SYS 拥有所有程序包 是公有同义词 可以由任何用户访问,16,内置程序包 8-2,一些内置程序包：,17,内置程序包 8-3,SQL SET SERVEROUTPUT ON SQL BEGIN DBMS_OUTPUT.PUT_LINE(打印三角形); FOR i IN 19 LOOP FOR j IN 1i LOOP DBMS_OUTPUT.PUT(*); END LOOP 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 empno, 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 ON SQL DECLARE 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 包用于读写操作系统文本文件 操作文件的一般过程是打开、读或写、关闭 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.fopen(TEST_DIR,employees.xml,w); utl_file.put(xmlfile,buffer); utl_file.fclose(xmlfile); end; /,23,内置程序包 8-8-2,SQL SET SERVEROUTPUT ON SQL 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_buffer); 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,总结,触