一.异常语法
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;
