행위

컬럼정보조회

DB CAFE

Dbcafe (토론 | 기여)님의 2019년 12월 19일 (목) 12:29 판
thumb_up 추천메뉴 바로가기


DECLARE
  TYPE TYPE_VARCHAR2 IS TABLE OF VARCHAR2(4000);
  V_TABLE_NAME TYPE_VARCHAR2;
  V_TABLE_COMT TYPE_VARCHAR2;
  V_CNT          NUMBER;
  V_MAX_PX       NUMBER;
  V_SQL          VARCHAR2(4000);
  V_VERSION      VARCHAR2(100) ;
BEGIN

  V_VERSION := 'ESTDB_10.0.12';
  
  -- 1. 전체 테이블 정보 조회 
  SELECT A.TABLE_NAME
       , REGEXP_SUBSTR(B.COMMENTS,'[^|]+',1,1) AS CMT

    BULK COLLECT INTO V_TABLE_NAME
                    , V_TABLE_COMT

    FROM USER_TABLES A
       , USER_TAB_COMMENTS B
   WHERE B.TABLE_NAME = A.TABLE_NAME
   ORDER BY A.TABLE_NAME;

  DBMS_OUTPUT.PUT_LINE('<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">');
  DBMS_OUTPUT.PUT_LINE('<html>');
  DBMS_OUTPUT.PUT_LINE('<head>');
  DBMS_OUTPUT.PUT_LINE('    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />');  
  DBMS_OUTPUT.PUT_LINE('    <title>'||V_VERSION||'</title>');
  DBMS_OUTPUT.PUT_LINE('    <style type="text/css">');
  DBMS_OUTPUT.PUT_LINE('        .entity{');
  DBMS_OUTPUT.PUT_LINE('            border: solid 1px #7E2828;');      
  DBMS_OUTPUT.PUT_LINE('            margin-bottom: 20px;');
  DBMS_OUTPUT.PUT_LINE('            font-size:10pt;');
  DBMS_OUTPUT.PUT_LINE('            line-height:16pt;');
  DBMS_OUTPUT.PUT_LINE('        }');
  DBMS_OUTPUT.PUT_LINE('        .header{');
  DBMS_OUTPUT.PUT_LINE('            background-color: #7E2828;');
  DBMS_OUTPUT.PUT_LINE('            color: white;');
  DBMS_OUTPUT.PUT_LINE('        }');
  DBMS_OUTPUT.PUT_LINE('        .column-header{');
  DBMS_OUTPUT.PUT_LINE('            background-color: #E8E8E8;');
  DBMS_OUTPUT.PUT_LINE('        }');
  DBMS_OUTPUT.PUT_LINE('        td{');                    
  DBMS_OUTPUT.PUT_LINE('            padding-left: 5px;');
  DBMS_OUTPUT.PUT_LINE('            padding-right: 5px;');  
  DBMS_OUTPUT.PUT_LINE('        }');
  DBMS_OUTPUT.PUT_LINE('    </style>');
  DBMS_OUTPUT.PUT_LINE('</head>');
  DBMS_OUTPUT.PUT_LINE('<body>');
  DBMS_OUTPUT.PUT_LINE('<h2>'||V_VERSION||' </h2>');          

  -- 2.테이블 별 LOOP
  FOR I IN V_TABLE_NAME.FIRST..V_TABLE_NAME.LAST LOOP
    
    SELECT MAX(A.COLUMN_ID) AS CNT 
         , (MAX(LENGTH(COLUMN_NAME)) * 14) AS MAX_PX

      INTO V_CNT
         , V_MAX_PX

      FROM USER_TAB_COLS A
     WHERE A.TABLE_NAME = V_TABLE_NAME(I)
    ;
    
    DBMS_OUTPUT.PUT_LINE('<font size ="2"><table class="entity" cellpadding="0" cellspacing="0" width="100%" style="word-break:break-all;">');
    DBMS_OUTPUT.PUT_LINE('<tr>');
    DBMS_OUTPUT.PUT_LINE('    <td colspan="5" class="header">'||V_TABLE_NAME(I)||'('||V_TABLE_COMT(I)||')'||'</td>');
    DBMS_OUTPUT.PUT_LINE('</tr>');
    DBMS_OUTPUT.PUT_LINE('    <tr class="column-header">');
    DBMS_OUTPUT.PUT_LINE('        <td>논리명</td>');
    DBMS_OUTPUT.PUT_LINE('        <td>물리명</td>');
    DBMS_OUTPUT.PUT_LINE('        <td>데이터 타입</td>');
    DBMS_OUTPUT.PUT_LINE('        <td ALIGN="CENTER">Not Null</td>');             
    DBMS_OUTPUT.PUT_LINE('        <td>설명</td>');
    DBMS_OUTPUT.PUT_LINE('    </tr>');
   
    -- 3. 컬러 정보 
    FOR J IN 1..V_CNT LOOP
    
      SELECT '    <tr>'||CHR(10)||'        <td WIDTH="'||V_MAX_PX*1.5||'">'||A.LG||'</td>'||CHR(10)||'        <td WIDTH="'||V_MAX_PX||'">'||A.COLUMN_NAME||'</td>'||CHR(10)||'        <td WIDTH="120">'||A.DATA_TYPE||'</td>'||CHR(10)||'        <td WIDTH="100" ALIGN="CENTER">'||A.NOTNULL||'</td>'||CHR(10)||'        <td>'||A.COMMENTS||'</td>'||CHR(10)||'    </tr>'
        INTO   V_SQL
        FROM ( SELECT REGEXP_SUBSTR(B.COMMENTS, '[^|]+', 1, 1)||CASE WHEN C.COLUMN_NAME IS NOT NULL THEN '(PK)' END AS LG
                    , A.COLUMN_NAME
                    , A.DATA_TYPE || (CASE WHEN A.DATA_TYPE IN ('CHAR', 'VARCHAR2', 'NVARCHAR') THEN '(' || A.DATA_LENGTH || ')'
                                           WHEN A.DATA_TYPE = 'NUMBER' AND A.DATA_SCALE = 0 AND  A.DATA_PRECISION IS NOT NULL THEN '(' || A.DATA_PRECISION || ')'
                                           WHEN A.DATA_TYPE = 'NUMBER' AND A.DATA_SCALE <> 0 THEN '(' || A.DATA_PRECISION || ',' || A.DATA_SCALE || ')' 
                                       END ) AS DATA_TYPE
                    , DECODE(A.NULLABLE,  'Y', 'N',  'N', 'Y') AS NOTNULL
                    , B.COMMENTS
                 FROM USER_TAB_COLS A
                    , USER_COL_COMMENTS B
                    , ( SELECT C.INDEX_NAME, C.TABLE_NAME, C.COLUMN_NAME
                          FROM USER_IND_COLUMNS C
                         WHERE EXISTS ( SELECT 1
                                          FROM USER_CONSTRAINTS S
                                         WHERE S.CONSTRAINT_TYPE = 'P'
                                           AND S.TABLE_NAME = C.TABLE_NAME
                                           AND S.INDEX_NAME = C.INDEX_NAME
                                      )
                      ) C
                WHERE A.COLUMN_NAME != 'HIW_SOLUTION_TPCODE'
                  AND A.TABLE_NAME = V_TABLE_NAME(I)
                  AND B.TABLE_NAME = A.TABLE_NAME
                  AND B.COLUMN_NAME = A.COLUMN_NAME
                  AND C.TABLE_NAME(+) = A.TABLE_NAME
                  AND C.COLUMN_NAME(+) = A.COLUMN_NAME
                  AND A.COLUMN_ID = J
              ) A ;
      
      DBMS_OUTPUT.PUT_LINE(V_SQL);
      
      V_SQL := NULL;
      
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('</table>');
    
    V_CNT := 0;
  
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('</body>');
  DBMS_OUTPUT.PUT_LINE('</html>');

END;
/