Oracle扩展PL/SQL简介(五)

7.    异常处理
exception
when <exception_expression> then
   …
when <exception_expression> then
   …
end;

查询预定义异常，可以得知自定义异常方法
select text from dba_source where name='STANDARD' and text like '%EXCEPTION_INIT%';

TEXT
----------------------------------------------------------------
    pragma EXCEPTION_INIT(CURSOR_ALREADY_OPEN, '-6511');
    pragma EXCEPTION_INIT(DUP_VAL_ON_INDEX, '-0001');
    pragma EXCEPTION_INIT(TIMEOUT_ON_RESOURCE, '-0051');
    pragma EXCEPTION_INIT(INVALID_CURSOR, '-1001');
    pragma EXCEPTION_INIT(NOT_LOGGED_ON, '-1012');
    pragma EXCEPTION_INIT(LOGIN_DENIED, '-1017');
    pragma EXCEPTION_INIT(NO_DATA_FOUND, 100);
    pragma EXCEPTION_INIT(ZERO_DIVIDE, '-1476');
    pragma EXCEPTION_INIT(INVALID_NUMBER, '-1722');
    pragma EXCEPTION_INIT(TOO_MANY_ROWS, '-1422');
    pragma EXCEPTION_INIT(STORAGE_ERROR, '-6500');
    pragma EXCEPTION_INIT(PROGRAM_ERROR, '-6501');
    pragma EXCEPTION_INIT(VALUE_ERROR, '-6502');
    pragma EXCEPTION_INIT(ACCESS_INTO_NULL, '-6530');
    pragma EXCEPTION_INIT(COLLECTION_IS_NULL , '-6531');
    pragma EXCEPTION_INIT(SUBSCRIPT_OUTSIDE_LIMIT,'-6532');
    pragma EXCEPTION_INIT(SUBSCRIPT_BEYOND_COUNT ,'-6533');
  pragma EXCEPTION_INIT(ROWTYPE_MISMATCH, '-6504');
  PRAGMA EXCEPTION_INIT(SYS_INVALID_ROWID, '-1410');
    pragma EXCEPTION_INIT(SELF_IS_NULL, '-30625');
    pragma EXCEPTION_INIT(CASE_NOT_FOUND, '-6592');
    pragma EXCEPTION_INIT(USERENV_COMMITSCN_ERROR, '-1725');
    pragma EXCEPTION_INIT(NO_DATA_NEEDED, '-6548');
pragma EXCEPTION_INIT(INVALID_USERENV_PARAMETER, -2003);
pragma EXCEPTION_INIT(ICD_UNABLE_TO_COMPUTE, -6594);

25 rows selected.

处理预定义异常，这些是oracle预先定义好的异常
declare
  v_dept departments%rowtype;
begin
  v_dept.department_id:=100;
  v_dept.department_name:='HR';
  insert into departments(department_id, department_name)
    values(v_dept.department_id, v_dept.department_name);
exception
  when DUP_VAL_ON_INDEX then
    dbms_output.put_line(SQLERRM); --打印错误码
end;
/
ORA-00001: unique constraint (HR.DEPT_ID_PK) violated

处理非预定义异常
declare
  Invalid_column_name exception; --定义异常
  pragma exception_init(Invalid_column_name,-904); --绑定异常码
  v_update_text varchar2(100):='update employees set EMAILS= "abc@def.com" where EMPLOYEE_ID= 181';
begin
  execute immediate v_update_text;
exception
  when Invalid_column_name then
   dbms_output.put_line('invalid column name! ');
end;
/
invalid column name!

自定义异常码
declare
  USER_ERROR exception; --定义异常
  pragma exception_init(USER_ERROR,-20001); --分配异常码，自定义异常码是20000以后的数字
begin
   dbms_output.put_line('自定义异常码：'||'-20001 ');
raise USER_ERROR;
exception
when USER_ERROR then
   dbms_output.put_line('不能修改员工工资！');
end;
/

作用域和可视性：
begin
  declare
    nested_excp exception;
  begin
   raise nested_excp;
  end;
exception
  when nested_excp then  --这里会出错，非nested_excp定义的作用域
  dbms_output.put_line(' nested_excp ');
end;
/

一般最后捕获异常方法when others then
declare
  nested_excp exception;
begin
  raise nested_excp;
exception
  when others then
  dbms_output.put_line(' nested_excp--');
  dbms_output.put_line(sqlcode || ' is ' || sqlerrm);
  raise; --再次把异常抛出
end;
/

declare
  v_dept departments%rowtype;
begin
  v_dept.department_id:=100;
  v_dept.department_name:='HR';
  insert into departments(department_id, department_name)
   values(v_dept.department_id, v_dept.department_name);
exception
  when others then
  dbms_output.put_line('最后的捕获异常，所有剩下的异常都捕获！');
end;
/

捕获异常代码和错误描述(SQLCODE SQLERRM)
前面代码已经有所使用：
declare
  v_dept departments%rowtype;
begin
  v_dept.department_id:=100;
  v_dept.department_name:='HR';
  insert into departments(department_id, department_name)
   values(v_dept.department_id, v_dept.department_name);
Exception
  When others then
  dbms_output.put_line(sqlcode || ' is ' || sqlerrm);
end;
/
-1 is ORA-00001: unique constraint (HR.DEPT_ID_PK) violated