dokuwiki

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