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
Oracle扩展PL/SQL简介(五)
最新推荐文章于 2024-08-24 23:33:05 发布