[카테고리:] oracle

SELECT SUBSTR(a.TABLE_NAME,0,2) AS "업무구분"
     , a.TABLE_NAME AS "테이블명"
     , b.COMMENTS AS "테이블한글명"
     , a.COLUMN_ID AS "컬럼순번"
     , a.COLUMN_NAME AS "컬럼명"
     , c.COMMENTS AS "컬럼한글명"
     , a.DATA_TYPE AS "타입"
     , CASE WHEN NVL(a.DATA_SCALE,0) = 0 THEN TO_CHAR(a.DATA_LENGTH)
            ELSE a.DATA_LENGTH||'('||a.DATA_SCALE||')'
       END AS "길이"
     , CASE WHEN d.COLUMN_NAME IS NOT NULL THEN TO_CHAR(d.KEY_SEQ)
            ELSE ''
       END AS "KEY"
     , CASE WHEN a.nullable = 'N' THEN 'Y'
            ELSE ''
       END AS "NOT NULL"
     , a.DATA_DEFAULT AS "초기값"
  FROM ALL_TAB_COLUMNS a
 INNER JOIN ALL_TAB_COMMENTS b
    ON a.OWNER = b.OWNER
   AND a.TABLE_NAME = b.TABLE_NAME
  LEFT OUTER JOIN ALL_COL_COMMENTS c
    ON a.OWNER = c.OWNER
   AND a.TABLE_NAME = c.TABLE_NAME
   AND a.COLUMN_NAME = c.COLUMN_NAME
  LEFT OUTER JOIN (
       SELECT ia.OWNER
            , ia.TABLE_NAME
            , ib.COLUMN_NAME
            , ib.POSITION AS KEY_SEQ
         FROM ALL_CONSTRAINTS ia
        INNER JOIN ALL_CONS_COLUMNS ib
           ON ia.OWNER  = ib.OWNER
          AND ia.TABLE_NAME = ib.TABLE_NAME
          AND ia.CONSTRAINT_NAME = ib.CONSTRAINT_NAME
          AND ia.CONSTRAINT_TYPE = 'P'
       ) d
    ON a.OWNER = d.OWNER
   AND a.TABLE_NAME = d.TABLE_NAME
   AND a.COLUMN_NAME = d.COLUMN_NAME
 WHERE a.OWNER = 'SEMA_ERP' /* bx-erp : ERP, semis : SEMA_OWN  */
   AND a.TABLE_NAME LIKE '%_HIST%' /*조회할 Table명*/
--   AND a.TABLE_NAME = 'AM_CARDCONFMDTALSRECPT' /*조회할 Table명*/
--   AND b.COMMENTS LIKE '%원천징수%' /*조회할 Table명*/
--   AND a.TABLE_NAME  = 'AM_SLIP_WRT_M'
--   AND a.COLUMN_NAME LIKE '%ACNUT_NM_DIV_CD%'
--    AND c.COMMENTS LIKE '%통화%코드%'
--   AND a.column_name NOT LIKE '%#_%'ESCAPE'#'
-- ORDER BY a.TABLE_NAME
--     , a.COLUMN_ID
ORDER BY a.TABLE_NAME
    , a.COLUMN_ID
--    , a.COLUMN_NAME 
;

WITH tempTbl AS (
    SELECT a.COLUMN_ID AS colId 
         , a.COLUMN_NAME AS colName
         , LOWER(SUBSTR(REPLACE(INITCAP(a.COLUMN_NAME),'_'),1,1))||SUBSTR(REPLACE(INITCAP(a.COLUMN_NAME),'_'),2,LENGTH(a.COLUMN_NAME)-1) AS camel 
         , ('/*'||c.COMMENTS || ','|| a.DATA_TYPE ||'('||a.DATA_LENGTH ||')'||'*/') AS cmt
         , ('/*'||c.COMMENTS ||'*/') AS cmt2
         , c.COMMENTS AS orgCmt
         , a.DATA_TYPE AS dataType
         , a.DATA_LENGTH AS dataLen
      FROM ALL_TAB_COLUMNS a
      LEFT OUTER JOIN ALL_COL_COMMENTS c
        ON a.OWNER = c.OWNER
       AND a.TABLE_NAME = c.TABLE_NAME
       AND a.COLUMN_NAME = c.COLUMN_NAME
     WHERE a.OWNER = 'SEMA_ERP'
       AND a.TABLE_NAME IN ('AM_CMMN_COSTPT_D') /*조회할 Table명*/
--       AND a.COLUMN_NAME IN ('COMPANY_CODE','BANK_NO_SEQ','BANK_NO','BANK_DIV','CUST_CODE','DEPOSIT_OWNER','USE_YN','DEPT_CODE','APL_STR_DTE'
--                            ,'APL_END_DTE','FUND_CHK_YN','FUND_CHK_NOTE','FUND_CHK_YN_B','BANK_RES_CODE','BANK_RES_DESC','INPUT_DATE','INPUT_DUTY_ID'
--                            ,'CHG_DATE','CHG_DUTY_ID'
--       )
)
SELECT MAX(aa.ommType) AS ommType
     , MAX(aa.colId) AS colId
     , aa.ommName
     , MAX(aa.ommDescription) AS ommDescription
     , MAX(aa.ommLength) AS ommLength
     , (MAX(aa.ommType)||' '||aa.ommName||'<length='||MAX(aa.ommLength)||' description="'||MAX(aa.ommDescription)||'">;') AS ommGenStr
     , MAX(aa.dmlStr) AS dmlStr
     , MAX(aa.dmlStr2) AS dmlStr2
  FROM (
        SELECT CASE WHEN a.dataType IN ('VARCHAR2') THEN 'String' 
                    WHEN a.dataType IN ('DATE') THEN 'Date'
                    WHEN a.dataType IN ('NUMBER') AND a.dataLen < 10 THEN 'Integer'
                    WHEN a.dataType IN ('NUMBER') AND a.dataLen >= 10 THEN 'BigDecimal'
               END ommType
             , a.camel AS ommName
             , a.orgCmt AS ommDescription
             , a.dataLen AS ommLength
             , a.colId 
             , (', '||RPAD(a.colName, b.maxLenCol, ' ') ||' AS '||RPAD(a.camel, b.maxLenCmt,' ')||' '||a.cmt) AS dmlStr
             , (', '||RPAD(a.colName, b.maxLenCol, ' ') ||' '||a.cmt2) AS dmlStr2
          FROM tempTbl a
         OUTER APPLY( SELECT MAX(LENGTHB(colName)) AS maxLenCol
                           , MAX(LENGTHB(camel)) AS maxLenCmt 
                        FROM tempTbl
                    ) b  
       ) aa
 GROUP BY aa.ommName
-- ORDER BY aa.ommName
 ORDER BY MAX(aa.colId)
-- ORDER BY MAX(aa.ommName)