다른 명령
표준관리 S/W 메타웍스 엔터티
표준도메인
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;
표준용어
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;
표준단어
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;
공통 코드
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);
메타웍스 연동 표준검사
테이블 논리/물리명표준체크 뷰
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;
컬럼 물리/논리명,타입 표준 체크 뷰
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);
도메인 검사
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%' ;