============================================
ORACLE8I : DYNAMIC SQL (NATIVE DYNAMIC SQL)
============================================

PURPOSE
---------

DBMS_SQL package을 사용하여 compile 시에 고정되어지는 제한을 극복
하며 Dynamic SQL statement을 구현할 수 있다.

Explanation
-------------

Oracle 8i에서의 Dynamic SQL은 두가지 방법이 있다.첫번째 방법은
"EXECUTE IMMEDIATE" 절을 사용하여 embedded dynamic sql을 구현하는
것이고, 두번째 방법은 선언되어지는 것 대신에 실행시에 문장을 보내는
ref cursor의 확장된 개념으로 query를 위해 사용되어지는 방법이다.

Example
---------

방법 1: EXECUTE IMMEDIATE
----------------------------

Syntax:
EXECUTE IMMEDIATE dynamic_sql_string
[INTO {define_var1 [, define_var2] ... | plsql_record }]
[USING [IN | OUT | IN OUT] bind_arg1 [,
[IN | OUT | IN OUT] bind_arg2] ...];

1. DDL문 실행. bind variable을 허용하지 않는다.

declare
str varchar2(200);
begin
str := 'create table msg (msg varchar2(120))';
EXECUTE IMMEDIATE str;
end;

2. bind variable을 사용하지 않는 non-query DML문 실행 예.

declare
str varchar2(200);
begin
str := 'insert into msg values (''Hello'')';
EXECUTE IMMEDIATE str;
end;

3. bind variable을 사용하지 않는 non-query DML문 실행 예.
variable은 default로 IN bind이므로 IN, OUT mode을 나타낼
필요는 없다.

declare
str varchar2(200);
val varchar2(20);
begin
str := 'insert into msg values (:b1)';
val := 'Bye';
EXECUTE IMMEDIATE str USING val;
end;

4. bind variable을 이용하여 single row select을 실행하고,
single define variable에 fetch하는 예.

declare
str varchar2(200);
val varchar2(20);
ret varchar2(20);
begin
str := 'select msg from msg where msg = :b1';
val := 'Hello';
EXECUTE IMMEDIATE str INTO ret USING val;
dbms_output.put_line('Value fetched from table: '||ret);
end;

Result:

Value fetched from table: Hello

5. 4.의 예와 같은 select문을 수행하며 , PL/SQL record type에 fetch하는 예.

declare
str varchar2(200);
val varchar2(20);
ret msg%rowtype;
begin
str := 'select msg from msg where msg = :b1';
val := 'Hello';
EXECUTE IMMEDIATE str INTO ret USING val;
dbms_output.put_line('Value fetched from table: '||ret.msg);
end;

Result:

Value fetched from table: Hello

6. returning절을 포함한 delete문을 수행하는 예. 이 경우에는 OUT mode로
선언된 bind variable이 존재해야 한다. delete된 row을 알기 위해서는
sql%rowcount을 사용하며, 모든 sql% attribute가 유효하다.

declare
str varchar2(200);
val varchar2(20);
ret varchar2(20);
begin
val := 'Bye';
str := 'delete from msg where msg = :b1 returning msg into :b2';
EXECUTE IMMEDIATE str USING val, OUT ret;
dbms_output.put_line('Deleted '||sql%rowcount||
' row(s) with value: '||ret);
end;

Result:

Deleted 1 row(s) with value: Bye

7. error을 check하면서 stored function을 만들고 만들어진 function을
실행하며, function은 return type으로 OUT bind variable이 제공된다.

declare
str varchar2(200);
-- Declare an exception to trap "compiled with errors"
compile_warn exception;
pragma exception_init(compile_warn,-24344);
val number;
ret number;
begin
begin
-- A deliberate error (spelling of number)
str := 'create or replace function doubleit (p1 in umber)'||
' return number as begin return p1*2; end;';
EXECUTE IMMEDIATE str;
exception
when compile_warn then
-- Correct and reexecute
str := replace(str,'in umber','in number');
EXECUTE IMMEDIATE str;
when others then raise;
end;

-- Call the stored function
str := 'begin :b1 := doubleit(:b2); end;';
val := 30;
EXECUTE IMMEDIATE str USING OUT ret, IN val;
dbms_output.put_line('Result of '||val||' doubled is '||ret);
end;

Result:

Result of 30 doubled is 60

8. 7의 예와 같은 function을 새로운 CALL 절을 이용하여 실행한다.

declare
str varchar2(200);
val number;
ret number;
begin
-- Call the stored function
str := 'CALL doubleit(:b2) INTO :b1';
val := 30;
EXECUTE IMMEDIATE str USING IN val, OUT ret;
dbms_output.put_line('Result of '||val||' doubled is '||ret);
end;

Result:

Result of 30 doubled is 60

9. 중복된 place holder가진 SQL문을 실행하는 예.

declare
str varchar2(200);
val_str varchar2(20);
val_num number;
ret number;
begin
-- DML : 이 경우는 SQL문으로 bind variable 수만큼의 place holder
-- 가 필요하다.
str := 'insert into msg values (:b1||'' ''||:b1)';
val_str := 'Hello';
EXECUTE IMMEDIATE str USING val_str, val_str;

-- PL/SQL 에서는 bind variable 수만큼의 place holder가 필요하지 않다.
str := 'begin :b1 := doubleit(:b2+:b2); end;';
val_num := 30;
EXECUTE IMMEDIATE str USING OUT ret, IN val_num;
dbms_output.put_line('Result of '||val_num||' quadrupled is '
||ret);
end;

Result:

Result of 30 quadrupled is 120

10. NOCOPY parameter을 가진 stored procedure을 부르는 예.

create or replace procedure add_stars (p1 in varchar2,
p2 out NOCOPY varchar2) as
begin
p2 := p1||'***';
end;

NOCOPY parameter는 version 8.1에서 소개되었다. value에 의해서기 보다는
reference에 의해 전달되어지는 output parameter이며, 성능을 증가시킬뿐만
아니라 메모리를 절약할 수 있다. value에 의해 전달되어지는 parameter처럼
bind variable이 선언되어진다.

declare
str varchar2(100) := 'call add_stars(:b1,:b2)';
in_str varchar2(20) := 'Hello World';
out_str varchar2(20);
begin
EXECUTE IMMEDIATE str USING IN in_str, OUT out_str;
dbms_output.put_line(out_str);
end;

Result:

Hello World***

방법 2: ref cursor
----------------------

Syntax:
OPEN cursor_var FOR dynamic_query_string
[USING bind_arg1 [, bind_arg2] ...];


다음의 예제를 수행하기 전에 수행되어질 사항:

create table msg (msg varchar2(120));
insert into msg values ('Hello');
insert into msg values ('Bye');

Weak: type my_cur_type is ref cursor;
Strong: type my_cur_type is ref cursor return emp%rowtype;

1. bind 없이 cursor을 open하는 예.

declare
type my_curs_type is REF CURSOR; -- must be weakly typed
curs my_curs_type;
str varchar2(200);
ret varchar2(20);
begin
str := 'select msg from msg';
-- No placeholders so no USING clause
OPEN curs FOR str;
loop
FETCH curs INTO ret;
exit when curs%notfound;
dbms_output.put_line(ret);
end loop;
CLOSE curs;
end;

Result:

Hello
Bye

2. bind variable을 사용하여 cursor을 open하고 single row을 fetch하는 예.

declare
type my_curs_type is REF CURSOR; -- must be weakly typed
curs my_curs_type;
str varchar2(200);
ret varchar2(20);
val varchar2(20);
begin
str := 'select msg from msg where msg = :b1';
val := 'Bye';
OPEN curs FOR str USING val;
FETCH curs INTO ret;
dbms_output.put_line('Value fetched from table: '||ret);
CLOSE curs;
end;

Result:

Value fetched from table: Bye

3. 새로운 bulk collect 을 이용하여 한번에 여러 row을 fetch하는 예.

declare
str varchar2(200);
type my_curs_type is REF CURSOR; -- must be weakly typed
curs my_curs_type;

-- Use a nested table to fetch into. This could equally be
-- a VARRAY or index by table.
type string_tab is table of varchar2(20);
ret_tab string_tab; -- don't need to initialise, fetching
-- will do this automatically
begin
str := 'select msg from msg';
OPEN curs FOR str;
FETCH curs BULK COLLECT INTO ret_tab;
dbms_output.put_line('Array fetch: ');
for i in 1..curs%rowcount loop
dbms_output.put_line(ret_tab(i));
end loop;
CLOSE curs;
end;

Result:

Array fetch:
Hello
Bye


Reference Documents
-------------------
Note:62592.1
Note:93939.1
PL/SQL User's Guide and Reference, Chapter 10