dokuwiki

PROCEDURE P_BONUS_DIFF
   IS
  cursor c_emp_sal is
  SELECT emp_bonus.last_name, emp_bonus.salary
    FROM emp_bonus
  ORDER BY emp_bonus.salary DESC
  for update of bonus;

   v_old_name   emp_bonus.last_name%TYPE;
   v_new_name   emp_bonus.last_name%TYPE;
   v_new_salary emp_bonus.salary%TYPE;
   v_old_salary emp_bonus.salary%TYPE;
   v_diff       number(30,2);
   v_bonus      number(30,2);

BEGIN
   open c_emp_sal;
   --fetch c_emp_sal into v_old_name, v_old_salary;
   loop
     fetch c_emp_sal into v_new_name, v_new_salary;
     exit when c_emp_sal%notfound;
     if v_new_salary != v_old_salary then
       v_diff := 100-v_new_salary/v_old_salary*100;
     end if;
     
     v_bonus := 0.05;
     if v_diff>10 then
       v_bonus := 0.2;
     end if;

       dbms_output.put_line(v_new_name || ' hat  ' ||
                            v_diff || '% von Bonus: ' ||
                            v_bonus);
                            
     update emp_bonus
       set bonus = v_new_salary*v_bonus
       where current of c_emp_sal;
       
     v_old_name   := v_new_name;
     v_old_salary := v_new_salary;
    -- exit when c_emp_sal%rowcount>5;
   end loop;
   close c_emp_sal;

END; -- Procedure