首页 > 代码库 > oracle异常(-)

oracle异常(-)

一、概述
异常分成三大类:预定义异常、非预定义异常、自定义异常
处理方法分为:直接抛出异常、内部块处理异常、游标处理异常

预定义异常:由PL/SQL定义的异常。由于它们已在standard包中预定义了,因此,这些预定义异常可以直接在程序中使用,而不必再定义部分声明。

非预定义异常:用于处理预定义异常所不能处理的Oracle错误。

自定义异常:用户自定义的异常,需要在定义部分声明后才能在可执行部分使用。用户自定义异常对应的错误不一定是Oracle错误,例如它可能是一个数据错误。

三种异常中,预定义与非预定义异常都与Oracle错误有关,并且由Oracle隐含自动抛出,
而自定义异常与Oracle错误没有任何关联,由开发人员为特定情况所定义的异常,需要显式抛出(raise)。


二、预定义异常

1、预定义异常的种类
1.1、ACCESS_INTO_NULL
说明:对应于ORA-06530,当创建对象类型应用是,在引用对象属性之前,如果没有初始化对象,
直接为对象属性赋值,则出发此异常。
例子:create type emp_type as object(name varchar2(10),sal number(6,2));
declare 
emp emp_type;
begin
emp.name:=‘scott‘;
exception
when ACCESS_INTO_NULL dbms_output.put_line(‘未初始化‘);
end;

1.2、CASE_NOT_FOUND
说明:对应于ORA-06592,编写CASE语句时,如果在when子句中没有包含必须的条件分支并且没有包含else子句,就会出发此异常。

1.3、COLLECTION_IS_NULL
说明:对应于ORA-06531,在集合元素(嵌套表或VARRAY)赋值时,如果没有初始化集合元素,则会出发此异常。
例子:declare
type ename_table_type is table of emp.ename%type;
ename_table ename_table_type;
begin
select ename into ename_table(2) from emp where empno=&no;
dbms_output.put_line(name_table(2));
exception
when COLLECTION_IS_NULL then dbms_output.put_ling(‘未初始化‘);
end;

1.4、CURSOR_ALREADY_OPEN
说明:ORA-06511,当打开已经打开的游标时,会触发此异常。

1.5、DUP_VAL_ON_INDEX
说明:对应于ORA-00001,当在唯一索引所对应的列上键入重复值时,会触发此异常。

1.6、INVALID_CURSOR
说明:对应于ORA-01001,当试图在不合法的游标上执行操作时,会触发此异常。例如从未打开的游标提取数据或关闭未打开的游标。

1.7、INVALID_NUMBER
说明:对应于ORA-01722,当内嵌sql语句不能有效地将字符转变成数字时,会隐含地触发此异常。

1.8、NO_DATA_FOUND
说明:对应于ORA-01403,当执行selct into未返回行,或者引用了索引表未初始化元素时,会触发此异常。

1.9、TOO_MANY_ROWS
说明:对应于ORA-01422,当执行select into返回超过一行,则触发此异常。

1.10、ZERO_DIVIDE
说明:对应于ORA-01476,当除数为0时,触发此异常。

1.11、SUBSCRIPT_BEYOND_COUNT
说明:对应于ORA-06533,当使用嵌套表或VARRAY元素时,如果元素下标超出范围,会触发此异常。
例子:declare
type emp_array_type is varray(20) of varchar2(10);
emp_array emp_array_type;
begin
emp_array:=emp_array_type(‘scott‘,‘mary‘);
dbms_output.put_line(emp_array(3));
exception
when SUBSCRIPT_BEYOND_COUNT then dbms_output.put_line(‘超出范围‘);
end;

1.12、SUBSCRIPT_OUTSIDE_LIMT
说明:对应于ORA-06532,当使用嵌套表或VARRAY元素时,如果下标为取负数,会触发此异常。

1.13、VALUE_ERROR
说明:对应于ORA-06502,当执行赋值操作时,如果变量长度不足以容纳实际数据,会触发此异常。

1.14、LOGIN_DENIED
说明:对应于ORA-01017,当连接Oracle数据库时,如果用户名或密码不正确,会触发此异常。

1.15、NOT_LOGGED_ON
说明:对应于ORA-01012,如果在没有连接到数据库的情况下,执行PL/SQL块,会触发此异常。

1.16、PROGRAM_ERROR
说明:对应于ORA-06501,如果出现此错误,则表示存在PL/SQL内部问题,用户此时可能需要重新安装数据字典和PL/SQL系统包。

1.17、ROWTYPE_MISMATCH
说明:对应于ORA-06504,当执行赋值操作时,如果宿主游标变量和PL/SQL游标变量的返回类型不兼容,会触发此异常。

1.18、SELF_IS_NULL
说明:对应于ORA-30625,当使用对象类型时,如果在null实例上调用成员方法,会触发此异常。

1.19、STORAGE_ERROR
说明:对应于ORA-06500,PL/SQL块运行时,如果超出内存空间或内存被损坏,会触发此异常。

1.20、SYS_INVALID_ROWID
说明:对应于ORA-01410,当将字符转变为ROWID时,如果使用了无效的字符串,会触发此异常。

1.21、TIMEOUT_ON_RESOURCE
说明:对应于ORA-00051,如果Oracle在等待资源时出现超时错误,会触发此异常。

2、预定义异常的处理

说明:这里说两个常见的异常no_data_found和too_many_rows,这两个异常多由select into语句触发。
举例:
declare
v_cnt number :=800;
v_name emp.ename%type;
begin
select ename into v_name from emp where sal=v_cnt;
dbms_output.put_line(‘姓名:‘ || v_name);
exception
when no_data_found then --直接抛出异常
dbms_output.put_line(‘不存在该工资值的雇员‘);
when too_many_rows then
dbms_output.put_line(‘存在多个雇员具有该工资‘);
when others then
rollback;
dbms_output.put_line(‘异常回滚退出‘);
end;

2.1、no_data_found异常
起因:给一个变量赋值时,查询的结果为空。
说明:如上面例子可以看到,一旦直接抛出异常,就会让过程中断。no_data_found这种异常,没有严重到要让程序中断的地步,可以完全交给由程序进行处理。

2.1.1、使用内部独立块处理
说明:这是一种比较好的处理方式了,不会因为这个异常而引起程序中断。
例如:
declare
v_cnt:=800;
v_name emp.ename%type;
begin
begin
select ename into v_name from emp where sal=v_cnt;
exception
when no_data_found then
v_name:=‘‘;
end;
dbms_output.put_line(‘姓名:‘ || v_name);
exception
when too_many_rows then
dbms_output.put_line(‘存在多个雇员具有该工资‘);
when others then
rollback;
dbms_output.put_line(‘异常回滚退出‘);
end;

2.1.2、使用游标处理
说明:游标操作可以完全避免no_data_found异常。
例如:
declare
v_cnt:=800;
v_name emp.ename%type;
cursor c_cursor is select ename from emp where sal=v_cnt;
begin
open c_cursor;
fetch c_cursor into v_name;
close c_cursor;
dbms_output.put_line(‘姓名:‘ || v_name);
exception
when too_many_rows then
dbms_output.put_line(‘存在多个雇员具有该工资‘);
when others then
rollback;
dbms_output.put_line(‘异常回滚退出‘);
end;

2.2、too_many_rows异常
起因:给一个变量赋值时,查询的结果有多条记录或select into 语句中变量名与表名相同。
说明:返回多条记录如果是可接受的,必须采用游标处理;如果是不可接受的,必须采用内部快处理。

(1)、返回多条记录如果是可接受的,则随便取一条,用游标处理。

(2)、返回多条记录如果是不可接受的,则必须捕获异常,用内部块处理。
举例:
declare
v_cnt:=800;
v_name emp.ename%type;
begin
begin
select ename into v_name from emp where sal=v_cnt;
exception
when no_data_found then
v_name:=‘‘;
when too_many_rows then
v_name:=‘‘;
dbms_output.put_line(‘存在多个雇员具有该工资‘);
end;
dbms_output.put_line(‘姓名:‘ || v_name);
exception
when others then
rollback;
dbms_output.put_line(‘异常回滚退出‘);
end;


三、非预定义异常
说明:非预定义异常用于处理与21个预定义异常无关的Oracle错误。
由于预定义异常只是与一部分Oracle错误相连的异常,所以如果要处理没有与预定义异常对应的Oracle错误时,
则需要为这些Oracle错误声明相应的非预定义异常。声明这样的异常需要使用exception_init编译指令。

exception_init编译指令的定义如下:
pragma exception_init(exception_name,Oracle_error_number);
exception_name是预先被声明的异常名,Oracle_error_number是错误号,这条命令必须写在定义部分。

例子:
declare
e_inte exception; --定义
pragma exception_intt(e_inte,-2291); --关联Oracle错误ORA-2291
begin
update emp set deptno=&dno where empno=&eno;
exception
when e_inte then
dbms_output.put_line(‘部门不存在‘);
end;

注意,通过exception_init,一个自定义异常只能和一个Oracle错误相连,在异常处理语句中,
sqlcode和sqlerrm将返回这个Oracle错误的代码和消息文本,而不是返回用户自定义消息


四、自定义异常

说明:自定义异常与Oracle错误没有任何关联,由开发人员为特定情况所定义的异常,需要显式抛出(raise)。

1、自定义异常的简述
尽管自定义异常的声明与变量的声明类似,但异常是一个错误状态,而不是一个数据项,
所以异常不能出现在赋值语句和sql语句中,但异常的作用域与定义部分其它变量的作用域相同。
如果一个自定义异常被传递到作用域外,则不能再通过原来的名字引用它。为了解决这个问题,
我们可以在包中声明异常,这个异常就可以在任何块中使用,使用时在异常前加包名前缀即可。

自定义异常由raise语句产生(由exception_inti编译指令声明的用户自定义异常也可通过对应的Oracle错误的出现而产生),
当然如果需要,预定义异常也可以使用raise语句来产生。

当一个异常产生是,控制权立即转交给块的异常处理部分。如果该块没有异常处理部分,
则向该块的外一层块传递。一旦控制权交给了异常处理部分,则再没有办法回到块可执行部分。

一条异常处理语句可以处理多个异常,只要在when子句中由or分割多个异常即可。

如果块中的异常没有被处理,则该块会带着未处理的异常返回调用它的程序,这会导致调用它的程序出错。
如果在存储过程中出现异常,则存储过程的out参数将得不到返回值。为了避免未处理异常带来的弊病,
我们最好在块的最外层使用others子句处理块中所有未处理的异常。这样就可以确保所有的错误都能被发现和处理。

如果是定义部分的一个赋值语句产生了异常。即使在当前块的异常处理部分中有处理该异常的处理语句,也不去执行,
而是立刻被传递到外部块中。当异常传递到外部块中以后,按照处理可执行部分中产生的异常一样去处理该异常。

在异常处理语句中也可以产生异常,这个异常可以通过raise语句产生,或是由于出现一个运行错误而产生。
这两种情况下产生的异常都被立刻传递到块外,这与定义部分产生的异常一样。为什么这样处理呢?
因为异常部分每一次只能有一个异常被处理,当一个异常被处理是,产生了另外一个异常,
而一次不能同时处理多个异常,所以将异常处理部分产生的异常传递到块外。

2、自定义异常的简单应用
说明:用户定义异常类型,使用raise显示抛出异常
declare
v_name:=‘mary‘;
v_dno:=80;
e_integrity exception; --定义自定义异常
e_no_rows exception;
pragma exception_init(e_integrity,-2291);
name emp.ename%type:=v_name;
dno emp.deptno%type:=v_dno;
begin
update emp set deptno=v_dno where ename=v_name;
if sql%notfound then
raise e_no_rows; --显示抛出异常
end if;
exception
when e_integrity then
dbms_output.put_line(‘该部门不存在‘);
when e_no_rows then
dbms_output.put_line(‘该雇员不存在‘);
end;

3、raise抛出异常的三种方法
1)、Raise exception:用于抛出当前程序中定义的异常或在 standard 中的系统异常。
2)、Raise package.exception:用于抛出有一些异常是定义在非标准包中的,如UTL_FILE,DBMS_SQL以及程序员创建的包中异常
3)、Raise:不带任何参数,这种情况只出现在希望将当前的异常传到外部程序时。


五、异常的函数

说明:当出现异常,通过使用异常函数可以取得错误号及相关错误信息,另外通过使用raise_application_error也可自定义错误号与错误信息。

1、sqlcode和sqlerrm函数

sqlcode返回异常对象的错误代码号,sqlerrm返回的是对应的错误信息,为了在plsql中处理其他未预料的Oracle错误,
可以在异常处理部分的when others子句后引用这两个函数来确定错误号和信息。

异常种类 SQLCODE SQLERRM
Oracle错误对应的异常 负数 Oracle错误
NO_DATA_FOUND +100 No data found
自定义异常 -1 User-Defined Exception
没有产生异常 0 Oracle-0000

注意:如果使用exception_init预编译指令声明与Oracle错误相连的自定义异常,则SQLCODE和SQLERRM返回
对应的Oracle错误代码和相应的错误信息,而不是返回+1和User-Defined。

如果SQLERRM是可以带一个数字参数,返回值是与这个数字参数相关的文本。

如果要在sql语句中使用sqlcode和sqlerrm,则一定要先把它们的值赋给局部变量,然后再将这些局部变量用在sql语句中。
因为这些函数是过程性的,不能直接用在sql语句中。

EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
v_message := ‘错误行号:‘ || DBMS_UTILITY.format_error_backtrace () || ‘错误代码:‘|| SQLCODE|| ‘错误提示‘|| SQLERRM;
DBMS_OUTPUT.put_line (v_message);
END;

2、raise_application_error
说明:该过程用于自定义错误信息,仅限数据库端子程序使用(过程、函数、包、触发器),不能在匿名块或客户端子程序中。。
语法:raise_application_error(error_number,mesage[,[true|false]]);
其中error_number定义错误号,必输是-20000到-20999之间的负整数;message指定错误信息,不长于2048字节;
第三个为可选参数,true则错误会被放在先前错误的堆栈中,fale则替换先前所有错误,默认为false。

例子:
create or replace procedure raise_comn(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_date_found then
dbms_output.put_line(‘雇员不存在‘);
end;


六、PL/SQL编译警告
说明:10g后新增功能,为了提高plsql子程序的健壮性并避免运行错误,可以激活警告检查功能。

1、PL/SQL警告的分类
severs:检查可能出现的不可预料的结果或错误结果,例如参数别名。
performance:检查可能引起的性能问题,例如执行insert时为number列提供varchar2数据。
informational:检查子程序中的死代码。
all:检查所有警告(上面3种都检查)。

2、PL/SQL警告消息的控制
说明:为了激活警告功能,需要设置初始化参数PLSQL_WARNINGS。
可以通过系统级、会话级、DBMS_VARNINGS系统包、ALTER PROCEDURE命令设置。
可以激活或禁止所有警告或某种警告。

alter system set plsql_warnings=‘enable:all‘; --系统级
alter session set plsql_warnings=‘enable:performance‘; --会话级
call dbms_warning.set_warning_setting_string(‘enable:severs‘,‘session‘); --DBMS_VARNINGS系统包
alter procedure hello compile plsql_warnings=‘enable:performance‘; --ALTER PROCEDURE命令
alter session set plsql_warnings=‘disable:all‘;

3、PL/SQL警告的使用

1)、检测死代码
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;

alter session set plsql_warnings=‘enable:informational‘;--激活
alter procedure dead_code compile;--编译
show errors;--显示

2)、检测引起性能问题的代码
create or replace precedure update_sql(name varchar2,salary varchar2) is
begin 
update emp set sql=salary where ename=name;
end;

alter session set plsql_warnings=‘enable:informational‘;--激活
alter procedure dead_code compile;--编译
show errors;--显示

oracle异常(-)