kurs:bonus_update
Differences
This shows you the differences between two versions of the page.
| — |
kurs:bonus_update [2014/09/10 21:22] (current) |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| + | <code> | ||
| + | PROCEDURE PC_ADD_BONUS | ||
| + | IS | ||
| + | cursor c_upd_bonus is | ||
| + | SELECT pc_employee_bonus.salary, pc_employee_bonus.employee_id, | ||
| + | pc_employee_bonus.last_name, pc_employee_bonus.bonus, | ||
| + | pc_employee_bonus.relativ_bonus | ||
| + | FROM pc_employee_bonus | ||
| + | ORDER BY pc_employee_bonus.salary DESC | ||
| + | FOR UPDATE OF pc_employee_bonus.bonus, pc_employee_bonus.relativ_bonus NOWAIT; | ||
| + | type erg is record ( | ||
| + | salary pc_employee_bonus.salary%TYPE, | ||
| + | employee_id pc_employee_bonus.employee_id%TYPE, | ||
| + | last_name pc_employee_bonus.last_name%TYPE, | ||
| + | bonus pc_employee_bonus.bonus%TYPE, | ||
| + | relativ_bonus pc_employee_bonus.relativ_bonus%TYPE); | ||
| + | |||
| + | erg_record erg; | ||
| + | old_salary number(30); | ||
| + | pre_old_salary number(30); | ||
| + | |||
| + | BEGIN | ||
| + | open c_upd_bonus; | ||
| + | |||
| + | dbms_output.put_line('Bonus eintragen:'); | ||
| + | |||
| + | old_salary := 0; | ||
| + | pre_old_salary := 0; | ||
| + | loop | ||
| + | fetch c_upd_bonus into erg_record; | ||
| + | exit when c_upd_bonus%NOTFOUND; | ||
| + | | ||
| + | if old_salary = erg_record.salary then | ||
| + | old_salary := pre_old_salary; | ||
| + | end if; | ||
| + | | ||
| + | if erg_record.salary > 5000 then | ||
| + | erg_record.bonus := erg_record.salary * 0.05; | ||
| + | end if; | ||
| + | if erg_record.salary <= 5000 then | ||
| + | erg_record.bonus := erg_record.salary * 0.2; | ||
| + | end if; | ||
| + | |||
| + | update pc_employee_bonus | ||
| + | set bonus = erg_record.bonus | ||
| + | where current of c_upd_bonus; | ||
| + | |||
| + | if erg_record.salary * 1.1 > old_salary then | ||
| + | erg_record.relativ_bonus := erg_record.salary * 0.05; | ||
| + | else | ||
| + | erg_record.relativ_bonus := erg_record.salary * 0.2; | ||
| + | end if; | ||
| + | |||
| + | update pc_employee_bonus | ||
| + | set relativ_bonus = erg_record.relativ_bonus | ||
| + | where current of c_upd_bonus; | ||
| + | |||
| + | dbms_output.put_line(erg_record.last_name||' Salary:'||erg_record.salary||' Bonus:'||erg_record.bonus||' Relativer Bonus:'||erg_record.relativ_bonus); | ||
| + | |||
| + | pre_old_salary := old_salary; | ||
| + | old_salary := erg_record.salary; | ||
| + | end loop; | ||
| + | |||
| + | if (c_upd_bonus%rowcount = 0) then | ||
| + | dbms_output.put_line('Kein Datensatz gefunden!!'); | ||
| + | else | ||
| + | dbms_output.put_line(c_upd_bonus%rowcount||' Datensätze upgedated!!'); | ||
| + | end if; | ||
| + | |||
| + | close c_upd_bonus; | ||
| + | |||
| + | END; -- Procedure | ||
| + | </code> | ||
kurs/bonus_update.txt · Last modified: 2014/09/10 21:22 (external edit)
Page Tools
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
