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