异常处理总结

Exception

例1：zero_divide ----除数为0

declare
    v_a number := &A;
    v_b number := &B;
    v_result number;
begin
    v_result := v_a / v_b;
    dbms_output.put_line(v_result);
exception
    when zero_divide then
        dbms_output.put_line('0');
end;
/
0
PL/SQL procedure successfully completed.

例2：value_error ----比如sqrt平方根不能接受负数

SQL> declare
  2      v_a number := &A;
  3      v_result number;
  4  begin
    v_result := sqrt(v_a);
  6      dbms_output.put_line(v_result);
  7  end;
  8  /
Enter value for a: -2
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 5
declare
    v_a number := &A;
    v_result number;
begin
    v_result := sqrt(v_a);
    dbms_output.put_line(v_result);
exception
    when value_error then
    dbms_output.put_line('enter the right type');
end;
/
enter the right type
PL/SQL procedure successfully completed.

declare
    v_a number := &A;
    v_result number;
    error_v exception;
begin
    case when v_a < 0 then
        raise error_v;
    end case;
    v_result := sqrt(v_a);
    dbms_output.put_line(v_result);
exception
    when error_v then
    dbms_output.put_line('enter the right type');    
end;
/

例3：no_data_found

DECLARE 
  v_ename emp.ename%TYPE;
  err_num NUMBER;
  err_msg VARCHAR2(100);
BEGIN
  SELECT ename INTO v_ename FROM emp WHERE empno=3000; 
EXCEPTION
  WHEN OTHERS THEN
      err_num := SQLCODE;
      err_msg := SUBSTR(SQLERRM, 1, 100);
      DBMS_OUTPUT.PUT_LINE(err_num||'----'||err_msg);
END;
 /
100----ORA-01403: no data found
PL/SQL procedure successfully completed.

这里使用了SQLCODE和SQLERRM函数

例4：再次抛出异常

SQL> declare
  2      v_course_no number := 430;
  3      v_total number;
  4      e_no_sections exception;
  5  begin
  6      begin
  7          select count(*) into v_total from section where course_no = v_course_no;
  8          if v_total=0 then
  9              raise e_no_sections;
 10          else
 11          DBMS_OUTPUT.PUT_LINE('course, '||v_course_no||' has '||v_total||' section');
 12          end if;
 13      exception
 14          when e_no_sections then
 15              DBMS_OUTPUT.PUT_LINE('inner exception');
 16          raise;
 17      end;
 18      DBMS_OUTPUT.PUT_LINE('done..');
 19  exception
 20      when e_no_sections then
 21          DBMS_OUTPUT.PUT_LINE('outer exception');
 22  end;
 23  /
inner exception
outer exception
PL/SQL procedure successfully completed.
内部的PLSQL块的exception中使用了raise，唤起外部plsql块的异常
所以没有执行DBMS_OUTPUT.PUT_LINE('done..');
而是直接进入了异常部分
SQL> declare
  2      v_course_no number := 430;
  3      v_total number;
  4      e_no_sections exception;
  5  begin
  6      begin
  7          select count(*) into v_total from section where course_no = v_course_no;
  8          if v_total=0 then
  9              raise e_no_sections;
 10          else
 11          DBMS_OUTPUT.PUT_LINE('course, '||v_course_no||' has '||v_total||' section');
 12          end if;
 13      exception
 14          when e_no_sections then
 15              DBMS_OUTPUT.PUT_LINE('inner exception');
 16         /* raise;*/
 17      end;
 18      DBMS_OUTPUT.PUT_LINE('done..');
 19  exception
 20      when e_no_sections then
 21          DBMS_OUTPUT.PUT_LINE('outer exception');
 22  end;
 23  /
inner exception
done..
PL/SQL procedure successfully completed.
没有raise再次抛出异常的例子

用户定义异常

通过raise唤起用户定义异常

declare
	v_a number := &enter_value_for_a;
	v_err exception;
begin
	if v_a > 2 then
		dbms_output.put_line(v_a);
	elsif v_a < 2 then
		raise v_err;
	end if;
exception
	when v_err then
		dbms_output.put_line('wrong!!! a < 2');
end;
/
Enter value for enter_value_for_a: 1
wrong!!! a < 2
PL/SQL procedure successfully completed.

RAISE_APPLICATION_ERROR

RAISE_APPLICATION_ERROR是oracle提供的一种特殊的内置过程，允许程序员为特定应用程序创建有意义的错误消息。

RAISE_APPLICATION_ERROR过程适用于用户定义异常。它的语法为：

    RAISE_APPLICATION_ERROR(error_number,error_message);

    或者

    RAISE_APPLICATION_ERROR(error_number,error_message,keep_errors);

正如所看到的那样，RAISE_APPLICATION_ERROR过程存在两种形式。第一种形式包含两个参数：error_number和erroe_message。

error_number是与特定错误消息相关联的错误编号。这个编号的范围在-20999和-20000之间。error_message是错误的文本，最多包含2048个字符。

    RAISE_APPLICATION_ERROR过程第二种形式还多包含一个参数：keep_errors,这是可选的Boolean参数。如果keep_errors被设置为True，新错误会被添加到已经被抛出的错误列表中.

这种错误列表被称为错误栈。如果keep_errors被设置为FALSE，新错误会替换已经被抛出的错误栈。keep_errors参数的默认值是FALSE。

目的是自定义一个错误编号和异常message，和oracle一起抛出错误

declare
    v_a number := &A;
    v_result number;
begin
    if v_a < 0 then
        raise_application_error(-20000,'wrong value');
    end if;
    v_result := sqrt(v_a);
    dbms_output.put_line(v_result);
end;
/
输入 a 的值:  -1
原值    2: v_a number := &A;
新值    2: v_a number := -1;
declare
*
第 1 行出现错误:
ORA-20000: wrong value
ORA-06512: 在 line 6

EXCEPTION_INIT

目的是把错误编号和我们自定义的异常关联起来,让用户体验更好

违反外键约束
DECLARE
  v_zip zipcode.zip%type := '&sv_zip';
BEGIN
  DELETE FROM zipcode WHERE zip = v_zip;
  DBMS_OUTPUT.PUT_LINE('Zip ' || v_zip || ' has been deleted');
  COMMIT;
END;
/
Enter value for sv_zip: 06870
DECLARE
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.STU_ZIP_FK) violated - child record
found
ORA-06512: at line 4
现在我们通过PRAGMA EXCEPTION_INIT()捕获这个异常，并给出我们更友好的提示
DECLARE
  v_zip zipcode.zip%type := '&sv_zip';
  e_child_exists EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_child_exists, -2292);
BEGIN
  DELETE FROM zipcode WHERE zip = v_zip;
  DBMS_OUTPUT.PUT_LINE('Zip ' || v_zip || ' has been deleted');
  COMMIT;
EXCEPTION
  WHEN e_child_exists THEN
    DBMS_OUTPUT.PUT_LINE('Delete students for this ' || 'zipcode first');
END;
/
Enter value for sv_zip: 06870
Delete students for this zipcode first
PL/SQL procedure successfully completed.

再来一个例子
insert数据，not null的列不插入任何值，会报错
begin
    insert into emp(empno,ename) values(null,'fan');
    commit;
end;
/
SQL> begin
  2  insert into emp(empno,ename) values(null,'fan');
commit;
end;
  5  /
begin
*
第 1 行出现错误:
ORA-01400: 无法将 NULL 插入 ("SCOTT"."EMP"."EMPNO")
ORA-06512: 在 line 2
然后我们关联ORA-01400这个错误
declare
    v_err exception;
    pragma exception_init(v_err,-1400);
begin
    insert into emp(empno,ename) values(null,'fan');
    commit;
exception
    when v_err then
        dbms_output.put_line('你插了空值到非空列');
end;
/

注意

no_data_found -1403不可以用，因为no_data_found的sqlcode函数返回时100

SQL> declare
  2      v_err exception;
  3      pragma exception_init(v_err,-01403);
  4      v_number number;
  5  begin
  6      select empno into v_number from emp where empno=7777;
  7  exception
  8      when v_err then
  9          dbms_output.put_line('no data found');
 10  end;
 11  /
    v_err exception;
    *
ERROR at line 2:
ORA-06550: line 2, column 5:
PLS-00701: illegal ORACLE error number -1403 for PRAGMA EXCEPTION_INIT

SQL> BEGIN
  2    DBMS_OUTPUT.PUT_LINE('Error code: ' || SQLCODE);
  3    DBMS_OUTPUT.PUT_LINE('Error message1: ' || SQLERRM(SQLCODE));
  4    DBMS_OUTPUT.PUT_LINE('Error message2: ' || SQLERRM(100));
  5    DBMS_OUTPUT.PUT_LINE('Error message3: ' || SQLERRM(200));
  6    DBMS_OUTPUT.PUT_LINE('Error message4: ' || SQLERRM(-20000));
  7  END;
  8  /
Error code: 0
Error message1: ORA-0000: normal, successful completion
Error message2: ORA-01403: no data found
Error message3:  -200: non-ORACLE exception
Error message4: ORA-20000:
PL/SQL procedure successfully completed.

declare
	v_a number := &enter_value_for_a;
	v_err exception;
	v_err_no number;
    v_err_msg varchar2(200);
begin
	if v_a > 2 then
		dbms_output.put_line(v_a);
	elsif v_a < 2 then
		raise v_err;
	end if;
exception
	when v_err then
		dbms_output.put_line('a < 2');
		v_err_no := sqlcode;
        v_err_msg := sqlerrm;
        dbms_output.put_line(v_err_no);
        dbms_output.put_line(v_err_msg);
end;
/
Enter value for enter_value_for_a: 1
a < 2
1
User-Defined Exception
PL/SQL procedure successfully completed.

同常SQLCODE函数会返回错误编号的复数。凡是也存在一些例外情况

当在异常部分的外部引用SQLCODE是，所返回的错误编号是0。错误编号是0意味着成功结束

当与用户定义异常一起使用SQLCODE函数时，返回的错误编号是+1.

当排除NO_DATA_FOUND异常时，SQLCODE函数返回100