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

  1. 2010.01.04 EXECUTE IMMEDIATE를 이용한 Dynamic SQL
ORACLE/SQL2010. 1. 4. 14:58

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

  ================================================
    * 데이터베이스 정보공유 커뮤니티 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]

티스토리툴바