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