触发器和程序包.ppt

第十一部分触发器,前言：触发器是一种特殊的存储过程，也就是说触发器具有存储过程的所有优势，是命名程序的一种，也是存储并运行在服务器端的。说其具有特殊性，是因为触发器的调用执行和存储过程不一样，存储过程的程序调用执行必须由程序员事先设计并编写好调用程序代码及对应的参数值，即存储过程的调用执行由程序员决定，而触发器程序不能被应用程序调用，也没有参数，当触发器程序创建并保存在数据库中后只要对应触发器触发器事件的发生，该触发器就会被自动调用执行。1、触发器程序的分类，（1）按触发事件分为：1）DML触发器：由insert、update、delete等操作触发的触发器称为DML触发器。DML触发器是传统的触发器，可以使用DML触发器实现复杂的数据完整性。2）DDL触发器：由create、alter、drop操作触发的触发器称为DDL触发器。使用DDL触发器实现跟踪用户对数据库的DDL操作。3）系统触发器：用户连接或断开数据库，由logon、logoff、shutdown、startup操作触发的触发器称为系统触发器，使用系统触发器实现对用户连接数据库信息和数据库启动或停止的信息。（2）按照执行特点分为：1）替代触发器：创建于视图上，触发事件和DML触发器相同，实现通过结构复杂的视图修改基表的数据，实现维护数据的完整性。2）一般触发器：DML、DDL、系统触发器又称为一般触发器。,第十一部分触发器,2、创建DML触发器：（1）语法：createorreplacetriggertrigger_namebefore|afterDML_statementofcolum,ontable_name|viewwhen(condition)foreachrowdeclaredeclarationsbeginexecutestatementexceptionendtrigger_name;（2）语法说明：1）trigger：触发器的关键字。2）before|after：确定触发器程序的执行时机。3）DML_statement：触发事件如update,delete,insert。4）ofcolumn：基于列及的触发器。5）ontable_name|view：确定触发器的载体。6）when(condition)：触发条件。7）foreachrow：基于行级的触发器。8）declare:变量常量的声明位置，Oracle触发器没有is或as。触发器没有参数。,第十一部分触发器,（3）实例：1）创建简单触发器，并测试。createorreplacetriggertri_insertemp1afterinsertonemp1begindbms_output.put_line(触发器程序执行了);endtri_insertemp1;2）创建相应update触发器。（独立实现）（4）触发器的级别：Oracle触发器分为行级触发器和语句级触发器。行级触发器表示DML操作每影响一行记录时触发器程序就被执行一次。而语句级触发器每执行一次DML语句就会调用执行触发器一次，与影响的行无关。使用foreachrow参数设置触发为行级触发器如果不指定都是语句级触发器。1）关于:new和:old变量的解释：:new和:old是在触发器被触发时产生的两个特殊的变量，它们数据类型triggering_table%rowtype类型，该变量的值是DML触发器所影响的记录，在编写触发器时可以将它们当作rowtype类型来处理它们。:new变量中保存的是insert或update时的新数据。:old变量中保存的是执行delete触发器将要被删除的数据和执行update触发器时要被更新的数据即表中的原数据。2）实例1：建立一个触发器,当职工表emp表被删除一条记录时，把被删除记录写到职工表删除日志表中。（独立实现）,setserveroutputoninsertintoemp1(empno)values(3333);,-创建日志信息表createtableemp_hisasselect*fromempwhereempno=3000;deletefromemp_his;-创建触发器createorreplacetriggerdel_empbeforedeleteonemp1foreachrowbegin-将修改前数据插入到日志记录表emp_his,以供监督使用。insertintoemp_his(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:m,:old.deptno);end;-测试触发器deleteempwhereempno=3000;,第十一部分触发器,2）实例1：实现emp1表的sal字段的default属性，默认值为1000。createorreplacetriggertri_defaultbeforeinsertonemp1foreachrowbeginif:new.salisnullthen:new.sal:=1000;endif;exceptionwhendup_val_on_indexthennull;whenothersthennull;end;实例2：实现某字段的自动编号功能。createsequencesque;createorreplacetriggertri_autoempnobeforeinsertonemp1foreachrowdeclarev_empnoemp1.empno%type;beginselectsque.nextvalintov_empnofromdual;if:new.empnoisnullthen:new.empno:=v_empno;endif;end;,insertintoemp1(ename)values(aaa);,insertintoemp1(ename)values(bbb);,第十一部分触发器,（5）字段级触发器：DML触发器用于实现复杂的数据完整性，而且通常都是行级触发器，这时必须精确触发器程序触发的时机，否则触发器会被无意义的触发执行，这样势必会浪费服务器资源，字段级触发器能够精确触发器触发的时机为必须操作某个或某几个字段时才会执行触发器程序。实例1：实现check约束，约束员工的工资在010000之间。setserveroutputoncreateorreplacetriggertri_checkemp1beforeinsertonempforeachrowbeginif:new.sal10000or:new.sal5000)declarev_diffnumber;beginv_diff:=:new.sal-:old.sal;dbms_output.put_line(原工资-新工资：工资差);dbms_output.put_line(:old.sal|-|:new.sal|:|v_diff);exceptionwhenothersthennull;endprint_update_sal_info;,updateempsetsal=6000whereempno=1;,第十一部分触发器,（7）DML触发器的触发谓词：一个触发器可以定义为响应多个触发事件的触发器，如beforeinsertorupdateordeleteontable，当向表中添加数据、修改数据、删除数据时触发触发器。判定是由哪种触发事件触发的触发器，就要使用谓词。触发器的谓词包括：1）inserting：当执行insert操作时，inserting返回true。2）updating：当执行update操作时，updating返回true。3）deleting：当执行delete操作时，deleting返回true。例：实现跟踪emp表的dml操作日志信息，包括记录操作的用户名、操作时间、操作类型等信息。A、创建日志信息表：createtableemp_dml_log(lognonumber(20),operuservarchar2(30),opertimedate,opertypevarchar2(10);,第十一部分触发器,B、创建触发器：createorreplacetriggeremp_dml_logafterinsertorupdateordeleteonempforeachrowbeginifinsertingtheninsertintoemp_dml_logvalues(seq2.nextval,user,sysdate,INSRET);elsifupdatingtheninsertintoemp_dml_logvalues(seq2.nextval,user,sysdate,UPDATE);elsifdeletingtheninsertintoemp_dml_logvalues(seq2.nextval,user,sysdate,DELETE);endif;endemp_dml_log;,第十一部分触发器,（8）insteadof触发器：我们已经学习了before触发器和after触发器，before触发器称为前触发器，即触发器程序优先于触发事件执行，after触发器是触发事件优先于触发器程序的执行而执行。insteadof触发器是这样一种触发器：即触发事件不真正执行，只执行触发器程序。大家回顾一下，在视图那章我们作了一个实验，就是通过视图修改多基表数据，没有修改成功，这是因为通过视图不允许同时修改多基表数据。但是通过建立基于多基表的视图就可以完成这样的功能。1）创建insteadof触发器语法：CREATEORREPLACETRIGGERtrigger_nameINSTEADOFINSERT|DELETE|UPDATEOFcolumn,columnONschema.view_nameREFERENCINGOLDASold|NEWASnew|PARENTasparentFOREACHROWtrigger_body;2）实例1：通过多基表视图修改表中数据：,第十一部分触发器,A、创建多基表视图：createorreplaceviewtitles_salesasselectt.title_id,t.title,t.price,s.ord_date,s.qtyfromtitlest,salesswheret.title_id=s.title_id;B、通过视图修改qty数据：updatetitles_salessetprice=price+1,qty=qty-1wheretitle_id=BU1111;C、创建替代触发器：createorreplacetriggertri_t_sinsteadofupdateontitles_salesbeginupdatetitlessetprice=price+1wheretitle_id=:old.title_id;updatesalessetqty=qty+1wheretitle_id=:old.title_id;end;,ERROR位于第1行:ORA-01732:此视图的数据操纵操作非法,D、通过insteadof触发器实现price、qty数据的修改：updatetitles_salessetprice=price+1,qty=qty-1wheretitle_id=BU1111;,第十一部分触发器,实例2：通过empinfo视图，智能向emp10,emp20表中添加10号和20号部门员工信息。A、创建表：createtableemp10asselect*fromempwheredeptno=10;createtableemp20asselect*fromempwheredeptno=20;B、创建多基表视图：createviewempinfoasselect*fromemp10unionselect*fromemp20;C、测试insertintoempinfo(empno,ename,deptno)values(2,aaa,10);-不可能执行成功。,第十一部分触发器,D、创建触发器：createorreplacetriggerauto_add_empinsteadofinsertonempinfodeclarebeginif:new.deptno=10theninsertintoemp10values(:new.empno,:new.ename,:new.job,:new.mgr,:new.hiredate,:new.sal,:m,:new.deptno);elsif:new.deptno=20theninsertintoemp20values(:new.empno,:new.ename,:new.job,:new.mgr,:new.hiredate,:new.sal,:m,:new.deptno);elsedbms_output.put_line(只能添加10，20号部门员工信息。);endif;endtri_name;E、重新测试：insertintoempinfo(empno,ename,deptno)values(2,aaa,10);-执行成功。注意：当删除表或者视图时，触发器也随之被删除。,第十一部分触发器,3、触发器的限制（1）CREATETRIGGER语句文本的字符长度不能超过32KB；（2）触发器体内的SELECT语句只能为SELECTINTO结构，或者为定义游标所使用的SELECT语句。（3）触发器中不能使用数据库事务控制语句COMMIT;ROLLBACK,SVAEPOINT语句；（4）由触发器所调用的过程或函数也不能使用数据库事务控制语句；（5）触发器中不能使用LONG,LONGRAW类型；（6）触发器内可以参照LOB类型列的列值，但不能通过:NEW修改LOB列中的数据；4、创建DDL触发器：（1）语法：createorreplacetriggertrigger_namebefore|aftercreateoralterordroponschema|databasedeclaredeclarationsbeginexcutestatements;exceptionexceptionhandles;endtirgger_name;语法说明：onschema:模式（用户）级触发器，只有触发器的创建者执行ddl操作才能触发。ondatabase:数据库级触发器，数据库中任何用户执行ddl操作都触发。注意：用户必须具有administerdatabasetrigger的系统权限，才能创建数据库级触发器。只有管理员才能给其他用户如scott赋予此权限。,第十一部分触发器,（2）实例：跟踪数据库用户的DDL操作日志，包括用户名、操作时间、数据库对象名、对象所有者、对象类型、操作信息。1）创建日志表：createtableddl_log(lognonumber(20)primarykey,unamevarchar2(30),oper_timedate,obj_namevarchar2(30),obj_ownervarchar2(30),obj_typevarchar2(20),oper_typevarchar2(20);2）创建序列，确定主键值。createsequenceseq_ddl_log;3）在日志表上创建触发器：createorreplacetriggerddl_logaftercreateoralterordropondatabasebegininsertintoddl_logvalues(seq_ddl_log.nextval,user,sysdate,sys.dictionary_obj_name,sys.dictionary_obj_owner,sys.dictionary_obj_type,sys.sysevent);endddl_log;,-测试createtableemp4asselect*fromemp;-查询：select*fromddl_log;,第十一部分触发器,5、创建系统事件触发器系统触发器就是相应数据库系统事件的触发器，包括数据库服务器的启动或关闭，用户的登录与退出、数据库服务错误等。创建系统触发器的语法如下：CREATEORREPLACETRIGGERsachema.trigger_nameBEFORE|AFTERdatabase_event_listONDATABASE|schema.SCHEMAWHEN_clausetrigger_body;database_event_list：一个或多个数据库事件，事件间用OR分开；,第十一部分触发器,实例1：创建LOGON、STARTUP和SERVERERROR事件触发器createorreplacetriggertrig_afterafterlogonorstartuporservererrorondatabasedeclarev_eventvarchar2(20);v_instancenumber;v_err_numnumber;v_dbnamevarchar2(50);v_uservarchar2(30);beginv_event:=sysevent;ifv_event=logonthenv_user:=login_user;insertintoeventlog(eventname,username)values(v_event,v_user);elsifv_event=servererrorthenv_err_num:=server_error(1);Insertintoeventlog(eventname,srv_error)values(v_event,v_err_num);elsev_Instance:=instance_num;v_dbname:=database_name;Insertintoeventlog(eventname,inst_num,db_name)values(v_event,v_instance,v_dbname);endif;end;,-系统操作的日志信息表createtableeventlog(eventnamevarchar2(20),usernamevarchar2(30),db_namevarchar2(50),inst_numnumber,srv_errornumber);,第十一部分触发器,实例2：创建LOGOFF和SHUTDOWN事件触发器createorreplacetriggertrig_beforebeforelogofforshutdownondatabasedeclarev_eventvarchar2(20);v_instancenumber;v_dbnamevarchar2(50);v_uservarchar2(30);beginv_event:=sysevent;ifv_event=logoffthenv_user:=login_user;insertintoeventlog(eventname,username)values(v_event,v_user);elsev_instance:=instance_num;v_dbname:=database_name;insertintoeventlog(eventname,inst_num,db_name)values(v_event,v_instance,v_dbname);endif;end;6、删除触发器：droptriggertrigger_name;,第十二部分程序包,前言：包（package）是一个Oracle数据库对象，利用包可以将一组逻辑相关的PL/SQL类型、数据项和子程序组织在一起。一个包可以包含某些子程序、类型定义、和变量说明等等。当一个包被建立后它就被保存在数据库中，用户可以调用包中的子程序，还可以使用包中的类型、变量和异常。包是Oracle为用户提供的分类组织和管理子程序及某些数据项定义的重要机制。用户可以创建包，利用包来编写自己的子程序，Oracle系统也以包的形式提供了很多有用的子程序供用户使用，系统包实际上就是PL/SQLAPI。1、程序包的优势：（1）便于组织归类，增强模块化。（2）使用程序包中任何一个方法，包中所有的方法全部加载进内存，提高使用效率。（3）全局类型、全局异常、变量和常量可以放到程序包中。（4）使程序代码具有封装性，安全性高。2、程序包的结构：Oracle程序包包括两个部分，包头和包体。包头称为包的声明部分，主要是函数、过程、类型和游标的公共声明，包头是面向应用程序的。包体的具体功能就是实现代码，可以是PL/SQL、C、Java程序，它对于应用程序来说是隐藏的。Oracle把包分为包头和包体两部分，好处是给用户统一的用户界面，另外也起到封装的作用。3、sys模式下的UTF_FILE包：在实际工作中，经常要把数据库中的信息汇总、归纳并使用打印机，因此就需要将数据库中的信息输出到操作系统文件中，然后在打印，另外一些资料信息也可能是以文本的形式存在的，有时把这些信息传到数据库中，以上操作可以通过sys模式下的UTF_FILE包实现，类似于Java中的流的操作。,第十二部分程序包,4、UTF_FILE包中常使用的类型、存储过程和函数及异常。（1）FILE_TYPE类型：实例化一个操作系统文件。使用时应该按包名.类型名定义操作系统文件的句柄。如UTF_FILE.FILE_TYPE（2）FOPEN函数：以ASCII码打开文件，返回值是文件句柄。FUNCTIONfopen(locationINVARCHAR2,filenameINVARCHAR2,open_modeINVARCHAR2,max_linesizeINBINARY_INTEGERDEFAULTNULL)说明：*location-文件的路径*filename-文件名包括扩张名*open_mode-打开方式(r,w,a)，r为只读（文件存在）、w为覆盖写、a为追加写*max_linesize每行的包括换行符最多字符数132767或者是NULL.FOPEN_NCHAR函数以UNICODE码打开文件。（3）PUT存储过程：PROCEDUREput(fileINfile_type,bufferINVARCHAR2);说明：写文件，file文件句柄，buffer需要写的文本，如果需要，转成UTF8字符串put_nchar(fileINfile_type,bufferINNVARCHAR2);写文件以UNICODE码写。,第十二部分程序包,（4）put_line存储过程：写一行文本，带有换行符。PROCEDUREput_line(fileINfile_type,bufferINVARCHAR2,autoflushINBOOLEANDEFAULTFALSE);说明：file文件句柄，buffer文本，autoflush设置是否自动清空缓冲，默认不清空缓冲。（5）new_line存储过程换行，默认换一行。PROCEDUREnew_line(fileINfile_type,linesINNATURAL:=1);（6）fclose存储过程：关闭文件。PROCEDUREfclose(fileINOUTfile_type);（7）fclose_all存储过程关闭所有文件。此过程没有参数。（8）get_line存储过程：读取一行。PROCEDUREget_line(fileINfile_type,bufferOUTVARCHAR2,lenINBINARY_INTEGERDEFAULTNULL);说明：file文件句柄，buffer下一行的内容，len读取的长度默认空，最大32767。（9）fflush存储过程：强制物理写。PROCEDUREfflush(fileINfile_type);,第十二部分程序包,（10）INVALID_PATH：无效路径。（11）INVALID_MODE：无效打开模式。（12）INVALID_OPERATION：无效操作。（13）READ_ERROR：读错误（特殊字符符号）。（14）WRITE_ERROR：写错误（特殊字符）。（15）INTERNAL_ERROR：同java中的ERROR。（16）CHARSETMISMATCH：字符集错误。5、设置UTF_FILE包可访问的目录：Oracle的UTF_FILE包是用户执行使用的，如果想让Oralce用户可以访问操作系统文件，必须先在初始化参数文件中添加参数UTF_FILE_DIR的值，如果UTF_FILE_DIR的值设置为*即UTF_FILE_DIR*表示可以访问操作系统的所以路径。,第十二部分程序包,6、写文件实例：实例1：输出简单的信息到操作系统文件。setserveroutputon-测试utl_file包declare-声明文件类型变量，以便实例化一个操作系统文件v_fileutl_file.file_type;begin-打开一个操作系统文件v_file:=utl_file.fopen(c:,a.txt,w);-项文件写信息utl_file.put_line(v_file,Hello,mydearfriends:);utl_file.put_line(v_file,Missyou.);-关闭文件utl_file.fclose(v_file);exceptionwhenutl_file.invalid_paththendbms_output.put_line(invalidpath);whenothersthennull;end;,第十二部分程序包,实例2：输出员工的详细信息到操作系统文件中：格式如下：部门名称：*-姓名工作工资雇佣日期。员工人数：*平均工资：*部门名称：*-姓名工作工资雇佣日期。员工人数：*平均工资：*,第十二部分程序包,declarev_fileutl_file.file_type;v_fnamevarchar2(30);cursorc_deptisselectd.deptno,d.dname,count(*)count_num,sum(nvl(e.sal,0)+nvl(m,0)total,avg(sal)avgsalfromdeptd,empewhered.deptno=e.deptnogroupbyd.deptno,d.dname;cursorc_emp(p_deptnonumber)isselectename,nvl(sal,0)sal,nvl(comm,0)comm,nvl(sal,0)+nvl(comm,0)incomefromempwheredeptno=p_deptno;v_enamechar(10);v_salchar(10);v_commchar(9);v_incomechar(10);beginifto_char(sysdate,mm)between2and10thenv_fname:=IncomeInfo|to_char(sysdate,yyyy)|0|(to_char(sysdate,mm)-1)|.txt;elsifto_char(sysdate,mm)1thenv_fname:=IncomeInfo|to_char(sysdate,yyyy)|(to_char(sysdate,mm)-1)|.txt;elsev_fname:=IncomeInfo|(to_char(sysdate,yyyy)-1)|12|.txt;endif;v_file:=utl_file.fopen(c:,v_fname,w);,第十二部分程序包,forv_deptinc_deptlooputl_file.put_line(v_file,部门名：|v_dept.dname);utl_file.put_line(v_file,=);openc_emp(v_dept.deptno);loopfetchc_empintov_ename,v_sal,v_comm,v_income;exitwhenc_emp%notfound;utl_file.put_line(v_file,姓名工资奖金总收入);utl_file.put_line(v_file,-);utl_file.put_line(v_file,v_ename|v_sal|v_comm|v_income);utl_file.new_line(v_file);endloop;closec_emp;utl_file.put_line(v_file,员工人数：|v_dept.count_num);utl_file.put_line(v_file,平均工资：|round(v_dept.avgsal,2);utl_file.new_line(v_file);endloop;utl_file.fclose(v_file);exceptionwhenothersthennull;end;,第十二部分程序包,7、读文件实例：（1）测试instr函数和substr函数1）instr函数：instr(C1,C2,I,J)在一个字符串中搜索指定的字符,返回发现指定的字符的位置;C1被搜索的字符串C2希望搜索的字符串I搜索的开始位置,默认为1J出现的位置,默认为1selectinstr(oracletraning,ra,1,2)instringfromdual;2）substr函数substr(string,start,count)取子字符串,从start开始,取count个selectsubstr3,8)fromdual;提取字符的实例应用：,instr和substr函数的综合实例：setserveroutputondeclarev_firnumber;v_secnumber;v_thinumber;v_fornumber;v_stringvarchar2(1000):=1000,zhang,xxx,2000,10;v_empnochar(5);v_namechar(10);v_jobchar(10);v_salchar(4);v_deptnochar(4);beginv_fir:=instr(v_string,1,1);v_sec:=instr(v_string,1,2);v_thi:=instr(v_string,1,3);v_for:=instr(v_string,1,4);dbms_output.put_line(v_fir|v_sec|v_thi|v_for);v_empno:=to_number(substr(v_string,1,v_fir-1);v_name:=substr(v_string,v_fir+1,v_sec-v_fir-1);v_job:=substr(v_string,v_sec+1,v_thi-v_sec-1);v_sal:=to_number(substr(v_string,v_thi+1,v_for-v_thi-1);v_deptno:=to_number(substr(v_string,v_for+1);dbms_output.put_line(v_empno|v_name|v_job|v_sal|v_deptno);end;,第十二部分程序包,（2）将操作系统中的文件内容添加到表中。1）操作系统文件test.txt内容：10,zhang,Accounting,3000,111,wang,Sales,2000,212,li,Accounting,2200,113,zhao,sales,2500,22）创建表empinfocreatetableempinfo(empnonumber,enamechar(10),jobchar(10),salnumber,deptnonumber);3）读文件操作并添将数据加到表中,第十二部分程序包,declarev_fileutl_file.file_type;v_stringvarchar2(2000);v_firnumber;v_secnumber;v_thinumber;v_fornumber;v_empnoempinfo.empno%type;v_enameempinfo.ename%type;v_jobempinfo.job%type;v_salempinfo.sal%type;v_deptnoempinfo.deptno%type;beginv_file:=utl_file.fopen(e:,test.txt,r);looputl_file.get_line(v_file,v_string);-分割信息v_fir:=instr(v_string,1,1);v_sec:=in