Differences

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

--- kurs:bonus_update [2014/09/10 21:22]
+++ 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>

dokuwiki

User Tools

Site Tools

Differences

Page Tools