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_beyond_count then
    dbms_output.put_line('超出下标范围');
end;

--SUBSCRIPT_OUTSIDE_LIMIT
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(-1));
exception
  when subscript_outside_limit then
  dbms_output.put_line('下标不能为负');
end;
--VALUE_ERROR
declare
v_ename varchar2(2);
begin
select ename into v_ename from emp where empno=&&no;
dbms_output.put_line(v_ename);
exception
  when value_error then
   dbms_output.put_line('变量尺寸不足');
end;
/*
非预定义例外
*/
--
  declare
  e_integrity exception;
  pragma exception_init(e_integrity,-2291);

begin
  update emp set deptno=&detpno where empno=&empno;
  exception
    when e_integrity then
    dbms_output.put_line('该部门不存在');
end;
/*
自定义例外
*/

  declare
  e_integrity exception;
  pragma exception_init(e_integrity,-2291);
  e_no_employee exception;
begin
  update emp set deptno=&detpno where empno=&empno;
  if sql%notfound then
    raise e_no_employee;
  end if;
  exception
    when e_integrity then
    dbms_output.put_line('该部门不存在');
    when e_no_employee then
     dbms_output.put_line('该雇员不存在');
end;
/**
例外函数
*/
-- SQLCODE,SQLERRM
declare
v_ename emp.ename%type;
begin
select ename into v_ename from emp where sal=&&v_sal;
dbms_output.put_line('雇员：'+v_ename);
exception
  when no_data_found then
   dbms_output.put_line('不存在工资为'||&v_sal||'的雇员');
  when others then
    dbms_output.put_line('错误号：'||sqlcode);
    dbms_output.put_line(sqlerrm);
end;
--RAISE_APPLICATION_ERROR

create or replace procedure raise_comm
(eno number,commission number)
is
v_comm emp.comm%type;
begin
select comm into v_comm from emp where empno=eno;
if v_comm is null then
raise_application_error(-20001,'该雇员无补助');
   end if;
exception
when no_data_found then
dbms_output.put_line('该雇员不存在');
end;

/*
编译警告
*/
create or replace procedure dead_code as
  x number:=10;
begin
   if x=10 then
x:=20;
   else
x:=100;--(死代码，永远不执行)
   end if;
end  dead_code ;

alter session set plsql_warnings='enable:informational';
alter procedure dead_code compile;

show errors;