User Tools

Site Tools


kurs:uebung_2.2
PROCEDURE P_CURS_BONUS_ERWEITERT
  IS
  v_old_name    xl_emp_bonus.last_name%type;
  v_new_name    xl_emp_bonus.last_name%type;
  v_old_salary  xl_emp_bonus.salary%type;
  v_new_salary  xl_emp_bonus.salary%type;
  v_differenz   number(30);
  v_bonus       number(30,2);
  
  rec_bonus   xl_emp_bonus%ROWTYPE;
  CURSOR curs_bonus IS
    SELECT xl_emp_bonus.last_name, xl_emp_bonus.salary
      FROM xl_emp_bonus
      ORDER BY xl_emp_bonus.salary DESC
      for update of bonus;
BEGIN
  OPEN curs_bonus;
  --FETCH curs_bonus INTO v_old_name, v_old_salary;
  LOOP
    FETCH curs_bonus INTO v_new_name, v_new_salary;
    exit when curs_bonus%NOTFOUND;
    if v_new_salary != v_old_salary then
      v_differenz := 100-v_new_salary/v_old_salary*100;
    end if;

    v_bonus := 0.05;
    IF v_differenz > 10 THEN
      v_bonus := 0.20;
    END IF;
    UPDATE xl_emp_bonus
      SET bonus_dynamisch = v_new_salary * v_bonus
      WHERE CURRENT OF curs_bonus;
      v_old_name := v_new_name;
      v_old_salary := v_new_salary;
    --exit when curs_bonus%ROWCOUNT>5;
   END LOOP;
   CLOSE curs_bonus;
END;
kurs/uebung_2.2.txt · Last modified: 2014/09/10 21:22 (external edit)