oracle_exception.sql
EXCEPTION
when exception1 [or exception2...] then
statement;...
[when exception1 [or exception2...] then
statement;...]
[when others then
statement1;...]
sqlplus scott/tiger
1.常见的预定义异常
(1)no_data_found
select语句异常用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('ename '||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;
declare
begin
update dept set deptno=&deptno where upper(dname)=upper('&dname');
exception
when dup_val_on_index then
dbms_output.put_line('deptno exists');
end;
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=&salary;
dbms_output.put_line(v_ename);
exception
when too_many_rows then
dbms_output.put_line('ename is too many rows');
end;
2.异常函数sqlcode,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_error primary key(error_id));
create sequence seq_errors
minvalue 1
maxvalue 999999999999999999
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;
3.raise_application_error
语法:
raise_application_error(-Oracle_error_number,error_message)
-Oracle_error_number:-20000 到-20999
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;
4.自定义异常
定义异常--->显示触发异常--->捕获处理异常
触发异常:为了处理与Oracle错误无关的异常情况,需要在执行部分出现异常情况时使用RAISE语句显式地触发异常。
DECLARE
out_of_stock EXCEPTION;
number_on_hand NUMBER (4);
BEGIN
...
IF number_on_hand < 1 THEN
RAISE out_of_stock;
END IF;
EXCEPTION
WHEN out_of_stock THEN
-- handle the error
END;
PRAGMA EXCEPTION_INIT(exception_name, -Oracle_error_number);
将程序中所声明的异常标示符和oracle一个特定的错误号关联起来。
Oracle_error_number:oracle 特定的错误编码ORA-error_number,oracle会自动抛出,不用显式抛出
例如:
-2291 :外键不存在
raise_application_error:
。平时用来测试的异常处理我们都是通过dbms_output.put_line来输出异常信息,但是在实际的应用中,需要把异常信息返回给调用的客户端。其实 RAISE_APPLICATION_ERROR 是将应用程序专有的错误从服务器端转达到客户端应用程序(其他机器上的SQLPLUS或者其他前台开发语言)
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;