oracle异常处理

2014-04-15

1.处理异常

EXCEPTION

when exception1 [or exception2...] then

statement;...

[when exception1 [or exception2...] then

statement;...]

[when others then

statement1;...]

2.常见的预定义异常

1）no_data_found

根据雇员编号获取雇员名称

set serveroutput on;

declare

v_name emp.ename%type;

begin

select ename into v_name from emp where empno=&no;

dbms_output.put_line(v_name);

exception

when no_data_found then

dbms_output.put_line('empno is not exist.');

end;

2）DUP_VAL_ON_INDEX

更新dept表中数据的数据，导致主键重复，引发DUP_VAL_ON_INDEX异常。

set serveroutput on;

declare

begin

update dept set deptno=&newno where deptno=&oldno;

exception 

when dup_val_on_index then

dbms_output.put_line('primary key duplicat');

end;

3)TOO_MANY_ROWS

获取员工名时，使用查询条件sal

set serveroutput on;

declare

v_name emp.ename%type;

begin

select ename into v_name from emp where sal=&sal;

dbms_output.put_line(v_name);

exception

when too_many_rows then

dbms_output.put_line('return too many rows.');

end;

3.异常函数salcode,sqlerrm

##创建表

create table errors(error_id number ,program_name varchar2(50),

error_code number,error_message varchar2(100),create_date date default sysdate,

constraint pk_errors primary key(error_id));

##创建序列

create sequence seq_errors

minvalue 1

maxvalue 999999999999999999999

start with 1

increment by 1

cache 20;

##匿名块

set serveroutput on;

declare 

v_ename emp.ename%type;

v_error_code number;

v_error_message varchar2(100);

begin

select ename into v_ename from emp where sal=&v_sal;

exception

when others then

v_error_code := sqlcode;

v_error_message := substr(sqlerrm,1,100); ---截断

insert into errors(error_id,program_name,error_code ,error_message)

values(seq_errors.nextval,'anonymous block',v_error_code,v_error_message);

commit;

end;

##查询验证

select * from errors;

4.raise_application_error 

1）语法

raise_application_error(error_number,error_message)

2）示例

DECLARE

   num_tables NUMBER;

BEGIN

   SELECT COUNT(*) INTO num_tables FROM USER_TABLES;

   IF num_tables < 1000 THEN

      raise_application_error(-20101, 'Expecting at least 1000 tables');

   ELSE

      NULL; 

   END IF;

END;

5.自定义异常

定义异常--->显示触发异常--->引用例外

1）语法

PRAGMA EXCEPTION_INIT(exception_name, -Oracle_error_number);

Oracle_error_number:ORA-error_number

2）示例

set serveroutput on;

declare

e_no_deptno exception;

PRAGMA EXCEPTION_INIT(e_no_deptno,-2291);

e_no_empno exception;

begin

update emp set deptno = &v_deptno where empno=&v_empno;

if sql%notfound then

raise e_no_empno;

end if;

exception

when e_no_empno then

dbms_output.put_line('empno is not exist.');

when e_no_deptno then

dbms_output.put_line('deptno is not exist.');

end;

转载于:https://my.oschina.net/xiaoq6427/blog/222891