下载文档原格式
Oracle 第8章触发器、内置程序包1、技术目标∙应用触发器∙使用内置程序包2、什么是触发器∙触发器是当特定事件出现时自动执行的存储过程∙特定事件可以是执行更新的DML语句和DDL语句∙触发器不能被显式调用，存储过程可以显示调用触发器的功能有：∙自动生成数据∙自定义复杂的安全权限∙提供审计和日志记录∙启用复杂的业务逻辑触发器可以与特定的表或视图相关联，用于检查对表/视图所做的数据修改，当执行insert、delete、update语句时，可激活触发器代码3、如何创建触发器创建触发器的语法为：CREATE [OR REPLACE] TRIGGER trigger_name {AFTER | BEFORE | INSTEAD OF}{insert | delete | update [OF column[, column] ...]} [OR {insert | delete | update [OF column[, column] ...] }] ON[schema.]table_or_view_name[REFERENCING [NEW AS new_row_name] [OLD ASold_row_name]][FOR EACH ROW][WHEN (condition)][DECLAREvariable_declation]BEGINstatements;[EXCEPTIONexception_handlers]END [trigger_name];语法说明：AFTER | BEFORE，指在事件发生之前或之后激活触发器INSTEAD OF，表示可以执行触发器代码来代替导致触发器调用的事件insert | delete | update，指定构成触发器事件的数据操纵类型，update可指定列列表REFERENCING，指定新行(即将更新)和旧行(更新前)的其他名称，默认为NEW和OLDtable_or_view_name，指要创建触发器的表或视图的名称FOR EACH ROW，指定是否对受影响的每行都执行触发器，即行级触发器，如不使用此句，则为语句级触发器WHEN，限制执行触发器的条件，该条件可包括新旧数据值的检查DECLARE...END，一个标准的PL/SQL块使用：在Emp表创建触发器，Sql代码1.CREATE OR REPLACE TRIGGER biu_emp_deptno2.--在添加或修改deptNo字段之前触发3.BEFORE INSERT OR UPDATE OF deptNo4.ON Emp5.--行级触发器6.FOR EACH ROW7.--列deptNo的新值不等于408.WHEN (New.deptNo <> 40)9.BEGIN10.--将comm列设置为011.:m := 0;12.END;13./注意：使用SHOW ERRORS命令可查看创建触发器时出现的错误4、触发器的组成部分触发器由以下3个部分组成：触发语句，定义激活触发器的DML 事件和DDL 事件，如：BEFORE INSERT OR UPDATE OF deptNoON Emp--行级触发器FOR EACH ROW这段代码表示，当对Emp表执行insert语句或对Emp表的deptNo列执行update语句时，触发器会在受影响的每一行上执行一次触发限制，执行触发器的条件，该条件必须为真才能激活触发器，如：--列deptNo的新值不等于40，触发器会执行WHEN (New.deptNo <> 40)触发操作，一些SQL 语句和代码，在发出了触发器语句且触发限制的值为真时运行，如：BEGIN--将comm列设置为0:m := 0;END;5、触发器的类型及使用触发器有如下的类型：每种触发器的作用：使用1：应用行级触发器，Sql代码1.--创建表TEST_TRG2.CREATE TABLE TEST_TRG (ID NUMBER, NAME VARCHAR2(20));3.--创建序列SEQ_TEST4.CREATE SEQUENCE SEQ_TEST;5.--为TEST_TRG表创建行级触发器6.CREATE OR REPLACE TRIGGER BI_TEST_TRG7.--在insert(添加)或者update(修改)ID字段时触发8.BEFORE INSERT OR UPDATE OF ID9.ON TEST_TRG --指定TEST_TRG表10.FOR EACH ROW --设置为行级触发器11.--触发器语句部分12.BEGIN13.--判断是不是insert语句14.IF INSERTING THEN15.--如果是insert操作，将序列的值设置给ID列16.SELECT SEQ_TEST.NEXTVAL INTO :NEW.ID FROM DUAL;17.ELSE18.--如果不是insert操作，不能修改ID列的值19.RAISE_APPLICATION_ERROR(-20020, '不允许更新ID值！');20.END IF;21.END;22./注意：如果一个触发器由多种语句触发，可用INSERTING、UPDATING、DELETING这些关键字进行检查，对应语句类型使用2：应用语句级触发器，Sql代码1.CREATE OR REPLACE TRIGGER trgdemo2.AFTER INSERT OR UPDATE OR DELETE3.ON order_master4.BEGIN5.--根据语句类型输出信息6.IF UPDATING THEN7.DBMS_OUTPUT.PUT_LINE('已更新ORDER_MASTER中的数据');8.ELSIF DELETING THEN9.DBMS_OUTPUT.PUT_LINE('已删除ORDER_MASTER中的数据');10.ELSIF INSERTING THEN11.DBMS_OUTPUT.PUT_LINE('已在ORDER_MASTER中插入数据');12.END IF;13.END;14./注意：语句级触发器时CREATE TRIGGER命令所创建触发器的默认类型使用3：应用INSTEAD OF触发器，同时向两个表中插入值，Sql代码1.--创建视图2.CREATE VIEW ord_view AS3.SELECT order_master.orderno, order_master.ostatus,4.order_detail.qty_deld, order_detail.qty_ord5.FROM order_master, order_detail6.WHERE order_master.orderno = order_detail.orderno;7.8.--创建INSTEAD OF触发器9.CREATE OR REPLACE TRIGGER order_mast_insert10.INSTEAD OF UPDATE ON ord_view11.--为NEW关键字取别名n12.REFERENCING NEW AS n13.FOR EACH ROW14.DECLARE15.--定义游标，访问order_master表16.CURSOR ecur IS SELECT * FROM order_master17.WHERE order_master.orderno = :n.orderno;18.--定义游标，访问order_detail表19.CUSEOR dcur IS20.select * from order_detail21.WHERE order_detail.orderno = :n.orderno;22.--定义游标变量23.a ecur%ROWTYPE;24.b dcur%ROWTYPE;25.BEGIN26.--打开游标27.OPEN ecur;28.OPEN dcur;29.--读取行30.FETCH ecur into a;31.FETCH dcur into b;32.--判断是否有行33.IF dur%NOTFOUND THEN --没有34.--添加记录35.INSERT INTO order_master (orderno, ostatus)36.VALUES (:n.orderno, :n.ostatus);37.ELSE --有38.--修改记录39.UPDATE order_master SET order_master.ostatus = :n.ostatus40.WHERE order_master.orderno = :n.orderno;41.END IF;42.43.IF ecur%NOTFOUND THEN44.INSERT INTO order_detail (qty_ord, qty_deld, orderno)45.VALUES(:n.qty_ord, :n.qty_deld, :n.orderno);46.ELSE47.UPDATE order_detail SET48.order_detail.qty_ord = :n.qty_ord,49.order_detail.qty_deld = :n.qty_deld50.WHERE order_detail.orderno = :n.orderno;51.END IF;52.--关闭游标53.CLOSE ecur;54.CLOSE dcur;55.END;56./注意：使用INSTEAD OF触发器有如下的限制，∙只能在行级使用，不能在语句级使用∙只能应用于视图，不能应用于表使用4：应用模式(DDL)触发器，对用户删除的对象进行日志记录，创建模式触发器的语法为：CREATE OR REPLACE TRIGGER trigger_name {BEFORE | AFTER} trigger_eventON [schema.]SCHEMAWHEN (trigger_condition)trigger_body;Sql代码1.--创建日志记录表2.CREATE TABLE dropped_obj3.(4.obj_name VARCHAR2(30),5.obj_type VARCHAR2(20),6.drop_date DATE7.);8.--创建触发器9.CREATE OR REPLACE TRIGGER log_drop_obj10.--在执行drop语句后触发11.AFTER DROP ON SCHEMA12.BEGIN13.--将被删除对象的信息添加到日志记录表中14.INSERT INTO dropped_obj15.VALUES (ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, SYSDATE);16.END;17./使用5：应用数据库级触发器，在数据库启动后执行，CREATE OR REPLACE TRIGGER system_startup--系统启动时触发AFTER STARTUP ON DATEBASEBEGIN--加入所需代码END;/6、启动、禁用、删除触发器，查看触发器信息启用和禁用触发器：ALTER TRIGGER 触发器名DISABLE; --禁用ALTER TRIGGER 触发器名ENABLE; --启用删除触发器：DROP TRIGGER 触发器名;查看触发器信息，使用USER_TRIGGERS数据字典：使用1：查看为表EMP设置的触发器名select TRIGGER_NAME from USER_TRIGGERSWHERE TABLE_NAME = 'EMP';使用2：查看触发器BIU_EMP_DEPTNO的类型、触发事件、触发条件，select TRIGGER_TYPE, TRIGGERING_EVENT, WHEN_CLAUSE from USER_TRIGGERSWHERE TRIGGER_NAME = 'BIU_EMP_DEPTNO';7、内置程序包Oracle提供了许多内置程序包，用于扩展数据库功能，数据库用户SYS 拥有所有程序包，程序包被定义为公有同义词，并将执行权限授予了PUBLIC用户组，任何用户都可访问，部分内置程序包如下：8、总结∙触发器是当特定事件出现时自动执行的存储过程∙触发器分为DML 触发器、DDL 触发器和数据库级触发器三种类型∙DML触发器的三种类型为行级触发器、语句级触发器和INSTEAD OF触发器∙了解一些常用的内置程序包。
触发器触发器是当特定事件出现时自动执行的存储过程特定事件可以是执行更新的DML语句和DDL语句触发器不能被显式调用❑触发器的功能：❑自动生成数据❑自定义复杂的安全权限❑提供审计和日志记录❑启用复杂的业务逻辑Oracle触发器类型：行级触发器对DML语句影响的每一行执行一次语句级触发器对每个DML语句只执行一次instead of 触发器用于用户不能直接使用DML 语句修改的视图一般用于连接视图模式级触发器DDL语句触发数据库级触发器在发生打开、关闭、登录和退出数据库等系统事件时执行注意事项：不能有参数不能包含事务控制不能声明long和blob clob变量/********************行级触发器******************/--对DML语句影响的每一行执行一次create [or replace] trigger trigger_namebefore|after--触发时机insert [or delete or update [of column_name,....]] --触发条件insert或update或delete on table_name[referencing NEW as别名OLD as别名 ] –-NEW OLD表示当前行的新值和旧值for each row [when (condition)]-- for each row指定为行级触发器 [declare.....]begin....end [trigger_name];说明：:NEW --新行:OLD --旧行before after 在触发事件之前和之后--after insert触发器不允许插入值例1：create or replace trigger trig_onebeforeinsert or update of sal on empreferencing new as mynew old as myoldfor each rowwhen (mynew.sal<0)begin:mynew.sal:=0;dbms_output.put_line('行级触发器触发....');end;--测试触发器update emp set sal=-100where empno in(7369,7499);条件谓词:insertingdeletingupdating例2：create table test_trig(myid number,myname varchar2(20));create sequence seq_trig;create or replace trigger trig_twobeforeinsert or update of myid on test_trigfor each rowbeginif inserting thenselect SEQ_TRIG.Nextval into :new.myid from dual;elseraise_application_error(-20001,'MYID列不允许更新');end if;end;--测试触发器insert into test_trig values(0,'aaa');insert into test_trig values(0,'bbb');insert into test_trig values(0,'ddd');commit;update test_trig set myid=3where myname='aaa';例3：--为test_trig2创建一个用于统计的触发器create table test_trig2(product_id varchar2(20),quantity number(3),price number(6,2),total number(8,2));create or replace trigger trig_threebeforeinsert or update of quantity,price on test_trig2for each rowbegin:new.total:=:new.quantity * :new.price;end;insert into test_trig2(product_id,quantity,price) values(100,5,190.5); commit;--测试触发器update test_trig2set quantity=8where product_id=100;commit;/*******************语句级触发器*****************/对每个DML语句只执行一次create or replace trigger trig_namebefore|afterinsert [or delete or update]on table_namedeclare .....begin ....end [trigger_name];例：create table test_emp_log(mywho varchar2(20),mywhen date,action varchar2(20));create or replace trigger trig_emp_log after insert or delete or updatedeclaremy_action test_emp_log.action%type;beginif inserting thenmy_action:='插入';elsif updating thenmy_action:='更新';elsif deleting thenmy_action:='删除';end if;insert into test_emp_log values(user,sysdate,my_action); end;--测试触发器update emp set sal=1000where empno=7369;/****************模式级触发器*****************/ --主要用于DDL语句(create,alter,drop)--主要用途:阻止DDL操作及为DDL提供安全监控,记录日志等create or replace trigger trigger_namebefore|after DDL...on schema[declare.....]begin....end [trigger_name];例1：create or replace trigger tigg_ddlbefore drop on scott.schemabeginif ora_dict_obj_name='EMP'then --ora_dict_obj_name()事件属性函数返回DDL语句的目标对象名raise_application_error(-20008,'emp不允许删除');end if;end;例2：create table dropped_obj(obj_name varchar2(30),--对象名称obj_type varchar2(20),--对象类型drop_date date --删除时间);create or replace trigger trig_drop_objafter drop on scott.schemabegininsert into dropped_objvalues(ora_dict_obj_name,ora_dict_obj_type,sysdate); end trig_drop_obj;-- ora_dict_obj_name()事件属性函数返回DDL语句的目标对象名-- ora_dict_obj_type()事件属性函数返回DDL语句的目标对象类型--测试触发器create table for_drop(id number);drop table for_drop;select * from dropped_obj;/****************instead of触发器*****************/ instead of 触发器定义在视图上的触发器一般用于不能直接使用DML语句修改数据的连接视图使用限制：只能是行级触发器只应用于视图--连接视图create or replace view v_emp_deptasselect e.empno,e.ename,e.sal,d.deptno,d.dname from emp einner join dept d on e.deptno=d.deptno;--instead of触发器create or replace trigger trig_insteadinstead of insert or update on v_emp_deptfor each rowdeclarecursor c_emp is select * from emp where empno=:new.empno;cursor c_dept is select * from dept where deptno=:new.deptno;v_emp emp%rowtype;v_dept dept%rowtype;beginopen c_dept;fetch c_dept into v_dept;if c_dept%notfound theninsert into dept(deptno,dname) values(:new.deptno,:new.dname);elseupdate dept set dname=:new.dname where deptno=:new.deptno;end if;close c_dept;open c_emp;fetch c_emp into v_emp;if c_emp%notfound theninsert into emp(empno,ename,sal)values(:new.empno,:new.ename,:new.sal);elseupdate emp set ename=:new.ename,sal=:new.sal where empno=:new.empno;end if;close c_emp;end;--测试触发器update v_emp_dept set dname='游戏部'where deptno=20;insert into v_emp_dept(empno,ename,sal,deptno,dname)values(8555,'西门吹雪',8888,80,'游戏部5');commit;/****************数据库级触发器*****************/数据库级触发器定义在整个数据库上，触发事件是数据库事件，如数据库的启动(startup)、关闭(shutdown)，对数据库的登录(logon)或退出(logoff)create or replace trigger trigger_namebefore|after数据库级别操作on databasedeclare ....begin ......end [trigger_name];例：--记录退出会话信息表create table logoff(username varchar2(20),logofftime date,info varchar2(50));create or replace trigger trig_serverbefore logoff on databasebegininsert into logoff values(user,sysdate,'退出系统'); end;/****************启用、禁用、删除触发器*****************/ alter trigger trigger_name enable|disablealter table table_name enable|disable all triggers --激活或禁用在表上的所有触发器drop trigger trigger_name --删除/****************触发器信息*****************/desc user_triggers;select trigger_type,triggering_event,when_clausefrom user_triggers where trigger_name='TRIGGER_NAME';内置程序包用户 SYS 拥有所有程序包可以由任何用户访问DBMS_OUTPUT包显示 PL/SQL 块和子程序的调试信息DBMS_RANDOM包可用来生成随机整数dbms_random.random函数返回(+ -)1-10位随机数declarev_num number;begindbms_output.put_line('dbms_random及dbms_output程序包演示:');-- put_line在缓冲区存储一条信息,并接换行符for i in1..10loopv_num:=dbms_random.random; --返回(+ -)1-10位随机数dbms_output.put(' 随机数:'||v_num); --在缓冲区存储一条信息不输出显示dbms_output.put('--->0--100以内:'||abs(mod(v_num,100)));dbms_output.new_line(); --缓冲区添加换行符(刷新缓冲)end loop;end;DBMS_LOB包提供用于处理大型对象的过程和函数DBMS_XMLQUERY包用于将查询结果转换为 XML 格式--由sql查询获取xml形式的clob数据select dbms_xmlquery.getXML('select ename,sal from emp') from dual;declareresultClob clob;xmlStr varchar2(32767);line varchar2(1000);line_num integer:=1;beginresultClob:=dbms_xmlquery.getXML('select ename,sal from emp');xmlStr:=dbms_lob.substr(resultClob,32767); --从clob中获取指定个数的字符串loopexit when xmlStr is null;line:=substr(xmlStr,1,instr(xmlStr,chr(10))-1); --获取xmlStr字符串中的一行 ,chr(10)换行符dbms_output.put_line(line_num||':'||line);xmlStr:=substr(xmlStr,instr(xmlStr,chr(10))+1);--获取余下的行line_num:=line_num+1; --行号自加end loop;end;UTL_FILE 包用于读写操作系统文本文件❑操作文件的一般过程是打开、读或写、关闭❑UTL_FILE 包指定文件路径依赖于 DIRECTORY 对象create directory TEMP_DIR as'F:\temp'; --创建名为TEMP_DIR的目录grant read,write on directory TEMP_DIR to scott; --授予scott用户在TEMP_DIR目录的读写权限--UTL_FILE 保存文件declarexmlResult clob;xmlFile UTL_FILE.file_type; --文件类型变量length integer;buffer varchar2(32767);beginxmlResult:=dbms_xmlquery.getXML('select ename,sal from emp');--由sql查询获取xml形式的clob数据length:=dbms_lob.getlength(xmlResult);--获取clob类型数据长度dbms_lob.read(xmlResult,length,1,buffer); --将clob数据读到缓冲变量buffer中xmlFile:=UTL_FILE.fopen('TEMP_DIR','emp.xml','w');--以可写方式打开目录(如无emp.xml则创建）返回文件类型UTL_FILE.put(xmlFile,buffer); --将buffer中的字符写到xmlFile中UTL_FILE.fclose(xmlFile); --关闭文件end;--UTL_FILE 读取文件declareinput_file UTL_FILE.file_type; --文件类型变量input_buffer varchar2(32767);begininput_file:=UTL_FILE.fopen('TEMP_DIR','emp.xml','r');--以读的方式打开目录下emp.xml,返回文件类型loopUTL_File.get_line(input_file,input_buffer); --获取文件中的一行数据放入input_buffer变量中dbms_output.put_line(input_buffer);end loop;exceptionwhen no_data_found thenUTL_FILE.fclose(input_file); --关闭文件end;。
第八章触发器和内置程序包回顾❑子程序是命名的PL/SQL 块，存储在数据库中，可带参数并可在需要时随时调用❑有两种类型的PL/SQL子程序，即过程和函数❑过程用户执行特定的任务，函数用于执行任务并返回值❑程序包是对相关类型、变量、常量、游标、异常、过程和函数等对象的封装❑程序包由两部分组成，即包规范和包主体❑使用程序包的优点是：模块化、更轻松的程序设计、信息隐藏、新增功能以及性能更佳目标❑理解和应用触发器❑了解内置程序包触发器❑触发器是当特定事件出现时自动执行的存储过程（不能带参数）❑特定事件可以是执行更新的DML语句和DDL语句❑触发器不能被显式调用❑触发器的功能：❑自动生成数据❑自定义复杂的安全权限❑提供审计和日志记录❑启用复杂的业务逻辑触发器的组成部分❑触发源：是什么对象触发的？通常是一个表，还可以是视图❑触发事件：触发源的什么事件触发的？如插入、更新、删除等DML操作❑触发时机：前置或后置，即在触发事件前触发还是后触发，还是直接替代触发事件❑触发器限制：用户对触发器的控制，当禁用时触发器无效❑触发动作：触发器被触发时所执行的动作❑触发范围：表级或行级DML触发器的分类：行级触发器语句级触发器（表级）使用触发器模拟日志（分别使用sysdba和scott用户来测试以下代码）--日志表create table mylog(curUser varchar2(100),curDate date,act varchar(6));--触发器定义create or replace trigger触发器名--触发器名after insert or delete or update-- 条件on表–数据源--不写 for each row 是表级触发器begin--触发内容，判断用户操作if inserting theninsert into mylog values(user,sysdate,'insert'); elsif updating theninsert into mylog values(user,sysdate,'update'); elseinsert into mylog values(user,sysdate,'delete'); end if;end;触发器中不能使用COMMIT或ROLLBACK如果要回滚的话，使用RAISE_APPLICA TION_ERROR();触发器建好后是不能修改数据源的使用rollback后再查看效果。
