sqlplus에서 셀렉트 update문 가져 오기.

  셀렉트 업데이트문 갖고 오기.

 

WITH
TAB_COMMENTS_CAST AS (
        SELECT *
          FROM USER_TAB_COMMENTS
         WHERE TABLE_NAME ='MEMBER' --여기에 테이블 명을 입력하세요 접속한 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 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
  -------------
  --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

이 글은 스프링노트에서 작성되었습니다.