"메타웍스(MetaWork) 엔터티"의 두 판 사이의 차이

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;

"메타웍스(MetaWork) 엔터티"의 두 판 사이의 차이

DB CAFE

2020년 3월 11일 (수) 11:10 판

1 표준도메인[편집]

2 표준용어[편집]

@@ 1번째 줄: / 1번째 줄: @@
+== 표준도메인 ==
 DROP TABLE ERD_ADM.DM_STD_DOMAIN CASCADE CONSTRAINTS;
@@ 25번째 줄: / 26번째 줄: @@
 ;
-COMMENT ON TABLE ERD_ADM.DM_STD_DOMAIN IS '설명';
+COMMENT ON TABLE ERD_ADM.DM_STD_DOMAIN IS '표준도메인';
 COMMENT ON COLUMN ERD_ADM.DM_STD_DOMAIN.I_NAME IS '인포타입';
@@ 66번째 줄: / 67번째 줄: @@
 CREATE UNIQUE INDEX ERD_ADM.DM_STD_DOMAIN_PK ON ERD_ADM.DM_STD_DOMAIN
 (I_NAME)
-LOGGING
+;
-TABLESPACE USERS
-PCTFREE    10
-INITRANS   2
-MAXTRANS   255
-STORAGE    (
-            INITIAL          64K
-            NEXT             1M
-            MINEXTENTS       1
-            MAXEXTENTS       UNLIMITED
-            PCTINCREASE      0
-            BUFFER_POOL      DEFAULT
-           );
 CREATE UNIQUE INDEX ERD_ADM.DM_STD_DOMAIN_UK1 ON ERD_ADM.DM_STD_DOMAIN
 (DECODE("DFLT_YN","D_NAME",NULL))
-LOGGING
+;
-TABLESPACE TS_ERDADM_D01
-PCTFREE    10
-INITRANS   2
-MAXTRANS   255
-STORAGE    (
-            INITIAL          64K
-            NEXT             1M
-            MINEXTENTS       1
-            MAXEXTENTS       UNLIMITED
-            PCTINCREASE      0
-            BUFFER_POOL      DEFAULT
-           );
 CREATE OR REPLACE TRIGGER ERD_ADM.TR_DM_STD_DOMAIN
    AFTER UPDATE OR DELETE ON ERD_ADM.DM_STD_DOMAIN
@@ 130번째 줄: / 105번째 줄: @@
 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;