序言
最近在工作中遇到这么一个场景:
- 在同一网段内存在着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 自定义异常处理步骤
- 在PL/SQL 块的定义部分定义异常情况:<异常情况> EXCEPTION;
- RAISE <异常情况>;
- 在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;