This shows you the differences between two versions of the page.
Next revision | Previous revision | ||
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> | <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> | </code> |