PL/SQL处理例外

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

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

--三.处理非预定义例外
--非预定义例外用来处理与预定义例外无关的ORACLE错误,若未处理,ORALCE错误传递给调用环境
--DEMO
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2    --定义例外
  3    e_integrity EXCEPTION;
  4    --关联例外与ORACLE错误
  5    PRAGMA EXCEPTION_INIT(e_integrity,-2291);
  6  BEGIN
  7    UPDATE emp SET deptno=&dno WHERE empno=&eno;
  8  EXCEPTION
  9    --引用例外
 10    WHEN e_integrity THEN
 11      dbms_output.put_line('该部门不存在');
 12    END;
 13  /
输入 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
  2    --定义例外
  3    e_integrity EXCEPTION;
  4    --关联例外与ORACLE错误
  5    PRAGMA EXCEPTION_INIT(e_integrity,-2291);
  6
  7    --自定义例外
  8    e_no_employee EXCEPTION;
  9  BEGIN
 10    UPDATE emp SET deptno=&dno WHERE empno=&eno;
 11    IF SQL%NOTFOUND THEN
 12      RAISE e_no_employee;
 13    END IF;
 14  EXCEPTION
 15    --引用例外
 16    WHEN e_integrity THEN
 17      dbms_output.put_line('该部门不存在');
 18    WHEN e_no_employee THEN
 19      dbms_output.put_line('该雇员不存在');
 20    END;
 21  /
输入 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
  2    v_ename emp.ename%TYPE;
  3  BEGIN
  4    SELECT ename INTO v_ename FROM emp
  5      WHERE sal=&&v_sal;
  6    dbms_output.put_line('雇员名：'||v_ename);
  7  EXCEPTION
  8    WHEN NO_DATA_FOUND THEN
  9      dbms_output.put_line('不存在工资为：'||&v_sal||'的雇员');
 10    WHEN OTHERS THEN
 11      dbms_output.put_line('错误号：'||SQLCODE);
 12      dbms_output.put_line('错误信息：'||SQLERRM);
 13  END;
 14  /
输入 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
  2    (eno NUMBER,commission NUMBER)
  3  IS
  4    v_comm emp.comm%TYPE;
  5  BEGIN
  6    SELECT comm INTO v_comm FROM emp WHERE empno=eno;
  7    IF v_comm IS NULL THEN
  8      RAISE_APPLICATION_ERROR(-20001,'该雇员无补助');
  9    END IF;
 10  EXCEPTION
 11    WHEN NO_DATA_FOUND THEN
 12      dbms_output.put_line('该雇员不存在');
 13  END;
 14  /

过程已创建。

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
  2    x NUMBER:=10;
  3  BEGIN
  4    IF x=10 THEN
  5      x:=20;
  6    ELSE
  7      x:=100;
  8    END IF;
  9  END dead_code;
 10  /

过程已创建。

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
  2    (name VARCHAR2,salary VARCHAR2)
  3  IS
  4  BEGIN
  5    UPDATE emp SET sal=salary WHERE ename=name;
  6  END;
  7  /

过程已创建。

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在等待池里出现超时