Differences

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

--- kurs:for_update_cursor_und_exception_im_block [2011/11/23 14:53]
mh
+++ kurs:for_update_cursor_und_exception_im_block [2014/11/11 12:35] (current)
mh
@@ Line 1: / Line 1: @@
 <code>
-PROCEDURE update_bonus
+SQL> connect sys/***@*** as sysdba
-   IS
+Connect durchgef³hrt.
-cursor c_w_s is
+SQL> grant execute on dbms_lock to hr;
-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
 </code>
 <code>
-PROCEDURE update_bonus
+create table emp_bonus as (
-   ( p_bonus_prozent IN number DEFAULT 0.1)
+ select employee_id, first_name, last_name, salary, 0 bonus
-   IS
+  from employees)
-   cursor c_set_emp_bonus is
+</code>
-     select salary, bonus from emp for
-       update of bonus nowait;
-e_update_wait EXCEPTION;
+<code>
+CREATE OR REPLACE PROCEDURE UPDATE_BONUS AS
+/*
++ cursor c_emp_bonus der alle felder der Tabelle
+selektiert
-pragma EXCEPTION_INIT(e_update_wait, -00054);
++ for schleife über den cursor
-r c_set_emp_bonus%rowtype;
++ im fehlerfall ora-00054 (muss man mit einem
+namen versehen (e_row_lock)
+=> eine sekunde warten (dbms_lock(1) ), dann erneut versuchen
-v_worked varchar2(10) := 'false';
+zusatzaufgabe:
++ dass sollte man natuerlich nicht unendlich oft machen
+sondern maximal 10mal
-BEGIN
+*/
-open c_set_emp_bonus;
+e_row_lock exception;
+pragma exception_init (e_row_lock, -00054);
-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;
+cursor c_emp_bonus is
-dbms_output.put_line(v_worked);
+  select employee_id,
+         first_name,
-END; -- Procedure
+         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;
 </code>

dokuwiki

User Tools

Site Tools

Differences

Page Tools