dokuwiki

PROCEDURE "GET_SALARY_EXCEPTION" 
( p_last_name IN VARCHAR2) AS
  v_salary number;

  v_department_name varchar2(50);
  v_err number := 0;
  v_sqlerrm varchar2(1000);
  e number;
  i number := 0;
  k number := 10;
BEGIN

begin
  select salary, department_name into v_salary, v_department_name
   from employees e, departments d
      where last_name       = p_last_name and     
            e.department_id = d.department_id;
  dbms_output.put_line(
       p_last_name || ' verdient ' || v_salary ||' und arbeitet in ' ||
       v_department_name );
       
  e:= k/i; -- achtung division durch 0!

  EXCEPTION
  when NO_DATA_FOUND then
    dbms_output.put_line('Name nicht in Tabelle');    
    v_err := 1;
    v_sqlerrm := SQLERRM;
  when TOO_MANY_ROWS then
    dbms_output.put_line('Name mehrfach in der  Tabelle');
    v_err := 2;
    v_sqlerrm := SQLERRM;
/*  when OTHERS then  
    dbms_output.put_line('Boeser Fehler');
    v_err := 3;
    v_sqlerrm := SQLERRM;*/
end;
  
if (v_err > 0) then
    dbms_output.put_line('jetzt bin ich nach dem Fehler! '
      || v_sqlerrm);
end if;

exception
    when OTHERS then  
    dbms_output.put_line('Boeser Fehler');
    v_err := 3;
    v_sqlerrm := SQLERRM;

END;

declare 
  v_emp_id number;
  v_err    number;
begin 
  v_err := 0;
  begin
  select employee_id into v_emp_id from bonus
     where last_name = 'Fay';  
    exception
  when NO_DATA_FOUND then
    dbms_output.put_line('nix da');
    v_err := 1;
  when TOO_MANY_ROWS then
    dbms_output.put_line('viel zu viel da');
    v_err := 2;
  when OTHERS then
    dbms_output.put_line('hui bu, echt schlimm');
    v_err := 3;    
  end; 

  if v_err != 0 then
    v_emp_id := 1;
    /*
    ...
    ...
    */    
  end if;
  dbms_output.put_line(v_emp_id ||' '||  v_err);
end;