oracle异常处理

最近在工作中遇到这么一个场景：

• 在同一网段内存在着A库和B库，需要将A库下某些表的数据同步到B库
• B库跑着定时任务，定时调用存储过程将A库下的数据同步到B库。
• B库和A库是通过建立dblink建立连接的。【关于dblink相关可能会后面单独写博客，先给自己挖个坑，慢慢填 哈哈】。
• 定时任务的频次呢是10分钟增量同步一次。

但是随着时间推移，问题出来了：一部分数据没同步过去。

排查步骤：

• 检查定时任务是不是被终止了
• 检查存储过程是不是出问题了
• 检查A库中的目标数据是不是不符合规范

排查思路是将从A库中同步到B库的数据都删了，然后手动调用过程，发现全部数据可以正常同步过去，这样就将上面三种情况都排除了。

这就令人很费解了，是哪出问题了呢？最后将问题定位在执行存储过程中，由于某种原因发生了异常，导致数据同步失败。这样看来是存储过程写的不够完善，某些异常没考虑进去。

吸取以上事例教训，下面就来探究下Oracle中异常处理流程

1 异常概念

和其他编程语言一样，用PL/SQL编写的程序，在运行过程中也会出现各种错误，这些错误就是异常。
举个栗子：

declare
v_result number(10) :=0;
begin
    v_result := 10/0;
    dbms_output.put_line('结果是：'||v_result);
end;

以上脚本执行时就会发生除数为0的异常

2 异常分类

• 预定义异常 （与Oracle 中的错误有关，当出现错误时会自动触发）
• 非预定义异常（与Oracle 中的错误有关，当出现错误时会自动触发）
• 自定义异常 （人为的为某种特殊情况定义的异常，不会自动触发，需要显示的操作来触发）

3 异常处理

3.1 异常处理语法

exception
    when exception1 [or exception2...] then   --异常列表
        statement...                          --异常处理语句
    when exception3 [or exception4...] then   --异常列表
         statement...                         --异常语句
    when others then                         
        statement...

--exception 表示申明异常块部分，它是异常处理部分开始标志
-- where 后面是异常列表
-- then 后面是异常处理语句 也就是发生的异常和异常列表里的异常匹配是，执行的指定语句
-- 可以有多个when
--when others then 是异常处理最后部分，表示如果抛出的异常和前面的都不匹配时执行此处  

3.2 预定义异常处理

在oracle中提供一些已经定义好的常用异常，oracle中一共提供了25中预定义异常。
可以根据以下语句查询

select * from dba_source where name='STANDARD' and text like '%EXCEPTION_INIT%'

下面是一些常见异常

举个栗子：

declare
v_result number(10) :=0;
begin
   v_result := 10/0;
   dbms_output.put_line('结果是：'||v_result);
   exception
       when ZERO_DIVIDE then
       dbms_output.put_line('除数为0了');
end;
--除数为0时会立即捕获异常，异常和when中匹配时，执行之后语句。避免程序中断
-- **异常匹配是从上到下的**

3.3 非预定义异常

oracle 中更多的是非预定义异常，它们只有错误编号和错误描述。而没有名称的异常是不能被捕获的。在oracle中允许开发人员为这样的异常取个名称，使他们能够被异常处理模块捕捉到

非预定义异常定义步骤：

• 申明一个异常名称
• 把这个名称和异常编号关联起来
• 在PL/SQL 块的异常情况处理部分对异常情况做出相应的处理
  举个栗子：

--删除指定部门的记录信息，以确保该部门没有员工。
INSERT INTO departments VALUES(50, 'FINANCE', 'CHICAGO');

DECLARE
   v_deptno departments.department_id%TYPE := &deptno;
   deptno_remaining EXCEPTION;    --申明异常
   PRAGMA EXCEPTION_INIT(deptno_remaining, -2292); --把异常名称和错误号关联起来
   /* -2292 是违反一致性约束的错误代码 */
BEGIN
   DELETE FROM departments WHERE department_id = v_deptno;
EXCEPTION
   WHEN deptno_remaining THEN
      DBMS_OUTPUT.PUT_LINE('违反数据完整性约束!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;

3.4 自定义异常

用户定义的异常错误是通过显式使用 RAISE 语句来触发。当引发一个异常错误时，控制就转向到 EXCEPTION块异常错误部分，执行错误处理代码。

3.4.1 自定义异常处理步骤

1. 在PL/SQL 块的定义部分定义异常情况：<异常情况> EXCEPTION;
2. RAISE <异常情况>；
3. 在PL/SQL 块的异常情况处理部分对异常情况做出相应的处理。

举个栗子：

--更新指定员工工资，增加100
DECLARE
   v_empno employees.employee_id%TYPE :=&empno;
   no_result  EXCEPTION;
BEGIN
   UPDATE employees SET salary = salary+100 WHERE employee_id = v_empno;
   IF SQL%NOTFOUND THEN
      RAISE no_result;
   END IF;
EXCEPTION
   WHEN no_result THEN
      DBMS_OUTPUT.PUT_LINE('你的数据更新语句失败了!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;

4 异常出现位置

4.1 申明部分引发异常错误

在程序申明部分出现错误，那么该错误就能影响到其他块

DECLARE
    abc number(3):='abc';
    其它语句
BEGIN
    其它语句
EXCEPTION
    WHEN OTHERS THEN
    其它语句
END;
-- 以上例子 由于abc number(3)='abc'; 出错，尽管在EXCEPTION中说明了WHEN OTHERS THEN语句，但WHEN OTHERS THEN也不会被执行。这时就需要如下写法才能捕获异常，在该错误语句块的外部有一个异常错误，则该错误能被抓住
BEGIN
    DECLARE
    abc number(3):='abc';
    其它语句
   BEGIN
    其它语句
   EXCEPTION
    WHEN OTHERS THEN
    其它语句
    END;
EXCEPTION
WHEN OTHERS THEN
    其它语句
END;

4.2 执行部分引发异常

• 1如果当前块对该异常错误设置了处理，则执行它并成功完成该块的执行，然后控制转给包含块。

• 如果没有对当前块异常错误设置定义处理器，则通过在包含块中引发它来传播异常错误。然后对该包含块执行步骤1)。

5 SQLCODE, SQLERRM异常处理函数

    由于ORACLE 的错信息最大长度是512字节，为了得到完整的错误提示信息，我们可用 SQLERRM和 SUBSTR 函数一起得到错误提示信息。

• SQLCODE 返回遇到的Oracle错误号
• SQLERRM 返回遇到的Oracle错误信息
  例如：

SQLCODE=-100    SQLERRM=’no_data_found ‘
SQLCODE=0       SQLERRM=’normal, successfual completion’

常用场景：

-- 1. 将ORACLE错误代码及其信息存入错误代码表
CREATE TABLE errors (errnum NUMBER(4), errmsg VARCHAR2(100));

DECLARE
   err_msg  VARCHAR2(100);
BEGIN
   /*  得到所有 ORACLE 错误信息  */
   FOR err_num IN -100 .. 0 LOOP
      err_msg := SQLERRM(err_num);
      INSERT INTO errors VALUES(err_num, err_msg);
   END LOOP;
END;
DROP TABLE errors;

--2. 查询ORACLE错误代码；
BEGIN
   INSERT INTO employees(employee_id, first_name,last_name,hire_date,department_id)
   VALUES(1111, '张','三', SYSDATE, 20);
   DBMS_OUTPUT.PUT_LINE('插入数据记录成功!');
  
   INSERT INTO employees(employee_id, first_name,last_name,hire_date,department_id)
   VALUES(2222, '李','四', SYSDATE, 20);
   DBMS_OUTPUT.PUT_LINE('插入数据记录成功!');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);   --将错误代码和错误信息打印出来
END;

--3. 利用ORACLE错误代码，编写异常错误处理代码；
DECLARE
   empno_remaining EXCEPTION;
   PRAGMA EXCEPTION_INIT(empno_remaining, -1);
   /* -1 是违反唯一约束条件的错误代码 */
BEGIN
   INSERT INTO employees(employee_id, first_name,last_name,hire_date,department_id)
   VALUES(3333, '王','五', SYSDATE, 20);
   DBMS_OUTPUT.PUT_LINE('插入数据记录成功!');
  
   INSERT INTO employees(employee_id, first_name,last_name,hire_date,department_id)
   VALUES(3333, '赵','六',SYSDATE, 20);
   DBMS_OUTPUT.PUT_LINE('插入数据记录成功!');
EXCEPTION
   WHEN empno_remaining THEN
      DBMS_OUTPUT.PUT_LINE('违反数据完整性约束!');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;

6 oracle 错误码归纳