dokuwiki

This is an old revision of the document!

---

PROCEDURE update_bonus
   IS
cursor c_w_s is
select salary, bonus from working_salary for update of bonus nowait;

e_row_lock exception;

pragma EXCEPTION_INIT
     (e_row_lock, -00054);

v_bonus number;
wc      number := 0;
done    boolean := false ;
BEGIN

while wc <= 100 and done = false loop
begin
for r_cws in c_w_s loop
  if r_cws.salary > 5000 then
    v_bonus := -2000;
  else
    v_bonus := 1000;
  end if;
  update working_salary set bonus = v_bonus
  where current of c_w_s;
  done := true;
end loop;
EXCEPTION
when e_row_lock then
  done := false;
  dbms_output.put_line(to_char(wc) || ' loop. row(s) locked, sleeping ...');
  dbms_lock.sleep(1);
  wc := wc + 1;
end;
end loop;

if done = false then
 dbms_output.put_line('failed');
 -- rather raise error
else
 dbms_output.put_line('update ');
end if;

END; -- Procedure

PROCEDURE update_bonus
   ( p_bonus_prozent IN number DEFAULT 0.1)
   IS
   cursor c_set_emp_bonus is
     select salary, bonus from emp for
       update of bonus nowait;

e_update_wait EXCEPTION;

pragma EXCEPTION_INIT(e_update_wait, -00054);

r c_set_emp_bonus%rowtype;

v_worked varchar2(10) := 'false';

BEGIN

open c_set_emp_bonus;

for i in 1..3 loop
  begin
    loop
      fetch c_set_emp_bonus into r;
      exit when c_set_emp_bonus%notfound;
 /*   for r in c_set_emp_bonus loop */
      update emp set bonus = salary*p_bonus_prozent
        where current of c_set_emp_bonus;
    end loop; 
    v_worked := 'true';
  exception when e_update_wait then
    dbms_lock.sleep(5);
    dbms_output.put_line('Wait '|| i);
  end;
  exit when v_worked = 'true';
end loop;

close c_set_emp_bonus;
dbms_output.put_line(v_worked);

END; -- Procedure