oracle异常及触发器.ppt

oracle异常及触发器 异常处理 什么是异常情态异常处理处理的是运行时错误 异常分为预定义异常和用户自定义异常 声明异常情态异常情态在块的声明部分进行声明 在块的执行部分进行触发 在块的异常部分进行处理 用户定义类型异常情态 需要程序员自己定义代码 对异常情况进行处理 例 Declaree toomanystudentsexception e 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 Storage 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 触发异常情态当预定义的异常情态错误发生时 就会触发该异常情态 用户定义的异常情态由raise语句触发 例 Declaree toomanystudentsexceptionv currentstudentsnumber 3 v maxstudentsnumber 3 Beginselectcurrent students max studentsintov currentstudents v maxstudentsfromclasseswheredepartment HIS andcourse 101 ifv currentstudents v maxstudentsthenraisee toomanystudentsEndif End 当引发异常情态时 控制转给该块的异常处理部分 如果没有异常处理部分 该异常处理部分将传给包含该块的块 一旦将控制转给异常处理部分 没有办法再将控制返回给该块的执行部分 处理异常情态异常部分的语法如下 Exceptionwhenexception namethen处理错误语句序列whenexception namethen处理错误语句序列whenothersthen处理错误语句序列end 一个处理器可以对多个异常情态进行处理 用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 ifv 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返回当前的错误信息正文 Exception 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 每次发生pragmaexception init时 一个oracle错误只能和一个用户定义的异常情态相关联 在异常处理器内部 sqlcode和sqlerrm将返回发生oracle错误的代码和错误信息 而不会返回用户定义的消息 例 Declareex hfyexception Pragmaexception init ex hfy 1400 Begininsertintostudentsvalues null Exceptionwhenex hfythendbms output put line 不能把空值插入到非空列 End 注意 这里 1400不能为别的 因为把空值插入到非空列的错误号就是这个 可以单独执行insert语句查看错误号 使用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 为缺省值 则新的错误将替换错误的当前列表 例 下面的过程为一个新的学生注册以前 检查是否在班级中有足够的地方容纳他 Createorreplaceprocedureregister 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 ifv 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错误消息 触发器 综述触发器可以理解为特殊的存储过程 当应用程序用一条满足触发器条件的SQLDML语句指向与触发器相连接的表时 Oracle将自动执行该触发器以执行任务 DML触发器 定义在对数据库表的操纵行为 insert delete update 上的触发器 前触发器 后触发器 语句触发 行触发 触发器 触发器 触发器 关于触发器中的old和new关键字 old表示数据操纵前的值 old只对UPDATE和DELETE操作 new表示数据操纵后的值 new只对UPDATE和INSERT操作 触发器 定义的一般语法 CREATE ORREPLACE TRIGGERtrigger BEFORE AFTER DELETE INSERT UPDATE OFcolumn column OR DELETE INSERT UPDATE OFcolumn column ONtableFOREACHROW WHENcondition BEGIN pl sqlblock END trigger 触发器示例 CREATEORREPLACETRIGGERlogempBEFOREINSERTORUPDATEORDELETEONempFOREACHROWDECLAREstatementtypeCHAR 20 BEGINIFINSERTINGTHENstatementtype INSERTTRIGGER ELSIFUPDATINGTHENstatementtype UPDATETRIGGER ELSEstatementtype DELETETRIGGER ENDIF DBMS OUTPUT PUT LINE statementtype END 触发器示例 下面分别做插入 删除和修改操作 观察结果 insertintoemp empno ename values 7938 JK INSERTTRI