"메타웍스(MetaWork) 엔터티"의 두 판 사이의 차이
DB CAFE
184번째 줄: | 184번째 줄: | ||
GRANT SELECT ON ERD_ADM.DM_STD_TERM TO ERWIN_DEV; | 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; |
2020년 3월 11일 (수) 11:11 판
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
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;
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;
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;