例外

Oracle 例外学习练习

--no_data_found
 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('Other');
 end;

--too_many_rows

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('Other');
end;
  

--zero_divide
declare
  pnum number;
begin
  pnum := 1 / 0;
exception
  when zero_divide then--then相当于一个大括号，可以有多条语句
    dbms_output.put_line('1.0不能做被除数');
    dbms_output.put_line('2.0不能做被除数');
  when others then
    dbms_output.put_line('其他');
end;

--value_error
declare
  pnum number;
begin
  pnum := 'abc';
exception
  when value_error then
    dbms_output.put_line('类型错误');
  when others then
    dbms_output.put_line('其他');
end;

--自定义例外：查询50号部门的员工姓名
declare
  cursor cemp is
    select ename from emp where deptno = 50;
  pename emp.ename%type;
  no_emp_found exception; --自定义例外
begin
  open cemp;
  fetch cemp
    into pename;
  if cemp%notfound then
    raise no_emp_found;
  end if;
exception
  when no_emp_found then
    dbms_output.put_line('没有找到员工');
  when others then
    dbms_output.put_line('Others');
    close cemp;
end;