---------------异常处理
错误分两种:编译时错误和运行时错误
运行时错误叫异常
declare
begin
exception
when
语句;
when
语句;
.....
when
语句;
end
-------预定义异常
oracle把某些错误编号命名
too_many_rows 返回行数过多(-01422)
no_data_found 无数据返回(-01403)
declare
v_name varchar2(10);
begin
select sname into v_name from student
where sno='1234';
dbms_output.put_line('该学生名字为:'||v_name);
exception
when no_data_found then
dbms_output.put_line('没此 学生');
when too_many_rows then
dbms_output.put_line('返回行数超过一');
when others then
dbms_output.put_line('发生其它错误');
end;
自定义异常
----打印King的工资,如果大于20000打印工资太高
declare
v_salary number;
new_ex1 exception;
begin
select salary into v_salary from employees where last_name='King';
if v_salary>20000 then
raise new_ex1; ---抛出异常
end if;
exception
when new_ex1 then
dbms_output.put_line('工资太高');
when others then
dbms_output.put_line('其它错误');
end;
------非预定义异常
pragma exception_init(异常名,错误号)---将错误编号和异常名绑定
declare
e_check exception;
e_null exception;
e_unique exception;
pragma exception_init(e_check,-02290);//异常名,错误编号
pragma exception_init(e_null,-01400);
pragma exception_init(e_unique,-00001);
begin
insert into student(sno,sname,ssex)
values(1,'张三','男');
exception
when e_check then
dbms_output.put_line('只能插入男或女');
when e_null then
dbms_output.put_line('ID和姓名不能为空');
when e_unique then
dbms_output.put_line('ID重复了');
when other then
dbms_output.put_line('其它错误');
end;
转自:http://blog.sina.com.cn/s/blog_61367d5f0100e4hn.html