异常处理

1、捕捉并处理预定义异常
declare
    v_ename emp.ename%type;
begin
    select ename into v_ename from emp where sal=&salary;
    dbms_output.put_line(v_name);
exception
    when no_data_found then
         dbms_output.put_line('no record');
    when too_many_rows then
         dbms_output.put_line('too many record');
end;

2、非预定义异常
declare
    e_2291 exception;
    pragma exception_init(e_integrity,-2291);
    name emp.ename%type:=lower('&name');
    dno emp.deptno%type:=&dno;
begin
    update emp set deptno=dno where lower(ename)=name;
exception
    when e_2291 then
        dbms_output.put_line('no such deptno');
end;

3、自定义异常
declare
    e_2291 exception;
    e_no_rows exception;
    pragma exception_init(e_integrity,-2291);
    name emp.ename%type:=lower('&name');
    dno emp.deptno%type:=&dno;
begin
    update emp set deptno=dno where lower(ename)=name;
    if sql%notfound then
        raise e_no_rows;
    end if;
exception
    when e_no_rows
        dbms_output.put_line('no such employee');
    when e_2291 then
        dbms_output.put_line('no such deptno');
end;

4、异常处理函数
a、sqlcode and sqlerrm
--查看oracle错误
begin
    delete from dept where deptno=&dno;
exception
    when others then
        dbms_output.put_line('错误号：'||sqlcode);
        dbms_output.put_line('错误信息：'||sqlerrm);
end;

b、raise_application_error

create or replace procedure update_sal
 (name varchar2,salary number)
is
begin
    update emp set sal=salary where lower(ename)=lower(name);
    if sql%notfound then
        raise_application_error(-20000,'no such employee');
    end if;
end;

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17012874/viewspace-693796/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17012874/viewspace-693796/