행위

Erwin erd sql 생성스크립트

DB CAFE

----------------------------------------------------------------------------------------------------
-- DB Table DDL Script 샘플 
----------------------------------------------------------------------------------------------------

-- < %EntityName - %TableName >
%Decl(TAB_OWNER,scott.)
%Decl(APP_USER,scott_app.)
%Decl(TableSpace,TABLESPACE TS_scott_D01)
%Decl(IndexSpace,TABLESPACE TS_scott_I01)
%Decl(IsGenFK,No)
%Decl(IsGenUK,Yes)


-- DROP TABLE %:TAB_OWNER%TableName

CREATE TABLE %:TAB_OWNER%TableName
(
      %ForEachColumn(,"   , ")
    {%ColName   %ColumnDataType %If(%Or(%==(%ColName,"REG_DT"), %==(%ColName, "MOD_DT"))){DEFAULT SYSDATE} %If(%==(%Substr(%ColumnDefault,1,7),"Default")) {DEFAULT %DefaultValue(%ColumnDefault)} %ColumnNullOption
    }
) %:TableSpace 
;

ALTER TABLE %:TAB_OWNER%TableName ADD CONSTRAINT PK_%Substr(%TableName,4,100) PRIMARY KEY (
      %ForEachColumn(,"   , ")
    {%If(%ColIsPK){%ColName}
    }
) USING INDEX %:IndexSpace

;


%if(%==(%:IsGenUK,Yes)){
-- UK INDEX 생성 
%Decl(UK_SEQ,0)
%ForEachKey{
%if(%==(%Substr(%KeyType,1,2),"AK")){
-- UNIQUE INDEX : %KeyName
%=(UK_SEQ,%+(%:UK_SEQ,1))
CREATE UNIQUE INDEX INDEX %:TAB_OWNER%TableName_U%:UK_SEQ ON %:TAB_OWNER.%TableName
    ( %ForEachKeyMem(,", ") {%ColName}
    );
}
}
}

%If(%==(%:IsGenFK,Yes)){
-- FK CONSTRAINT 생성 
%Decl(FK_SEQ,0)
%ForEachChildRel() {
    %=(FK_SEQ, %+(%:FK_SEQ,1))
-- FK : %Child (%PhysRelName) --
ALTER TABLE %:TAB_OWNER%Child
    ADD CONSTRAINT %Child_F%:FK_SEQ
    FOREIGN KEY (%ChildFK(", ",,))
    REFERENCES %:TAB_OWNER%If(%==(%Substr(%Parent,1,6), _참조_)){%Substr(%Parent,7)}%Else{%Parent}(%ParentPK(", ",,))
;

}
}

-- <COMMENT>
COMMENT ON TABLE %:TAB_OWNER%TableName IS '%EntityName';

%ForEachColumn() {
COMMENT ON COLUMN %:TAB_OWNER%TableName.%ColName IS '%AttName';
}

-- GRANT & CREATE SYNONYM ------
GRANT SELECT, INSERT, UPDATE, DELETE ON %:TAB_OWNER%TableName TO %Substitute(%:APP_USER,.,);
CREATE OR REPLACE SYNONYM %:APP_USER%TableName FOR %:TAB_OWNER%TableName;
-- CREATE OR REPLACE PUBLIC SYNONYM %TableName FOR %:TAB_OWNER%TableName;
-- GRANT TO DEV USER 
GRANT SELECT, INSERT, UPDATE, DELETE ON %:TAB_OWNER%TableName TO scott_DEV;
CREATE OR REPLACE SYNONYM scott_dev.%TableName FOR %:TAB_OWNER%TableName;