ORACLE PL/SQL异常处理(Exception)学习笔记

2022-09-20 15:11:03

1、PL/SQL错误类型

 

错误类型

报告者

处理方法

编译时错误

PL/SQL编译器

交互式地处理：编译器报告错误，你必须更正这些错误

运行时错误

PL/SQL运行时引擎

程序化地处理：异常由异常处理子程序引发并进行捕获

 

 

2、异常的声明

 

有两种异常：用户自定义异常和预定义异常

 

用户自定义异常就是由程序员自己定义的一个错误。该错误还不是非常重要，所以并没有将整个错误包含在Oracle的错误中。例如，它可能是一个与数据有关的错误。而预定义异常则对应于一般的SQL和PL/SQL错误。

 

用户自定义异常是在PL/SQL块的声明部分声明的。像变量一样，异常也有一个类型（EXCEPTION）和有效范围。例如：

 

 

[php] view plaincopy
 
  1. DECLARE  
  2.   
  3. Exception_name EXCEPTION;  
  4.   
  5. …  
  6.   
  7.    

 

 

 

 

3、异常的引发

 

与异常相关联的错误发生的时候，就会引发相应的异常。用户自定义异常是通过RAISE语句显式引发的，而预定义异常则是在它们关联的ORACLE错误发生的时候隐式引发的。如果发生了一个还没有和异常进行关联的ORACLE错误的时候，也会引发一个异常。该异常可以使用OTHERS子程序进行捕获。预定义的异常也可以使用RAISE进行显式地引发，如果需要这样做的话。

 

 

 

[php] view plaincopy
 
  1. …  
  2.   
  3. RAISE exception_name;  
  4.   
  5. …  

  

 

 

4、异常的处理

发生异常的时候，程序的控制就会转移到代码块的异常处理部分。异常处理部分是由异常处理子程序组成的，这些异常处理子程序可以是针对某些异常的，也可以是针对所有异常的。与该异常相关联的错误发生，并引发了该异常的时候，就会执行异常处理部分的代码。

 

异常处理部分的语法如下：

 

 

 

[php] view plaincopy
 
  1. EXCEPTION  
  2.   
  3. WHEN exception_name THEN  
  4.   
  5. Sequence_of_statements1;  
  6.   
  7. WHEN exception_name THEN  
  8.   
  9. Sequence_of_statements2;  
  10.   
  11. [WHEN OTHERS THEN  
  12.   
  13. Sequence_of_statements3;]  
  14.   
  15. END;  

 

 

每一个异常处理部分都是由WHEN子句和引发异常以后要执行的语句组成的。WHEN标识这个处理子程序是针对哪个异常的。

 

OTHERS异常处理子程序

PL/SQL定义了一个异常处理子程序，即OTHERS。当前异常处理部分定义的所有WHEN语句都没有处理的任意一个已引发的异常，都会导致执行这个OTHERS异常处理子程序。该异常处理子程序应该总是作为代码块的最后一个异常处理子程序，这样就会首先扫描前面的异常处理子程序。WHEN OTHERS会捕获所有异常，不管这些异常是预定义的，还是用户自定义的。

 

检查错误堆栈—SQLCODE和SQLERRM

PL/SQL使用两个内置函数SQLCODE和SQLERRM提供错误信息。SQLCODE返回的是当前的错误代号，而SQLERRM返回的是当前的错误信息文本。如果是用户自定义的异常，SQLCODE就会返回值1，SQLERRM就会返回“ User-defined Exception”。

 

下面是一个使用SQLCODE和SQLERRM的例子

 

 

[php] view plaincopy
 
  1. DECLARE  
  2.   
  3.   -- Exception to indicate an error condition  
  4.   
  5.   e_DuplicateAuthors EXCEPTION;  
  6.   
  7.    
  8.   
  9.   -- IDs for three authors  
  10.   
  11.   v_Author1 books.author1%TYPE;  
  12.   
  13.   v_Author2 books.author2%TYPE;  
  14.   
  15.   v_Author3 books.author3%TYPE;  
  16.   
  17.     
  18.   
  19.   -- Code and text of other runtime errors  
  20.   
  21.   v_ErrorCode log_table.code%TYPE;  
  22.   
  23.   v_ErrorText log_table.message%TYPE;  
  24.   
  25. BEGIN  
  26.   
  27.   /* Find the IDs for the 3 authors of ‘Oracle9i DBA 101‘ */  
  28.   
  29.   SELECT author1, author2, author3  
  30.   
  31.     INTO v_Author1, v_Author2, v_Author3  
  32.   
  33.     FROM books  
  34.   
  35.     WHERE title = ‘Oracle9i DBA 101‘;  
  36.   
  37.     
  38.   
  39.   /* Ensure that there are no duplicates */  
  40.   
  41.   IF (v_Author1 = v_Author2) OR (v_Author1 = v_Author3) OR  
  42.   
  43.      (v_Author2 = v_Author3) THEN  
  44.   
  45.      RAISE e_DuplicateAuthors;  
  46.   
  47.   END IF;  
  48.   
  49. EXCEPTION  
  50.   
  51.   WHEN e_DuplicateAuthors THEN  
  52.   
  53.     /* Handler which executes when there are duplicate authors for 
  54.  
  55.        Oracle9i DBA 101.  We will insert a log message recording  
  56.  
  57.        what has happened. */  
  58.   
  59.     INSERT INTO log_table (info)  
  60.   
  61.       VALUES (‘Oracle9i DBA 101 has duplicate authors‘);  
  62.   
  63.   WHEN OTHERS THEN  
  64.   
  65.     /* Handler which executes for all other errors. */  
  66.   
  67.     v_ErrorCode := SQLCODE;  
  68.   
  69.     -- Note the use of SUBSTR here.  
  70.   
  71.     v_ErrorText := SUBSTR(SQLERRM, 1, 200);  
  72.   
  73.     INSERT INTO log_table (code, message, info) VALUES  
  74.   
  75.       (v_ErrorCode, v_ErrorText, ‘Oracle error occurred‘);  
  76.   
  77. END;  
  78.   
  79. /  

 

 

由于该堆栈上每一条错误消息文本的最大长度均为512个字节，但是堆栈中可能会有多条消息文本。在上面的例子中，v_ErrorText只有200个字符。如果该错误消息文本长度大于200个字符，那么赋值语句

 

v_ErrorText := SQLERRM;

 

就会引发预定义的异常VALUE_ERROR。为了防止发生这种异常，我们使用了内置函数SUBSTR。

 

注意，SQLCODE和SQLERRM的返回值首先会被分配给局部变量，然后再在SQL语句中使用这些局部变量。因为这些函数都是过程化的函数，所以不能直接在SQL语句中使用它们。

 

通过下面这个例子我们看看错误号和相应的错误消息文本之间的关系

 

 

[php] view plaincopy
 
  1. set serveroutput on  
  2.   
  3. BEGIN  
  4.   
  5.   DBMS_OUTPUT.PUT_LINE(‘SQLERRM(0): ‘ || SQLERRM(0));  
  6.   
  7.   DBMS_OUTPUT.PUT_LINE(‘SQLERRM(100): ‘ || SQLERRM(100));  
  8.   
  9.   DBMS_OUTPUT.PUT_LINE(‘SQLERRM(10): ‘ || SQLERRM(10));  
  10.   
  11.   DBMS_OUTPUT.PUT_LINE(‘SQLERRM: ‘ || SQLERRM);  
  12.   
  13.   DBMS_OUTPUT.PUT_LINE(‘SQLERRM(-1): ‘ || SQLERRM(-1));  
  14.   
  15.   DBMS_OUTPUT.PUT_LINE(‘SQLERRM(-54): ‘ || SQLERRM(-54));  
  16.   
  17. END;  
  18.   
  19. /  
  20.   
  21. --运行结果如下  
  22.   
  23. SQL> @SQLERRM.sql  
  24.   
  25. SQLERRM(0): ORA-0000: normal, successful completion  
  26.   
  27. SQLERRM(100): ORA-01403: no data found  
  28.   
  29. SQLERRM(10):  -10: non-ORACLE exception  
  30.   
  31. SQLERRM: ORA-0000: normal, successful completion  
  32.   
  33. SQLERRM(-1): ORA-00001: unique constraint (.) violated  
  34.   
  35. SQLERRM(-54): ORA-00054: resource busy and acquire with NOWAIT specified  
  36.   
  37.    
  38.   
  39. PL/SQL procedure successfully completed.  

  

 

 

EXCEPTION_INIT pragma

 

你可以将一个经过命名的异常和一个特别的ORACLE错误相关联。这会使你专门能够捕获此错误，而不是通过WHEN OTHERS处理器来进行捕获。EXCEPTION_INIT pragma的语法如下：

 

PRAGMA EXCEPTION_INIT(exception_name,Oracle_error_number);

 

这里，exception_name是在PRAGMA前面声明的异常的名字，而Oracle_error_number是与此命名异常相关的所需错误代码。这个PRAGMA必须在声明部分。

 

下面这个例子在运行时刻如果遇到“ORA-1400:mandatory NOT NULL column missing or NULL during insert”错误时将引发e_MissingNull--用户定义的异常。

 

 

 

[php] view plaincopy
 
  1. DECLARE  
  2.   
  3.   e_MissingNull EXCEPTION;  
  4.   
  5.   PRAGMA EXCEPTION_INIT(e_MissingNull, -1400);  
  6.   
  7. BEGIN  
  8.   
  9.   INSERT INTO students (id) VALUES (NULL);  
  10.   
  11. EXCEPTION  
  12.   
  13.   WHEN e_MissingNull then  
  14.   
  15.     INSERT INTO log_table (info) VALUES (‘ORA-1400 occurred‘);  
  16.   
  17. END;  
  18.   
  19. /  

 

 

 

每次发生PRAGMA EXCEPTION_INIT时，一个Oracle错误只能和一个用户自定义异常相关联。在异常处理内部，SQLCODE和SQLERRM将会返回发生Oracle错误的代码和错误消息，但是不会返回用户定义的消息。

 

 

使用RAISE_APPLICATION_ERROR

你可以使用内置函数RAISE_APPLICATION_ERROR以创建自己的错误消息，这可能要比已命名的异常更具说明性。用户定义消息从块中传递到调用环境中的方式和ORACLE错误是一样的。语法如下：

 

RAISE_APPLICATION_ERROR(error_number,error_message,[keep_errors]);

 

error_number是从-200000到-20999之间的参数，error_message是与此错误相关的正文，不能多于512个字节。而keep_errors是一个布尔值，是可选的，如果为TRUE，那么新的错误将被添加到已经引发的错误列表中（如果有的话）。如果为FALSE（这是缺省的设置），那么新的错误将替换错误的当前列表。

 

例如下面的这个例子将在为一个新的学生注册以前检查是否在班级中有足够的地方容纳他。

 

 

[php] view plaincopy
 
  1. CREATE OR REPLACE PROCEDURE Register (  
  2.   
  3.   /* Registers the student identified by the p_StudentID parameter in the class 
  4.  
  5.      identified by the p_Department and p_Course parameters. Before calling 
  6.  
  7.      ClassPackage.AddStudent, which actually adds the student to the class, this 
  8.  
  9.      procedure verifies that there is room in the class, and that the class 
  10.  
  11.      exists. */  
  12.   
  13.   p_StudentID IN students.id%TYPE,  
  14.   
  15.   p_Department IN classes.department%TYPE,  
  16.   
  17.   p_Course IN classes.course%TYPE) AS  
  18.   
  19.    
  20.   
  21.   v_CurrentStudents NUMBER;  -- Current number of students in the class  
  22.   
  23.   v_MaxStudents NUMBER;      -- Maximum number of students in the class  
  24.   
  25.    
  26.   
  27. BEGIN  
  28.   
  29.   /* Determine the current number of students registered, and the maximum 
  30.  
  31.      number of students allowed to register. */  
  32.   
  33.   SELECT current_students, max_students  
  34.   
  35.     INTO v_CurrentStudents, v_MaxStudents  
  36.   
  37.     FROM classes  
  38.   
  39.     WHERE course = p_Course  
  40.   
  41.     AND department = p_Department;  
  42.   
  43.    
  44.   
  45.   /* Make sure there is enough room for this additional student. */  
  46.   
  47.   IF v_CurrentStudents + 1 > v_MaxStudents THEN  
  48.   
  49.     RAISE_APPLICATION_ERROR(-20000, ‘Can‘‘t add more students to ‘ ||  
  50.   
  51.       p_Department || ‘ ‘ || p_Course);  
  52.   
  53.   END IF;  
  54.   
  55.    
  56.   
  57.   /* Add the student to the class. */  
  58.   
  59.   ClassPackage.AddStudent(p_StudentID, p_Department, p_Course);  
  60.   
  61.    
  62.   
  63. EXCEPTION  
  64.   
  65.   WHEN NO_DATA_FOUND THEN  
  66.   
  67.     /* Class information passed to this procedure doesn‘t exist. Raise an error 
  68.  
  69.        to let the calling program know of this. */  
  70.   
  71.     RAISE_APPLICATION_ERROR(-20001, p_Department || ‘ ‘ || p_Course ||  
  72.   
  73.       ‘ doesn‘‘t exist!‘);  
  74.   
  75. END Register;   
  76.   
  77. /  

  

 

 

5、异常的传播

 

1）在执行部分引发的异常

当一个异常是在块的执行部分引发的，PL/SQL使用下面的方法决定要激活哪个异常处理器：

如果当前块对该异常设置了处理器，那么执行它并成功完成该块的执行，然后控制会转给包含块。

如果当前块没有对当前异常定义处理器，那么通过在包含块中引发它来传播异常。然后对包含块执行步骤一。

 

2）在声明部分引发的异常

如果在声明部分的赋值操作引发了一个异常，那么该异常将立即传播给包含块。发生这种情况以后，在前面给出的法则将进一步被应用到异常的传播上。尽管在当前块中有一个处理器，它也不会被执行。

 

3）在异常处理部分引发的异常

在异常处理器中也可能引发异常，这可以是通过RAISE语句显式引发的，也可以是由运行时刻错误隐含引发的。无论怎样，异常都立即被传播给包含块，这和声明部分引发的异常相类似。

 

 

6、使用异常的准则

 

1）异常的范围

异常像变量一样，也是有一定范围的。如果用户自定义异常传播到它的范围之外，就不能再通过名称引用它。

 

 

 

[php] view plaincopy
 
  1. BEGIN  
  2.   
  3.   DECLARE  
  4.   
  5.     e_UserDefinedException EXCEPTION;  
  6.   
  7.   BEGIN  
  8.   
  9.     RAISE e_UserDefinedException;  
  10.   
  11.   END;  
  12.   
  13. EXCEPTION  
  14.   
  15.   /* e_UserDefinedException is out of scope here - can only be 
  16.  
  17.      handled by an OTHERS handler */  
  18.   
  19.   WHEN OTHERS THEN  
  20.   
  21.     /* Just re-raise the exception, which will be propagated to the 
  22.  
  23.        calling environment */  
  24.   
  25.     RAISE;  
  26.   
  27. END;  
  28.   
  29. /  

 

 

 

一般而言，如果打算将用户自定义的错误传播到代码块之外，最好的方法就是在包中定义该异常，以使其在该代码块之外仍可见，或使用RAISE_APPLICATION_ERROR引发该异常。如果创建一个成为GLOBALS的包，并在其中定义了一个e_UserDefinedException异常，那么这个异常在外部块中仍然可见。

 

如下例所示

 

 

[php] view plaincopy
 
  1. CREATE OR REPLACE PACKAGE Globals AS  
  2.   
  3. /* This package contains global declarations. Objects declared here will  
  4.  
  5.    be visible via qualified references for any other blocks or procedures. 
  6.  
  7.    Note that this package does not have a package body. */  
  8.   
  9.    
  10.   
  11.   /* A user-defined exception. */  
  12.   
  13.   e_UserDefinedException EXCEPTION;  
  14.   
  15. END Globals;  
  16.   
  17. /  
  18.   
  19.    
  20.   
  21. --有了这个和GLOBALS包以后，就可以重写前面的代码：  
  22.   
  23.    
  24.   
  25. BEGIN  
  26.   
  27.   BEGIN  
  28.   
  29.     RAISE Globals.e_UserDefinedException;  
  30.   
  31.   END;  
  32.   
  33. EXCEPTION  
  34.   
  35.   /* Since e_UserDefinedException is still visible, we can handle it  
  36.  
  37.      explicitly */  
  38.   
  39.   WHEN Globals.e_UserDefinedException THEN           --引用包中定义异常  
  40.   
  41.     /* Just re-raise the exception, which will be propagated to the 
  42.  
  43.        calling environment */  
  44.   
  45.     RAISE;  
  46.   
  47. END;  
  48.   
  49. /  

 

 

 

2）避免未处理的异常

优秀的编程经验是在整个程序中避免出现任何未经过处理的异常。这可以通过在程序的最顶层使用一个OTHERS子程序来实现。该处理子程序可以只登记错误并登记错误发生的位置，通过这种方法，就可以保证每个错误都会得到检查。

 

如下例所示

 

 

[php] view plaincopy
 
  1. DECLARE  
  2.   
  3. v_errornumber number;  
  4.   
  5. v_errortext varchar2(200);  
  6.   
  7. Begin  
  8.   
  9. …  
  10.   
  11. EXCEPTION  
  12.   
  13. WHEN OTHERS THEN  
  14.   
  15. v_errornumber:=SQLCODE;  
  16.   
  17. v_errortext:=SUBSTR(SQLERRM,1,200);  
  18.   
  19. INSERT INTO log_table(code,message,info)  
  20.   
  21. VALUES  
  22.   
  23. (v_errornumber,v_errortext,’Oracle error occurred at’||TO_CHAR(SYSDATE,’DD-MON-YY HH24:MI:SS’));  
  24.   
  25. END;  

  

 

 

3）标识错误发生的位置

由于整个代码块都使用同一个异常处理部分检查并处理异常，所以很难确定引发这个错误的是哪一条SQL语句。考虑下面示例

 

 

 

[python] view plaincopy
 
  1. BEGIN  
  2.   
  3. SELECT…  
  4.   
  5. SELECT..  
  6.   
  7. SELECT…  
  8.   
  9. EXCEPTION  
  10.   
  11. WHEN NO_DATA_FOUND THEN  
  12.   
  13. --which select statement raised the exception?  
  14.   
  15. END;  
  16.   
  17.    
  18.   
  19. --解决上述问题的方法有两种。第一种是添加一个标识该SQL语句的计数器：  
  20.   
  21.    
  22.   
  23. DECLARE  
  24.   
  25. V_selectcounter NUMBER:=1;  
  26.   
  27. BEGIN  
  28.   
  29. SELECT…  
  30.   
  31. V_selectcounter NUMBER:=2;  
  32.   
  33. SELECT…  
  34.   
  35. V_selectcounter NUMBER:=3;  
  36.   
  37. SELECT…  
  38.   
  39. EXCEPTION  
  40.   
  41. WHEN NO_DATA_FOUND THEN  
  42.   
  43. INSERT INTO log_table(info) VALUES(‘NO DATA FOUND IN SELECT’||v_selectcounter);  
  44.   
  45. END;  
  46.   
  47.    
  48.   
  49. --另一种方法是将每一条语句都放置在它自己的子块中：  
  50.   
  51.    
  52.   
  53. BEGIN  
  54.   
  55. BEGIN  
  56.   
  57.               SELECT…  
  58.   
  59.        EXCEPTION  
  60.   
  61.               WHEN NO_DATA_FOUND THEN  
  62.   
  63.                      INSERT INTO log_table(info) VALUES(‘NO DATA FOUND IN SELECT 1’);  
  64.   
  65.        END;  
  66.   
  67. BEGIN  
  68.   
  69.               SELECT…  
  70.   
  71.        EXCEPTION  
  72.   
  73.               WHEN NO_DATA_FOUND THEN  
  74.   
  75.                      INSERT INTO log_table(info) VALUES(‘NO DATA FOUND IN SELECT 2’);  
  76.   
  77.        END;  
  78.   
  79. BEGIN  
  80.   
  81.               SELECT…  
  82.   
  83.        EXCEPTION  
  84.   
  85.               WHEN NO_DATA_FOUND THEN  
  86.   
  87.                      INSERT INTO log_table(info) VALUES(‘NO DATA FOUND IN SELECT 3’);  
  88.   
  89.        END;  
  90.   
  91. END;  

  

 

 

7、异常代码的编写风格

 

1）RAISE_APPLICATION_ERROR和RAISE的比较

 

RAISE_APPLICATION_ERROR

RAISE

允许我们填写自己的错误消息文本，该文本可以包含应用程序专用的数据

不允许包含消息文本

不能使用已命名的异常处理子程序进行捕获，只能使用OTHERS处理子程序进行捕获

可以使用已命名的处理子程序捕获该异常，只要该异常在它自己的异常范围内即可

 

通常而言，推荐对设计给终端用户看的错误，使用RAISE_APPLICATION_ERROR。因为对于他们而言，具体的错误编号和描述性文本非常有用。而另一方面，对设计为由程序直接进行处理的异常，推荐使用RAISE。

 

2）将异常作为控制语句

因为引发异常会将程序的控制逻辑转移到代码块的异常处理部分，所以可以将RAISE语句用作控制语句，就像GOTO语句一样。例如，如果我们有很深的嵌套循环，并需要立即从中退出的时候，这可能会非常有用。

 

http://blog.csdn.net/wanghai__/article/details/4736501

ORACLE PL/SQL异常处理(Exception)学习笔记,布布扣,bubuko.com

ORACLE PL/SQL异常处理(Exception)学习笔记

上一篇：CI 2.2.0可以使用AR模式操作Oracle 10g数据库


下一篇：typedef和#define的区别——————【Badboy】