oracle--预定义异常

/*
1，access_into_null 没有初始化对象异常
*/
create type emp_type as object (
id number(10), –对象（创建）
name varchar2(50)
);
declare
emp emp_type:=emp_type(1,”);
begin
emp.name := ‘王瑶’;
exception
when access_into_null then
dbms_output.put_line(‘没有初始化emp对象’);
end;

declare –记录（声明）
type emp_type is record (
id number(10),
name varchar2(50)
);
emp1 emp_type;
begin
emp1.name := ‘王瑶’;
exception
when access_into_null then
dbms_output.put_line(‘没有初始化emp1对象’);
end;

/*
2.case_not_fount case语句时，缺少符合的条件，并且没有else语句
*/

declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno = &no;
case
when v_sal <1000 then
dbms_output.put_line(‘sal《1000’);
when v_sal <2000 then
dbms_output.put_line(‘sal《2000’);
when v_sal <3000 then
dbms_output.put_line(‘sal《3000’);
when v_sal <4000 then
dbms_output.put_line(‘sal《4000’);
end case;
exception
when case_not_found then
dbms_output.put_line(‘case中没有相关数据’);
end;

/*
3.collection_is_null 没有初始化集合（嵌套表，变长数组）
*/

declare
type v_varray_type is varray(100) of varchar2(10);
name v_varray_type;
begin
name(1) := ‘wang’;
name(2) := ‘ba’;
name(3) := ‘tao’;
exception
when collection_is_null then
dbms_output.put_line(‘集合没有初始化’);
end;

/*
4.cursor_already_open 再次打开已经打开的游标
*/

declare
cursor v_cursor is select * from emp;
begin
—open v_cursor;
for i in v_cursor loop
dbms_output.put_line(i.empname || ’ ’ || i.sal);
end loop;
exception
when cursor_already_open then
dbms_output.put_line(‘游标已经打开…………’);
end;

/*
5.dup_val_on_index 在唯一索引列上插入重复值
*/

begin
update emp set empno = &new_name where empno = &old_name;
exception
when dup_val_on_index then
dbms_output.put_line(‘唯一索引违规’);
end;

/*
6.invalid_cursor 操作不合法的游标-处理，关闭未打卡的游标，
*/

declare
cursor v_cursor is select * from emp;
v_emp emp%rowtype;

begin
loop
fetch v_cursor into v_emp;
exit when v_cursor%notfound;
dbms_output.put_line(v_emp.empname);
end loop;
close v_cursor;
exception
when invalid_cursor then
dbms_output.put_line(‘操作无效的游标’);
end;

/*
7.invalid_number 不能将有效的字符转化为数字’
*/
begin
update emp set sal = sal + ‘1OO’;
exception
when invalid_number then
dbms_output.put_line(‘被操作的数字格式又问提…..’);
end;

/*
8.no_data_found
9.too_many_rows
*/

/*
10. zero_divide 除数分母为0
*/

declare
v_1 int:=100;
v_2 int :=0;
v_3 int;
begin
v_3 := v_1 / v_2;
exception
when zero_divide then
dbms_output.put_line(‘除数分母为0…..’);
end;

/*
11. subscript_beyond_count 嵌套表或变长数组下标超阶
*/

declare
type v_varray_type is varray(100) of varchar2(50);
v_varray v_varray_type := v_varray_type(”,”,”);
begin
dbms_output.put_line(v_varray(4));
exception
when subscript_beyond_count then
dbms_output.put_line(‘数组下标超界…..’);
end;

/*
12. subscript_outside_limit 嵌套表或变长数组下标为负值时
*/

declare
type v_varray_type is varray(100) of varchar2(50);
v_varray v_varray_type := v_varray_type(”,”,”);
begin
dbms_output.put_line(v_varray(-1));
exception
when subscript_outside_limit then
dbms_output.put_line(‘数组下标不合法（超出限制）…..’);
end;

/*
13. value_error 变量长度不够
*/

declare
v_char varchar(1) ;
begin
select empname into v_char from emp where empno = 5;
dbms_output.put_line(v_char);
exception
when value_error then
dbms_output.put_line(‘变量长度不够…..’);
end;