[ORACLE] 테이블 레코드값 INSERT 쿼리 자동 생성

/*

 * PGM ID   :   ORACLE SQL - UNLOAD_INSERT_QUERY_GENERATOR

 * TITLE    :   테이블 레코드값 INSERT 쿼리 자동 생성

 * AUTHOR   :   grapro.lim@gmail.com

 * DATE     :   2011.11.18

 * VERSION  :   1.0

 * CONTENTS :   SELECT후 결과값을 복사하여 결과값 SQL문장을 다시 SELECT함

                PARAMETER 2개 설정 필요

 * MODIFIED : 

 */

WITH VTBL AS

(

        SELECT  ROWNUM      AS RNO

                , 100       AS STMT_CNT     /* PARAMETER : 한 번에 INSERT할 ROW수 */

                , TNAME     AS TBL

        FROM    TAB

        WHERE   TNAME IN (                  /* PARAMETER : INSERT QUERY를 생성할 테이블명 */

                 'TEMP_INSERT'

                ,'TBL_RULESET_INFO_HTRY'

                )

)

SELECT  'SELECT CASE WHEN CHK_S=''S'' THEN ''INSERT INTO '||TBL||' NOLOGGING ('||INSERT_COLS||') '' END || ''SELECT '||SELECT_COLS||' FROM DUAL'' || CASE WHEN CHK_E=''E'' THEN '';'' ELSE '' UNION ALL'' END AS QRY FROM (SELECT CASE WHEN MOD(ROWNUM+'||STMT_CNT||'-1 ,'||STMT_CNT||') = 0 THEN ''S'' END AS CHK_S, CASE WHEN MOD(ROWNUM ,'||STMT_CNT||') = 0 OR MAX(ROWNUM) OVER() = ROWNUM THEN ''E'' END AS CHK_E, A.* FROM '||TBL||' A)'

        ||CASE WHEN RNO = MAX(RNO) OVER () THEN ';' ELSE ' UNION ALL' END 

        AS INSERT_QUERY_GENERATOR

FROM    (

        SELECT  MIN(RNO)    AS RNO

                , MIN(STMT_CNT) AS STMT_CNT

                , TBL

                , SUBSTR(MAX(SYS_CONNECT_BY_PATH(COLUMN_NAME, ',')), 2) AS INSERT_COLS

                , REPLACE(SUBSTR(MAX(SYS_CONNECT_BY_PATH(SEL_COL, '$')), 2), '$', ', ') AS SELECT_COLS

        FROM    (

                SELECT  ROW_NUMBER() OVER (PARTITION BY A.TBL ORDER BY B.COLUMN_ID) AS RNUM

                        , A.RNO

                        , A.STMT_CNT

                        , A.TBL

                        , B.COLUMN_ID

                        , B.COLUMN_NAME

                        , B.DATA_TYPE

                        , CASE

                          WHEN DATA_TYPE IN ('CHAR','VARCHAR2')           THEN '''''''||REPLACE('||COLUMN_NAME||','''''''','''''''''''')||'''''' AS '||COLUMN_NAME

                          WHEN DATA_TYPE IN ('NUMBER','INTEGER','LONG')   THEN '''||NVL2('||COLUMN_NAME||',TO_CHAR('||COLUMN_NAME||'),''NULL'')||'' AS '||COLUMN_NAME

                          WHEN DATA_TYPE IN ('DATE')                      THEN 'TO_DATE(''''''||TO_CHAR('||COLUMN_NAME||',''YYYYMMDDHH24MISS'')||'''''',''''YYYYMMDDHH24MISS'''') AS '||COLUMN_NAME

                          END AS SEL_COL

                FROM    VTBL A 

                        , COLS B

                WHERE   A.TBL = B.TABLE_NAME

                )

        START WITH RNUM = 1

        CONNECT BY PRIOR RNUM = RNUM - 1 AND PRIOR TBL = TBL

        GROUP BY TBL

        ) A

ORDER BY RNO        

;