-
테이블정의서 HTML 변환과정ORACLE 2022. 10. 14. 11:23728x90반응형SMALL반응형
[오라클] 테이블정의서 HTML 변환과정
▼ 첨부파일 ▼
SET SERVEROUTPUT ON;
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';
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
JOIN USER_TAB_COMMENTS B ON 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>');
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>');
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', 'NVARCHAR2') 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
JOIN USER_COL_COMMENTS B ON A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME = B.COLUMN_NAME
LEFT JOIN (
SELECT 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 ON A.TABLE_NAME = C.TABLE_NAME AND A.COLUMN_NAME = C.COLUMN_NAME
WHERE A.COLUMN_NAME != 'HIW_SOLUTION_TPCODE'
AND A.TABLE_NAME = V_TABLE_NAME(I)
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;
/728x90728x90반응형LIST'ORACLE' 카테고리의 다른 글
오라클 db exp 백업 (0) 2023.03.29 [오라클]사용자 권한 추가 테이블스페이스 추가 (0) 2023.02.10