【转】 oracle exception总结(读书笔记)

【转】 oracle exception总结(读书笔记)
2011年11月04日
　　一，基础-什么是异常情态?
　　1):exception即异常情态，如：
　　DECLARE
　　exptname EXCEPTION;       --异常情态的定义
　　[PRAGMA EXCEPTION_INIT;]  --异常情态与run time error number相关联
　　.
　　.
　　BEGIN
　　.
　　.
　　END;
　　每一个异常情态都【应当】有一个exception handler(异常处理器)与之对应，
　　从而在【代码执行部分】发生对应的异常情态(exception也可叫run time error)
　　时处理异常，而防止异常扩大。
　　当然你也可以只声明异常情态，不与相应的run time error number对应，
　　甚至没有对应的exception handler，这时的这个异常情态声明是没有意义的：
　　DECLARE
　　exptname EXCEPTION;
　　BEGIN
　　DBMS_OUTPUT.PUT_LINE('Hello World!');
　　END;
　　2):自定义与预定义异常
　　DECLARE
　　exptname EXCEPTION;
　　BEGIN
　　DBMS_OUTPUT.PUT_LINE('Hello World!');
　　RAISE exptname;
　　END;
　　上面只定义了异常情态，而没有对应的exception handler，所以RAISE时
　　会出现run time error，因为RAISE也是在BEGIN .. END之间的，所以也是
　　一个run time的实际执行部分。
　　预定义异常：
　　BEGIN
　　RAISE CASE_NOT_FOUND;
　　EXCEPTION WHEN CASE_NOT_FOUND then
　　DBMS_OUTPUT.PUT_LINE('case语句没有正确结束!');
　　END;
　　这些预定义的异常都是在standard包(这个包中有大部分系统函数，如:left,right等)中预先定义的，你也可以在包中定义自己的
　　异常：
　　CREATE OR REPLACE PACKAGE test_pkg AS
　　exptname EXCEPTION;
　　END;
　　BEGIN
　　RAISE test_pkg.exptname;
　　EXCEPTION WHEN test_pkg.exptname THEN
　　DBMS_OUTPUT.PUT_LINE('触发了自定义异常!');
　　END;
　　3):异常情态的流程
　　注意：异常情态是在定义部分定义的(预定义异常除外)，执行部分(begin .. end中)触发的，exception handler
　　中处理的。
　　看完上面的流程你应该有一个疑问:定义部分和handler部分的异常又怎样捕获呢?
　　答案当然是在其包含块中捕获，看下面：
　　BEGIN
　　DECLARE
　　v_int INT:='ABC';
　　BEGIN
　　NULL;
　　EXCEPTION WHEN OTHERS THEN
　　DBMS_OUTPUT.PUT_LINE('I can only check then BEGIN .. END part error!'||chr(10)||'Other part i can''t check!');
　　END;
　　EXCEPTION WHEN OTHERS THEN
　　DBMS_OUTPUT.PUT_LINE('BEGIN .. END part have error occured!'||chr(10)||'Please check that part!');
　　END;  
　　上面的父块就成功捕获了调用块中的定义部分的run time error，而被调用块的异常部分没有被执行;
　　其实很好理解，对于子块如果定义部分出现了错误，它的BEGIN .. END部分代码都不会被执行，
　　而handler只处理BEGIN .. END部分出现的异常情态，所以当然就捕获不到定义部分的异常。
　　对于异常handler部分出现的异常更好理解了，因为handler只要处理BEGIN .. END中的异常情态：
　　BEGIN
　　DECLARE
　　CURSOR test_cur IS SELECT 1 FROM DUAL;
　　BEGIN
　　RAISE CURSOR_ALREADY_OPEN;
　　EXCEPTION WHEN CURSOR_ALREADY_OPEN THEN
　　CLOSE test_cur;
　　WHEN INVALID_CURSOR THEN
　　DBMS_OUTPUT.PUT_LINE('游标已经关闭或者没有这个游标的声明!');
　　END;  
　　EXCEPTION WHEN INVALID_CURSOR THEN
　　DBMS_OUTPUT.PUT_LINE('游标已经关闭或者没有这个游标的声明!');
　　END;
　　4):异常作用域
　　BEGIN
　　DECLARE
　　exptname EXCEPTION;
　　BEGIN
　　RAISE exptname;
　　END;
　　EXCEPTION WHEN exptname THEN
　　DBMS_OUTPUT.PUT_LINE('触发了子块中异常!');
　　END;
　　上面的语句是不能执行的，因为exptname的作用域只在子块中生效，
　　其实异常情态定义和变量定义一样，作用域生效原则和变量也是一样的。
　　鉴于上面的原因，如果有必要可以预定义自己的异常：
　　CREATE OR REPLACE PACKAGE test_pkg AS
　　exptname EXCEPTION;
　　END;
　　BEGIN
　　BEGIN
　　RAISE test_pkg.exptname;
　　END;
　　EXCEPTION WHEN test_pkg.exptname THEN
　　DBMS_OUTPUT.PUT_LINE('触发了子块中异常!');
　　END;
　　5):常见的预定义情态
　　INVALID_CURSOR  --发生关闭已经关闭的游标或者不存在的游标时出现
　　CURSOR_ALREADY_OPEN  
　　NO_DATA_FOUND  --针对select .. into ..，没有返回行时，或者引用未初始化的plsql索引表
　　TOO_MANY_ROWS  --select .. into 返回多行
　　INVALID_NUMBER与VALID_ERROR  --字符串不能转换成数字错误
　　过程性语句中字符串不能转换成数字，会引发VALUE_ERROR异常情态：
　　DECLARE
　　v_int INT;
　　v_varchar2 varchar2(100);
　　BEGIN
　　v_varchar2:='a12';
　　v_int:=v_varchar2;
　　EXCEPTION WHEN VALUE_ERROR THEN
　　v_int:=REGEXP_REPLACE(v_varchar2,'[^[:digit:]]','' );
　　DBMS_OUTPUT.PUT_LINE(v_int);
　　END;
　　sql语句中则会引发INVALID_NUMBER异常情态：
　　CREATE TABLE test(x INT);
　　BEGIN
　　INSERT INTO TEST VALUES('AB123');
　　EXCEPTION WHEN INVALID_NUMBER THEN
　　DBMS_OUTPUT.PUT_LINE('无效数字!');
　　END;
　　还有两个特殊的异常：
　　PGROGRAM_ERROR和STORAGE_ERROR
　　如果出现STORAGE_ERROR则证明内在不够，如果出现PROGRAM_ERROR则说明你要联系oracle
　　了，因为program_error是PL/SQL引擎出现错误。
　　6):异常handler关键字or，多异常情况匹配
　　BEGIN
　　RAISE VALUE_ERROR;
　　RAISE INVALID_NUMBER;  --注意：这行不执行，因为只要异常情态触发了，就不再返回BEGIN .. END部分执行下面的代码了
　　EXCEPTION WHEN INVALID_NUMBER OR VALUE_ERROR THEN
　　DBMS_OUTPUT.PUT_LINE('Not numeric!');
　　END;
　　7):sqlcode与sqlerrm【过程性函数】
　　DECLARE
　　x VARCHAR(200);
　　y VARCHAR2(200);
　　BEGIN
　　RAISE NO_DATA_FOUND;
　　EXCEPTION WHEN OTHERS THEN
　　x:=SQLCODE;
　　y:=SQLERRM(SQLCODE);
　　INSERT INTO test VALUES(x,y);
　　insert into test VALUES(SYSDATE,SYSTIMESTAMP);
　　END;
　　注意，因为sqlcode与sqlerrm是过程性的函数，所以不可以在sql语句下直接使用，不能像sysdate等系统函数。
　　DECLARE
　　x VARCHAR2(100);
　　y VARCHAR2(200);
　　BEGIN
　　x:=SQLCODE;
　　y:=SQLERRM(0);
　　--SELECT SQLERRM(0) INTO y FROM DUAL;  不能在sql中使用过程性函数，可能跟纯度级别有关系吧
　　DBMS_OUTPUT.PUT_LINE(x||chr(9)||y);
　　END;
　　且sqlerrm只会返回系统内部的oracle错误信息，不会返回用户自定义的信息：
　　BEGIN
　　RAISE NO_DATA_FOUND;
　　EXCEPTION WHEN NO_DATA_FOUND THEN
　　DBMS_OUTPUT.PUT_LINE('Hello '||SQLERRM);
　　END;  
　　如果返回用户定义的消息，岂不死循环了。
　　二，异常传播机制
　　其实在上面的叙述中都已经一一详细介绍过。
　　三，plsql常用异常处理模块--实际应用部分
　　1):dbms_utility.format_call_stack与dbms_utility.format_error_stack简单使用
　　create or replace procedure error_test1 as
　　begin
　　DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_CALL_STAC K);
　　raise value_error;
　　end;
　　create or replace procedure error_test2 as
　　begin
　　error_test1;
　　exception when others then
　　DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STA CK);
　　end;
　　注意：想得到正确的调用堆栈要把dbms_utility.format_call_stack放在最终调用子程序中，
　　因为调用方向总是从外向里调用，所以要得到详细的堆栈当然要沿着调用路线。
　　错误堆栈则相反，因为异常总是按从里向外路线传递，所以想捕获整个错误堆栈就要在祖块捕获。
　　2):在《Oracle 8i PL/SQL程序设计》一书的P265有一个详细的利用dbms_utility.format_error_stack和dbms_utility.format_call_stack的
　　例子程序，非常不错，可以详细的看一看。