'DYNAMIC SQL'에 해당되는 글 1건

  1. 2010.01.04 EXECUTE IMMEDIATE를 이용한 Dynamic SQL
ORACLE/SQL2010. 1. 4. 14:58
반응형
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

  ================================================
    * 데이터베이스 정보공유 커뮤니티 oracleclub.com
    * 강좌 작성자 : 김정식 (oramaster _at_ naver.com)
  ================================================
※ oracleclub 강좌를 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
※ oracleclub 강좌는 개인의 학습용으로만 사용 할 수 있습니다. 학원 홍보용이나 수익을 얻기 위한 용도로
    사용을 하시면 안됩니다. ^^
반응형

'ORACLE > SQL' 카테고리의 다른 글

Oracle EXISTS Versus IN  (0) 2010.06.18
NOT IN과 NOT EXISTS의 차이점  (0) 2010.04.02
SQL *Plus 명령어와 SQL문 구분하기  (0) 2010.01.03
DB 최적화를 고려한 다건조회 페이징처리  (0) 2010.01.03
SQL TIP  (0) 2010.01.03
Posted by [PineTree]