序言

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

  • 在同一网段内存在着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 错误码归纳

05-09 09:51