Erwin erd sql 생성스크립트
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
----------------------------------------------------------------------------------------------------
-- 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;