Native Dynamic SQL의 이해와 예제

출처 :http://okjsp.pe.kr/seq/12984

52.32.10.46

================================================================= Oracle8i : Dynamic SQL (Native Dynamic SQL) ================================================================= DBMS_SQL package을 사용하여 compile 시에 고정되어지는 제한을 극복 하며 Dynamic SQL statement을 구현할 수 있다. Oracle 8i에서의 Dynamic SQL은 두가지 방법이 있다.첫번째 방법은 "EXECUTE IMMEDIATE" 절을 사용하여 embedded dynamic sql을 구현하는 것이고, 두번째 방법은 선언되어지는 것 대신에 실행시에 문장을 보내는 ref cursor의 확장된 개념으로 query를 위해 사용되어지는 방법이다. 방법 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] ...]; 예제: -------- (i) DDL문 실행. bind variable을 허용하지 않는다. declare str varchar2(200); begin str := 'create table msg (msg varchar2(120))'; EXECUTE IMMEDIATE str; end; (ii) bind variable을 사용하지 않는 non-query DML문 실행 예. declare str varchar2(200); begin str := 'insert into msg values (''Hello'')'; EXECUTE IMMEDIATE str; end; (iii) 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; (iv) 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 (v) (iv)예와 같은 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 (vi) 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 (vii) 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 (viii) (vii)예와 같은 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 (ix) 중복된 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 (x) 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; (i) 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 (ii) 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 (iii) 새로운 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 Further Information ------------------- PL/SQL User's Guide and Reference, Chapter 10. from oracle 白面書生