User Tools

Site Tools


kurs:bonus_update

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

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)