dokuwiki

SQL> connect sys/***@*** as sysdba
Connect durchgef³hrt.
SQL> grant execute on dbms_lock to hr;

create table emp_bonus as (
 select employee_id, first_name, last_name, salary, 0 bonus
  from employees)

CREATE OR REPLACE PROCEDURE UPDATE_BONUS AS 
/*
+ cursor c_emp_bonus der alle felder der Tabelle
selektiert

+ for schleife über den cursor

+ im fehlerfall ora-00054 (muss man mit einem 
namen versehen (e_row_lock)
=> eine sekunde warten (dbms_lock(1) ), dann erneut versuchen 

zusatzaufgabe:
+ dass sollte man natuerlich nicht unendlich oft machen
sondern maximal 10mal 

*/

e_row_lock exception;
pragma exception_init (e_row_lock, -00054);


cursor c_emp_bonus is 
  select employee_id, 
         first_name,
         last_name,
         salary,
         bonus 
    from emp_bonus for update of bonus nowait;
    
v_bonus number;   
v_done  boolean := false;
i number := 0;
BEGIN
  
  while (v_done = false and i <= 10) loop
  i := i + 1;
  begin            
    for r_emp in c_emp_bonus loop
      if r_emp.salary > 10000 then
        v_bonus := 0;
      else 
        v_bonus := r_emp.salary * 0.1;
      end if;
        update emp_bonus set bonus = v_bonus
          where current of c_emp_bonus;                
    end loop;
    v_done := true;
    exception 
      when e_row_lock then
          dbms_output.put_line('lock found, retrying ' || i);
          dbms_lock.sleep(1);
  end;              
  end loop;
  
END UPDATE_BONUS;