[펌]EXECUTE IMMEDIATE를 이용한 Dynamic SQL

1. EXECUTE IMMEDIATE를 이용한 Dynamic SQL 

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

첫번째 방법은  "EXECUTE IMMEDIATE" 절을 사용하여 embedded dynamic sql을 구현하는
방법이고,

두번째 방법은 선언되어지는 것 대신에 실행시에 문장을 보내는 ref cursor의 확장된 개념으로
query를 위해 사용되어지는 방법 입니다.

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

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

 

2. 간단하게 테이블을 생성하는 예제 입니다

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

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;
PL/SQL 처리가 정상적으로 완료되었습니다.
 
 
-- 생성된 테이블을 확인해 봅니다.
SQL> DESC total;
 이름                                      널?      유형
 ----------------------------------------- -------- -----------
 TOTAL                                              NUMBER

 

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

SQL> CONN system/manager 
SQL> GRANT create any table TO scott;

 

3. 테이블 생성 후 INSERT 예제

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

===============================================================

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

PL/SQL 처리가 정상적으로 완료되었습니다.

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