오라클임시테이블 생성 GLOBAL TEMPORARY TABLE

오라클 임시 테이블을 생성은 아래와 같이 하면 된다

1. 정의 
CREATE GLOBAL TEMPORARY TABLE 임시테이블 (
      컬럼명 DATATYPE....
 ) [ON COMMIT DELETE ROWS | ON COMMIT REVERSE ROWS];

 

2. 옵션의 의미

 1) ON COMMIT DELETE ROWS - (Default) 한 Transaction 동안만 Data를 저장
 2) ON COMMIT REVERSE ROWS - 한 Session 동안만 Data를 저장

 

3. 오라클 임시 테이블 생성 예

 

CREATE OR REPLACE PROCEDURE TEMP_TBL
IS                         
     v_exit_cnt         NUMBER(1);                                         
     V_TBL_NAME         VARCHAR2(150);         
     V_SQL              VARCHAR2(1024);  

    CURSOR C1_CUR IS                               
        SELECT TABLE_NAME
        FROM USER_TABLES
        WHERE TABLE_NAME='TMP_PRO';
                
BEGIN

    OPEN C1_CUR;
    FETCH C1_CUR INTO V_TBL_NAME;
   
    --임시 테이블이 존재시 테이블 삭제
    IF C1_CUR%FOUND THEN
        V_SQL := 'DROP TABLE TMP_PRO';
        EXECUTE IMMEDIATE V_SQL;
    END IF;
   

    --임시테이블 생성
    V_SQL := 'CREATE  GLOBAL TEMPORARY TABLE TMP_PRO ('
        || '    COL1    CHAR(10), COL2    VARCHAR2(20), COL3    NUMBER(10)'
        || '  ) ON  COMMIT  DELETE ROWS';
    EXECUTE IMMEDIATE V_SQL;
   
    --임시테이블에 정보 INSERT
    V_SQL :=  'INSERT INTO TMP_PRO'
            ||'     SELECT 1,2,3 FROM DUAL '
            ||'     UNION ALL '
            ||'     SELECT 4,5,6 FROM DUAL ';
    EXECUTE IMMEDIATE V_SQL;
   
    CLOSE C1_CUR;
  
COMMIT;                                                          
                                                          
EXCEPTION                                                                              
    WHEN     OTHERS THEN                                                          
            dbms_output.put_line(SQLERRM);                   
            ROLLBACK;                                                          
END;