Oracle开发基础-异常处理

IT小Chen 2021-04-13

678

---参考：《Oracle 11g SQL和 PLSQL从入门到精通》

一：异常简介

1.1传递异常到调用环境

1.2捕捉并处理异常

二：捕捉并处理异常

2.1预定义异常

2.2非预定义异常

2.3自定义异常

三：使用异常处理函数

SQLCODE、SQLERRM、RAISE_APPLICATION_ERROR

一：异常简介

为了提高应用程序的健壮性，使得应用程序可以安全正常的运行，应用开发人员应该考虑到PL/SQL块可能出现的各种异常情况，并进行相应的处理。

异常(exception)是一种PL/SQL标识符，它包括预定义异常、非预定义异常和自定义异常三种类型。

如果不捕捉和处理异常，那么Oracle会将错误传递到调用环境，如果捕捉并处理异常，那么Oracle会在PL/SQL块内解决运行错误。

1.1传递异常到调用环境

当编写PL/SQL块时，如果没有提供异常处理部分，那么当执行PL/SQL块时会将错误传递到调用块或PL/SQL运行环境。

DECLARE
  v_ename emp.ename%TYPE;
BEGIN
  SELECT ename INTO v_ename FROM emp WHERE empno = &no;
  dbms_output.put_line('ename is :' || v_ename);
END;
/
 
Enter value for no: 88
old   4:   SELECT ename INTO v_ename FROM emp WHERE empno = &no;
new   4:   SELECT ename INTO v_ename FROM emp WHERE empno = 88;
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4

1.2 捕捉并处理异常

语法如下：

EXCEPTION
  WHEN exception1 [OR exceptino2 ...] THEN
    statement1;
    statement2;
    ...
  [WHEN exception 3 [OR exception 4 ...] THEN
    statement1;
    statement2;
    ...
  [WHEN OTHERS THEN
    statement1;
    statement2;
    ...

示例：

DECLARE
  v_ename emp.ename%TYPE;
BEGIN
  SELECT ename INTO v_ename FROM emp WHERE empno = &no;
  dbms_output.put_line('ename is :' || v_ename);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('empno does not exist, please check the empno!');
END;
/
Enter value for no: 88
old   4:   SELECT ename INTO v_ename FROM emp WHERE empno = &no;
new   4:   SELECT ename INTO v_ename FROM emp WHERE empno = 88;
empno does not exist, please check the empno!
 
PL/SQL procedure successfully completed.

二：捕捉并处理异常

2.1预定义异常

预定义异常是指PL/SQL所提供的系统异常。

预定义异常标识符

(1) ACCESS_INTO_NILL:该异常对应于ORA-06530错误。
(2) CASE_NOT_FOUND:该异常对应于ORA-06592错误。
(3) COLLECTION_IS_NULL:该异常对应于ORA-06531错误。
(4) CURRENT_ALREADY_OPEN:该异常对应于ORA-06511错误。
(5) DUP_VAL_ON_INDEX:该异常对应于ORA-01001错误。
(6) INVALID_CURSOR:该异常对应于ORA-01722错误。
(7) INVALID_NUMBER:该异常对应于ORA-01722错误。
(8) LOGIN_DENIED:该异常对应于ORA-01017错误。
(9) NO_DATA_FOUND:该异常对应于ORA-01403错误。
(10)NOT_LOGGED_ON:该异常对应于ORA-01012错误。
(11)PROGRAM_ERROR:该异常对应于ORA-06501错误。
(12)ROWTYPE_MISMATCH:该异常对应于ORA-06504错误。
(13)SELF_IS_NULL:该异常对应于ORA-30625错误。
(14)STORAGE_ERROR:该异常对应于ORA-06500错误。
(15)SUBCRIPT_BEYOND_COUNT:该异常对应于ORA-06533错误。
(16)SUBSCRIPT_OUTSIDE_LIMIT:该异常对应于ORA-06532错误。
(17)SYS_INVALID_ROWID:该异常对应于ORA-01410错误。
(18)TIMEOUT_ON_RESOURCE:该异常对应于ORA-00051错误。
(19)TOO_MANY_ROWS:该异常对应于ORA-01422错误。
(20)VALUE_ERROR:该异常对应于ORA-06502错误。
(21)ZERO_DIVIDE:该异常对应于ORA-01476错误。

示例一：使用预定义异常

SQL> set serveroutput on
DECLARE
  v_ename emp.ename%TYPE;
BEGIN
  SELECT ename INTO v_ename FROM emp WHERE sal = &salary;
  dbms_output.put_line('ename is:' || v_ename);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('There is no employee with this sal!');
  WHEN TOO_MANY_ROWS THEN
    dbms_output.put_line('Multiple employees have this salary!');
END;
/
Enter value for salary: 800
old   4:   SELECT ename INTO v_ename FROM emp WHERE sal = &salary;
new   4:   SELECT ename INTO v_ename FROM emp WHERE sal = 800;
ename is:SMITH
 
PL/SQL procedure successfully completed.
 
Enter value for salary: 1500
old   4:   SELECT ename INTO v_ename FROM emp WHERE sal = &salary;
new   4:   SELECT ename INTO v_ename FROM emp WHERE sal = 1500;
There is no employee with this sal!
 
PL/SQL procedure successfully completed.

2.2非预定义异常

用于处理和预定义异常无关的Oracle错误。

预定义异常只能处理21种固定的Oracle错误，而PL/SQL块可能还会遇到其他Oracle错误;

例如：

BEGIN
  UPDATE emp SET deptno = &dno WHERE empno = &eno;
END;
/
 
Enter value for dno: 12
Enter value for eno: 7369
old   2:   UPDATE emp SET deptno = &dno WHERE empno = &eno;
new   2:   UPDATE emp SET deptno = 12 WHERE empno = 7369;
BEGIN
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not found
ORA-06512: at line 2

为了提高PL/SQL块的健壮性，应使用非预定义异常处理这些Oracle错误。

SQL>
DECLARE
  e_integrity EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_integrity, -2291);
  name emp.ename%TYPE := lower('&name');
  dno  emp.deptno%TYPE := &dno;
BEGIN
  UPDATE emp SET deptno = dno WHERE LOWER(ename) = name;
EXCEPTION
  WHEN e_integrity THEN
    dbms_output.put_line('The deptno is not exists!');
END;
/
Enter value for name: king
old   4:   name emp.ename%TYPE := lower('&name');
new   4:   name emp.ename%TYPE := lower('king');
Enter value for dno: 88
old   5:   dnoemp.deptno%TYPE := &dno;
new   5:   dnoemp.deptno%TYPE := 88;
The deptno is not exists!
 
PL/SQL procedure successfully completed.

2.3自定义异常

如果输入不存的雇员号，不会触发e_integrity，也不出现错误

Enter value for name: cjc
old   4:   name emp.ename%TYPE := lower('&name');
new   4:   name emp.ename%TYPE := lower('cjc');
Enter value for dno: 88
old   5:   dnoemp.deptno%TYPE := &dno;
new   5:   dnoemp.deptno%TYPE := 88;
 
PL/SQL procedure successfully completed.

添加对不存在雇员错误的异常处理

SQL>
DECLARE
  e_integrity EXCEPTION;
  e_no_rows EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_integrity, -2291);
  name emp.ename%TYPE := lower('&name');
  dno  emp.deptno%TYPE := &dno;
BEGIN
  UPDATE emp SET deptno = dno WHERE LOWER(ename) = name;
  IF SQL%NOTFOUND THEN
    RAISE e_no_rows;
  END IF;
EXCEPTION
  WHEN e_integrity THEN
    dbms_output.put_line('The deptno is not exists!');
  WHEN e_no_rows THEN
    dbms_output.put_line('The ename is not exists!');
END;
/
Enter value for name: cjc
old   5:   name emp.ename%TYPE := lower('&name');
new   5:   name emp.ename%TYPE := lower('cjc');
Enter value for dno: 88
old   6:   dnoemp.deptno%TYPE := &dno;
new   6:   dnoemp.deptno%TYPE := 88;
The ename is not exists!
 
PL/SQL procedure successfully completed.

三：使用异常处理函数

异常处理函数用于取得Oracle错误号和错误消息。

SQLCODE:取得错误号；

SQLERRM:取得错误消息。

另外在使用内置过程RAISE_APPLICATION_ERROR，可以在建立子程序(过程、函数、包)时自定义错误号和错误消息。

示例1：使用SQLCODE和SQLERRM

BEGIN
  DELETE FROM dept WHERE deptno = &dno;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Error Num is:' || SQLCODE);
    dbms_output.put_line('Error Mem is:' || SQLERRM);
END;
/
 
Enter value for dno: 10
old   2:   DELETE FROM dept WHERE deptno = &dno;
new   2:   DELETE FROM dept WHERE deptno = 10;
Error Num is:-2292
Error Mem is:ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated - child
record found
 
PL/SQL procedure successfully completed.
 
RAISE_APPLICATION_ERROR

该过程用于在PL/SQL子程序中自定义错误消息。

语法如下：

raise_application_error(error_number,message[,{TRUE | FALSE}]);

示例：

CREATE OR REPLACE PROCEDURE update_sal(name VARCHAR2, salary NUMBER) IS
BEGIN
  UPDATE emp SET sal = salary WHERE LOWER(ename) = LOWER(name);
  IF SQL%NOTFOUND THEN
    RAISE_APPLICATION_ERROR(-20000, 'Then ename is not exists!');
  END IF;
END;
/
Procedure created.
 
SQL>exec update_sal('cjc',30000);
BEGIN update_sal('cjc',30000); END;
 
*
ERROR at line 1:
ORA-20000: Then ename is not exists!
ORA-06512: at "SCOTT.UPDATE_SAL", line 5
ORA-06512: at line 1

更多数据库相关学习资料，可以查看我的ITPUB博客，网名chenoracle：

http://blog.itpub.net/29785807/

oracle

文章转载自IT小Chen，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle开发基础-异常处理

评论