Differences

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

--- kurs:for_update_cursor_und_exception_im_block [2010/05/26 13:26]
mh created
+++ 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;
+</code>
-e_row_lock exception;
+<code>
+create table emp_bonus as (
+ select employee_id, first_name, last_name, salary, 0 bonus
+  from employees)
+</code>
-pragma EXCEPTION_INIT
+<code>
-     (e_row_lock, -00054);
+CREATE OR REPLACE PROCEDURE UPDATE_BONUS AS
+/*
++ cursor c_emp_bonus der alle felder der Tabelle
+selektiert
-v_bonus number;
++ for schleife über den cursor
-wc      number := 0;
-done    boolean := false ;
-BEGIN
-while wc <= 100 and done = false loop
++ im fehlerfall ora-00054 (muss man mit einem
-begin
+namen versehen (e_row_lock)
-for r_cws in c_w_s loop
+=> eine sekunde warten (dbms_lock(1) ), dann erneut versuchen
-  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
+zusatzaufgabe:
- dbms_output.put_line('failed');
++ dass sollte man natuerlich nicht unendlich oft machen
- -- rather raise error
+sondern maximal 10mal
-else
- dbms_output.put_line('update ');
-end if;
-END; -- Procedure
+*/
+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;
 </code>

dokuwiki

User Tools

Site Tools

Differences

Page Tools