This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
kurs:for_update_cursor_und_exception_im_block [2014/09/10 21:22] 127.0.0.1 external edit |
kurs:for_update_cursor_und_exception_im_block [2014/11/11 12:35] 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 | + | */ |
- | </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 | 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> | </code> |