참고: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;
