PL/SQL处理例外



 Oracle内置函数SQLCODE和SQLERRM是特别用在OTHERS处理器中，分别用来返回Oracle的错误代码和错误消息。
　　
　　OTHERS处理器应该是异常处理块中的最后的异常处理器，因为它是用来捕获除了别的异常处理器处理以外的所有的Oracle异常，所以在程序的最外层使用一个OTHERS处理器的话，将可以确保所有的错误都会被检测到。
　　
　　在一个内在的异常中，SQLCODE返回Oracle错误的序号，而SQLERRM返回的是相应的错误消息，错误消息首先显示的是错误代码。SQLCODE返回的是负数，除非Oracle的错误为“ORA-01403：NO DATA FOUND”（译：ORA-01403：未找到数据），当Oracle错误为“ORA-01403：NO DATA FOUND”时，其对应的SQLCODE为+100。对于用户自定义的异常，SQLCODE返回的是+1，而SQLERRM返回的是User-Defined Exception。
　　
　　一个Oracle的错误消息最多只能包含512个字节的错误代码。

 

      如果没有异常被触发，则SQLCODE返回0，SQLERRM返回“ORA-0000：normal, successful completion”。

 

      实例
      Exception
         when osi_general_error then
         when OTHERS THEN
               out_ErrorMsg := SUBSTR(SQLERRM,1,100);
               out_ErrorNbr := ABS(SQLCODE);

转载于 http://blog.csdn.net/azkaser/article/details/4925084

--一.例外简介
--1.例外分类：预定义例外,非预定义例外,自定义例外
--2.处理例外：例外传递-->捕捉例外

--二.处理预定义例外
--见ORALCE预定义异常列表

--三.处理非预定义例外
--非预定义例外用来处理与预定义例外无关的ORACLE错误,若未处理,ORALCE错误传递给调用环境
--DEMO
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
   --定义例外
   e_integrity EXCEPTION;
   --关联例外与ORACLE错误
   PRAGMA EXCEPTION_INIT(e_integrity,-2291);
 BEGIN
   UPDATE emp SET deptno=&dno WHERE empno=&eno;
 EXCEPTION
   --引用例外
   WHEN e_integrity THEN
     dbms_output.put_line('该部门不存在');
   END;
 /
输入 dno 的值:  11
输入 eno 的值:  7788
原值    7:   UPDATE emp SET deptno=&dno WHERE empno=&eno;
新值    7:   UPDATE emp SET deptno=11 WHERE empno=7788;
该部门不存在

PL/SQL 过程已成功完成。

--四.处理自定义例外
--自定义例外处理处理与ORACLE错误无关,由开发人员为特定情况所定义的例外
--DEMO
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
   --定义例外
   e_integrity EXCEPTION;
   --关联例外与ORACLE错误
   PRAGMA EXCEPTION_INIT(e_integrity,-2291);
  7    --自定义例外
   e_no_employee EXCEPTION;
 BEGIN
   UPDATE emp SET deptno=&dno WHERE empno=&eno;
   IF SQL%NOTFOUND THEN
     RAISE e_no_employee;
   END IF;
 EXCEPTION
   --引用例外
   WHEN e_integrity THEN
     dbms_output.put_line('该部门不存在');
   WHEN e_no_employee THEN
     dbms_output.put_line('该雇员不存在');
   END;
 /
输入 dno 的值:  10
输入 eno 的值:  1111
原值   10:   UPDATE emp SET deptno=&dno WHERE empno=&eno;
新值   10:   UPDATE emp SET deptno=10 WHERE empno=1111;
该雇员不存在

PL/SQL 过程已成功完成。

--五.使用例外函数
--1.SQLCODE,SQLERRM
--SQLCODE用于返回ORACLE错误号
--SQLERRM用于返回该错误号所对应的错误消息
SQL> undef v_sal
SQL> DECLARE
   v_ename emp.ename%TYPE;
 BEGIN
   SELECT ename INTO v_ename FROM emp
     WHERE sal=&&v_sal;
   dbms_output.put_line('雇员名：'||v_ename);
 EXCEPTION
   WHEN NO_DATA_FOUND THEN
     dbms_output.put_line('不存在工资为：'||&v_sal||'的雇员');
   WHEN OTHERS THEN
     dbms_output.put_line('错误号：'||SQLCODE);
     dbms_output.put_line('错误信息：'||SQLERRM);
 END;
 /
输入 v_sal 的值:  9876
原值    5:     WHERE sal=&&v_sal;
新值    5:     WHERE sal=9876;
原值    9:     dbms_output.put_line('不存在工资为：'||&v_sal||'的雇员');
新值    9:     dbms_output.put_line('不存在工资为：'||9876||'的雇员');
不存在工资为：9876的雇员

PL/SQL 过程已成功完成。

--2.RAISE_APPLICATION_ERROR在PL/SQL应用程序中自定义错误消息
--该过程只能在数据库端的子程序中执行,而不能在匿名块或客户端的子程序中使用
--语法：raise_application_error(error_number,message[,{TRUE,FALSE}]);
--error_number：定义错误号,错误号必须是在-20000至-20999之间的负整数
--message：指定错误消息
--TRUE：该错误会与之前的错误一起放在堆栈中保存
--FALSE：默认值,替换先前的所有错误
--DEMO
SQL> CREATE or REPLACE PROCEDURE raise_comm
   (eno NUMBER,commission NUMBER)
 IS
   v_comm emp.comm%TYPE;
 BEGIN
   SELECT comm INTO v_comm FROM emp WHERE empno=eno;
   IF v_comm IS NULL THEN
     RAISE_APPLICATION_ERROR(-20001,'该雇员无补助');
   END IF;
 EXCEPTION
   WHEN NO_DATA_FOUND THEN
     dbms_output.put_line('该雇员不存在');
 END;
 /

过程已创建。

SQL> exec raise_comm(7788,100)
BEGIN raise_comm(7788,100); END;

*
第 1 行出现错误:
ORA-20001: 该雇员无补助
ORA-06512: 在 "SCOTT.RAISE_COMM", line 8
ORA-06512: 在 line 1

--六.PL/SQL编译警告
--1.PL/SQL警告分类
--SEVERE:该种警告用于检查可能出现的不可预料结果或错误结果，例如参数的别名问题。
--PERFORMANCE：该类警告用于检查可能引起的性能问题，例如在执行INSERT操作时，为NUMBER列提供了VARCHAR2类型的数据。
--INFORMATION：该类警告用于检查子程序中的死代码。
--ALL：用于检查所有警告。

--2.控制PL/SQL警告信息
ALTER SYSTEM SET PLSQL_WARNINGS='ENABLE:ALL';
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:PERFORMANCE';
ALTER PROCEDURE hello COMPILE PLSAL_WARNINGS='ENABLE:PERFORMANCE';
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL';
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEVERE','DISABLE:PERFORMANCE','ERROR:06002';
CALL DBMS_WARNINGS.SET_WARNING_SETTING_STRING('ENABLE:ALL','SESSION');

--3.使用PL/SQL编译警告
--3.1检测死代码
SQL> CREATE OR REPLACE PROCEDURE dead_code AS
   x NUMBER:=10;
 BEGIN
   IF x=10 THEN
     x:=20;
   ELSE
     x:=100;
   END IF;
 END dead_code;
 /

过程已创建。

SQL> ALTER SESSION SET PLSQL_WARNINGS='ENABLE:INFORMATIONAL';

会话已更改。

SQL> ALTER PROCEDURE dead_code COMPILE;

SP2-0805: 过程已变更, 但带有编译警告

SQL> show errors;
PROCEDURE DEAD_CODE 出现错误:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/7      PLW-06002: 无法执行的代码
7/5      PLW-06002: 无法执行的代码

--3.2检测引起性能问题的代码
SQL> show errors;
PROCEDURE DEAD_CODE 出现错误:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/7      PLW-06002: 无法执行的代码
7/5      PLW-06002: 无法执行的代码
SQL> ed a

SQL> CREATE OR REPLACE PROCEDURE update_sal
   (name VARCHAR2,salary VARCHAR2)
 IS
 BEGIN
   UPDATE emp SET sal=salary WHERE ename=name;
 END;
 /

过程已创建。

SQL> ALTER SESSION SET PLSQL_WARNINGS='ENABLE:PERFORMANCE';

会话已更改。

SQL> ALTER PROCEDURE update_sal COMPILE;

SP2-0805: 过程已变更, 但带有编译警告

SQL> show errors;
PROCEDURE UPDATE_SAL 出现错误:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/22     PLW-07202: 绑定类型可能会导致从列类型转换为其它类型

ORALCE预定义EXCEPTION列表

常见预定义例外	ORACLE服务错误号	错误描述
ACCESS_INTO_NULL	ORA-06530	引用对象属性前，必须先初始化对象
CASE_NOT_FOUND	ORA-06592	CASE语句中没有包含必须的条件分支且未包含ELSE子句
COLLECTION_IS_NULL	ORA-06531	在给集合元素赋值前，必须首先初始化集合元素
CURSOR_ALREADY_OPEN	ORA-06511	重新打开已经打开的游标
DUP_VAL_ON_INDEX	ORA-00001	在唯一索引所对应的列上键入重复值时
INVALID_VURSOR	ORA-01001	视图在不合法的游标上执行操作时（关闭未打开的游标，从未打开的游标上提取数据）
INVALID_NUMBER	ORA-01722	内嵌SQL不能有效地将字符转换成数字时
NO_DATA_FOUND	ORA-01403	当执行SELECT   INTO未返回行或引用了索引表未初始化的元素时
TOO_MANY_ROWS	ORA-01422	当执行SELECT   INTO返回值超过一行时
ZERO_DIVIDE	ORA-01476	除0
SUBSCRIPT_BEYOND_COUNT	ORA-06533	使用嵌套表或VARRAY，元素下标超出元素的范围时
SUBSCRIPT_OUTSIDE_LIMIT	ORA-06532	使用嵌套表或VARRAY，元素下标为负数时
VALUE_ERROR	ORA-06502	所赋的值比变量长度要大时
其他预定义例外	ORACLE服务错误号	错误描述
LOGIN_DENIED	ORA-01017	PL/SQL应用程序需要连接到ORALCE数据库，提供了不正确的用户名或口令时
NOT_LOGGEN_ON	ORA-01012	PL/SQL应用程序未连接到ORALCE数据库，执行了PL/SQL块中访问数据库时
PROGRAM_ERROR	ORA-06501	存在PL/SQL内部错误（用户可能需要重新安装数据字典和PL/SQL系统包）
ROWTYPE_MISMATH	ORA-06504	当赋值时，宿主游标变量与PL/SQL游标返回的类型不兼容
SELF_IS_NULL	ORA-30625	当使用对象类型时，如果在NULL实例上调用成员的方法
STORAGE_ERROR	ORA-06500	PL/SQL运行时，超出内存空间或内存被损坏
SYS_INVALID_ROWID	ORA-01410	字符串转变成ROWID时，必须使用有效的字符串，如无效时
TIMOUT_ON_RESOURCE	ORA-00051	ORACLE在等待池里出现超时