This shows you the differences between two versions of the page.
— |
kurs:p_bonus_diff [2014/09/10 21:22] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | <code> | ||
+ | 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 | ||
+ | </code> |