============================================
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