oracle处理例外
/*
预定义例外
*/
declare
  v_ename emp.ename%type;
begin
  select ename into v_ename from  emp where empno=&no;
  dbms_output.put_line('雇员名：'||v_ename);
  exception
   when TOO_MANY_ROWS THEN
dbms_output.put_line('查询只能返回单行');
   when NO_DATA_FOUND then
dbms_output.put_line('雇员号不正确');
end;

--ACCESS_INTO_NULL

create type emp_type as object
  (name varchar2(10),sal number(6,2));
 
GO

declare
   emp emp_type;
begin
   emp.name:='scott';
   exception
     when access_into_null then
    dbms_output.put_line('首先初始化对象emp');
end;

--CASE_NOT_FOUND
undef no
declare
  v_sal emp.sal%type;
begin
  select sal into v_sal from emp where empno=&&no;
  dbms_output.put_line('v_sal=='||v_sal);
case
  when v_sal<1000 then
    dbms_output.put_line('小于1000');
  when v_sal<2000 then
     dbms_output.put_line('小于2000');
  when v_sal<3000 then
    dbms_output.put_line('小于3000');
end case;
  exception
  when case_not_found then
   dbms_output.put_line('在Case语句中缺少与'||v_sal||'相关的事件');

end;

--COLLECTION_IS_NULL
declare
type ename_table_type is table of emp.ename%type;
ename_table ename_table_type;
begin
  select ename into ename_table(2) from emp where empno=&no;
  dbms_output.put_line('雇员名：'||ename_table(2));
  exception
    when collection_is_null then
     dbms_output.put_line('必须使用构造方法初始化集合元素');
end;
--CURSOR_ALREADY_OPEN
declare
cursor emp_cursor is select * from emp;
begin
  open emp_cursor;
  for emp_record in emp_cursor loop
    dbms_output.put_line('雇员：'||emp_record .ename);
  end loop;
  exception
    when cursor_already_open then
    dbms_output.put_line('游标已打开');
end;

--DUM_VAL_ON_INDEX
begin
  update dept set deptno=&new_no where deptno=&old_no;
exception
  when dup_val_on_index then
      dbms_output.put_line('在deptno上不能出现重复值');
end;

--INVALID_CURSOR
declare
cursor emp_cursor is select * from emp;
emp_record emp_cursor%rowtype;
begin
fetch emp_cursor into emp_record;
exception
when invalid_cursor then
dbms_output.put_line('请检查游标是否已经打开');
end;

--INVALID_NUMBER
begin
update emp set sal=sal+'1b';
exception
when invalid_number then
dbms_output.put_line('输入的数字不正确');
end;

--NO_DATA_FOUND
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where lower(ename)=lower('&name');
exception
when no_data_found then
dbms_output.put_line('不存在该雇员');
end;
--TOO_MANY_ROWS
declare
v_ename emp.ename%type;
begin
select ename into v_ename from emp where sal>2000;
exception
when too_many_rows then
dbms_output.put_line('返回多行，需用集合变量');
end;
--ZERO_DIVIDE
declare
num1 int:=100;
num2 int:=0;
num3 number(6,2);
begin
num3:=num1/num2;
exception
when zero_divide then
dbms_output.put_line('分母不能为零');
end;
--SUBSCRIPT_BEYOND_COUNT
declare
type emp_array_type is varray(20) of varchar2(10);
emp_array emp_array_type;
begin
emp_array:=emp_array_type('scott','marry');
dbms_output.put_line(emp_array(3));
exception
  when subscript_beyon