一、异常简介
·为了提高程序的健壮性,必须考虑异常情况,否则程序会被终止
·异常(Exception)是一种PL/SQL标识符
·通过在PL/SQL块中引入异常处理部分,可以捕获各种例外,并根据例外的情况进行相应的处理
二、异常分类
1.oracle提供的预定义异常
①预定义的异常是PL/SQL所提供的系统异常
②当PL/SQL应用程序违反oracle规则或系统限制时,会触发一个内部异常
③PL/SQL提供20多种预定义的异常
④常见的预定义异常:
ACCESS_INTO_NULL:对象未初始化异常
CASE_NOT_FOUND:WHEN子句中没有包含CASE的情况
COLLECTION_IS_NULL:集合未初始化异常
CURSOR_ALREADY_OPEN:重新打开游标时的异常
DUP_VAL_ON_INDEX:当在唯一索引对应的列上键入重复值时的异常
INVALID_CURSOR:在不合法的游标上执行操作的异常
INVALID_NUMBER:不能有效地将字符转变成数字的异常
NO_DATA_FOUND:没有数据返回的异常
TOO_DATA_ROWS:返回超多一行数据时的异常
ZERO_DIVIDE:使用数字值除0时的异常
SUBSCRIPT_OUTSIDE_LIMIT:元素下标为负的异常
SUBSCRIPT_BEYOND_COUNT:元素下标越界的异常
VALUE_ERROR:变量长度不足以容纳实际数据时的异常
LOGIN_DENIED:用户名和口令错误时的异常
NOT_LOGGED_ON:没有连接数据库时的异常
PROGRAM_ERROR:PL/SQL的内部问题,需要重新安装数据字典和PL/SQL系统包
ROWTYPE_MISMATCH:宿主游标变量和PL/SQL游标变量的返回类型不兼容时的异常
STORAGE_ERROR:超出内存空间和内存损坏时的异常
SYS_INVALID_ROWID:将字符串转化为ROWID时的异常
TIMEOUT_ON_RESOURCE:等待资源超时的异常
OTHERS:代表所有没有在前面被捕获的异常返回行数超过一行
实例:
①TOO_MANY_ROWS 在隐式游标处理时,select返回行数超过一行
SQL> DECLARE
2 v_ename emp.ename%TYPE;
3 v_sal emp.sal%TYPE;
4 BEGIN
5 SELECT ename,sal INTO v_ename,v_sal FROM emp WHERE deptno=&n;
6 dbms_output.put_line(v_ename||':'||v_sal);
7 EXCEPTION
8 WHEN too_many_rows THEN
9 dbms_output.put_line('You return rows more than one!');
10 WHEN OTHERS THEN
11 dbms_output.put_line('Other''s error!');
12 END;
13 /
You return rows more than one!
PL/SQL procedure successfully completed
②NO_DATA_FOUND 在访问数据时,没有发现数据
SQL> DECLARE
2 v_ename emp.ename%TYPE;
3 v_sal emp.sal%TYPE;
4 BEGIN
5 SELECT ename,sal INTO v_ename,v_sal FROM emp WHERE empno=#
6 dbms_output.put_line(v_ename||':'||v_sal);
7 EXCEPTION
8 WHEN no_data_found THEN
9 dbms_output.put_line('No data found,Please input correct number!');
10 WHEN OTHERS THEN
11 dbms_output.put_line('Others''s error!');
12 END;
13 /
No data found,Please input correct number!
PL/SQL procedure successfully completed
③ZERO_DIVIDE 除数为零
SQL> DECLARE
2 v_num1 NUMBER :=10;
3 v_num2 NUMBER;
4 v_num3 NUMBER;
5 BEGIN
6 v_num2:=&nn;
7 v_num3:=v_num1/v_num2;
8 dbms_output.put_line('Number is:'||v_num3);
9 EXCEPTION
10 WHEN zero_divide THEN
11 dbms_output.put_line('Divisor is equal to zero,Please input correct Number!');
12 WHEN OTHERS THEN
13 dbms_output.put_line('Other''s error!');
14 end;
15 /
Divisor is equal to zero,Please input correct Number!
PL/SQL procedure successfully completed
2.自定义异常
①自定义异常的步骤
·定义异常名称: 类型exception
·举出异常(raise exception)
·捕获异常
实例:
SQL> DECLARE
2 e_user_err EXCEPTION;
3 v_deptno dept.deptno%TYPE :=&NO;
4 BEGIN
5 UPDATE dept SET loc='HongKong' WHERE deptno=v_deptno;
6 IF SQL%NOTFOUND THEN
7 RAISE e_user_err;
8 END IF;
9 COMMIT;
10 EXCEPTION
11 WHEN e_user_err THEN
12 dbms_output.put_line('This department'||v_deptno||' is not in dept table,Please input corrent number!');
13 END;
14 /
This department99 is not in dept table,Please input corrent number!
PL/SQL procedure successfully completed
3.非预定义异常
①通过oracle ora-xxxx错误代码,建立异常捕获
SQL> DECLARE
2 e_emp_remain EXCEPTION;
3 PRAGMA EXCEPTION_INIT(e_emp_remain,-2292);
4 v_deptno dept.deptno%TYPE :=&NO;
5 BEGIN
6 DELETE FROM dept WHERE deptno=v_deptno;
7 COMMIT;
8 EXCEPTION
9 WHEN e_emp_remain THEN
10 dbms_output.put_line('Don''t remove this'||v_deptno||' department!'||',This record is in emp!');
11 WHEN OTHERS THEN
12 dbms_output.put_line('Others error!');
13 END;
14 /
PL/SQL procedure successfully completed
SQL> /
Don't remove this10 department!,This record is in emp!
PL/SQL procedure successfully completed
②获取others中错误代码和错误信息
Sqlcode:oracle 错误代码
Sqlerrm:oracle 错误信息
SQL> CREATE TABLE ERRORS(CODE NUMBER,msg VARCHAR2(255));
SQL> DECLARE
2 v_code errors.code%TYPE;
3 v_msg errors.msg%TYPE;
4 v_sal emp.sal%TYPE;
5 BEGIN
6 SELECT sal INTO v_sal FROM emp WHERE deptno=&NO;
7 EXCEPTION
8 WHEN no_data_found THEN
9 dbms_output.put_line('No this department ID');
10 WHEN OTHERS THEN
11 dbms_output.put_line('Others error');
12 v_code :=SQLCODE;
13 v_msg :=SQLERRM;
14 dbms_output.put_line(v_msg);
15 INSERT INTO ERRORS VALUES(v_code,v_msg);
16 INSERT INTO ERRORS VALUES(v_code,v_msg);
17 COMMIT;
18 END;
19 /
Others error
ORA-01422: 实际返回的行数超出请求的行数
PL/SQL procedure successfully completed
SQL> select * from errors;
CODE MSG
---------- --------------------------------------------------------------------------------
-1422 ORA-01422: 实际返回的行数超出请求的行数
-1422 ORA-01422: 实际返回的行数超出请求的行数
-1422 ORA-01422: 实际返回的行数超出请求的行数
-1422 ORA-01422: 实际返回的行数超出请求的行数
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31393890/viewspace-2129129/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31393890/viewspace-2129129/