Oracle 教程8 触发器和内置程序包

1、ACCP V4.0第八章第八章触发器和内置程序包ACCP V4.02回顾q子程序是命名的 PL/SQL 块，存储在数据库中，可带参数并可在需要时随时调用q有两种类型的PL/SQL子程序，即过程和函数q过程用户执行特定的任务，函数用于执行任务并返回值q程序包是对相关类型、变量、常量、游标、异常、过程和函数等对象的封装q程序包由两部分组成，即包规范和包主体q使用程序包的优点是：模块化、更轻松的程序设计、信息隐藏、新增功能以及性能更佳ACCP V4.03目标q理解和应用触发器q了解内置程序包ACCP V4.04触发器q触发器是当特定事件出现时自动执行的存储过程q特定事件可以是执行更新的DML语句和D

2、DL语句q触发器不能被显式调用q触发器的功能：q自动生成数据q自定义复杂的安全权限q提供审计和日志记录q启用复杂的业务逻辑ACCP V4.05创建触发器的语法CREATE OR REPLACE TRIGGER trigger_nameAFTER | BEFORE | INSTEAD OFINSERT OR UPDATE OF column_list OR DELETEON table_or_view_nameREFERENCING OLD AS old / NEW AS newFOR EACH ROWWHEN (condition)pl/sql_block;ACCP V4.06触发器的组成部分

3、 3-1触发器由三部分组成：q触发器语句（事件）q定义激活触发器的 DML 事件和 DDL 事件q触发器限制q执行触发器的条件，该条件必须为真才能激活触发器q触发器操作（主体）q包含一些 SQL 语句和代码，它们在发出了触发器语句且触发限制的值为真时运行ACCP V4.07触发器的组成部分 3-2 SQL CREATE OR REPLACE TRIGGER trig_salAFTER UPDATE OF empsal ON salary_records触发器语句为 salary_records 表创建 trig-sal 触发器在更新 emp_sal 列之后激活触发器触发器限制SQL FOR E

4、ACH 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 块中的代码ACCP V4.08触发器的组成部分 3-3Oracle 数据库更新表表保存更新激活触发器触发器AFTER 触发器的工作原理BEFORE 触发器的工作原理更新表表激活触发器触发

5、器保存更新Oracle 数据库ACCP V4.09创建触发器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;/ACCP V4.010触发器类型 6-1q触发器的类型有：触发器类型模式(DDL)触发器DML触发器数据库级触发器语句级触发器行级触发器INSTEAD OF触

6、发器ACCP V4.011触发器类型 6-2q DDL 触发器q 数据库级触发器q DML 触发器q 语句级触发器q 行级触发器q INSTEAD OF 触发器在模式中执行 DDL 语句时执行在发生打开、关闭、登录和退出数据库等系统事件时执行在对表或视图执行DML语句时执行无论受影响的行数是多少，都只执行一次对DML语句修改的每个行执行一次用于用户不能直接使用 DML 语句修改的视图ACCP V4.012触发器类型 6-3q行级触发器SQL CREATE TABLE TEST_TRG (ID NUMBER, NAME VARCHAR2(20);SQL CREATE SEQUENCE SEQ_T

7、EST;SQL CREATE OR REPLACE TRIGGER BI_TEST_TRGBEFORE INSERT OR UPDATE OF IDON TEST_TRGFOR EACH ROWBEGIN IF INSERTING THEN SELECT SEQ_TEST.NEXTVAL INTO :NEW.ID FROM DUAL; ELSE RAISE_APPLICATION_ERROR(-20020, 不允许更新ID值！); END IF;END;/ACCP V4.013触发器类型 6-4SQL CREATE OR REPLACE TRIGGER trgdemo AFTER INSERT

8、 OR UPDATE OR DELETE ON order_masterBEGIN 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;/q语句级触发器ACCP V4.014触发器类型 6-5SQL CREATE OR REPLACE TRIG

9、GER 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; /qINSTEAD OF 触发器ACCP V4.015触发器类型 6-6SQL CREATE TABLE dropped_obj ( obj_name VARCHAR2(30), obj_type VARCHAR2(20), drop_date DA

10、TE);SQL CREATE OR REPLACE TRIGGER log_drop_objAFTER DROP ON SCHEMABEGIN INSERT INTO dropped_obj VALUES( ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, SYSDATE);END;/q模式触发器ACCP V4.016q启用和禁用触发器q删除触发器启用、禁用和删除触发器SQL ALTER TRIGGER aiu_item;SQL ALTER TRIGGER aiu_item;SQL DROP TRIGGER aiu_itemfile;ACCP V4.017查看有关触发

11、器的信息SQL SELECT TRIGGER_NAME FROM USER_TRIGGERSWHERE TABLE_NAME=EMP;SQL SELECT TRIGGER_TYPE, TRIGGERING_EVENT, WHEN_CLAUSEFROM USER_TRIGGERSWHERE TRIGGER_NAME = BIU_EMP_DEPTNO;qUSER_TRIGGERS 数据字典视图包含有关触发器的信息ACCP V4.018内置程序包 8-1q扩展数据库的功能q为 PL/SQL 提供对 SQL 功能的访问q用户 SYS 拥有所有程序包q是公有同义词q可以由任何用户访问ACCP V4.01

12、9内置程序包 8-2q一些内置程序包：程序包名称程序包名称说明说明STANDARD和DBMS_STANDARD定义和扩展PL/SQL语言环境DBMS_LOB提供对 LOB数据类型进行操作的功能DBMS_OUTPUT处理PL/SQL块和子程序输出调试信息DBMS_RANDOM提供随机数生成器DBMS_SQL允许用户使用动态 SQLDBMS_XMLDOM用DOM模型读写XML类型的数据DBMS_XMLPARSERXML解析，处理XML文档内容和结构DBMS_XMLQUERY提供将数据转换为 XML 类型的功能DBMS_XSLPROCESSOR提供XSLT功能，转换XML文档UTL_FILE用 PL

13、/SQL 程序来读写操作系统文本文件ACCP V4.020内置程序包 8-3SQL SET SERVEROUTPUT ONSQL 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;/qDBMS_OUTPUT包显示 PL/SQL 块和子程序的调试信息。ACCP V4.021内置程序包 8-4qDBMS_LOB 包提供用于处理大型对象的过程和函数qDBMS

14、_XMLQUERY 包用于将查询结果转换为 XML 格式ACCP V4.022内置程序包 8-5SQL 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(x

15、mlstr,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;/ACCP V4.023内置程序包 8-6SQL SET SERVEROUTPUT ONSQL DECLARE l_num NUMBER; counter NUMBER;BEGIN counter:=1; WHILE counter CREATE DIRECTORY TEST_

16、DIR AS C:DEVELOP;SQL GRANT READ, WRITE ON DIRECTORY TEST_DIR TO SCOTT;qUTL_FILE 包用于读写操作系统文本文件q操作文件的一般过程是打开、读或写、关闭qUTL_FILE 包指定文件路径依赖于 DIRECTORY 对象ACCP V4.025内置程序包 8-8SQL SET SERVEROUTPUT ONSQL DECLARE input_file UTL_; input_buffer VARCHAR2(4000);BEGIN input_ UTL_( TEST_DIR, employees.xml, r); LOOP UTL_(input_); DBMS_OUTPUT.PUT_LINE(input_buffer); END LOOP; UTL_(input_file);EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(-);E