oracle pl sql 异常,oracle pl/sql之异常处理(exception)

一.异常语法

EXCEPTION

when exception1 [or exception2...] then

statement;...]

[when exception1 [or exception2...] then

statement;...]

[when others then

statement1;...]

二.预定义异常

(1)NO_DATA_FOUND

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

set serveroutput on;

declare

v_ename emp.ename%type;

begin

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

dbms_output.put_line('v_name is :'||v_ename);

exception

when no_data_found then

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

end;

(2)DUP_VAL_ON_INDEX

---更新dept表中的数据，导致主键重复

set serveroutput on;

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

---获取员工名的时候，使用查询条件salary

set serveroutput on;

declare

v_ename emp.ename%type;

begin

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

exception

when too_many_rows then

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

end;

三.异常函数sqlcode,sqlerrm

---删除errors表，如果存在

drop table errors purge;

---创建errors表结构

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;

---使用sqlcode/sqlerrm函数把输出错误到errors表

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;

四.raise_application_error

自定义错误输出，错误号(error_numbert)必须大于20000

语法：

raise_application_error(error_number,error_message)

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;

五.自定义异常

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

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;