oracle异常及触发器

oracle异常及触发器0异常处理什么是异常情态异常处理处理的是运行时错误，异常分为预定义异常和用户自定义异常。声明异常情态异常情态在块的声明部分进行声明，在块的执行部分进行触发，在块的异常部分进行处理。用户定义类型异常情态,需要程序员自己定义代码，对异常情况进行处理。例：Declaree_toomanystudentsexception;1e_toomanystudents的作用域和本块的其他变量相同。预定义型异常情态预定义型异常情态可以直接使用，没有必要声明。Invalid_cursor:当执行非法的游标操作时，会引发这个错误，如试图关闭已关闭的游标。Cursor_already_open：试图打开已经打开的游标，会引发这个错误。No_data_found：当selectinto语句没有返回行时，和引用没有赋值过的pl/sql表的元素时会引发这个错误。Too_may_rows：当selectinto语句返回多个行时，会引发这个错误。Invalid_number：当试图从字符串转换为数值失败时，会引发这个错误，在过程性语句中会引发value_error错误。如：下面的语句引发invalid_number错误，因为‘x’不是数值。Insertintostudents(id,first_name,last_name)values(‘x’,’scott’,’smith’);2Storage_error和program_error：是内部的异常情态，通常不会引发他们。Value_error：当在过程性语句中发生了算术、转换、截尾或限制性错误时会引发这个情态。如果在sql语句中发生错误，会引发invalid_number错误。这个错误可能是一条赋值语句或selectinto语句的执行结果。下面的两个例子都引发value_error错误。Declarev_tempvarvarchar2(3);Beginv_tempvar:='ABCD';End;Declarev_tempvarnumber(2);Beginselectidintov_tempvarfromstudentswherelast_name='smith';End;3触发异常情态当预定义的异常情态错误发生时，就会触发该异常情态。用户定义的异常情态由raise语句触发。例：Declaree_toomanystudentsexceptionv_currentstudentsnumber(3);v_maxstudentsnumber(3);Beginselectcurrent_students,max_studentsintov_currentstudents,v_maxstudentsfromclasseswheredepartment=‘HIS’andcourse=‘101’;4ifv_currentstudents>v_maxstudentsthenraisee_toomanystudentsEndif;End;当引发异常情态时，控制转给该块的异常处理部分。如果没有异常处理部分，该异常处理部分将传给包含该块的块。一旦将控制转给异常处理部分，没有办法再将控制返回给该块的执行部分。处理异常情态异常部分的语法如下：Exceptionwhenexception_namethen处理错误语句序列whenexception_namethen处理错误语句序列whenothersthen处理错误语句序列end;5一个处理器可以对多个异常情态进行处理，用or连接例：Exceptionwhenno_data_foundortoo_many_rowstheninsertintolog_table(info)values(‘aselecterroroccurred’);End;Others异常处理器将对所有语法的异常情态进行处理，一般放在异常处理的最后，可以保证所有的错误都被检测到。Declaree_toomanystudentsexceptionv_currentstudentsnumber(3);v_maxstudentsnumber(3);v_errorcodenumber;v_errortextvarchar2(200);Beginselectcurrent_students,max_studentsintov_currentstudents,v_maxstudentsfromclasseswheredepartment=‘HIS’andcourse=‘101’;6ifv_currentstudents>v_maxstudentsthenraisee_toomanystudentsEndif;Exceptionwhene_toomanystudentstheninsertintolog_table(info)values(‘history101has’||v_currentstudents||‘students:maxallowedis‘||v_maxstudents);whenothersthenv_errorcode:=sqlcode;v_errortext:=substr(sqlerrm,1,200);insertintolog_table(info)values(‘anothererroroccurred’);end;*sqlcode和sqlerrm先被赋值给本地变量，然后这些变量在sql语句中被使用，sqlcode和sqlerrm不能直接在sql语句中使用，因为他们是过程性的函数。sqlcode返回当前的错误号，sqlerrm返回当前的错误信息正文。

7代码消息使用的sqlerrm函数0ora-0000:normal,successfulcompletionsqlerrm(0)+100Ora-1403:nodatafoundsqlerrm(100)+10User-definedexceptionSqlerrm(10)nullora-0000:normal,successfulcompletionsqlerrm-1Ora-0001:uniqueconstraint(.)violatedSqlerrm(-1)-54Ora-00054:resourcebusyandacquirebynowaitspecifiedSqlerrm(-54)8Exception_init可以将一个经过命名的异常情态与一个特别的oracle错误相联系。这样可以用when扑获此错误，不用others扑获。通过exception_initpragma实现的。其语法如下：Pragmaexception_init(exception_name,oracle_error_number)Pragma必须在声明部分例：下面的例子在运行时刻如遇到“ora-1400：mandatorynotnullcolumnmissingornullduringinsert”错误时，将引发e_missingnull异常情态。Declaree_missingnullexception;

pragmaexception_init(e_missingnull,-1400);Begininsertintostudents(id)values(null);Exceptionwhene_missingnulltheninsertintolog_table(info)values(‘ora-1400occurred’);End;9每次发发生pragmaexception_init时，，一个个oracle错误误只能能和一一个用用户定定义的的异常常情态态相关关联。。在异异常处处理器器内部部，sqlcode和sqlerrm将返返回发发生oracle错错误的的代码码和错错误信信息，，而不不会返返回用用户定定义的的消息息。例：Declareex_hfyexception;Pragmaexception_init(ex_hfy,-1400);Begininsertintostudentsvalues(null);Exceptionwhenex_hfythendbms_output.put_line('不不能把把空值值插入入到非非空列列');End;注意，，这里里-1400不不能为为别的的，因因为把把空值值插入入到非非空列列的错错误号号就是是这个个。可可以单单独执执行insert语语句查查看错错误号号。10使用raise_application_error可以用用raise_application_error创创建自自己的的错误误消息息，这这比命命名的的异常常情态态更具具有说说明性性。其其语法法如下下：Raise_application_error(error_number,error_message,[keep-errors]);这里error_number是是从－－20,000到－－20,999之间间的数数，error_message是与与此错错误相相关的的错误误正文文。Keep_errors为布布尔值值，如如果他他为true，，则新新的错错误被被添加加到已已经引引发的的错误误列表表中（（如果果有的的话））。如如果为为false（（为缺缺省值值），，则新新的错错误将将替换换错误误的当当前列列表。。例：：下面面的过过程为为一个个新的的学生生注册册以前前，检检查是是否在在班级级中有有足够够的地地方容容纳他他。11Createorreplaceprocedureregister(p_studentidinstudents.id%type,p_departmentinclasses.department%type,p_courseinclasses.course%type)asv_currentstudentsnumber;v_maxstudentsnumber;Beginselectcurrent_students,max_studentsintov_currentstudent,v_maxstudentsfromclasseswheredepartment=p_departmentandcourse=p_course;12ifv_currentstudents+1>v_maxstudentsthenraise_application_error(-20000,‘can’’’taddmorestudentsto’’||p_department||‘‘‘‘||p_course);endif;classpackage.addstudent(p_studentid,p_department,p_course);Exceptionwhenno_data_foundthenraise_application_error(-20001,p_department||‘‘‘||p_course||‘‘doesn’’’texist!’);Endregister;以上程程序运运行时时，当当没有有足够够的空空间容容纳新新的学学生时时，返返回ora-20000:can’’taddmorestudentstohis101错误误消息息。13触发器器综述触发器器可以以理解解为特特殊的的存储储过程程。当当应用用程序序用一一条满满足触触发器器条件件的SQLDML语句句指向向与触触发器器相连连接的的表时时，Oracle将将自动动执行行该触触发器器以执执行任任务。。DML触发发器，，定义义在对对数据据库表表的操操纵行行为（（insert,delete,update)上上的触触发器器。前触发发器、、后触触发器器。语句触触发、、行触触发14触发器器15触发器器16触发器器关于触触发器器中的的old和和new关关键字字：old表示示数据据操纵纵前的的值，，old只只对UPDATE和和DELETE操作作。new表示示数据据操纵纵后的的值，，new只只对UPDATE和和INSERT操作作。17触发器器定义的的一般般语法法：CREATE[ORREPLACE]TRIGGERtrigger{BEFORE|AFTER}{DELETE|INSERT|UPDATE[OFcolumn[,column]……]}[OR{DELETE|INSERT|UPDATE[OFcolumn[,column]…]}]…ONtableFOREACHROW[WHENcondition]BEGIN…pl/sqlblock..END[trigger]18触发发器器示示例例CREATEORREPLACETRIGGERlogempBEFOREINSERTORUPDATEORDELETEONempFOREACHROWDECLAREstatementtypeCHAR(20);BEGINIFINSERTINGTHENstatementtype:='INSERTTRIGGER!';ELSIFUPDATINGTHENst