꿈꾸는 개발자, DBA 커뮤니티 구루비

Oracle 8i에서의 Dynamic SQL은 두가지 방법이 있다.

첫번째 방법은 EXECUTE IMMEDIATE 절을 사용하여 EMBEDDED DYNAMIC SQL을 구현하는 방법이고, 두번째 방법은 선언되어지는 것 대신에 실행시에 문장을 보내는 REF CURSOR의 확장된 개념으로 QUERY를 위해 사용되어지는 방법 이다.

여기서는 EXECUTE IMMEDIATE를 이용한 Dynamic SQL에 대해서 알아보겠다.

EXECUTE IMMEDIATE 문법

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

간단하게 테이블을 생성하는 예제

첫번째 예제는 간단하게 테이블을 생성하는 예제 이다.

 
SQL> CREATE OR REPLACE PROCEDURE dynamic_sql_01
     IS

        str VARCHAR2(200);

     BEGIN

        str := 'CREATE TABLE  total (total number)';
        EXECUTE IMMEDIATE str;

    END;
    /
 
-- 프로시저를 실행해서 테이블을 생성 해보자. 
SQL> EXEC dynamic_sql_01;
 
-- 생성된 테이블을 확인해 보자 
SQL> DESC total;
 이름                        널?      유형
 --------------- -------- -----------
 TOTAL                    NUMBER


-- 프로시저 생성시 "ORA-01031: 권한이 불충분합니다" 에러가 발생하면
-- SYS권한으로 접속을 해서 EXECUTE IMMEDIATE를 실행하는 유저에게
-- CREATE ANY TABLE 권한을 부여 하면 된다.   

SQL> CONN / AS SYSDBA  
SQL> GRANT create any table TO scott;

테이블 생성 후 INSERT 예제

두번째 예제는 TABLE_ROWS라는 테이블을 생성하고, 다이나믹 하게 테이블명을 입력하면 테이블명과 테이블에 등록된 데이터수를 TABLE_ROWS에 INSERT하고 출력하는 예제 이다.

 
SQL> CREATE OR REPLACE PROCEDURE dynamic_sql_02
        (v_table_name IN VARCHAR2)
     IS
   
        v_str VARCHAR2(200);
        v_cnt NUMBER;
        v_temp VARCHAR2(50);
    
        CURSOR cur_exists IS
        SELECT TABLE_NAME
        FROM USER_TABLES
        WHERE table_name = 'TABLE_ROWS';
    
     BEGIN
    
        OPEN cur_exists;
        FETCH cur_exists INTO v_temp;
    
        -- 테이블이 존재하면 테이블을 삭제 합니다. 
        IF  cur_exists%FOUND THEN     
            v_str := 'DROP TABLE  table_rows';
            EXECUTE IMMEDIATE v_str;
        END IF;    

        -- 테이블 생성
        v_str := 'CREATE  TABLE  table_rows (total number, table_name varchar2(50))';
        EXECUTE IMMEDIATE v_str;    

        -- 데이터 카운트 조회
        v_str := 'SELECT COUNT(*) cnt FROM '||v_table_name ;
        EXECUTE IMMEDIATE v_str INTO v_cnt ;

        -- 데이터 insert
        v_str := 'INSERT INTO table_rows  VALUES ('||v_cnt||', :A1 )';   
        EXECUTE IMMEDIATE v_str USING v_table_name;

        DBMS_OUTPUT.PUT_LINE(' 테이블 명 : '||v_table_name||'  데이터 수 : '||v_cnt);    

        CLOSE cur_exists;     

     END;
     /
 
SQL> SET SERVEROUTPUT ON:

-- emp 테이블명과 테이블의 데이터카운트를 INSERT 한다. 
SQL> EXEC dynamic_sql_02('emp');
테이블 명 : emp  데이터 수 : 14

-- 정상적으로 처리되었는지 확인해 보자.
SQL> SELECT * FROM table_rows;
 
     TOTAL TABLE_NAME
---------- --------------
        14 emp