http://blog.sina.com.cn/s/blog_53a072c10100rp3f.html
控制PL/SQL错误 EXCEPTION,SQLCODE,SQLERRM --浏览时可通过查找功能跳跃式的寻找自己要查看的内容! 一、错误控制一览 在PL/SQL中,警告或错误被称为异常。异常可以是内部(运行时系统)定义的或是用户定义的。内部定义的案例包括除零操作和内存溢出等。一些常见的内部异常都有一个预定义的名字,如ZERO_DIVIDE和STORAGE_ERROR等。对于其它的内部异常,我们可以手动为它们命名。 我们可以在PL/SQL块、子程序或包的声明部分自定义异常。例如,我们可以定义一个名为insufficient_funds的异常来标示帐户透支的情况。与内部异常不同的是,用户自定义异常必须有一个名字。 错误发生时,异常就会被抛出。也就是说,正常的执行语句会被终止,控制权被转到PL/SQL块的异常控制部分或子程序的异常控制部分。内部异常会由运行时系统隐式地抛出,而用户定义异常必须显式地用RAISE语句抛出,RAISE语句也可以抛出预定义异常。 为了控制被抛出的异常,我们需要单独编写被称为"exception handler"的异常控制程序。异常控制程序运行后,当前块就会停止执行,封闭块继续执行下一条语句。如果没有封闭块,控制权会直接交给主环境。 下例中,我们为一家股票代码(Ticker Symbol)为XYZ的公司计算并保存市盈率(price-to-earning)。如果公司的收入为零,预定义异常ZERO_DIVIDE就会被抛出。这将导致正常的执行被终止,控制权被交给异常控制程序。可选的OTHERS处理器可以捕获所有的未命名异常。 DECLARE 上面的例子演示了异常控制,但对于INSERT语句的使用就有些低效了。使用下面的语句就要好一些: INSERT INTO stats (symbol, ratio) 在下面这个例子中,子查询为INSERT语句提供了数据。如果earnings是零的话,函数DECODE就会返回空,否则DECODE就会返回price与earnings的比值。 二、异常的优点 使用异常来控制错误有几个优点。如果没有异常控制的话,每次执行一条语句,我们都必须进行错误检查: BEGIN 错误处理和正常的处理内容界限不明显,导致代码混乱。如果我们不编写错误检查代码,一个错误就可能引起其它错误,有时还可能是一些无关错误。 但有了异常后,我们就能很方便的控制错误,而且不需要编写多个检查代码: BEGIN 异常能把错误控制程序单独分离出来,改善可读性,主要的算法不会受到错误恢复算法影响。异常还可以提高可靠性。我们不需要在每一个可能出现错误的地方编写错误检查代码了,只要在PL/SQL块中添加一个异常控制代码即可。这样,如果有异常被抛出,我们就可以确保它能够被捕获并处理。 三、预定义PL/SQL异常 当我们的PL/SQL程序与Oracle规则相冲突或超过系统相关(system-dependent)的限制时,内部异常就会被抛出。每个Oracle错误都有一个错误编号,但异常只能按名称捕获,然后被处理。所以,PL/SQL把一些常见Oracle错误定义为异常。例如,如果SELECT INTO语句查询不到数据时,PL/SQL就会抛出预定义异常NO_DATA_FOUND。 要控制其它Oracle异常,我们可以使用OTHERS处理器。函数SQLCODE和SQLERRM在OTHERS处理器中特别有用,因为它们能返回Oracle错误编号和消息。另外,我们还可以使用编译指示(pragma)EXCEPTION_INIT把一个异常名称和一个Oracle错误编号关联起来。PL/SQL在STANDARD包中声明了全局预定义异常。所以,我们不需要自己声明它们。我们可以为下面列表中命名的预定义异常编写处理程序: 异常 Oracle错误号 SQLCODE值 预定义异常的简要描述: 异常 抛出时机 四、自定义PL/SQL异常 PL/SQL允许我们定义自己的异常。与预定义异常不同的是,用户自定义异常必须声明,并且需要用RAISE语句显式地抛出。 1、声明PL/SQL异常 异常只能在PL/SQL块、子程序或包的声明部分声明。下例中,我们声明一个名为past_due的异常: DECLARE 异常和变量的声明是相似的。但是要记住,异常是一种错误情况(error condition),而不是数据项。与变量不同的是,异常不能出现在赋值语句或是SQL语句中。但是,变量的作用域规则也适用于异常。 2、PL/SQL异常的作用域规则 在同一个块内,异常不能声明两次。但可以在不同的块声明相同的异常。 块中声明的异常对于当前块来说是本地的,但对于当前块的所有子块来说是全局的。因为块只能引用本地或全局的异常,所以封闭块不能引用声明在子块中的异常。 如果我们在子块中重新声明了一个全局的异常,本地声明的异常的优先级是要高于全局的。所以,子块就不能引用全局的异常,除非全局异常在它的所在块中用标签作了标记,这种情况下可以使用下面的语法来引用全局异常: block_label.exception_name 下例中演示了作用范围规则: DECLARE 上例中的封闭块并不能捕获抛出来的异常,因为在子块中声明的past_due优先级要高于封闭块声明的异常。虽然它们的名字相同,但实际上是两个不同的past_due异常,就像两个acct_num变量只是共享着相同的名字一样,实际上它们是完全不同的两个变量。因此,RAISE语句和WHEN子句所引用的是不同的异常。如果想让封闭块能捕获到子块中的past_due异常,我们就必须从子块中删除声明,或是在封闭块中添加OTHERS处理器。 3、把PL/SQL异常与编号关联:编译指示EXCEPTION_INIT 要想控制没有预定义名称的错误(通常为 ORA- 消息),我们就必须使用OTHERS处理器或编译指示EXCEPTION_INIT。编译指示就是能在编译期而非运行时进行处理的编译指令。 在PL/SQL中,编译指示EXCPTION_INIT能告诉编译器把异常名称和错误编号关联起来。这就能让我们按名称来引用所有的内部异常,并为它编写特定的处理程序。在我们看到的错误栈或是错误消息序列中,最顶层的就是我们能捕获和处理的信息。 我们可以把编译指示EXCEPTION_INIT写在PL/SQL块、子程序或包的声明部分,语法如下: PRAGMA EXCEPTION_INIT(exception_name, -Oracle_error_number); 其中exception_name是已经声明过的异常名称,Oracle_error_number是Oracle错误编号。编译指示必须和异常声明处于同一个声明中,并且只能在异常声明之后出现。如下例所示: DECLARE 4、自定我们自己的错误消息:过程RAISE_APPLICATION_ERROR 过程RAISE_APPLICATION_ERROR能帮助我们从存储子程序中抛出用户自定义的错误消息。这样,我们就能把错误消息报告给应用程序而避免返回未捕获异常。 调用RAISE_APPLICATION_ERROR的语法如下: raise_application_error(error_number, message[, {TRUE | FALSE}]); error_number是一个范围在-20000至-20999之间的负整数,message是最大长度为2048字节的字符串。如果第三个可选参数为TRUE的话,错误就会被放到前面错误的栈顶。如果为FALSE(默认值),错误就会替代前面所有的错误。 RAISE_APPLICATION_ERROR是包DBMS_STANDARD的一部分,所以,我们对它的引用不需要添加限定修饰词。 应用程序只能从一个正在执行的存储子程序或方法中调用raise_application_error。在调用时,raise_application_error会结束子程序并把用户定义的错误编号和消息返回给应用程序。错误编号和消息可以像其它的Oracle错误一样被捕获。 在下面的例子中,我们在雇员工资栏的内容为空的情况下调用raise_application_error: CREATE PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER) AS 调用程序会得到一个PL/SQL异常,它能在OTHERS处理器中使用错误报告函数SQLCODE和SQLERRM来进行处理。同样,我们也可以使用编译指示EXCEPTION_INIT把raise_application_error返回的错误编号映射到异常本身。如下面的Pro*C例子所示: EXEC SQL EXECUTE DECLARE END-EXEC; 这项技术能让调用程序在特定的异常处理程序中控制错误。 5、重新声明预定义异常 请记住,PL/SQL把预定义的异常作为全局内容声明在包STANDARD中,所以,我们没有必要重新声明它们。重新声明预定义异常是错误的做法,因为我们的本地声明会覆盖掉全局声明。例如,如果我们声明了一个invalid_number,当PL/SQL抛出预定义异常INVALID_NUMBER时,我们为异常INVALID_NUMBER编写的异常控制程序就无法正确地捕获到它了。这种情况下,我们必须像下面这样使用点标志来指定预定义异常: EXCEPTION 五、如何抛出PL/SQL异常 内部异常会由运行时系统隐式地抛出,其中也包括使用编译指示EXCEPTION_INIT与Oracle错误编号关联起来的用户自定义异常。但是,用户自定义的异常就必须显式地用RAISE语句抛出。 1、使用RAISE语句抛出异常 PL/SQL块和子程序应该只在错误发生或无法完成正常程序处理的时候才抛出异常。下例中,我们用RAISE语句抛出一个用户自定义的out_of_stack异常: DECLARE 我们也可以显式地抛出预定义异常。这样,为预定义异常编写的处理程序也就能够处理其它错误了,示例如下: DECLARE 异常被抛出时,如果PL/SQL在当前块或子程序中没有找到对应的异常控制程序,异常就会被继续向上一级传递。也就是说异常会把它自身传递到后继的封闭块直到找到异常处理程序或是再也没有可以搜索到的块为止。在后一种情况下,PL/SQL会向主环境抛出一个未捕获异常。 但是,异常是不能通过远程过程调用(RPC)来传递的。因此,PL/SQL块不能捕获由远程子程序抛出的异常。 BEGIN 因为异常past_due所在的块并没有专门针对它的处理程序,所以异常就被传递到封闭块。但是,按照作用域规则,封闭块是不能引用子块声明的异常。所以,只有OTHERS处理器才能捕获到这个异常。如果没有用户定义异常的处理程序,调用这个程序就会得到下面的错误: ORA-06510: PL/SQL: unhandled user-defined exception 七、重新抛出PL/SQL异常 有时我们需要重新抛出捕获到异常,也就是说,我们想在本地处理之后再把它传递到封闭块。比如,在异常发生的时候,我们可能需要回滚事务,然后在封闭块中写下错误日志。 要重新抛出异常,只要在本地处理程序中放置一个RAISE语句即可,示例如下: DECLARE 如果在RAISE语句中省略了异常名称——只允许在异常处理程序中这样做——程序就会把当前的异常重新抛出。 八、处理PL/SQL异常 异常抛出时,PL/SQL块或子程序的正常执行就会停止,控制权转到块或子程序的异常处理部分,语法如下: EXCEPTION 为捕获抛出的异常,我们需要编写异常处理程序。每个处理程序都由一个WHEN子句和语句序列组成。这些语句执行完毕后,块或子程序就会结束,控制权不再返回异常被抛起的地方。换句话说,也就是我们不能再次返回异常发生的地方继续执行我们的程序。 可选的OTHERS处理器总是块或子程序的最后一个处理程序,它可以用于捕获所有的未命名异常。因此,块或子程序只能有一个OTHERS处理器。如下例所示,OTHERS处理器能够保证所有的异常都会被控制: EXCEPTION 如果我们想让两个或更多的异常执行同样的语句序列,只需把异常名称用关键字OR隔开,放在同一个WHEN子句中即可,如下例所示: EXCEPTION 只要在WHEN子句的异常列表中有一项与被抛出异常相匹配,相关的语句序列就会被执行。关键字OTHERS不能出现在异常名称列表中;它只能单独使用。我们可以有任意数量的异常处理程序,而且每个处理程序都与一个异常列表及其对应的语句序列相关联。但是,异常名称只能在块或子程序的异常处理部分出现一次。 变量作用范围的规则在这里也同样适用,所以我们可以在异常处理程序中引用本地或全局变量。但是,当游标FOR循环中有异常抛出时,游标就会在异常处理程序调用之前被隐式地关闭。因此,显式游标的属性值在异常处理程序中就不再可用了。 1、声明中控制异常 如果在声明时使用了错误的初始化表达式也有可能引发异常。例如,下面的声明就是因常量credit_limit不能存储超过999的数字而抛出了异常: DECLARE 当前块中的处理程序并不能捕获到抛出的异常,这是因为声明时抛出的异常会被立即传递到最近的封闭块中去。 2、异常句柄中控制异常 在一个块或子程序中,一次只能有一个异常被激活。所以,一个被异常处理程序抛出的异常会被立即传递到封闭块,在那儿,封闭块会为它查找新的处理程序。从那一刻起,异常传递才开始正常化。参考下面的例子: EXCEPTION 3、异常分支 GOTO语句不能跳转到异常控制程序。同样,GOTO语句也不能从异常控制程序跳转到当前块。例如,下面的GOTO语句就是非法的: DECLARE 但是,GOTO语句可以从一个异常控制程序中跳转到一个封闭块。 在异常处理程序中,我们可以使用内置函数SQLCODE和SQLERRM来查出到底发生了什么错误,并能够获取相关的错误信息。对于内部异常来说,SQLCODE会返回Oracle错误编号。SQLCODE返回的总是一个负数,除非发生的Oracle错误是没有找到数据,这时返回的是+100。SQLERRM会返回对应的错误消息。消息是以Oracle错误编号开头的。 如果我们没有使用编译指令EXCEPTION_INIT把异常与编号关联的话,SQLCODE和SQLERRM就会分别返回+1和消息"User-Defined Exception"。Oracle错误消息最大长度是512个字符,其中包括错误编号、嵌套消息和具体表和字段的名称。 如果没有异常抛出,SQLCODE返回0,SQLERRM返回消息"ORA-0000: normal, successful completion"。 我们可以把错误编号传递给SQLERRM,让它返回对应的错误消息。但是,一定要保证我们传递给SQLERRM的错误编号是负数。下例中,我们把一个正数传递给SQLERRM,结果就不是我们想要的那样的了: DECLARE 把正数传给SQLERRM时,如果传递的是+100,返回的结果是"no data found",其他情况总是会返回消息"user-defined exception"。把0传递给SQLERRM,就会返回消息"normal, successful completion"。 我们不能直接在SQL语句中使用SQLCODE或SQLERRM。我们必须先把它们的值赋给本地变量,然后再在SQL中使用变量,如下例所示: DECLARE 字符串函数SUBSTR可以保证用SQLERRM为err_msg赋值时不会引起VALUE_ERROR异常。函数SQLCODE和SQLERRM在OTHERS异常处理程序中特别有用,因为它们能让我们知道哪个内部异常被抛出。 注意:在使用编译指示RESTRICT_REFERENCES判断存储函数的纯度时,如果函数调用了SQLCODE和SQLERRM,我们就不能指定约束为WNPS和RNPS了。 5、捕获未控制异常 记住,如果被抛出的异常找不到合适的异常控制程序,PL/SQL会向主环境抛出一个未捕获的异常错误,然后由主环境决定如何处理。例如,在Oracle预编译程序环境中,任何一个执行失败的SQL语句或PL/SQL块所涉及到的改动都会被回滚。 未捕获也能影响到子程序。如果我们成功地从子程序中退出,PL/SQL就会把值赋给OUT参数。但是,如果我们因未捕获异常而退出程序,PL/SQL就不会为OUT参数进行赋值。同样,如果一个存储子程序因异常而执行失败,PL/SQL也不会回滚子程序所做的数据变化。 我们可以在每个PL/SQL程序的顶级使用OTHERS句柄来捕获那些没有被子程序捕捉到的异常。 九、PL/SQL错误控制技巧 这里,我们将学习三个提高程序灵活性的技巧。 1、模拟TRY..CATCH..块 异常控制程序能让我们在退出一个块之前做一些恢复操作。但是在异常程序完成后,语句块就会终止。我们不能从异常句柄再重新回到当前块。例如,如果下面的SELECT INTO语句引起了ZERO_DIVIDE异常,我们就不能执行INSERT语句了: DECLARE SELECT price / NVL(earnings, 0) INSERT INTO stats(symbol, ratio) 其实我们可以控制某一条语句引起的异常,然后继续下一条语句。只要把可能引起异常的语句放到它自己的子块中,并编写对应的异常控制程序。一旦在子块中有错误发生,它的本地异常处理程序就能捕获并处理异常。当子块结束时,封闭块程序会继续执行紧接着的下一条语句。如下例: DECLARE BEGIN INSERT INTO stats(symbol, ratio) 在上面这个例子中,如果SELECT INTO语句抛出了ZERO_DIVIDE异常,本地异常处理程序就会捕捉到它并把pe_ratio赋值为0。当处理程序完成时,子块也就终止,INSERT语句就会被执行。 2、反复执行的事务 异常发生后,我们也许还不想放弃我们事务,仍想重新尝试一次。这项技术的实现方法就是: 把事务装入一个子块中。 DECLARE 3、使用定位变量标记异常发生点 只用一个异常句柄来捕获一系列语句的话,可能无法知道到底是哪一条语句产生了错误: BEGIN 要想解决这个问题,我们可以使用一个定位变量来跟踪执行语句,例如: DECLARE |