oracle异常及触发器

1、oracle 异常及触发器0异常处理理什么是异异常情态态异常处理理处理的的是运行行时错误误，异常常分为预预定义异异常和用用户自定定义异常常。声明异常常情态异常情态态在块的的声明部部分进行行声明，在块的的执行部部分进行行触发，在块的的异常部部分进行行处理。用户定义义类型异异常情态态,需要要程序员员自己定定义代码码，对异异常情况况进行处处理。例：Declaree_toomanystudentsexception;1e_toomanystudents的作作用域和和本块的的其他变变量相同同。预定义型型异常情情态预定义型型异常情情态可以以直接使使用，没没有必要要声明。Invalid_cursor:当当执

2、行非非法的游游标操作作时，会会引发这这个错误误，如试试图关闭闭已关闭闭的游标标。Cursor_already_open：试图图打开已已经打开开的游标标，会引引发这个个错误。No_data_found：当selectinto语句句没有返返回行时时，和引引用没有有赋值过过的pl/sql表的的元素时时会引发发这个错错误。Too_may_rows：当selectinto语语句返回回多个行行时，会会引发这这个错误误。Invalid_number：当当试图从从字符串串转换为为数值失失败时，会引发发这个错错误，在在过程性性语句中中会引发发value_error错错误。如：下面面的语句句引发invalid_n

3、umber错误误，因为为x不是数数值。Insertintostudents(id,first_name,last_name)values(x,scott,smith);2Storage_error和program_error：是内部部的异常常情态，通常不不会引发发他们。Value_error：当当在过程程性语句句中发生生了算术术、转换换、截尾尾或限制制性错误误时会引引发这个个情态。如果在在sql语句中中发生错错误，会会引发invalid_number错误误。这个个错误可可能是一一条赋值值语句或或selectinto语句的的执行结结果。下下面的两两个例子子都引发发value_error错错误。D

4、eclarev_tempvarvarchar2(3);Beginv_tempvar:=ABCD;End;Declarev_tempvarnumber(2);Beginselectidintov_tempvarfromstudentswherelast_name=smith;End;3触发异常常情态当预定义义的异常常情态错错误发生生时，就就会触发发该异常常情态。用户定定义的异异常情态态由raise语句触触发。例：Declaree_toomanystudentsexceptionv_currentstudentsnumber(3);v_maxstudents number(3);Beginsele

5、ctcurrent_students,max_studentsinto v_currentstudents,v_maxstudentsfrom classeswheredepartment=HIS andcourse=101;4ifv_currentstudentsv_maxstudents thenraisee_toomanystudentsEndif;End;当引发异异常情态态时，控控制转给给该块的的异常处处理部分分。如果果没有异异常处理理部分，该异常常处理部部分将传传给包含含该块的的块。一一旦将控控制转给给异常处处理部分分，没有有办法再再将控制制返回给给该块的的执行部部分。处理异常常情态

6、异常部分分的语法法如下：Exceptionwhen exception_name then处理错误误语句序序列when exception_name then处理错误误语句序序列when othersthen处理错误误语句序序列end;5一个处理理器可以以对多个个异常情情态进行行处理，用or连接例：Exceptionwhenno_data_found or too_many_rowstheninsertintolog_table(info)values (aselecterroroccurred);End;Others异异常处理理器将对对所有语语法的异异常情态态进行处处理，一一般放在在异常处处

7、理的最最后，可可以保证证所有的的错误都都被检测测到。Declaree_toomanystudentsexceptionv_currentstudentsnumber(3);v_maxstudents number(3);v_errorcodenumber;v_errortextvarchar2(200);Beginselectcurrent_students,max_studentsinto v_currentstudents,v_maxstudentsfrom classeswheredepartment=HIS andcourse=101;6ifv_currentstudentsv_max

8、students thenraisee_toomanystudentsEndif;Exceptionwhen e_toomanystudents theninsertintolog_table(info)values(history 101has | v_currentstudents | students:maxallowedis | v_maxstudents);when othersthenv_errorcode:=sqlcode;v_errortext:=substr(sqlerrm,1,200);insertintolog_table(info)values(another erro

9、r occurred);end;*sqlcode和sqlerrm先被赋赋值给本本地变量量，然后后这些变变量在sql语语句中被被使用，sqlcode和sqlerrm不能直直接在sql语语句中使使用，因因为他们们是过程程性的函函数。sqlcode返回当当前的错错误号，sqlerrm返回回当前的的错误信信息正文文。 7代码消息使用的sqlerrm函数0ora-0000:normal,successful completionsqlerrm(0)+100Ora-1403:no data foundsqlerrm(100)+10User-defined exceptionSqlerrm(10)nullo

10、ra-0000:normal,successful completionsqlerrm-1Ora-0001:unique constraint(.) violatedSqlerrm(-1)-54Ora-00054:resource busy and acquire by nowait specifiedSqlerrm(-54)8Exception_init可以将一一个经过过命名的的异常情情态与一一个特别别的oracle错误误相联系系。这样样可以用用when扑获获此错误误，不用用others扑获。通过exception_initpragma实实现的。其语法法如下：Pragmaexception_

11、init(exception_name,oracle_error_number)Pragma必必须在声声明部分分例：下面面的例子子在运行行时刻如如遇到“ora-1400：mandatory notnull columnmissing or nullduring insert”错错误时，将引发发e_missingnull异常常情态。Declaree_missingnull exception;pragmaexception_init(e_missingnull,-1400);Begininsertintostudents(id)values(null);Exceptionwhen e_missi

12、ngnulltheninsertintolog_table(info)values(ora-1400occurred);End;9每次发生生pragma exception_init时，一一个oracle错误误只能和和一个用用户定义义的异常常情态相相关联。在异常常处理器器内部，sqlcode和sqlerrm将返回回发生oracle错错误的代代码和错错误信息息，而不不会返回回用户定定义的消消息。例：Declareex_hfyexception;Pragmaexception_init(ex_hfy,-1400);Begininsertintostudentsvalues(null);Except

13、ionwhenex_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是

14、从从20,000到20,999之间的的数，error_message是与此此错误相相关的错错误正文文。Keep_errors为布尔尔值，如如果他为为true，则则新的错错误被添添加到已已经引发发的错误误列表中中（如果果有的话话）。如如果为false（为为缺省值值），则则新的错错误将替替换错误误的当前前列表。例：下下面的过过程为一一个新的的学生注注册以前前，检查查是否在在班级中中有足够够的地方方容纳他他。11 Createorreplaceprocedureregister(p_studentidinstudents.id%type,p_departmentinclasses.departmen

15、t%type,p_courseinclasses.course%type)asv_currentstudentsnumber;v_maxstudents number;Beginselectcurrent_students,max_studentsinto v_currentstudent,v_maxstudentsfrom classeswheredepartment=p_departmentandcourse=p_course; 12ifv_currentstudents+1v_maxstudentsthenraise_application_error(-20000,cant addmo

16、re students to|p_department| |p_course);endif;classpackage.addstudent(p_studentid,p_department,p_course);Exceptionwhen no_data_foundthenraise_application_error(-20001,p_department | |p_course|doesnt exist!);Endregister;以上程序序运行时时，当没没有足够够的空间间容纳新新的学生生时，返返回ora-20000:cantadd morestudentstohis 101错误误消息。13

17、触发器综述触发器可可以理解解为特殊殊的存储储过程。当应用用程序用用一条满满足触发发器条件件的SQLDML语语句指向向与触发发器相连连接的表表时，Oracle将将自动执执行该触触发器以以执行任任务。DML触触发器，定义在在对数据据库表的的操纵行行为（insert,delete,update)上上的触发发器。前触发器器、后触触发器。语句触发发、行触触发14触发器15触发器16触发器关于触发发器中的的old和new关键键字：old表表示数据据操纵前前的值，old只对UPDATE和和DELETE操作。new表表示数据据操纵后后的值，new只对UPDATE和和INSERT操作。17触发器定义的一一般语法

18、法：CREATEOR REPLACE TRIGGERtriggerBEFORE|AFTERDELETE|INSERT|UPDATEOF column,columnORDELETE|INSERT|UPDATEOF column,columnONtableFOREACHROWWHENconditionBEGINpl/sql block.ENDtrigger18触发器示示例CREATEORREPLACETRIGGER logempBEFOREINSERTORUPDATEORDELETE ON empFOREACHROWDECLAREstatementtype CHAR(20);BEGINIFINSERTINGTHENstatementtype:=INSERT TRIGGER!;ELSIFUPDATINGTHENstatementtype:=UPDATE TRIGGER!;ELSEstatementtype:=DELETE TRIGGER!;ENDIF;DBMS_OUTPUT.PUT_LINE(statementtype);END;19触发器示示例下面分别别做插入入、删除除和修改改操作，观察结结果：insertintoemp(empno,ename)values( 79