이펙티브 오라클 (2009년)
목록
정적 SQL을 사용하라 0 0 43,792

by 구루비스터디 정적SQL PLSQL [2018.05.26]

정적 SQL 사용

  • 코드를 조금 더 작성 하더라도, 정적 SQL 을 사용해라.
  • 동적 SQL 은 확장성이 떨어지며, 디버깅, 유지보수가 어렵고, 같은 기능의 정적 SQL에 비해 느리게 수행된다.
  • 몇줄의 코드를 줄이기 위해서 동적 SQL 을 사용하면 안된다, 정적 SQL 을 사용했을때 적어도 수백줄 정도는 되거나, 정적 SQL 로는 답이 안나올때만 동적 SQL 을 사용해라.


정적 SQL 의 이점

  • 정적 SQL은 컴파일 시간에 검사된다.
  • PL/SQL은 데이터 유형, 크기 등의 유효성을 검증한다.
  • 종속성 이 설정되고 데이터 사전에 유지된다.
  • 시간이 흘러 데이터베이스 객체가 변한다 하더라도 코드가 자동으로 이 변화에 맞도록 진화한다.
  • 정적 SQL은 한 번 파싱된 후 여러 번 실행 된다.
  • 정적 SQL이 보다 빠르다.


정적 SQL, 동적 SQL 비교 데모#1

동적 SQL


create or replace function get_value_dyn
( p_empno in number, p_cname in varchar2 ) return varchar2
as
  l_value varchar2(4000);
begin
  execute immediate
  'select ' || p_cname || ' from emp where empno = :x'
  into l_value
  using p_empno;

  return l_value;
end;
/


정적 SQL


create or replace function get_value_static
( p_empno in number, p_cname in varchar2 ) return varchar2
as
  l_value varchar2(4000);
begin
  select decode( upper(p_cname), 'ENAME', ename,
                                 'EMPNO', empno,
				 'HIREDATE', to_char(hiredate, 'YYYYMMDDHH24MISS'))
    into l_value
    from emp
   where empno = p_empno;

  return l_value;
end;
/


Native Dynamic SQL 과 DBMS_SQL Package 데모#2

Native Dynamic SQL

  • Easy To Use
  • Faster than DBMS_SQL
  • Supports User-Defined Types
  • Supports Fetching Into Records

DECLARE
  TYPE EmpCurTyp IS REF CURSOR;
  cur EmpCurTyp;
  stmt_str VARCHAR2(200);
  name VARCHAR2(20);
  salary NUMBER;
BEGIN
  stmt_str := 'SELECT ename, sal FROM emp WHERE job = :1';
  OPEN cur FOR stmt_str USING 'SALESMAN'; 

LOOP
  FETCH cur INTO name, salary; 
  EXIT WHEN cur%NOTFOUND; 
  -- <process data>  
END LOOP; 
CLOSE cur;
END;
/


DBMS_SQL Package

  • Supported in Client-Side Programs
  • Supports DESCRIBE
  • Supports Multiple Row Updates and Deletes with RETURNING Clause
  • Supports SQL Statements Larger than 32KB
  • Lets You Reuse SQL Statements|

DECLARE  
  stmt_str varchar2(200);
  cur_hdl int;
  rows_processed int;
  name varchar2(10);
  salary int;
BEGIN
cur_hdl := dbms_sql.open_cursor; -- open cursor 
stmt_str := 'SELECT ename, sal FROM emp WHERE job = :jobname';
dbms_sql.parse(cur_hdl, stmt_str, dbms_sql.native); 

-- supply binds (bind by name) 
dbms_sql.bind_variable(cur_hdl, 'jobname', 'SALESMAN'); 

-- describe defines 
dbms_sql.define_column(cur_hdl, 1, name, 200); 
dbms_sql.define_column(cur_hdl, 2, salary); 

rows_processed := dbms_sql.execute(cur_hdl); -- 
execute 

LOOP 
  -- fetch a row 
  IF dbms_sql.fetch_rows(cur_hdl) > 0 then 

    -- fetch columns from the row 
    dbms_sql.column_value(cur_hdl, 1, name); 
    dbms_sql.column_value(cur_hdl, 2, salary); 

    -- <process data> 

  ELSE
    EXIT; 
  END IF; 
END LOOP; 
dbms_sql.close_cursor(cur_hdl); -- close cursor
END;
/


데모

데모#1 - 정적 SQL


SQL> create table emp(empno number, ename varchar2(30), hiredate date);

테이블이 생성되었습니다.

SQL> insert into emp (empno, ename, hiredate)
select rownum, object_name, created from all_objects where rownum < 101;  2

100 개의 행이 만들어졌습니다.

SQL> create index emp_pk on emp (empno);

인덱스가 생성되었습니다.

SQL> create or replace function get_value_dyn
  2  ( p_empno in number, p_cname in varchar2 ) return varchar2
  3  as
  4    l_value varchar2(4000);
  5  begin
  6    execute immediate
  7    'select ' || p_cname || ' from emp where empno = :x'
  8    into l_value
  9    using p_empno;
 10
 11    return l_value;
 12  end;
 13  /

함수가 생성되었습니다.

SQL> create or replace function get_value_static
  2  ( p_empno in number, p_cname in varchar2 ) return varchar2
  3  as
  4    l_value varchar2(4000);
  5  begin
  6    select decode( upper(p_cname), 'ENAME', ename,
  7                                   'EMPNO', empno,
  8                              'HIREDATE', to_char(hiredate, 'YYYYMMDDHH24MISS'))
  9      into l_value
 10      from emp
 11     where empno = p_empno;
 12
 13    return l_value;
 14  end;
 15  /

함수가 생성되었습니다.

SQL> exec runstats_pkg.rs_start;

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

SQL> declare
  l_dummy varchar2(30);
begin
  2    3    4    for i in 1 .. 500
  loop
  5    6      for x in ( select empno from emp )
  7      loop
  8        l_dummy := get_value_dyn ( x.empno, 'ENAME' );
  9        l_dummy := get_value_dyn ( x.empno, 'EMPNO' );
 10        l_dummy := get_value_dyn ( x.empno, 'HIREDATE' );
 11      end loop;
 12    end loop;
 13  end;
 14  /

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

SQL> exec runstats_pkg.rs_middle;

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

SQL> declare
  2    l_dummy varchar2(30);
begin
  3    4    for i in 1 .. 500
  5    loop
  6      for x in ( select empno from emp )
  7      loop
  8        l_dummy := get_value_static ( x.empno, 'ENAME' );
  9        l_dummy := get_value_static ( x.empno, 'EMPNO' );
 10        l_dummy := get_value_static ( x.empno, 'HIREDATE' );
 11      end loop;
 12    end loop;
 13  end;
 14  /

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

SQL> exec runstats_pkg.rs_stop(1000);
Run1 ran in 1364 hsecs
Run2 ran in 1168 hsecs
run 1 ran in 116.78% of the time

Name                                  Run1        Run2        Diff
STAT...consistent gets             253,548     303,521      49,973
STAT...consistent gets from ca     253,548     303,521      49,973
STAT...session logical reads       253,566     303,545      49,979
STAT...no work - consistent re     102,520     152,505      49,985
STAT...table fetch by rowid        100,000     150,000      50,000
LATCH.cache buffers chains         510,805     608,849      98,044
STAT...buffer is not pinned co     200,000     300,000     100,000
STAT...session cursor cache hi     149,993           4    -149,989
STAT...parse count (total)         150,012           8    -150,004
LATCH.shared pool simulator        200,355         156    -200,199

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
717,414     612,138    -105,276    117.20%

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




데모#2 - DBMS_SQL


SQL> create table emp (empno number, ename varchar2(20));

테이블이 생성되었습니다.

SQL> declare
  v_empno number := 1;
  v_ename varchar2(20) := 'Tiger Woods';
  stmt_str varchar2(200);
begin
  stmt_str := 'INSERT INTO emp VALUES (:empno, :ename)';
  EXECUTE IMMEDIATE stmt_str USING v_empno, v_ename;
end;
/  2    3    4    5    6    7    8    9

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

SQL> select * from emp;

     EMPNO ENAME
---------- --------------------
         1 Tiger Woods

SQL> declare
  2    v_empno number := 2;
  3    v_ename varchar2(20) := 'Elin Nordegren';
  4
  5    cur_hdl number;
  6    rows_processed number;
  7    stmt_str varchar2(200);
  8  begin
  9    stmt_str := 'INSERT INTO emp VALUES (:empno, :ename)';
 10    cur_hdl := dbms_sql.open_cursor;
 11
 12    dbms_sql.parse(cur_hdl, stmt_str, dbms_sql.native);
 13    dbms_sql.bind_variable(cur_hdl, ':empno', v_empno);
 14    dbms_sql.bind_variable(cur_hdl, ':ename', v_ename);
 15
 16    rows_processed := dbms_sql.execute(cur_hdl);
 17    dbms_sql.close_cursor(cur_hdl);
 18
 19  end;
 20  /

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

SQL> select * from emp;

     EMPNO ENAME
---------- --------------------
         1 Tiger Woods
         2 Elin Nordegren

SQL> set serveroutput on;
SQL> declare
  v_empno number := 1;
  v_ename varchar2(20);
  2    3    4    stmt_str varchar2(200);
begin
  5    6    stmt_str := 'UPDATE emp SET empno = empno * 10 WHERE empno = :empno RETURNING ename INTO :ename';
  7    EXECUTE IMMEDIATE stmt_str USING v_empno, OUT v_ename;
  8
  9    DBMS_OUTPUT.PUT_LINE('ENAME: ' || v_ename);
 10  end;
 11  /
ENAME: Tiger Woods

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

SQL>  select * from emp;

     EMPNO ENAME
---------- --------------------
        10 Tiger Woods
         2 Elin Nordegren

SQL> declare
  2    v_empno number := 1;
  3    v_ename_array dbms_sql.varchar2_table;
  4
  5    cur_hdl number;
  6    rows_processed number;
  7    stmt_str varchar2(200);
  8  begin
  9    stmt_str := 'UPDATE emp SET empno = empno * 10 WHERE empno > :empno RETURNING ename INTO :ename';
 10    cur_hdl := dbms_sql.open_cursor;
 11
 12    dbms_sql.parse(cur_hdl, stmt_str, dbms_sql.native);
 13    dbms_sql.bind_variable(cur_hdl, ':empno', v_empno);
 14    dbms_sql.bind_array(cur_hdl, ':ename', v_ename_array);
 15
 16    rows_processed := dbms_sql.execute(cur_hdl);
 17    dbms_sql.variable_value(cur_hdl, ':ename', v_ename_array);
 18    dbms_sql.close_cursor(cur_hdl);
 19
 20    for x in 1 .. rows_processed
 21    loop
 22      dbms_output.put_line('ENAME: ' || v_ename_array(x));
 23    end loop;
 24
 25  end;
 26  /
ENAME: Tiger Woods
ENAME: Elin Nordegren

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

SQL> select * from emp;

     EMPNO ENAME
---------- --------------------
       100 Tiger Woods
        20 Elin Nordegren
"구루비 데이터베이스 스터디모임" 에서 2009년에 "이펙티브 오라클" 도서를 스터디하면서 정리한 내용 입니다.

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

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

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

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