이펙티브 오라클 (2009년)
암시적 혹은 명시적 커서를 사용할 지 선택하라 0 0 56,738

by 구루비스터디 암시적커서 명시적커서 [2018.05.26]


단일 행 선택을 위해 암시적 커서 사용하기 데모#1


SELECT INTO 는..
  • 코드의 양이 적고 읽기 쉽다
  • 보다 빠르게 실행 된다
  • 코드의 안전성이 보다 우수하다
    • ORA-01422: 실제 인출은 요구된 것보다 많은 수의 행을 추출합니다.
    • ORA-01403: 데이터가 없습니다.


암시적 커서

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;
  ...


제한된 수의 행을 가진 결과 집합에 암시적 커서 사용하기 데모#2


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 처리가 정상적으로 완료되었습니다.

"구루비 데이터베이스 스터디모임" 에서 2009년에 "이펙티브 오라클" 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3510

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입