15일차 # 7-25: CURRENT OF + FOR UPDATE cursor

참고:http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/static.htm#sthref652

 * current of를 사용하려면 커서가 for update와 함께 선언되어야 함

--> FOR UPDATE cursor

  * without need to explicitly reference the row ID

- Simulating CURRENT OF Clause with ROWID Pseudocolumn: http://goo.gl/io8Wq

  * simulation

drop table test_emp purge;

create table test_emp as select * from emp;

create index test_emp_empno_idx on test_emp(empno);

begin

 for i in 1..10 loop

   insert into test_emp select * from test_emp;

 end loop;

 commit;

end;

/  

select count(*) from test_emp;

   ------------------------------

  1> UNIQUE KEY

create or replace procedure test_emp_sal_update_proc1(p_deptno number, p_percent number)

is

 cursor emp_cur(dno number) is

   select * from test_emp

   where deptno = dno

   FOR UPDATE;

begin

 for r in emp_cur(p_deptno) loop

   update test_emp

   set sal = r.sal*p_percent

   where empno = r.empno;

 end loop;

end;

/

   ------------------------------

  2> ROWID

create or replace procedure test_emp_sal_update_proc2(p_deptno number, p_percent number)

is

 cursor emp_cur(dno number) is

   select ROWID as id, e.* --> ROWID 추가

   from test_emp e

   where deptno = dno

   FOR UPDATE;

begin

 for r in emp_cur(p_deptno) loop

   update test_emp

   set sal = r.sal*p_percent

   where ROWID = r.id; --> WHERE 절에서 ROWID 사용

 end loop;

end;

/

   ------------------------------

  3> CURRENT OF

create or replace procedure test_emp_sal_update_proc3(p_deptno number, p_percent number)

is

 cursor emp_cur (dno number) is

   select * 

   from test_emp

   where deptno = dno

   FOR UPDATE;

begin

 for r in emp_cur(p_deptno) loop

   update test_emp

   set sal = r.sal*p_percent

   where CURRENT OF emp_cur; --> CURRENT OF 절

 end loop;

end;

/

   ------------------------------

set timing on

exec test_emp_sal_update_proc1(10, 1.2)      --> Elapsed: 00:00:31.65

rollback;

exec test_emp_sal_update_proc2(10, 1.2)      --> Elapsed: 00:00:00.39

rollback;

exec test_emp_sal_update_proc3(10, 1.2)      --> Elapsed: 00:00:00.28

rollback;

   ------------------------------

  * NOWAIT

[session 1] [session 2]

----------- -----------

drop table emp_test purge;

create table emp_test

        as 

select empno, ename, job, sal, deptno 

from emp;

select * from emp_test;

select * from emp_test;

declare

 cursor cur_emp is

   select *

   from emp_test FOR UPDATE;

 rec_emp cur_emp%rowtype;

begin

 open cur_emp;

 loop  

   fetch cur_emp into rec_emp;

   exit when cur_emp%notfound;

   if rec_emp.job = 'CLERK' then

     update emp_test

     set sal = sal * 1.1

     where current of cur_emp;

   end if;

 end loop;

 close cur_emp;

end;

/

select * from emp_test;

update emp_test

set deptno = 40

where ename like 'S%';

commit;

select * from emp_test;

select * from emp_test;

rollback;

   ------------------------------------------------------------

[session 1] [session 2]

----------- -----------

drop table emp_test purge;

create table emp_test

        as 

select empno, ename, job, sal, deptno 

from emp;

select * from emp_test;

update emp_test

set deptno = 40

where ename like 'S%';

declare

 cursor cur_emp is

   select *

   from emp_test for update NOWAIT; --> nowait 추가

 rec_emp cur_emp%rowtype;

begin

 open cur_emp;

 loop  

   fetch cur_emp into rec_emp;

   exit when cur_emp%notfound;

   if rec_emp.job = 'CLERK' then

     update emp_test

     set sal = sal * 1.1

     where current of cur_emp;

   end if;

 end loop;

 close cur_emp;

end;

/

==> 에러: ORA-00054: 리소스가 사용 중이어서 NOWAIT가 지정되었거나 시간 초과가 만료된 상태로 획득합니다.

<-- session 2에서 SMITH와 SCOTT의 행에 LOCK을 걸었기 때문

commit;

/

select * from emp_test;