--系统列外

set serveroutput on

declare

  pename emp.ename%type;

begin

  select ename into pename  from emp where empno =1234;

exception

  when no_data_found then dbms_output.put_line('没有查到数据');

  when others then dbms_output.put_line('其他');

end;

/

 --系统例外： too_many_rows

 set serveroutput on

 declare

   pename emp.ename%type;

 begin

   select ename into pename from emp where deptno = 10;

 exception

   when too_many_rows then dbms_output.put_line('select into 匹配多行');

   when others  then dbms_output.put_line('其他');

 end;

 /

 --系统例外 : value_error

 set serveroutput on

 declare

   pnum number;

 begin

   pnum := 'abc';

 exception

   when value_error then dbms_output.put_line('算术或转换错误');

   when others then dbms_output.put_line('其他');

 end;

 /

 --系统例外 zero_divide

 set serveroutput on

 declare

   pnum number;

 begin

   pnum := 1/0;

 exception

   when zero_divide then dbms_output.put_line('0不能做除数');

   when others then dbms_output.put_line('其他');

 end;

 /

--自定义例外: 

set serveroutput on

declare

  cursor cemp is select ename from emp where deptno =50;

  pename emp.ename%type;

  --自定义列外

  not_emp_data exception;

begin

  open cemp;

  fetch cemp into pename;

  if cemp%notfound then

      raise not_emp_data;

  end if;

  --如果程序程序中出现例外,oracle会通过pmon(process monitor)自动关闭清理资源

  close cemp;  

exception

  when not_emp_data then dbms_output.put_line('自定义例外:没有查询到数据');

  when others then dbms_output.put_line('其他列外');

end;

/