13.处理例外
1.例外分类:预定义例外,非预定义例外,自定义例外三种
2.例外处理:
传递例外:如果在例外处理部分EXCEPTON没有捕捉例外,ORACLE会将例外传递到调用环境.
捕捉并处理例外:使用例外处理部分完成
- exception
- when exception1 [or exception2...] then
- statement1;
- statement2;
- .....
- when ....
- ...
- when others then --必须是例外处理部分的最后一条子句
- statement1;
- ...
2.处理预定义例外
常用预定义例外
2.1access_into_null:ora-06530错误.在引用对象属性之前,必须首先初始化对象,否则触发例外
2.2case_not_found:ora-06592.在编写CASE语句时,如果在WHEN子句中没有包含必须的条件分支,并且没有包含ELSE子句,就会触发
- undef no
- declare
- v_sal emp.sal%type;
- begin
- select sal into v_sal from emp where empno=&&no;
- case
- when v_sal<1000 then
- update emp set salsal=sal+100 where empno=&no;
- when v_sal<2000 then
- update emp set salsal=sal+150 where empno=&no;
- when v_sal<3000 then
- update emp set salsal=sal+200 where empno=&no;
- end case;
- exception
- when case_not_found then
- dbms_output.put_line('在CASE语句中缺少与'||v_sal||'相关的条件');
- end;
2.3collection_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('雇员名:||ename_table(2);
- exception
- when collection_null then
- dbms_output.put_line('必须使用构造方法初始化集合元素');
- end;
2.4cursor_already_open:ora-06511
当重新打开已经打开的游标时,会隐含的触发例外.已经使用OPEN打开了显示游标,并执行FOR循环,就会隐含的触发该例外
- declare
- cursor emp_cursor is select ename,sal from emp;
- begin
- open emp_cursor;
- for emp_record in emp_cursor loop
- dbms_output.put_line(emp_record.ename);
- end loop;
- exception
- when cursor_already_open then
- dbms_output.put_line('游标已打开');
- end;
2.5DUP_VAL_ON_INDEX:ORA-00001
当在唯一索引所对应的列上键入重复值时,触发例外
- begin
- update dept set deptno=&new_no where deptno=&old_no;
- exception
- when dup_val_on_index then
- dbms_output.put_line('在DEPTNO列上不能出现重复值');
- end;
2.6invalid_cursor:ora-01001
当试图在不合法的游标上执行操作时,会隐含的触发例外.要从未打开的游标提取数据,或者关闭未打开的游标,则触发例外
- declare
- cursor emp_cursor is select ename,sal from emp;
- emp_record emp_cursor%rowtype;
- begin
- fetch emp_cursor into emp_record;
- close emp_cursor;
- exception
- when invalid_cursor then
- dbms_output.put_line('请检查游标是否已经打开');
- end;
2.7invalid_number:ora-01722
当内嵌SQL语句不能有效的将字符转变成数字时,会隐含触发例外
- begin
- update emp set salsal=sal+'1oo';
- exception
- when invalid_number then
- dbms_output.put_line('输入的数字值不正确');
- end;
2.8no_date_found:ora-01403
当执行SELECT INTO 未返回行,或者引用了索引表未初始化的元素时,会隐含触发例外
- declare
- v_sal emp.sal%type;
- begin
- select sal into v_sal from emp where lower(ename)=lower('&name');
- exception
- when no_data_found then
- dbms_output.put_line('不存在该雇员');
- end;
2.9too_many_rows:ora-01422
当执行select into 语句时,如果返回超过一行,则会触发例外
- declare
- v_ename emp.ename%type;
- begin
- select ename into v_ename from emp where sal=&sal;
- exception
- when too_many_rows then
- dbms_output.put_line('返回多行');
- end;
2.10zero_divide:ora-01476
如果使用数字值除0,则会隐含触发例外
- declare
- num1 int:=100;
- num2 int:=0;
- num3 number(6,2);
- begin
- num3:=num1/num2;
- exception
- when zero_divide then
- dbms_output.put_line('分母不能为0');
- end;
2.11subscript_beyond_count:ora-06533
当使用嵌套表或VARRAY元素时,如果元素下标超出了嵌套表或VARRAY元素的范围,则回隐含的触发例外
- declare
- type emp_array_type is varray(20) of varchar2(10);
- emp_output.put_line(emp_array(3));
- begin
- emp_array:=emp_array_type('scott','maray');
- dbms_output.put_line(emp_array(3));
- exception
- when subscript_beyond_count then
- dbms_output.put_line('超出下标范围');
- end;
2.12subscript_outside_limit:ora-06532
当使用嵌套表或VARRAY元素时,如果元素下标为负值,则会隐含触发例外
- declare
- type emp_array_type is varray(20) of varray2(10);
- emp_array emp_array_type;
- begin
- emp_array:=emp_array_type('scott','mary');
- dbms_output.put_line(emp_array(-1);
- exception
- when subscript_outside_limit then
- dbms_output.put_line('嵌套表和VARRAY下标不能为负值');
- end;
2.13value_error:ora-06502
如果变量长度不足以容纳实际数据,则会隐含的出发例外
- declare
- v_ename varchar2(5);
- begin
- select ename into v_ename from emp where empno=&no;
- dbms_output.put_line(v_ename);
- exception
- when value_error then
- dbms_output.put_line('变量尺寸不足');
- end;
2.14其他预定义例外
login_denied:ora-01017连接数据库时,提供了不正确的用户名和口令
not_logged_on:ora-01012没有连接到数据库
program_error:ora-06501存在PL/SQL内部问题,可能需要重新安装数据字典和PL/SQL系统包
rowtype_mismatch:ora-06504宿主游标变量和PL/SQL游标变量的返回类型不兼容
self_is_null:ora-30625使用对象类型时,如果在NULL实例上调用成员方法,则会隐含触发例外
storage_error:ora-06500如果超出内存或者内存被损坏
sys_invalid_rowid:ora-01410当字符串转变为ROWID时,必须使用有效的字符串,否则触发例外
timeout_on_resource:ora-00051ORACLE在等待资源时出现超时错误
3.处理非预定义例外
使用预定义例外,只能处理21个ORACLE错误.
使用非预定义例外包括三步:
在定义部分定义例外名,然后在例外和ORACLE错误之间建立关联,最终在例外处理部分捕捉并处理例外.
当定义ORACLE错误和例外之间的关联关系时,需要使用伪过程EXCEPTION_INIT
- declare
- e_integrity exception;
- 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;
4.处理自定义例外
自定义例外与ORACLE错误没有任何关联
与预定义和非预定义不同,自定义例外必须显示触发
- declare
- e_integrity exception;
- pragma exception_init(e_integrity,-2291);
- 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;
5.使用例外函数
函数SQLCODE用于取得ORACLE错误号,而SQLERRM则用于取得与之相关的错误信息
通过在存储过程.函数.包中使用RAISE_APPLICATION_ERROR可以自定义错误号和错误消息
5.1SQLCODE和SQLERRM
- undef v_sal
- 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;
5.2raise_application_error
用于在PL/SQL应用程序中自定义错误消息
该过程只能在数据库端的子程序(过程,函数,包,触发器)中使用,不能在匿名快和客户端的子程序中使用.
raise_application_error(error_number,message[,[true|false]]);
error_number:必须在-20000和-20999之间的负整数.MESSAGE不能超过2048字节
TRUE:该错误会被放在先前错误堆栈中,FALSE:则会替换先前所有错误.
- 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;
6.pl/sql编译警告
6.1PL/SQL警告分类:
severe:该警告用于检查可能出现的不可预料结果或错误结果,例如参数的别名问题
performance:用于检查可能出现的不可预料结果或错误结果,例如参数的别名问题
inforamational:用于检查子程序中的死代码
all:该关键字用于检查所有警告(severe,perforamce,informational)
6.2控制PL/SQL警告消息
为了使数据库在编译PL/SQL子程序时发出警告消息,需设置初始化参数PLSQL_WARNINGS.
初始化PLSQL_WARNINGS不仅可在系统级或会话级设置,也可在ALTER PROCEDURE命令中进行设置激活或禁止
- alter system set plsql_warnings='enable:all';
- alter session set plsql_warnings='enable:performance';
- alter procedure hello compile plsql_warnings='enable:performance';
- alter session set plsql_warnings='disable:all';
- alter session set plsql_warnings='enable:severe','disable:performance','error:06002';
当激活或禁止PL/SQL编译警告时,不仅可以使用ALTER SYSTEM,ALTER SESSION,ALTER PROCEDURE命令,还可使用PL/SQL系统包DBMS_WARNINGS
- SQL>call dbms_warning.set_warning_setting_string('enable:all','session');
7.使用PL/SQL编译警告
检测死代码
为了检测该子程序是否包含死代码,必须首先激活警告检查,然后重新编译子程序,最后使用SHOW ERRORS命令显示警告错误
- alter session set plsql_warnings='enable:informational';
- alter prodedure dead_code compile;
- show errors
检测引起性能问题的代码
编写PL/SQL子程序时,如果数值与变量的数据类型不符合,ORACLE会隐含的转换数据类型,但因为数据类型转换会影响子程序性能,所以在编写PL/SQL子程序时应该尽可能避免性能问题.
- create or replace procedure update_sal(name varchar2,salary varchar2)
- is
- begin
- upodate emp set sal=salary where ename=name;
- end;
为了检测该子程序是否会引起性能问题,应首先激活警告检查,然后重新编译子程序,最后再使用SHOW ERRORS显示警告错误
- alter session set plsql_warnings='enable:performance';
- alter procedure update_sal compile;
- show errors
感谢April-Myhou 侯老师!
转载于:https://blog.51cto.com/4155621/951800