"메타웍스(MetaWork) 엔터티"의 두 판 사이의 차이
DB CAFE
잔글 (DBCAFE님이 Meta work 엔터티 문서를 메타웍스(MetaWork) 엔터티 문서로 이동했습니다) |
(→컬럼 물리/논리명,타입 표준 체크 뷰) |
||
(사용자 2명의 중간 판 9개는 보이지 않습니다) | |||
9번째 줄: | 9번째 줄: | ||
}} | }} | ||
== 표준도메인 == | == 표준도메인 == | ||
+ | <source lang=sql> | ||
DROP TABLE ERD_ADM.DM_STD_DOMAIN CASCADE CONSTRAINTS; | DROP TABLE ERD_ADM.DM_STD_DOMAIN CASCADE CONSTRAINTS; | ||
72번째 줄: | 73번째 줄: | ||
COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.CD_TYPE IS '코드타입(1:공통코드, 2:개별코드, 3:외부코드, 9:기타-관리안함)'; | COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.CD_TYPE IS '코드타입(1:공통코드, 2:개별코드, 3:외부코드, 9:기타-관리안함)'; | ||
+ | </source> | ||
− | + | <source lang=sql> | |
CREATE UNIQUE INDEX ERD_ADM.DM_STD_DOMAIN_PK ON ERD_ADM.DM_STD_DOMAIN | CREATE UNIQUE INDEX ERD_ADM.DM_STD_DOMAIN_PK ON ERD_ADM.DM_STD_DOMAIN | ||
(I_NAME) | (I_NAME) | ||
80번째 줄: | 82번째 줄: | ||
(DECODE("DFLT_YN","D_NAME",NULL)) | (DECODE("DFLT_YN","D_NAME",NULL)) | ||
; | ; | ||
+ | </source> | ||
+ | |||
+ | <source lang=sql> | ||
CREATE OR REPLACE TRIGGER ERD_ADM.TR_DM_STD_DOMAIN | CREATE OR REPLACE TRIGGER ERD_ADM.TR_DM_STD_DOMAIN | ||
AFTER UPDATE OR DELETE ON ERD_ADM.DM_STD_DOMAIN | AFTER UPDATE OR DELETE ON ERD_ADM.DM_STD_DOMAIN | ||
101번째 줄: | 106번째 줄: | ||
END; | END; | ||
/ | / | ||
− | + | </source> | |
− | + | <source lang=sql> | |
CREATE OR REPLACE SYNONYM ERWIN_DEV.DM_STD_DOMAIN FOR ERD_ADM.DM_STD_DOMAIN; | CREATE OR REPLACE SYNONYM ERWIN_DEV.DM_STD_DOMAIN FOR ERD_ADM.DM_STD_DOMAIN; | ||
− | + | </source> | |
− | + | <source lang=sql> | |
ALTER TABLE ERD_ADM.DM_STD_DOMAIN ADD ( | ALTER TABLE ERD_ADM.DM_STD_DOMAIN ADD ( | ||
CONSTRAINT DM_STD_DOMAIN_PK | CONSTRAINT DM_STD_DOMAIN_PK | ||
112번째 줄: | 117번째 줄: | ||
USING INDEX ERD_ADM.DM_STD_DOMAIN_PK | USING INDEX ERD_ADM.DM_STD_DOMAIN_PK | ||
ENABLE VALIDATE); | ENABLE VALIDATE); | ||
− | + | </source> | |
+ | <source lang=sql> | ||
GRANT SELECT ON ERD_ADM.DM_STD_DOMAIN TO ERWIN_DEV; | GRANT SELECT ON ERD_ADM.DM_STD_DOMAIN TO ERWIN_DEV; | ||
+ | </source> | ||
== 표준용어== | == 표준용어== | ||
+ | |||
+ | <source lang=sql> | ||
DROP TABLE ERD_ADM.DM_STD_TERM CASCADE CONSTRAINTS; | DROP TABLE ERD_ADM.DM_STD_TERM CASCADE CONSTRAINTS; | ||
148번째 줄: | 157번째 줄: | ||
COMMENT ON COLUMN ERD_ADM.DM_STD_TERM.MODDT IS '수정일시'; | COMMENT ON COLUMN ERD_ADM.DM_STD_TERM.MODDT IS '수정일시'; | ||
+ | </source> | ||
− | + | <source lang=sql> | |
CREATE UNIQUE INDEX ERD_ADM.DM_STD_TERM_PK ON ERD_ADM.DM_STD_TERM | CREATE UNIQUE INDEX ERD_ADM.DM_STD_TERM_PK ON ERD_ADM.DM_STD_TERM | ||
(TERM) | (TERM) | ||
157번째 줄: | 167번째 줄: | ||
(ETERM) | (ETERM) | ||
; | ; | ||
− | + | </source> | |
+ | <source lang=sql> | ||
CREATE OR REPLACE TRIGGER ERD_ADM.TR_DM_STD_TERM | CREATE OR REPLACE TRIGGER ERD_ADM.TR_DM_STD_TERM | ||
AFTER UPDATE OR DELETE ON ERD_ADM.DM_STD_TERM | AFTER UPDATE OR DELETE ON ERD_ADM.DM_STD_TERM | ||
177번째 줄: | 188번째 줄: | ||
END; | END; | ||
/ | / | ||
+ | </source> | ||
− | + | <source lang=sql> | |
CREATE OR REPLACE SYNONYM ERWIN_DEV.DM_STD_TERM FOR ERD_ADM.DM_STD_TERM; | CREATE OR REPLACE SYNONYM ERWIN_DEV.DM_STD_TERM FOR ERD_ADM.DM_STD_TERM; | ||
− | + | </source> | |
− | + | <source lang=sql> | |
ALTER TABLE ERD_ADM.DM_STD_TERM ADD ( | ALTER TABLE ERD_ADM.DM_STD_TERM ADD ( | ||
CONSTRAINT DM_STD_TERM_C1 | CONSTRAINT DM_STD_TERM_C1 | ||
191번째 줄: | 203번째 줄: | ||
USING INDEX ERD_ADM.DM_STD_TERM_PK | USING INDEX ERD_ADM.DM_STD_TERM_PK | ||
ENABLE VALIDATE); | ENABLE VALIDATE); | ||
− | + | </source> | |
+ | <source lang=sql> | ||
GRANT SELECT ON ERD_ADM.DM_STD_TERM TO ERWIN_DEV; | GRANT SELECT ON ERD_ADM.DM_STD_TERM TO ERWIN_DEV; | ||
+ | </source> | ||
== 표준단어 == | == 표준단어 == | ||
+ | <source lang=sql> | ||
DROP TABLE ERD_ADM.DM_STD_VOCA CASCADE CONSTRAINTS; | DROP TABLE ERD_ADM.DM_STD_VOCA CASCADE CONSTRAINTS; | ||
247번째 줄: | 262번째 줄: | ||
COMMENT ON COLUMN ERD_ADM.DM_STD_VOCA.MODDT IS '수정일시'; | COMMENT ON COLUMN ERD_ADM.DM_STD_VOCA.MODDT IS '수정일시'; | ||
− | + | </source> | |
− | + | <source lang=sql> | |
CREATE UNIQUE INDEX ERD_ADM.DM_STD_VOCA_PK ON ERD_ADM.DM_STD_VOCA | CREATE UNIQUE INDEX ERD_ADM.DM_STD_VOCA_PK ON ERD_ADM.DM_STD_VOCA | ||
(VOCA) | (VOCA) | ||
260번째 줄: | 275번째 줄: | ||
(EABB) | (EABB) | ||
; | ; | ||
+ | </source> | ||
+ | <source lang=sql> | ||
CREATE OR REPLACE TRIGGER ERD_ADM.TR_DM_STD_VOCA | CREATE OR REPLACE TRIGGER ERD_ADM.TR_DM_STD_VOCA | ||
AFTER UPDATE OR DELETE ON ERD_ADM.DM_STD_VOCA | AFTER UPDATE OR DELETE ON ERD_ADM.DM_STD_VOCA | ||
280번째 줄: | 297번째 줄: | ||
END; | END; | ||
/ | / | ||
− | + | </source> | |
− | + | <source lang=sql> | |
CREATE OR REPLACE SYNONYM ERWIN_DEV.DM_STD_VOCA FOR ERD_ADM.DM_STD_VOCA; | CREATE OR REPLACE SYNONYM ERWIN_DEV.DM_STD_VOCA FOR ERD_ADM.DM_STD_VOCA; | ||
+ | </source> | ||
− | + | <source lang=sql> | |
ALTER TABLE ERD_ADM.DM_STD_VOCA ADD ( | ALTER TABLE ERD_ADM.DM_STD_VOCA ADD ( | ||
CONSTRAINT DM_STD_VOCA_PK | CONSTRAINT DM_STD_VOCA_PK | ||
291번째 줄: | 309번째 줄: | ||
USING INDEX ERD_ADM.DM_STD_VOCA_PK | USING INDEX ERD_ADM.DM_STD_VOCA_PK | ||
ENABLE VALIDATE); | ENABLE VALIDATE); | ||
+ | </source> | ||
+ | <source lang=sql> | ||
GRANT SELECT ON ERD_ADM.DM_STD_VOCA TO ERWIN_DEV; | GRANT SELECT ON ERD_ADM.DM_STD_VOCA TO ERWIN_DEV; | ||
+ | </source> | ||
== 공통 코드 == | == 공통 코드 == | ||
− | + | <source lang=sql> | |
DROP TABLE ERD_ADM.TB_DM_CM_CD CASCADE CONSTRAINTS; | DROP TABLE ERD_ADM.TB_DM_CM_CD CASCADE CONSTRAINTS; | ||
340번째 줄: | 361번째 줄: | ||
COMMENT ON COLUMN ERD_ADM.TB_DM_CM_CD.ATT7 IS '부가속성7'; | COMMENT ON COLUMN ERD_ADM.TB_DM_CM_CD.ATT7 IS '부가속성7'; | ||
+ | </source> | ||
− | + | <source lang=sql> | |
CREATE UNIQUE INDEX ERD_ADM.PK_TB_DM_CM_CD ON ERD_ADM.TB_DM_CM_CD | CREATE UNIQUE INDEX ERD_ADM.PK_TB_DM_CM_CD ON ERD_ADM.TB_DM_CM_CD | ||
(GRP_CD, CM_CD) | (GRP_CD, CM_CD) | ||
; | ; | ||
+ | </source> | ||
+ | <source lang=sql> | ||
ALTER TABLE ERD_ADM.TB_DM_CM_CD ADD ( | ALTER TABLE ERD_ADM.TB_DM_CM_CD ADD ( | ||
CONSTRAINT PK_TB_DM_CM_CD | CONSTRAINT PK_TB_DM_CM_CD | ||
352번째 줄: | 376번째 줄: | ||
USING INDEX ERD_ADM.PK_TB_DM_CM_CD | USING INDEX ERD_ADM.PK_TB_DM_CM_CD | ||
ENABLE VALIDATE); | ENABLE VALIDATE); | ||
+ | </source> | ||
+ | |||
+ | = 메타웍스 연동 표준검사 = | ||
+ | |||
+ | == 테이블 논리/물리명표준체크 뷰 == | ||
+ | <source lang=sql> | ||
+ | CREATE OR REPLACE FORCE VIEW V_TABLE_META_CHECK | ||
+ | AS | ||
+ | WITH | ||
+ | temp | ||
+ | AS | ||
+ | (SELECT OWNER | ||
+ | , COMMENTS | ||
+ | , TABLE_NAME -- , SUBSTR (TABLE_NAME, 7, 30) SUB_TABLE_NAME | ||
+ | , REGEXP_REPLACE (SUBSTR (TABLE_NAME, 7, 30) | ||
+ | , '_[0-9]$' | ||
+ | , '') SUB_TABLE_NAME | ||
+ | FROM DBA_TAB_COMMENTS | ||
+ | WHERE OWNER IN ('SCOTT', 'CYKIM') AND TABLE_NAME LIKE 'TB%') | ||
+ | SELECT OWNER | ||
+ | , COMMENTS | ||
+ | , TABLE_NAME | ||
+ | , SUB_TABLE_NAME | ||
+ | , EABB | ||
+ | , VOCA | ||
+ | , COLUMN_VALUE | ||
+ | , STD_VOCA_TABLE_NAME | ||
+ | , SUBSTR (COMMENTS, 1, 3) || STD_VOCA_TABLE_NAME AS FULL_STD_VOCA_TABLE_NAME | ||
+ | FROM (SELECT A.OWNER | ||
+ | , A.COMMENTS | ||
+ | , A.TABLE_NAME | ||
+ | , A.SUB_TABLE_NAME | ||
+ | , B.EABB | ||
+ | , B.VOCA | ||
+ | , COLUMN_VALUE | ||
+ | , LISTAGG (VOCA, '') | ||
+ | WITHIN GROUP (ORDER BY COLUMN_VALUE) | ||
+ | OVER (PARTITION BY A.TABLE_NAME) AS STD_VOCA_TABLE_NAME | ||
+ | FROM (SELECT T.OWNER | ||
+ | , T.COMMENTS | ||
+ | , T.TABLE_NAME | ||
+ | , TRIM (REGEXP_SUBSTR (T.SUB_TABLE_NAME | ||
+ | , '[^_]+' | ||
+ | , 1 | ||
+ | , LEVELS.COLUMN_VALUE)) AS SUB_TABLE_NAME | ||
+ | , LEVELS.COLUMN_VALUE | ||
+ | FROM TEMP T | ||
+ | , TABLE ( | ||
+ | CAST ( | ||
+ | MULTISET ( | ||
+ | SELECT LEVEL | ||
+ | FROM DUAL | ||
+ | CONNECT BY LEVEL <= | ||
+ | LENGTH ( | ||
+ | REGEXP_REPLACE ( | ||
+ | T.SUB_TABLE_NAME | ||
+ | , '[^_]+')) | ||
+ | + 1) AS SYS.ODCINUMBERLIST)) | ||
+ | LEVELS) A | ||
+ | LEFT JOIN ERD_ADM.DM_STD_VOCA B | ||
+ | ON A.SUB_TABLE_NAME = B.EABB | ||
+ | WHERE B.V_TYPE <= 20) | ||
+ | WHERE COMMENTS <> SUBSTR (COMMENTS, 1, 3) || STD_VOCA_TABLE_NAME | ||
+ | AND NOT REGEXP_LIKE (TABLE_NAME, '_[1-9]$') | ||
+ | ORDER BY TABLE_NAME, COLUMN_VALUE; | ||
+ | </source> | ||
+ | |||
+ | == 컬럼 물리/논리명,타입 표준 체크 뷰 == | ||
+ | <source lang=sql> | ||
+ | CREATE OR REPLACE FORCE VIEW V_TAB_COL_CHECK | ||
+ | AS | ||
+ | SELECT A.OWNER | ||
+ | , A.TABLE_NAME | ||
+ | , D."담당자" TABLE_OWNER | ||
+ | , C.COMMENTS AS "TABLE_HAN_NAME" | ||
+ | , A.COLUMN_NAME | ||
+ | , A.DATA_TYPE AS "COL_TYPE" | ||
+ | , B.PDATATYPE AS "META_TYPE" | ||
+ | , A.COMMENTS AS "COL_COMMENT" | ||
+ | , B.TERMNAME AS "META_COMMENT" | ||
+ | , DECODE ( | ||
+ | A.DATA_TYPE | ||
+ | , B.PDATATYPE, '' | ||
+ | , 'ALTER TABLE xxx.' | ||
+ | || A.TABLE_NAME | ||
+ | || ' MODIFY (' | ||
+ | || A.COLUMN_NAME | ||
+ | || ' ' | ||
+ | || B.PDATATYPE | ||
+ | || ');') AS TYPE_SQL | ||
+ | , DECODE ( | ||
+ | A.COMMENTS | ||
+ | , B.TERMNAME, '' | ||
+ | , 'COMMENT ON COLUMN xxx.' | ||
+ | || A.TABLE_NAME | ||
+ | || '.' | ||
+ | || A.COLUMN_NAME | ||
+ | || ' IS ''' | ||
+ | || B.TERMNAME | ||
+ | || ''';') AS COMT_SQL | ||
+ | FROM ( SELECT OWNER | ||
+ | , TABLE_NAME | ||
+ | , COLUMN_NAME -- , PK | ||
+ | , CASE DATA_TYPE | ||
+ | WHEN 'DATE' | ||
+ | THEN | ||
+ | 'DATE' | ||
+ | WHEN 'TIMESTAMP(6)' | ||
+ | THEN | ||
+ | 'TIMESTAMP' | ||
+ | WHEN 'CLOB' | ||
+ | THEN | ||
+ | 'CLOB' | ||
+ | WHEN 'BLOB' | ||
+ | THEN | ||
+ | 'BLOB' | ||
+ | ELSE | ||
+ | DATA_TYPE | ||
+ | || '(' | ||
+ | || NVL (DATA_TYPE_2, DATA_LENGTH) | ||
+ | || ')' | ||
+ | END AS DATA_TYPE -- , NULLABLE | ||
+ | , COMMENTS | ||
+ | FROM (SELECT A.OWNER | ||
+ | , A.TABLE_NAME | ||
+ | , A.COLUMN_ID | ||
+ | , B.POSITION PK | ||
+ | , A.COLUMN_NAME | ||
+ | , A.DATA_TYPE | ||
+ | , A.DATA_PRECISION | ||
+ | || DECODE ( | ||
+ | DECODE (A.DATA_SCALE | ||
+ | , 0, NULL | ||
+ | , A.DATA_SCALE) | ||
+ | , NULL, NULL | ||
+ | , ',' || A.DATA_SCALE) DATA_TYPE_2 | ||
+ | , A.DATA_LENGTH | ||
+ | , A.DATA_PRECISION | ||
+ | , A.DATA_SCALE | ||
+ | , A.NULLABLE | ||
+ | , A.COMMENTS | ||
+ | , ROW_NUMBER () | ||
+ | OVER ( | ||
+ | PARTITION BY A.OWNER | ||
+ | , A.TABLE_NAME | ||
+ | , A.COLUMN_ID | ||
+ | ORDER BY A.COLUMN_ID, B.POSITION) RN | ||
+ | FROM (SELECT COL.OWNER | ||
+ | , COL.TABLE_NAME | ||
+ | , COL.COLUMN_ID | ||
+ | , -- COL.COLUMN_NAME, | ||
+ | REGEXP_REPLACE (COL.COLUMN_NAME | ||
+ | , '([[:digit:]]+)$') AS COLUMN_NAME | ||
+ | , COL.DATA_TYPE | ||
+ | , COL.DATA_LENGTH | ||
+ | , COL.DATA_PRECISION | ||
+ | , COL.DATA_SCALE | ||
+ | , COL.NULLABLE | ||
+ | , -- COM.COMMENTS | ||
+ | REGEXP_REPLACE (COMMENTS | ||
+ | , '([[:digit:]]+)$') AS COMMENTS | ||
+ | FROM DBA_TAB_COLUMNS COL, DBA_COL_COMMENTS COM | ||
+ | WHERE COL.COLUMN_NAME = COM.COLUMN_NAME | ||
+ | AND COL.OWNER = COM.OWNER | ||
+ | AND COL.TABLE_NAME = COM.TABLE_NAME | ||
+ | AND COM.OWNER IN ('SCOTT', 'CYKIM') -- AND COM.TABLE_NAME LIKE:IN_TABLE_NAME || '%' | ||
+ | AND COL.TABLE_NAME LIKE 'TB%') A | ||
+ | , DBA_CONS_COLUMNS B | ||
+ | WHERE B.OWNER(+) = A.TABLE_NAME | ||
+ | AND B.TABLE_NAME(+) = A.TABLE_NAME | ||
+ | AND B.COLUMN_NAME(+) = A.COLUMN_NAME) X | ||
+ | WHERE X.RN = 1 | ||
+ | ORDER BY X.TABLE_NAME, X.COLUMN_ID) A | ||
+ | LEFT OUTER JOIN ERD_ADM.V_STD_TERM B | ||
+ | ON A.COLUMN_NAME = B.TERMENGNAME | ||
+ | LEFT OUTER JOIN DBA_TAB_COMMENTS C | ||
+ | ON C.OWNER = A.OWNER AND C.TABLE_NAME = A.TABLE_NAME | ||
+ | LEFT OUTER JOIN MIGCOMMON.UV_ERD_TBL_MGMT_USER D | ||
+ | ON C.TABLE_NAME = D.TABLE_NAME | ||
+ | WHERE ( A.DATA_TYPE <> B.PDATATYPE | ||
+ | OR A.COMMENTS <> B.TERMNAME | ||
+ | OR B.PDATATYPE IS NULL); | ||
+ | </source> | ||
+ | |||
+ | == 도메인 검사 == | ||
+ | <source lang=sql> | ||
+ | SELECT A.OWNER,A.TABLE_NAME,A.COLUMN_NAME,A.DATA_TYPE,A.CHAR_LENGTH, B.COMMENTS | ||
+ | FROM DBA_TAB_COLS A | ||
+ | JOIN DBA_COL_COMMENTS B | ||
+ | ON A.OWNER = B.OWNER | ||
+ | AND A.TABLE_NAME = B.TABLE_NAME | ||
+ | AND A.COLUMN_NAME = B.COLUMN_NAME | ||
+ | WHERE 1=1 | ||
+ | AND A.COLUMN_NAME LIKE '%TRNM_DGR' | ||
+ | AND A.OWNER IN ('TBA','FDS') | ||
+ | AND A.TABLE_NAME LIKE 'TB%' | ||
+ | ; | ||
+ | </source> | ||
+ | [[category:oracle]] |
2021년 5월 10일 (월) 13:59 기준 최신판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 표준관리 S/W 메타웍스 엔터티[편집]
1.1 표준도메인[편집]
DROP TABLE ERD_ADM.DM_STD_DOMAIN CASCADE CONSTRAINTS;
CREATE TABLE ERD_ADM.DM_STD_DOMAIN
(
I_NAME VARCHAR2(100 BYTE) NOT NULL,
D_NAME VARCHAR2(100 BYTE) NOT NULL,
D_CLASS VARCHAR2(100 BYTE) NOT NULL,
DFLT_YN VARCHAR2(1 BYTE),
D_ENAME VARCHAR2(100 BYTE),
D_DTYPE VARCHAR2(50 BYTE),
D_DLEN VARCHAR2(10 BYTE),
D_DSCALE NUMBER(3),
D_LDTYPE_LEN VARCHAR2(100 BYTE),
D_PDTYPE_LEN VARCHAR2(100 BYTE),
D_VALID VARCHAR2(1000 BYTE),
D_DESC VARCHAR2(2000 BYTE) NOT NULL,
D_PARSED VARCHAR2(200 BYTE),
REGTR VARCHAR2(30 BYTE) NOT NULL,
REGDT DATE NOT NULL,
MODFR VARCHAR2(30 BYTE) NOT NULL,
MODDT DATE NOT NULL,
CD_TYPE VARCHAR2(1 BYTE),
CHK_RMK VARCHAR2(1000 BYTE)
)
;
COMMENT ON TABLE ERD_ADM.DM_STD_DOMAIN IS '표준도메인';
COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.I_NAME IS '인포타입';
COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.D_NAME IS '도메인';
COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.D_CLASS IS '도메인유형';
COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.DFLT_YN IS '디폴트여부';
COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.D_ENAME IS '도메인물리명';
COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.D_DTYPE IS '데이터타입';
COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.D_DLEN IS '데이터길이';
COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.D_DSCALE IS '소수점자리수';
COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.D_LDTYPE_LEN IS '논리데이터타입';
COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.D_PDTYPE_LEN IS '물리데이터타입';
COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.D_VALID IS '유효값';
COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.D_DESC IS '도메인정의';
COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.D_PARSED IS '단어분해';
COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.REGTR IS '등록자';
COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.REGDT IS '등록일시';
COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.MODFR IS '수정자';
COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.MODDT IS '수정일시';
COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.CD_TYPE IS '코드타입(1:공통코드, 2:개별코드, 3:외부코드, 9:기타-관리안함)';
CREATE UNIQUE INDEX ERD_ADM.DM_STD_DOMAIN_PK ON ERD_ADM.DM_STD_DOMAIN
(I_NAME)
;
CREATE UNIQUE INDEX ERD_ADM.DM_STD_DOMAIN_UK1 ON ERD_ADM.DM_STD_DOMAIN
(DECODE("DFLT_YN","D_NAME",NULL))
;
CREATE OR REPLACE TRIGGER ERD_ADM.TR_DM_STD_DOMAIN
AFTER UPDATE OR DELETE ON ERD_ADM.DM_STD_DOMAIN
FOR EACH ROW
DECLARE
V_DT DATE DEFAULT SYSDATE;
V_TYPE VARCHAR(1);
BEGIN
IF UPDATING THEN
V_TYPE := 'U';
ELSIF DELETING THEN
V_TYPE := 'D';
END IF;
INSERT INTO ERD_ADM.DM_STD_DOMAIN_HST
(H_DT, H_TYPE, I_NAME, D_NAME, D_CLASS, DFLT_YN, D_ENAME, D_DTYPE, D_DLEN, D_DSCALE
, D_LDTYPE_LEN, D_PDTYPE_LEN, D_VALID, D_DESC, D_PARSED, REGTR, REGDT, MODFR, MODDT)
VALUES(V_DT, V_TYPE, :OLD.I_NAME, :OLD.D_NAME, :OLD.D_CLASS, :OLD.DFLT_YN, :OLD.D_ENAME, :OLD.D_DTYPE, :OLD.D_DLEN, :OLD.D_DSCALE
, :OLD.D_LDTYPE_LEN, :OLD.D_PDTYPE_LEN, :OLD.D_VALID, :OLD.D_DESC, :OLD.D_PARSED, :OLD.REGTR, :OLD.REGDT, :OLD.MODFR, :OLD.MODDT)
;
END;
/
CREATE OR REPLACE SYNONYM ERWIN_DEV.DM_STD_DOMAIN FOR ERD_ADM.DM_STD_DOMAIN;
ALTER TABLE ERD_ADM.DM_STD_DOMAIN ADD (
CONSTRAINT DM_STD_DOMAIN_PK
PRIMARY KEY
(I_NAME)
USING INDEX ERD_ADM.DM_STD_DOMAIN_PK
ENABLE VALIDATE);
GRANT SELECT ON ERD_ADM.DM_STD_DOMAIN TO ERWIN_DEV;
1.2 표준용어[편집]
DROP TABLE ERD_ADM.DM_STD_TERM CASCADE CONSTRAINTS;
CREATE TABLE ERD_ADM.DM_STD_TERM
(
TERM VARCHAR2(100 BYTE) NOT NULL,
ETERM VARCHAR2(30 BYTE) NOT NULL,
I_NAME VARCHAR2(100 BYTE) NOT NULL,
PARSED_TERM VARCHAR2(200 BYTE),
REGTR VARCHAR2(30 BYTE) NOT NULL,
REGDT DATE NOT NULL,
MODFR VARCHAR2(30 BYTE) NOT NULL,
MODDT DATE NOT NULL
)
;
COMMENT ON TABLE ERD_ADM.DM_STD_TERM IS '표준용어';
COMMENT ON COLUMN ERD_ADM.DM_STD_TERM.TERM IS '용어';
COMMENT ON COLUMN ERD_ADM.DM_STD_TERM.ETERM IS '영문명';
COMMENT ON COLUMN ERD_ADM.DM_STD_TERM.I_NAME IS '인포타입';
COMMENT ON COLUMN ERD_ADM.DM_STD_TERM.PARSED_TERM IS '단어분해';
COMMENT ON COLUMN ERD_ADM.DM_STD_TERM.REGTR IS '등록자';
COMMENT ON COLUMN ERD_ADM.DM_STD_TERM.REGDT IS '등록일시';
COMMENT ON COLUMN ERD_ADM.DM_STD_TERM.MODFR IS '수정자';
COMMENT ON COLUMN ERD_ADM.DM_STD_TERM.MODDT IS '수정일시';
CREATE UNIQUE INDEX ERD_ADM.DM_STD_TERM_PK ON ERD_ADM.DM_STD_TERM
(TERM)
;
CREATE UNIQUE INDEX ERD_ADM.DM_STD_TERM_U1 ON ERD_ADM.DM_STD_TERM
(ETERM)
;
CREATE OR REPLACE TRIGGER ERD_ADM.TR_DM_STD_TERM
AFTER UPDATE OR DELETE ON ERD_ADM.DM_STD_TERM
FOR EACH ROW
DECLARE
V_DT DATE DEFAULT SYSDATE;
V_TYPE VARCHAR(1);
BEGIN
IF UPDATING THEN
V_TYPE := 'U';
ELSIF DELETING THEN
V_TYPE := 'D';
END IF;
INSERT INTO ERD_ADM.DM_STD_TERM_HST
(H_DT, H_TYPE, TERM, ETERM, I_NAME, PARSED_TERM, REGTR, REGDT, MODFR, MODDT)
VALUES(V_DT, V_TYPE, :OLD.TERM, :OLD.ETERM, :OLD.I_NAME, :OLD.PARSED_TERM
, :OLD.REGTR, :OLD.REGDT, :OLD.MODFR, :OLD.MODDT);
END;
/
CREATE OR REPLACE SYNONYM ERWIN_DEV.DM_STD_TERM FOR ERD_ADM.DM_STD_TERM;
ALTER TABLE ERD_ADM.DM_STD_TERM ADD (
CONSTRAINT DM_STD_TERM_C1
CHECK (TERM = TRANSLATE(TERM, '@_ ','@'))
ENABLE VALIDATE
, CONSTRAINT DM_STD_TERM_PK
PRIMARY KEY
(TERM)
USING INDEX ERD_ADM.DM_STD_TERM_PK
ENABLE VALIDATE);
GRANT SELECT ON ERD_ADM.DM_STD_TERM TO ERWIN_DEV;
1.3 표준단어[편집]
DROP TABLE ERD_ADM.DM_STD_VOCA CASCADE CONSTRAINTS;
CREATE TABLE ERD_ADM.DM_STD_VOCA
(
VOCA VARCHAR2(100 BYTE) NOT NULL,
EABB VARCHAR2(30 BYTE) NOT NULL,
ENAME VARCHAR2(100 BYTE) NOT NULL,
NOUSE VARCHAR2(1000 BYTE),
CLS_YN VARCHAR2(1 BYTE) NOT NULL,
SRC VARCHAR2(100 BYTE),
V_DESC VARCHAR2(4000 BYTE) NOT NULL,
V_TYPE NUMBER(2) NOT NULL,
BIZAREA VARCHAR2(100 BYTE),
V_NO NUMBER(5) NOT NULL,
E_NO NUMBER(5) NOT NULL,
REGTR VARCHAR2(30 BYTE) NOT NULL,
REGDT DATE NOT NULL,
MODFR VARCHAR2(30 BYTE) NOT NULL,
MODDT DATE DEFAULT SYSDATE NOT NULL
)
;
COMMENT ON TABLE ERD_ADM.DM_STD_VOCA IS '표준단어';
COMMENT ON COLUMN ERD_ADM.DM_STD_VOCA.VOCA IS '표준단어';
COMMENT ON COLUMN ERD_ADM.DM_STD_VOCA.EABB IS '영문약어';
COMMENT ON COLUMN ERD_ADM.DM_STD_VOCA.ENAME IS '영문명';
COMMENT ON COLUMN ERD_ADM.DM_STD_VOCA.NOUSE IS '유사어';
COMMENT ON COLUMN ERD_ADM.DM_STD_VOCA.CLS_YN IS '분류어여부';
COMMENT ON COLUMN ERD_ADM.DM_STD_VOCA.SRC IS '출처';
COMMENT ON COLUMN ERD_ADM.DM_STD_VOCA.V_DESC IS '단어정의';
COMMENT ON COLUMN ERD_ADM.DM_STD_VOCA.V_TYPE IS '단어구분(10:표준,11:복합어,40:금칙(등록),41:복합금칙(등록),49:금칙(자동생성), 50:복합금칙(삭제대상), 90:미사용,99:검토중)';
COMMENT ON COLUMN ERD_ADM.DM_STD_VOCA.BIZAREA IS '업무영역';
COMMENT ON COLUMN ERD_ADM.DM_STD_VOCA.V_NO IS '단어순번';
COMMENT ON COLUMN ERD_ADM.DM_STD_VOCA.E_NO IS '약어순번';
COMMENT ON COLUMN ERD_ADM.DM_STD_VOCA.REGTR IS '등록자';
COMMENT ON COLUMN ERD_ADM.DM_STD_VOCA.REGDT IS '등록일시';
COMMENT ON COLUMN ERD_ADM.DM_STD_VOCA.MODFR IS '수정자';
COMMENT ON COLUMN ERD_ADM.DM_STD_VOCA.MODDT IS '수정일시';
CREATE UNIQUE INDEX ERD_ADM.DM_STD_VOCA_PK ON ERD_ADM.DM_STD_VOCA
(VOCA)
;
CREATE UNIQUE INDEX ERD_ADM.DM_STD_VOCA_U01 ON ERD_ADM.DM_STD_VOCA
(CASE WHEN "V_TYPE"<20 THEN "ENAME" ELSE NULL END )
;
CREATE UNIQUE INDEX ERD_ADM.DM_STD_VOCA_UK01 ON ERD_ADM.DM_STD_VOCA
(EABB)
;
CREATE OR REPLACE TRIGGER ERD_ADM.TR_DM_STD_VOCA
AFTER UPDATE OR DELETE ON ERD_ADM.DM_STD_VOCA
FOR EACH ROW
DECLARE
V_DT DATE DEFAULT SYSDATE;
V_TYPE VARCHAR(1);
BEGIN
IF UPDATING THEN
V_TYPE := 'U';
ELSIF DELETING THEN
V_TYPE := 'D';
END IF;
INSERT INTO ERD_ADM.DM_STD_VOCA_HST
(H_DT, H_TYPE, VOCA, EABB, ENAME, NOUSE, CLS_YN, SRC, V_DESC, V_TYPE, BIZAREA, V_NO, E_NO, REGTR, REGDT, MODFR, MODDT)
VALUES(V_DT, V_TYPE, :OLD.VOCA, :OLD.EABB, :OLD.ENAME, :OLD.NOUSE, :OLD.CLS_YN, :OLD.SRC
, :OLD.V_DESC, :OLD.V_TYPE, :OLD.BIZAREA, :OLD.V_NO, :OLD.E_NO, :OLD.REGTR, :OLD.REGDT, :OLD.MODFR, :OLD.MODDT);
END;
/
CREATE OR REPLACE SYNONYM ERWIN_DEV.DM_STD_VOCA FOR ERD_ADM.DM_STD_VOCA;
ALTER TABLE ERD_ADM.DM_STD_VOCA ADD (
CONSTRAINT DM_STD_VOCA_PK
PRIMARY KEY
(VOCA)
USING INDEX ERD_ADM.DM_STD_VOCA_PK
ENABLE VALIDATE);
GRANT SELECT ON ERD_ADM.DM_STD_VOCA TO ERWIN_DEV;
1.4 공통 코드[편집]
DROP TABLE ERD_ADM.TB_DM_CM_CD CASCADE CONSTRAINTS;
CREATE TABLE ERD_ADM.TB_DM_CM_CD
(
GRP_CD VARCHAR2(30 BYTE) NOT NULL,
CM_CD VARCHAR2(30 BYTE) NOT NULL,
CD_NM VARCHAR2(200 BYTE) NOT NULL,
CD_DESC VARCHAR2(2000 BYTE),
USE_YN VARCHAR2(1 BYTE) NOT NULL,
ORD_NO NUMBER(3),
ATT1 VARCHAR2(100 BYTE),
ATT2 VARCHAR2(100 BYTE),
ATT3 VARCHAR2(100 BYTE),
ATT4 VARCHAR2(100 BYTE),
ATT5 VARCHAR2(100 BYTE),
ATT6 VARCHAR2(100 BYTE),
ATT7 VARCHAR2(100 BYTE)
)
;
COMMENT ON COLUMN ERD_ADM.TB_DM_CM_CD.GRP_CD IS '그룹코드';
COMMENT ON COLUMN ERD_ADM.TB_DM_CM_CD.CM_CD IS '공통코드';
COMMENT ON COLUMN ERD_ADM.TB_DM_CM_CD.CD_NM IS '공통코드명';
COMMENT ON COLUMN ERD_ADM.TB_DM_CM_CD.CD_DESC IS '설명';
COMMENT ON COLUMN ERD_ADM.TB_DM_CM_CD.USE_YN IS '사용여부';
COMMENT ON COLUMN ERD_ADM.TB_DM_CM_CD.ORD_NO IS '순번';
COMMENT ON COLUMN ERD_ADM.TB_DM_CM_CD.ATT1 IS '부가속성1';
COMMENT ON COLUMN ERD_ADM.TB_DM_CM_CD.ATT2 IS '부가속성2';
COMMENT ON COLUMN ERD_ADM.TB_DM_CM_CD.ATT3 IS '부가속성3';
COMMENT ON COLUMN ERD_ADM.TB_DM_CM_CD.ATT4 IS '부가속성4';
COMMENT ON COLUMN ERD_ADM.TB_DM_CM_CD.ATT5 IS '부가속성5';
COMMENT ON COLUMN ERD_ADM.TB_DM_CM_CD.ATT6 IS '부가속성6';
COMMENT ON COLUMN ERD_ADM.TB_DM_CM_CD.ATT7 IS '부가속성7';
CREATE UNIQUE INDEX ERD_ADM.PK_TB_DM_CM_CD ON ERD_ADM.TB_DM_CM_CD
(GRP_CD, CM_CD)
;
ALTER TABLE ERD_ADM.TB_DM_CM_CD ADD (
CONSTRAINT PK_TB_DM_CM_CD
PRIMARY KEY
(GRP_CD, CM_CD)
USING INDEX ERD_ADM.PK_TB_DM_CM_CD
ENABLE VALIDATE);
2 메타웍스 연동 표준검사[편집]
2.1 테이블 논리/물리명표준체크 뷰[편집]
CREATE OR REPLACE FORCE VIEW V_TABLE_META_CHECK
AS
WITH
temp
AS
(SELECT OWNER
, COMMENTS
, TABLE_NAME -- , SUBSTR (TABLE_NAME, 7, 30) SUB_TABLE_NAME
, REGEXP_REPLACE (SUBSTR (TABLE_NAME, 7, 30)
, '_[0-9]$'
, '') SUB_TABLE_NAME
FROM DBA_TAB_COMMENTS
WHERE OWNER IN ('SCOTT', 'CYKIM') AND TABLE_NAME LIKE 'TB%')
SELECT OWNER
, COMMENTS
, TABLE_NAME
, SUB_TABLE_NAME
, EABB
, VOCA
, COLUMN_VALUE
, STD_VOCA_TABLE_NAME
, SUBSTR (COMMENTS, 1, 3) || STD_VOCA_TABLE_NAME AS FULL_STD_VOCA_TABLE_NAME
FROM (SELECT A.OWNER
, A.COMMENTS
, A.TABLE_NAME
, A.SUB_TABLE_NAME
, B.EABB
, B.VOCA
, COLUMN_VALUE
, LISTAGG (VOCA, '')
WITHIN GROUP (ORDER BY COLUMN_VALUE)
OVER (PARTITION BY A.TABLE_NAME) AS STD_VOCA_TABLE_NAME
FROM (SELECT T.OWNER
, T.COMMENTS
, T.TABLE_NAME
, TRIM (REGEXP_SUBSTR (T.SUB_TABLE_NAME
, '[^_]+'
, 1
, LEVELS.COLUMN_VALUE)) AS SUB_TABLE_NAME
, LEVELS.COLUMN_VALUE
FROM TEMP T
, TABLE (
CAST (
MULTISET (
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <=
LENGTH (
REGEXP_REPLACE (
T.SUB_TABLE_NAME
, '[^_]+'))
+ 1) AS SYS.ODCINUMBERLIST))
LEVELS) A
LEFT JOIN ERD_ADM.DM_STD_VOCA B
ON A.SUB_TABLE_NAME = B.EABB
WHERE B.V_TYPE <= 20)
WHERE COMMENTS <> SUBSTR (COMMENTS, 1, 3) || STD_VOCA_TABLE_NAME
AND NOT REGEXP_LIKE (TABLE_NAME, '_[1-9]$')
ORDER BY TABLE_NAME, COLUMN_VALUE;
2.2 컬럼 물리/논리명,타입 표준 체크 뷰[편집]
CREATE OR REPLACE FORCE VIEW V_TAB_COL_CHECK
AS
SELECT A.OWNER
, A.TABLE_NAME
, D."담당자" TABLE_OWNER
, C.COMMENTS AS "TABLE_HAN_NAME"
, A.COLUMN_NAME
, A.DATA_TYPE AS "COL_TYPE"
, B.PDATATYPE AS "META_TYPE"
, A.COMMENTS AS "COL_COMMENT"
, B.TERMNAME AS "META_COMMENT"
, DECODE (
A.DATA_TYPE
, B.PDATATYPE, ''
, 'ALTER TABLE xxx.'
|| A.TABLE_NAME
|| ' MODIFY ('
|| A.COLUMN_NAME
|| ' '
|| B.PDATATYPE
|| ');') AS TYPE_SQL
, DECODE (
A.COMMENTS
, B.TERMNAME, ''
, 'COMMENT ON COLUMN xxx.'
|| A.TABLE_NAME
|| '.'
|| A.COLUMN_NAME
|| ' IS '''
|| B.TERMNAME
|| ''';') AS COMT_SQL
FROM ( SELECT OWNER
, TABLE_NAME
, COLUMN_NAME -- , PK
, CASE DATA_TYPE
WHEN 'DATE'
THEN
'DATE'
WHEN 'TIMESTAMP(6)'
THEN
'TIMESTAMP'
WHEN 'CLOB'
THEN
'CLOB'
WHEN 'BLOB'
THEN
'BLOB'
ELSE
DATA_TYPE
|| '('
|| NVL (DATA_TYPE_2, DATA_LENGTH)
|| ')'
END AS DATA_TYPE -- , NULLABLE
, COMMENTS
FROM (SELECT A.OWNER
, A.TABLE_NAME
, A.COLUMN_ID
, B.POSITION PK
, A.COLUMN_NAME
, A.DATA_TYPE
, A.DATA_PRECISION
|| DECODE (
DECODE (A.DATA_SCALE
, 0, NULL
, A.DATA_SCALE)
, NULL, NULL
, ',' || A.DATA_SCALE) DATA_TYPE_2
, A.DATA_LENGTH
, A.DATA_PRECISION
, A.DATA_SCALE
, A.NULLABLE
, A.COMMENTS
, ROW_NUMBER ()
OVER (
PARTITION BY A.OWNER
, A.TABLE_NAME
, A.COLUMN_ID
ORDER BY A.COLUMN_ID, B.POSITION) RN
FROM (SELECT COL.OWNER
, COL.TABLE_NAME
, COL.COLUMN_ID
, -- COL.COLUMN_NAME,
REGEXP_REPLACE (COL.COLUMN_NAME
, '([[:digit:]]+)$') AS COLUMN_NAME
, COL.DATA_TYPE
, COL.DATA_LENGTH
, COL.DATA_PRECISION
, COL.DATA_SCALE
, COL.NULLABLE
, -- COM.COMMENTS
REGEXP_REPLACE (COMMENTS
, '([[:digit:]]+)$') AS COMMENTS
FROM DBA_TAB_COLUMNS COL, DBA_COL_COMMENTS COM
WHERE COL.COLUMN_NAME = COM.COLUMN_NAME
AND COL.OWNER = COM.OWNER
AND COL.TABLE_NAME = COM.TABLE_NAME
AND COM.OWNER IN ('SCOTT', 'CYKIM') -- AND COM.TABLE_NAME LIKE:IN_TABLE_NAME || '%'
AND COL.TABLE_NAME LIKE 'TB%') A
, DBA_CONS_COLUMNS B
WHERE B.OWNER(+) = A.TABLE_NAME
AND B.TABLE_NAME(+) = A.TABLE_NAME
AND B.COLUMN_NAME(+) = A.COLUMN_NAME) X
WHERE X.RN = 1
ORDER BY X.TABLE_NAME, X.COLUMN_ID) A
LEFT OUTER JOIN ERD_ADM.V_STD_TERM B
ON A.COLUMN_NAME = B.TERMENGNAME
LEFT OUTER JOIN DBA_TAB_COMMENTS C
ON C.OWNER = A.OWNER AND C.TABLE_NAME = A.TABLE_NAME
LEFT OUTER JOIN MIGCOMMON.UV_ERD_TBL_MGMT_USER D
ON C.TABLE_NAME = D.TABLE_NAME
WHERE ( A.DATA_TYPE <> B.PDATATYPE
OR A.COMMENTS <> B.TERMNAME
OR B.PDATATYPE IS NULL);
2.3 도메인 검사[편집]
SELECT A.OWNER,A.TABLE_NAME,A.COLUMN_NAME,A.DATA_TYPE,A.CHAR_LENGTH, B.COMMENTS
FROM DBA_TAB_COLS A
JOIN DBA_COL_COMMENTS B
ON A.OWNER = B.OWNER
AND A.TABLE_NAME = B.TABLE_NAME
AND A.COLUMN_NAME = B.COLUMN_NAME
WHERE 1=1
AND A.COLUMN_NAME LIKE '%TRNM_DGR'
AND A.OWNER IN ('TBA','FDS')
AND A.TABLE_NAME LIKE 'TB%'
;