第八章

1. 第八章 触发器和内置程序包

2. 回顾 • 子程序是命名的 PL/SQL 块，存储在数据库中，可带参数并可在需要时随时调用 • 有两种类型的PL/SQL子程序，即过程和函数 • 过程用户执行特定的任务，函数用于执行任务并返回值 • 程序包是对相关类型、变量、常量、游标、异常、过程和函数等对象的封装 • 程序包由两部分组成，即包规范和包主体 • 使用程序包的优点是：模块化、更轻松的程序设计、信息隐藏、新增功能以及性能更佳

3. 目标 • 理解和应用触发器 • 了解内置程序包

4. 触发器 • 触发器是当特定事件出现时自动执行的存储过程 • 特定事件可以是执行更新的DML语句和DDL语句 • 触发器不能被显式调用 • 触发器的功能： • 自动生成数据 • 自定义复杂的安全权限 • 提供审计和日志记录 • 启用复杂的业务逻辑

5. 创建触发器的语法 CREATE [OR REPLACE] TRIGGER trigger_name AFTER | BEFORE | INSTEAD OF [INSERT] [[OR] UPDATE [OF column_list]] [[OR] DELETE] ON table_or_view_name [REFERENCING {OLD [AS] old / NEW [AS] new}] [FOR EACH ROW] [WHEN (condition)] pl/sql_block;

6. 触发器的组成部分 3-1 触发器由三部分组成： • 触发器语句（事件） • 定义激活触发器的 DML 事件和 DDL 事件 • 触发器限制 • 执行触发器的条件，该条件必须为真才能激活触发器 • 触发器操作（主体） • 包含一些 SQL 语句和代码，它们在发出了触发器语句且触发限制的值为真时运行

7. 触发器的组成部分3-2 触发器语句 触发器限制 触发器操作 SQL> … FOR EACH ROW WHEN (NEW.empsal>OLD.empsal) DECLARE Sal_diff NUMBER; … SQL> … BEGIN sal_diff:=:NEW.empsal-:OLD.empsal; DBMS_OUTPUT.PUT_LINE(‘工资差额：’sal_diff); END; SQL> CREATE OR REPLACE TRIGGER trig_sal AFTER UPDATE OF empsal ON salary_records … 只有在WHEN子句中的条件得到满足时，才激活trig_sal触发器 如果WHEN子句中的条件得到满足，将执行BEGIN 块中的代码 在更新 emp_sal 列之后激活触发器 为 salary_records表创建 trig-sal触发器

8. 触发器的组成部分 3-3 AFTER 触发器的工作原理 BEFORE 触发器的工作原理 Oracle 数据库 更新 保存更新 更新 激活 表 触发器 表 激活 保存更新 触发器 Oracle 数据库

9. 创建触发器 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; /

10. 触发器类型 6-1 • 触发器的类型有： 触发器类型 模式(DDL) 触发器 数据库级 触发器 DML 触发器 行级触发器 语句级触发器 INSTEAD OF触发器

11. 触发器类型 6-2 • DDL 触发器 • 数据库级触发器 • DML 触发器 • 语句级触发器 • 行级触发器 • INSTEAD OF 触发器 在模式中执行 DDL 语句时执行 在发生打开、关闭、登录和退出数据库等系统事件时执行 在对表或视图执行DML语句时执行 无论受影响的行数是多少，都只执行一次 对DML语句修改的每个行执行一次 用于用户不能直接使用 DML 语句修改的视图

12. 触发器类型 6-3 • 行级触发器 SQL> CREATE TABLE TEST_TRG (ID NUMBER, NAME VARCHAR2(20)); SQL> CREATE SEQUENCE SEQ_TEST; SQL> CREATE OR REPLACE TRIGGER BI_TEST_TRG BEFORE INSERT OR UPDATE OF ID ON TEST_TRG FOR EACH ROW BEGIN IF INSERTING THEN SELECT SEQ_TEST.NEXTVAL INTO :NEW.ID FROM DUAL; ELSE RAISE_APPLICATION_ERROR(-20020, '不允许更新ID值！'); END IF; END; /

13. 触发器类型6-4 • 语句级触发器 SQL> CREATE OR REPLACE TRIGGER trgdemo AFTER INSERT OR UPDATE OR DELETE ON order_master BEGIN IF UPDATING THEN DBMS_OUTPUT.PUT_LINE(‘已更新 ORDER_MASTER 中的数据'); ELSIF DELETING THEN DBMS_OUTPUT.PUT_LINE(‘已删除 ORDER_MASTER 中的数据'); ELSIF INSERTING THEN DBMS_OUTPUT.PUT_LINE(‘已在 ORDER_MASTER 中插入数据'); END IF; END; /

14. 触发器类型6-5 • INSTEAD OF 触发器 SQL> CREATE OR REPLACE TRIGGER upd_ord_view INSTEAD OF UPDATE ON ord_view FOR EACH ROW BEGIN UPDATE order_master SET vencode=:NEW.vencode WHERE orderno = :NEW.orderno; DBMS_OUTPUT.PUT_LINE(‘已激活触发器'); END; /

15. 触发器类型6-6 • 模式触发器 SQL> CREATE TABLE dropped_obj ( obj_name VARCHAR2(30), obj_type VARCHAR2(20), drop_date DATE); SQL> CREATE OR REPLACE TRIGGER log_drop_obj AFTER DROP ON SCHEMA BEGIN INSERT INTO dropped_obj VALUES( ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, SYSDATE); END; /

16. 启用、禁用和删除触发器 • 启用和禁用触发器 • 删除触发器 SQL> ALTER TRIGGER aiu_itemfile DISABLE; SQL> ALTER TRIGGER aiu_itemfile ENABLE; SQL> DROP TRIGGER aiu_itemfile;

17. 查看有关触发器的信息 • 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';

18. 内置程序包 8-1 • 扩展数据库的功能 • 为 PL/SQL 提供对 SQL 功能的访问 • 用户 SYS 拥有所有程序包 • 是公有同义词 • 可以由任何用户访问

19. 内置程序包 8-2 • 一些内置程序包：

20. 内置程序包 8-3 • DBMS_OUTPUT包显示 PL/SQL 块和子程序的调试信息。 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; /

21. 内置程序包 8-4 • DBMS_LOB 包提供用于处理大型对象的过程和函数 • DBMS_XMLQUERY 包用于将查询结果转换为 XML 格式

22. 内置程序包 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; /

23. 内置程序包 8-6 • DBMS_RANDOM包可用来生成随机整数 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; /

24. 内置程序包 8-7 • UTL_FILE 包用于读写操作系统文本文件 • 操作文件的一般过程是打开、读或写、关闭 • UTL_FILE 包指定文件路径依赖于 DIRECTORY 对象 SQL> CREATE DIRECTORY TEST_DIR AS 'C:\DEVELOP'; SQL> GRANT READ, WRITE ON DIRECTORY TEST_DIR TO SCOTT;

25. 内置程序包 8-8 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; /

26. 总结 • 触发器是当特定事件出现时自动执行的存储过程 • 触发器分为 DML 触发器、DDL 触发器和数据库级触发器三种类型 • DML 触发器的三种类型包括行级触发器、语句级触发器和 INSTEAD OF触发器 • 一些常用的内置程序包： • DBMS_OUTPUT 包输出 PL/SQL 程序的调试信息 • DBMS_LOB 包提供操作 LOB 数据的子程序 • DBMS_XMLQUERY将查询结果转换为 XML 格式 • DBMS_RANDOM提供随机数生成器 • UTL_FILE用于读写操作系统文本文件