SELECT <columns> INTO <variables> FROM <tables> ...
...
is
cursor c is select <columns> from <tables> ...
...
begin
open c;
fetch c into <variables>;
if ( c%notfound )
then
raise no_data_found;
end if;
fetch c into <variables>;
if ( c%found )
then
raise too_many_rows;
end if;
close c;
...
for x in ( select ... from ... where ... )
loop
process...
end loop;
데모#1 - 단일 행 선택을 위해 암시적 커서 사용하기
SQL> create table t ( object_id primary key, object_name ) organization index
as
select object_id, object_name from all_objects; 2 3
테이블이 생성되었습니다.
SQL> create or replace procedure explicit
2 as
3 l_object_name t.object_name%type;
4 l_dummy t.object_name%type;
5
6 cursor c ( l_object_id in number )
7 is
8 select object_name from t where object_id = l_object_id;
9 begin
10 for i in 1 .. 30000
11 loop
12 open c(i);
13 fetch c into l_object_name;
14 if ( c%notfound )
15 then
16 l_object_name := null;
17 end if;
18 fetch c into l_dummy;
19 if ( c%found )
20 then
21 raise too_many_rows;
22 end if;
23 close c;
24 end loop;
25 end;
26 /
프로시저가 생성되었습니다.
SQL> create or replace procedure implicit
2 as
3 l_object_name t.object_name%type;
4 begin
5 for i in 1 .. 30000
6 loop
7 begin
8 select object_name into l_object_name
9 from t
10 where object_id = i;
11 exception
12 when no_data_found then
13 l_object_name := null;
14 end;
15 end loop;
16 end;
17 /
프로시저가 생성되었습니다.
SQL> exec runstats_pkg.rs_start;
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> exec explicit;
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> exec runstats_pkg.rs_middle;
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> exec implicit;
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> exec runstats_pkg.rs_stop(1000);
Run1 ran in 1082 hsecs
Run2 ran in 497 hsecs
run 1 ran in 217.71% of the time
Name Run1 Run2 Diff
LATCH.cache buffers chains 63,349 60,927 -2,422
STAT...recursive calls 83,462 36,544 -46,918
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
69,270 63,883 -5,387 108.43%
PL/SQL 처리가 정상적으로 완료되었습니다.
데모#2 - 제한된 수의 행을 가진 결과 집합에 암시적 커서 사용하기
SQL> create or replace procedure explicit
as
l_rec dept%rowtype;
cursor c
is
select * from dept;
begin
open c;
loop
fetch c into l_rec;
exit when c%notfound;
end loop;
close c;
end;
/ 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
프로시저가 생성되었습니다.
SQL> create or replace procedure implicit
as
begin
for x in ( select * from dept )
loop
null;
end loop;
end;
/ 2 3 4 5 6 7 8 9
프로시저가 생성되었습니다.
SQL> exec runstats_pkg.rs_start;
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> begin
for i in 1 .. 30000
loop
explicit;
end loop;
end;
/ 2 3 4 5 6 7
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> exec runstats_pkg.rs_middle;
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> begin
for i in 1 .. 30000
loop
implicit;
end loop;
end;
/
2 3 4 5 6 7
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> SQL> exec runstats_pkg.rs_stop(1000);
Run1 ran in 655 hsecs
Run2 ran in 478 hsecs
run 1 ran in 137.03% of the time
Name Run1 Run2 Diff
STAT...session logical reads 180,030 90,039 -89,991
STAT...consistent gets from ca 180,010 90,013 -89,997
STAT...consistent gets 180,010 90,013 -89,997
STAT...table scan blocks gotte 120,000 30,000 -90,000
STAT...no work - consistent re 120,000 30,000 -90,000
STAT...recursive calls 180,001 30,003 -149,998
LATCH.cache buffers chains 360,944 180,970 -179,974
STAT...table scan rows gotten 480,000 120,000 -360,000
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
362,233 182,149 -180,084 198.87%
PL/SQL 처리가 정상적으로 완료되었습니다.