orcle数据库里有类似java exception类的情况,这时候需要捕获exception,相当于java中的try。。。catch
系统例外:
1.no_data_found 一般用于select查找数据库中不存在的值时,即没有找到数据
set serverout on
declare
pname emp.ename%type;
begin
select ename into pname from emp where empno=1234;
exception
when no_data_found then dbms_output.put_line('can not found antthing');
when others then dbms_output.put_line('exception others');
end;
2.too_many_rows 匹配了太多行
set serverout on
declare
pename emp.ename%type;
begin
select ename into pename from emp where Deptno = 10;
exception
when too_many_rows then
dbms_output.put_line('pename matchs too many rows');
when others then
dbms_output.put_line('exception others');
end;
3.zero_divide 0不能做除数
set serverout on
declare
pnum number;
begin
pnum:=1/0;
exception
when zero_divide then
dbms_output.put_line('zero can not be this one!');
when others then
dbms_output.put_line('exception others');
end;
4.value_error 算数或转换错误
set serveroutput on
declare
pnum number;
begin
pnum:='abc';
exception
when value_error then
dbms_output.put_line('changes wrong!');
when others then
dbms_output.put_line('exception others');
end;
自定义例外
例外和变量一样是可以自己声明,定义的。当抛出例外(异常)的时候,直接跳到exception那里,关闭游标不执行了怎么办。orcle会自动调用一个进程,关闭游标。
set serveroutput on
declare
cursor cemp is select ename from emp where Deptno=50;
pename emp.ename%type;
no_emp_found exception;
begin
open cemp;
fetch cemp into pename;
if cemp%notfound then
raise no_emp_found;
end if;
close cemp;
exception
when no_emp_found then
dbms_output.put_line('can not found employee!');
when others then
dbms_output.put_line('except others');
end;