JAVA와 놀자

WITH

TAB_COMMENTS_CAST AS (

        SELECT * 

          FROM USER_TAB_COMMENTS

         WHERE TABLE_NAME ='XXSB_DCT_SM_USER' --여기에 테이블 명을 입력하세요 접속한 USER 테이블만 가능

        ),

TAB_COLUMNS_CAST AS (

        SELECT A.COLUMN_NAME

             , A.COLUMN_ID

          FROM USER_TAB_COLUMNS     A

             , TAB_COMMENTS_CAST    B

         WHERE A.TABLE_NAME = B.TABLE_NAME

         ORDER BY A.COLUMN_ID

        ),

PK_COLUMNS_CAST AS (

        SELECT A.*

          FROM USER_IND_COLUMNS   A

             , USER_CONSTRAINTS   B

             , TAB_COMMENTS_CAST  C

         WHERE A.TABLE_NAME = B.TABLE_NAME

           AND A.INDEX_NAME  = B.CONSTRAINT_NAME

           AND B.CONSTRAINT_TYPE = 'P'

           AND A.TABLE_NAME = C.TABLE_NAME

         ORDER BY A.COLUMN_POSITION

        )

  -------------

  --SELECT 시작

  -------------

  --SELECT COMMENTS || ' SELECT' FROM TAB_COMMENTS_CAST UNION ALL

  SELECT DECODE(ROWNUM, 1,'SELECT ','     ,')|| DECODE(SUBSTR(COLUMN_NAME, LENGTH(COLUMN_NAME)-1,2),'YN','DECODE('||COLUMN_NAME||',''1'',''1'',''0'') AS '||COLUMN_NAME, COLUMN_NAME)

    FROM TAB_COLUMNS_CAST 

   WHERE COLUMN_NAME NOT IN('INSERT_ID','INSERT_IP','INSERT_PGM','INSERT_DATE','UPDATE_ID','UPDATE_IP','UPDATE_PGM','UPDATE_DATE')

  UNION ALL

  SELECT '  FROM '||TABLE_NAME  FROM TAB_COMMENTS_CAST

  UNION ALL

  SELECT DECODE(A.COLUMN_POSITION, 1,' WHERE ','   AND ') || A.COLUMN_NAME || LPAD(' = ',B.MAXLEN-LENGTH(A.COLUMN_NAME),' ') || '#'|| A.COLUMN_NAME ||'#' 

    FROM PK_COLUMNS_CAST A

       , (

            SELECT MAX(LENGTH(COLUMN_NAME))+3 AS MAXLEN FROM PK_COLUMNS_CAST

         ) B

  UNION ALL SELECT '' FROM DUAL UNION ALL SELECT '' FROM DUAL 

  -------------

  --INSERT 시작

  -------------

  --UNION ALL SELECT COMMENTS || ' INSERT' FROM TAB_COMMENTS_CAST

  UNION ALL

  SELECT 'INSERT INTO '||TABLE_NAME FROM TAB_COMMENTS_CAST

  UNION ALL

  SELECT '(' FROM DUAL

  UNION ALL

  SELECT DECODE(COLUMN_ID, 1,'    ',',')|| COLUMN_NAME

    FROM TAB_COLUMNS_CAST

  UNION ALL

  SELECT ')' FROM DUAL

  UNION ALL

  SELECT 'VALUES' FROM DUAL

  UNION ALL

  SELECT '(' FROM DUAL

  UNION ALL

  SELECT DECODE(COLUMN_ID, 1,'    ','  ,')|| CASE WHEN COLUMN_NAME IN('INSERT_DATE','UPDATE_DATE') THEN 'SYSDATE' ELSE DECODE(SUBSTR(COLUMN_NAME, LENGTH(COLUMN_NAME)-1,2),'YN','DECODE('||'#'||COLUMN_NAME||'#'||',''1'',''1'',''2'')', '#'||COLUMN_NAME||'#') END

    FROM TAB_COLUMNS_CAST

  UNION ALL

  SELECT ')' FROM DUAL

  UNION ALL SELECT '' FROM DUAL UNION ALL SELECT '' FROM DUAL 

  -------------

  --UPDATE 시작

  -------------

  --UNION ALL SELECT COMMENTS || ' UPDATE' FROM TAB_COMMENTS_CAST

  UNION ALL

  SELECT 'UPDATE '||TABLE_NAME FROM TAB_COMMENTS_CAST

  UNION ALL

  SELECT DECODE(ROWNUM, 1,'   SET ',',') || A.COLUMN_NAME || LPAD(' = ', B.MAXLEN-LENGTH(A.COLUMN_NAME),' ')

        || DECODE(A.COLUMN_NAME,'UPDATE_DATE','SYSDATE',DECODE(SUBSTR(A.COLUMN_NAME, LENGTH(A.COLUMN_NAME)-1,2),'YN','DECODE(#'||A.COLUMN_NAME||'#,''1'',''1'',''2'')', '#'||A.COLUMN_NAME||'#') )

    FROM ( 

            SELECT A.COLUMN_NAME

              FROM TAB_COLUMNS_CAST  A

                 , PK_COLUMNS_CAST      B

             WHERE A.COLUMN_NAME = B.COLUMN_NAME(+)

               AND B.COLUMN_NAME IS NULL

               AND A.COLUMN_NAME NOT IN ('INSERT_ID','INSERT_IP','INSERT_PGM','INSERT_DATE')

             ORDER BY A.COLUMN_ID

          ) A

        ,(

            SELECT MAX(LENGTH(A.COLUMN_NAME))+3 AS MAXLEN

              FROM TAB_COLUMNS_CAST  A

                 , PK_COLUMNS_CAST   B

             WHERE A.COLUMN_NAME = B.COLUMN_NAME(+)

               AND B.COLUMN_NAME IS NULL

               AND A.COLUMN_NAME NOT IN ('INSERT_ID','INSERT_IP','INSERT_PGM','INSERT_DATE')

         ) B

  UNION ALL

  SELECT DECODE(A.COLUMN_POSITION, 1,' WHERE ','   AND ') || A.COLUMN_NAME || LPAD(' = ', B.MAXLEN-LENGTH(A.COLUMN_NAME),' ') || '#'|| A.COLUMN_NAME ||'#' 

    FROM PK_COLUMNS_CAST A

       , (

            SELECT MAX(LENGTH(COLUMN_NAME))+3 AS MAXLEN FROM PK_COLUMNS_CAST

         ) B

  UNION ALL SELECT '' FROM DUAL UNION ALL SELECT '' FROM DUAL 

  -------------

  --DELETE 시작

  -------------

  --UNION ALL SELECT COMMENTS || ' DELETE' FROM TAB_COMMENTS_CAST

  UNION ALL

  SELECT 'DELETE FROM '||TABLE_NAME FROM TAB_COMMENTS_CAST

  UNION ALL

  SELECT DECODE(A.COLUMN_POSITION, 1,' WHERE ','   AND ') || A.COLUMN_NAME || LPAD(' = ',B.MAXLEN-LENGTH(A.COLUMN_NAME),' ') || '#'|| A.COLUMN_NAME ||'#' 

    FROM PK_COLUMNS_CAST A

       , (

            SELECT MAX(LENGTH(COLUMN_NAME))+3 AS MAXLEN FROM PK_COLUMNS_CAST

         ) B