"컬럼정보조회"의 두 판 사이의 차이
DB CAFE
1번째 줄: | 1번째 줄: | ||
+ | <syntaxhighlight lang="sql"> | ||
DECLARE | DECLARE | ||
126번째 줄: | 127번째 줄: | ||
END; | END; | ||
/ | / | ||
+ | </syntaxhighlight> |
2018년 8월 9일 (목) 14:13 판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
<syntaxhighlight lang="sql">
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; / </syntaxhighlight>