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 for i in 1..3 loop begin if not c_set_emp_bonus%isopen then open c_set_emp_bonus; end if; 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; if c_set_emp_bonus%isopen then close c_set_emp_bonus; end if; dbms_output.put_line(v_worked); END; -- Procedure