Oracle异常( 其四)

- 预定义异常

1、zero_divide:零被整除（7/0） 例： ``` begin dbms.output_put.line('7/0') exception when zero_divide then dbms.output_put.line('零不能被整除') end; ``` 2、no_data_found：没找到数据 例： ``` declare v_sno student.sno%type; begin select sno into v_sno from student where sname=&sname; dbms.output_put.line('v_sno'); exception when no_data_found then dbms.output_put.line('没找到数据！') end; ``` 3、to_many_rows：匹配多条数据 例： ``` declare v_sno student.sno%type; begin select sno into v_sno from student where sno=&sno; exception when too_many_rows then dbms_output.put_line('select into语句匹配了多行'); when others then dbms_output.put_line('其他例外'); end; ```

- 非预定义异常

1、记录已存在 ，违反唯一限制:ORA-00001 insert into student values(‘1111’,‘张小龙’,‘女’,17,‘04’); 2、违反外键约束:ORA-02291 insert into student values(‘1111b’,‘张小龙’,‘女’,17,‘06’); 3、引用的数据不存在 insert into student(sno,sname,ssex,sage,deptno) values(‘1111b’,‘张小龙’,‘女’,17,‘06’); 4、输入的数据不够:ORA-00947 insert into student values(‘1111b’,‘张小龙’,‘女’,17); 5、值过多：ORA-00913 insert into student values(‘1111b’,‘张小龙’,‘女’,17,‘06’,‘sadad’);

例1、删除数据产生异常

declare err_delereferences exception; pragma exception_init(err_delereferences,-02292); begin delete from course where cno=&cno; exception when err_delereferences then dbms_output.put_line('被其它数据引用'); when others then dbms_output.put_line('其他'); end;

2、子查询时产生的异常（没获得数据）

declare v_grade score.grade%type; err_subnodata exception; pragma exception_init(err_subnodata,-01427); begin select grade from score where sno in(select sno from student where sname='安然') ; exception when err_subnodata then dbms_output.put_line('子查询时没获得数据'); when others then dbms_output.put_line('其他'); end;

- 用户自定义异常