Oracle Database TIP
EXECUTE IMMEDIATE를 이용한 Dynamic SQL 9 8 57,986

by 김정식 EXECUTE IMMEDIATE DYNAMIC SQL [2003.03.16]


  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
    

- 강좌 URL : http://www.gurubee.net/lecture/1720

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by 궁금이 [2005.06.15 09:46:53]
다이나믹 PROC를 실행 결과를 리턴 받을 순 없나요?
예) RETURN = EXECUTE IMMEDIATE v_str USING v_table_name;


by MiNi [2006.06.16 12:10:27]
3. 테이블 생성 후 INSERT 예제 여기서

OPEN cur_exists 끝에 ; <= 이게 빠졌네요...
이거 넣고 돌리셔야 에러 없을꺼에요~~

by 손님 [2007.03.22 20:18:08]
정작 원하는 다이나믹 sql은 없넹 ㅡㅡ;;
select문도 좀 써줘요~ 다이나믹하게 select문을 생성해서
뿌려주는 방법~ 도 올려줘요~ select문은 execute immediate로 실행해도
결과는 알수없죠~ 결과를 뿌려주는 방법아시는분 글올려줘요

by 웅 [2007.08.07 10:18:39]
v_str := 'SELECT COUNT(*) cnt FROM '||v_table_name ;
이것이 다이나믹 시퀄아닌가요?

그나저나 v_str := 'INSERT INTO table_rows VALUES ('||v_cnt||', :A1 )';
EXECUTE IMMEDIATE v_str USING v_table_name;
여기서 using이 뜻하는 바가 뭔지 설명좀 해주실 분~~

by 개경 [2007.08.17 15:01:59]
INSERT INTO table_rows VALUES ('||v_cnt||',:A1) 중
:A1에 들어가는 변수 아닌가요?
table_rows 테이블의 속성을 보면
CREATE TABLE table_rows (total number, table_name varchar2(50))
table_name 이 들어가네요..

by 웅 [2007.08.17 18:02:39]
아~ 그렇군요. 설명 감사합니다.

백문이 불여일타...테스트해봤으면 찾을 수 있었을 문제였군요.
성의부족입니다..쩝

by 시가있는풍경 [2007.11.14 17:04:06]
바인드 변수죠~

by 구름 [2008.01.09 08:59:57]
805 버젼에서도 가능한 구문좀 알려주세요
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입