Differences

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

--- kurs:for_update_cursor_und_exception_im_block [2011/11/23 14:58]
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;
+</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
+*/
-</code>
-<code>
+e_row_lock exception;
-PROCEDURE update_bonus
+pragma exception_init (e_row_lock, -00054);
-   ( 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';
+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
-for i in 1..3 loop
-  begin
+  while (v_done = false and i <= 10) loop
-    if not c_set_emp_bonus%isopen then
+  i := i + 1;
-      open c_set_emp_bonus;
+  begin
-    end if;
+    for r_emp in c_emp_bonus loop
-    loop
+      if r_emp.salary > 10000 then
-      fetch c_set_emp_bonus into r;
+        v_bonus := 0;
-      exit when c_set_emp_bonus%notfound;
+      else
- /*   for r in c_set_emp_bonus loop */
+        v_bonus := r_emp.salary * 0.1;
-      update emp set bonus = salary*p_bonus_prozent
+      end if;
-        where current of c_set_emp_bonus;
+        update emp_bonus set bonus = v_bonus
-    end loop;
+          where current of c_emp_bonus;
-    v_worked := 'true';
+    end loop;
-  exception when e_update_wait then
+    v_done := true;
-    dbms_lock.sleep(5);
+    exception
-    dbms_output.put_line('Wait '|| i);
+      when e_row_lock then
-  end;
+          dbms_output.put_line('lock found, retrying ' || i);
-  exit when v_worked = 'true';
+          dbms_lock.sleep(1);
-end loop;
+  end;
+  end loop;
-if c_set_emp_bonus%isopen then
-  close c_set_emp_bonus;
+END UPDATE_BONUS;
-end if;
-dbms_output.put_line(v_worked);
-END; -- Procedure
 </code>

dokuwiki

User Tools

Site Tools

Differences

Page Tools