동적 SQL

-- 동적 SQL

    동적 쿼리는 프로시져등에서 인자로 받은 변수를 조합하여 쿼리문을 생성하여 사용하는 경우를 말한다.

    예를 들어 블로그에서 사용자별 게시판 테이블을 만들 때.

  -- 주의

      RESOURCE 롤은 테이블을 생성할 수 있지만 동적 SQL을 이용하여 테이블을 생성할수 는 없다. 

      동적 SQL로 테이블을 생성하기 위해서는 CREATE TABLE 권한을 설정 해 주어야 한다.

 -- SYS 계정

  cmd>sqlplus sys/"암호" AS sysdba;

  sql>GRANT CREATE TABLE TO 사용자;

------------------------------------------------------------

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] ...]; 

------------------------------------------------------------

CREATE  OR  REPLACE  PROCEDURE  dynTest

IS

    ssql   VARCHAR2(200);

    vid   NUMBER;

    vmemo  VARCHAR2(20);

    TYPE  my  IS  RECORD

    (

           id   NUMBER

  ,memo   VARCHAR2(20)

    );

    rec  MY;

BEGIN

     -- 테이블이 존재하면 테이블 제거

     FOR  temp  IN  (SELECT * FROM  tab  WHERE  tname='TEST') LOOP

             EXECUTE   IMMEDIATE   'DROP  TABLE  test  PURGE';

     END LOOP;

     -- 테이블작성

     ssql:='CREATE  TABLE  test(id NUMBER, memo  VARCHAR2(20))';

     EXECUTE   IMMEDIATE ssql;

     -- 데이터추가

     ssql:='INSERT  INTO  test  VALUES(:1, :2)';

     FOR  n  IN  1..5  LOOP

            vid:=n;

   vmemo:=CHR(n+64);

   EXECUTE   IMMEDIATE  ssql  USING  vid, vmemo;

     END LOOP;

     -- 데이터 출력

     ssql:='SELECT  *  FROM   test  WHERE  id=1';

     EXECUTE   IMMEDIATE  ssql  INTO  rec;

     DBMS_OUTPUT.PUT_LINE(rec.id || '   ' || rec.memo);

END;

/

EXEC  dynTest;

SELECT  *  FROM  tab;

SELECT  *  FROM  test;