행위

컬럼정보조회

DB CAFE

DBCAFE (토론 | 기여)님의 2018년 8월 9일 (목) 14:10 판
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('

'||V_VERSION||'

'); 

 -- 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('

'); DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE(' '); DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE(' '); DBMS_OUTPUT.PUT_LINE(' '); DBMS_OUTPUT.PUT_LINE(' '); DBMS_OUTPUT.PUT_LINE(' '); DBMS_OUTPUT.PUT_LINE(' '); DBMS_OUTPUT.PUT_LINE(' '); DBMS_OUTPUT.PUT_LINE(' '); -- 3. 컬러 정보 FOR J IN 1..V_CNT LOOP SELECT ' '||CHR(10)||' '||CHR(10)||' '||CHR(10)||' '||CHR(10)||' '||CHR(10)||' '||CHR(10)||' ' 
       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('
'||V_TABLE_NAME(I)||'('||V_TABLE_COMT(I)||')'||'
논리명물리명데이터 타입Not Null설명
'||A.LG||''||A.COLUMN_NAME||''||A.DATA_TYPE||''||A.NOTNULL||''||A.COMMENTS||'

'); 

   V_CNT := 0;
 
 END LOOP;
 DBMS_OUTPUT.PUT_LINE('</body>');
 DBMS_OUTPUT.PUT_LINE('</html>');

END; /

원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=컬럼정보조회&oldid=62"