by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

정적 SQL을 사용하라




☞ 정적 SQL 사용

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


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

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

동적 SQL 정적 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;
/
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 DBMS_SQL Package
  • Easy To Use
  • Faster than DBMS_SQL
  • Supports User-Defined Types
  • Supports Fetching Into Records
  • 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
  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;
/
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 비교
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

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.