이펙티브 오라클 (2009년)
데이터 반환하기 0 0 48,177

by 구루비스터디 참조커서 REF CURSOR PLSQL [2018.05.26]


오라클은 저장 프로시저로부터 결과 집합을 반환하지 않는다?


REF CUROSR 데모#1

  • 개발 용이성 - 모든 커서는 결과 집합을 다룰 수 있다
  • 융통성 - 클라이언트 애플리케이션은 한 번에 몇 개의 행을 인출해 올지를 선택 할 수 있다
  • 성능 - 데이터 인출 중 배열이 차면 즉시 클라이언트에 전송


구분INDEX BYREF CUROSR비고
첫 번째 행까지의 시간825ms25ms-800ms
마지막 행까지의 시간1,375ms850ms-515ms
모든 행을 인출하는 데 걸린 시간2,220ms885ms-1,315ms
TRUNC(AVG(UGA))76,98876,988
TRUNC(AVG(PGA))4,266,132244,793


INDEX BY


create or replace package demo_pkg
as

  type varchar2_array is table of varchar2(30) index by binary_integer;
  procedure index_by ( p_owner in varchar2, p_object_name out varchar2_array,
                       p_object_type out varchar2_array, p_timestamp out varchar2_array );

end;
/

create or replace package body demo_pkg
as

  procedure index_by ( p_owner in varchar2, p_object_name out varchar2_array,
                       p_object_type out varchar2_array, p_timestamp out varchar2_array )
  is
  begin
    select object_name, object_type, timestamp
      bulk collect into p_object_name, p_object_type, p_timestamp
      from t
     where owner = p_owner;
  end;

end;
/


REF CURSOR


create or replace package demo_pkg
as

  type rc is ref cursor;
  procedure ref_cursor ( p_owner in varchar2, p_cursor in out rc );

end;
/

create or replace package body demo_pkg
as

  procedure ref_cursor ( p_owner in varchar2, p_cursor in out rc )
  is
  begin
    open p_cursor for
    select object_name, object_type, timestamp from t where owner = p_owner;
  end;

end;
/


데모


데모#1 - REF CURSOR


18:43:59 SQL> create or replace package pkg_ref_cursor as
  type rc is ref cursor return all_objects%rowtype;
  procedure p(v out rc);
end;
/18:44:01   2  18:44:01   3  18:44:01   4  18:44:01   5

패키지가 생성되었습니다.

경   과: 00:00:00.00
18:44:03 SQL> create or replace package body pkg_ref_cursor as

  procedure p(v out rc) is
  begin
    open v for select * from all_objects where rownum < 1001;
  end;

end;
/18:44:06   2  18:44:06   3  18:44:06   4  18:44:06   5  18:44:06   6  18:44:06   7  18:44:06   8  18:44:06   9

패키지 본문이 생성되었습니다.

경   과: 00:00:00.00
18:44:07 SQL> var rcv refcursor
18:44:10 SQL> exec runstats_pkg.rs_start;

PL/SQL 처리가 정상적으로 완료되었습니다.

경   과: 00:00:00.04
18:44:13 SQL> exec pkg_ref_cursor.p(:rcv);

PL/SQL 처리가 정상적으로 완료되었습니다.

경   과: 00:00:00.00
18:44:18 SQL> exec runstats_pkg.rs_middle;

PL/SQL 처리가 정상적으로 완료되었습니다.

경   과: 00:00:00.02
18:44:19 SQL> print :rcv;

OWNER                          OBJECT_NAME     SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED  LAST_DDL TIMESTAMP        STATUS          T G S
------------------------------ --------------- ------------------------------ ---------- -------------- ------------------- -------- -------- ------------------- --------------- - - -
SYS                            DUAL                                                  258            258 TABLE               05/06/30 05/06/30 2005-06-30:19:10:20 VALID        N N N
...
(중략)
...
SYS                            ALL_CONS_COLUMN                                      2541                VIEW                05/06/30 08/07/08 2005-06-30:19:11:19 VALID        N N N
                               S


1000 개의 행이 선택되었습니다.

경   과: 00:00:00.31
18:44:22 SQL> exec runstats_pkg.rs_stop(1000);
Run1 ran in 671 hsecs
Run2 ran in 493 hsecs
run 1 ran in 136.11% of the time

Name                                  Run1        Run2        Diff
STAT...index fetch by key                0       1,915       1,915
STAT...buffer is pinned count            0       3,014       3,014
STAT...table fetch by rowid              0       3,422       3,422
STAT...table scan rows gotten            0       3,468       3,468
STAT...index scans kdiixs1               0       8,056       8,056
STAT...buffer is not pinned co           0      16,298      16,298
STAT...no work - consistent re           0      21,015      21,015
STAT...consistent gets                   9      21,460      21,451
STAT...consistent gets from ca           9      21,460      21,451
STAT...session logical reads            29      21,482      21,453
LATCH.cache buffers chains           3,719      43,408      39,689
STAT...bytes sent via SQL*Net        1,638      42,182      40,544

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
6,179      45,446      39,267     13.60%

PL/SQL 처리가 정상적으로 완료되었습니다.

경   과: 00:00:00.02

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

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

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

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

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