oracle之异常学习

ORACLE异常包括预定义异常，非预定义异常，自定义异常。
[b]1、预定义异常[/b]
即oracle已定义的异常，无需在程序中定义，由ORACLE自动将其引发，可以直接使用定义的异常名称捕获。ORACLE预定义的异常情况大约有24个，如下。
错误号 异常错误信息名称 异常产生原因
ORA-0001 dup_val_on_index 违反了唯一性限制
ORA-0051 timeout_on_resource 在等待资源时发生超时
ORA-0061 transaction_backed_out 由于发生死锁事务被撤消
ORA-1001 invalid_cursor 试图使用一个无效的游标
ORA-1012 not_logged_on 没有连接到ORACLE
ORA-1017 login_denied 无效的用户名/口令
ORA-1403 no_data_found SELECT INTO没有找到数据
ORA-1410 sys_invalid_id 无效的 rowid 字符串
ORA-1422 too_many_rows SELECT INTO 返回多行
ORA-1476 zero_divide 除数为 0
ORA-1722 invalid_number 转换一个数字失败
ORA-6500 storage_error 内存不够引发的内部错误
ORA-6501 program_error 内部错误
ORA-6502 value_error 转换或截断错误(赋值时，变量长度不足以容纳实际数据)
ORA-6504 rowtype_mismatch 主游标变量与 PL/SQL变量有不兼容行类型
ORA-6511 curser_already_open 试图打开一个已处于打开状态的游标
ORA-6530 access_into_null 试图为null 对象的属性赋值
ORA-6531 collection_is_null 集合元素未初始化
ORA-6532 subscript_outside_limit 对嵌套或varray索引得引用超出声明范围以外
ORA-6533 subscript_beyond_count 对嵌套或varray 索引得引用大于集合中元素的个数.
ORA-6592 case_not_found case中若未包含相应的when，并且没有设置.
self_is_null 使用对象类型时，在 null 对象上调用对象方法


预定义异常可以通过异常名捕获处理，示例如下（所有示例以PLSQL执行）：

declare
cc varchar2(1000);
nn number := 0;
begin
    select t.label_value into cc from mem_users_label t where t.user_id = '111';
    dbms_output.put_line('data found');
    EXCEPTION
    WHEN no_data_found THEN
      dbms_output.put_line('no data found');
end;

PLSQL执行结果为：

no data found

[b]2、非预定义异常[/b]
即其他标准的ORACLE错误，但是oracle并没有对其进行定义。对这种异常情况的处理，需要用户在程序中定义，然后由ORACLE自动将其引发。
创建测试表

create table ZL_TEST1
(
  id         VARCHAR2(10) not null,
  name       VARCHAR2(10),
  age        VARCHAR2(50),
  primary key(id)
);

create table ZL_TEST2
(
  id         VARCHAR2(10),
  name       VARCHAR2(30),
  age        NUMBER
);
alter table ZL_TEST2 add constraint PRI_FOREIGNID foreign key (ID)
  references ZL_TEST1 (ID);

插入测试数据

insert into zl_test1 values(1,'aa',12);
insert into zl_test1 values(2,'ab',12);
insert into zl_test1 values(3,'ac',12);

测试代码

declare
deptno_remaining EXCEPTION;
PRAGMA EXCEPTION_INIT(deptno_remaining, -2292);
begin
  delete from zl_test1 where id='1';
  EXCEPTION
    WHEN deptno_remaining THEN
      dbms_output.put_line('sqlcode is:'||SQLCODE||',error messesge is:'||SQLERRM);   
end;

[b]3、自定义异常[/b]
需要根据实际情况自己定义错误消息，RAISE_APPLICATION_ERROR用于自定义错误消息，并且消息号必须在－20000～－20999之间，以便与oracle定义的错误区分开。用户定义的异常错误可以通过显式使用RAISE语句来触发，也可以通过RAISE_APPLICATION_ERROR定义。当引发一个异常错误时，控制就转向到 EXCEPTION块异常错误部分，执行错误处理代码。
RAISE触发异常：

DECLARE
   no_result  EXCEPTION;
BEGIN
   UPDATE zl_test1 SET name = 'test' WHERE id = 8;
   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;

RAISE_APPLICATION_ERROR定义异常
RAISE_APPLICATION_ERROR由oracle的DBMS_STANDARD包提供，语法如下：

RAISE_APPLICATION_ERROR(error_number,error_message,[keep_errors] );

error_number 是从 –20,000 到 –20,999 之间的参数，
error_message 是相应的提示信息(< 2048 字节)，
keep_errors 为可选，如果keep_errors =TRUE ,则新错误将被添加到已经引发的错误列表中。如果keep_errors=FALSE(缺省),则新错误将替换当前的错误列表。
示例：

创建表：
CREATE TABLE errlog(
  Errcode NUMBER,
  Errtext CHAR(40));

创建函数：
CREATE OR REPLACE FUNCTION get_salary(p_deptno NUMBER)
RETURN NUMBER 
AS
  v_sal NUMBER;
BEGIN
  IF p_deptno IS NULL THEN
    RAISE_APPLICATION_ERROR(-20991, ’部门代码为空’);
  ELSIF p_deptno<0 THEN
    RAISE_APPLICATION_ERROR(-20992, ’无效的部门代码’);
  ELSE
    SELECT SUM(employees.salary) INTO v_sal FROM employees 
    WHERE employees.department_id=p_deptno;
    RETURN v_sal;
  END IF;
END;

测试：
DECLARE 
  V_salary NUMBER(7,2);
  V_sqlcode NUMBER;
  V_sqlerr VARCHAR2(512);
  Null_deptno EXCEPTION;
  Invalid_deptno EXCEPTION;
  PRAGMA EXCEPTION_INIT(null_deptno,-20991);
  PRAGMA EXCEPTION_INIT(invalid_deptno, -20992);
BEGIN
  V_salary :=get_salary(10);
  DBMS_OUTPUT.PUT_LINE('10号部门工资：' || TO_CHAR(V_salary));

  BEGIN
    V_salary :=get_salary(-10);
  EXCEPTION
    WHEN invalid_deptno THEN
      V_sqlcode :=SQLCODE;
      V_sqlerr  :=SQLERRM;
      INSERT INTO errlog(errcode, errtext) 
      VALUES(v_sqlcode, v_sqlerr);
      COMMIT;
  END inner1;

  V_salary :=get_salary(20);
  DBMS_OUTPUT.PUT_LINE('部门号为20的工资为：'||TO_CHAR(V_salary));

  BEGIN
    V_salary :=get_salary(NULL);
  END inner2;

  V_salary := get_salary(30);
  DBMS_OUTPUT.PUT_LINE('部门号为30的工资为：'||TO_CHAR(V_salary));

  EXCEPTION
    WHEN null_deptno THEN
      V_sqlcode :=SQLCODE;
      V_sqlerr  :=SQLERRM;
      INSERT INTO errlog(errcode, errtext) VALUES(v_sqlcode, v_sqlerr);
      COMMIT;
    WHEN OTHERS THEN
         DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END outer;

[b]4、总结[/b]
EXCEPTION必须放在begin/end代码块中：如上面的示例，inner1的代码块中包含exception，捕获到invalid_deptno异常并处理后继续执行，get_salary(20)正常执行，inner2代码块中并没有exception捕获异常，故此代码块终止，调到其父代码块，异常被最后的exception捕获，inner2到最后的exception中的sql不会被执行，即get_salary(30)不会被执行。
如果循环中需要捕获异常，需要在可能存在异常的sql处添加begin/end,组成sql块；或者将exception放在循环外面，此时当某次循环中出现异常时整个循环终止，异常被exception捕获。

declare
cursor mycur is select * from mem_agingtype_labinf_syncbak where rownum<10;
c_row mycur%rowtype;
cc varchar2(1000);
nn number := 0;
begin
  for c_row in mycur loop
    nn := nn+1;
    begin
    select t.label_value into cc from mem_users_label t where t.user_id = c_row.user_id;
    EXCEPTION
    WHEN no_data_found THEN
      dbms_output.put_line('no data found');
    end;
    dbms_output.put_line(nn);
  end loop;
  dbms_output.put_line('end loop');
end;