[ORACLE 4장] 테이블 생성 예제

[ORACLE] 테이블 생성 예제

--------------------------------------------------------------------------------

-- 테이블 : 통합코드 이력

-- 테이블ID : WEX001H

--------------------------------------------------------------------------------

CREATE TABLE WEX001H

(

    INTG_CD VARCHAR2(10) ,

    INTG_GRCD VARCHAR2(10) ,

    INTG_CDNM VARCHAR2(10) ,

    INTG_CD_DESC VARCHAR2(500) ,

    REG_DT DATE ,

    RGR_ID VARCHAR2(10) ,

    UPD_DT DATE ,

    UPDR_ID VARCHAR2(10)

)

;

COMMENT ON TABLE WEX001H IS '통합코드 이력';

COMMENT ON COLUMN WEX001H.INTG_CD IS '통합코드';

COMMENT ON COLUMN WEX001H.INTG_GRCD IS '통합그룹코드';

COMMENT ON COLUMN WEX001H.INTG_CDNM IS '통합코드명';

COMMENT ON COLUMN WEX001H.INTG_CD_DESC IS '통합코드설명';

COMMENT ON COLUMN WEX001H.REG_DT IS '등록일자';

COMMENT ON COLUMN WEX001H.RGR_ID IS '등록자ID';

COMMENT ON COLUMN WEX001H.UPD_DT IS '수정일자';

COMMENT ON COLUMN WEX001H.UPDR_ID IS '수정자ID';

--------------------------------------------------------------------------------

-- 테이블 : 통합그룹코드

-- 테이블ID : WEX002D

--------------------------------------------------------------------------------

CREATE TABLE WEX002D

(

    INTG_GRCD VARCHAR2(10) ,

    FNC_SNO VARCHAR2(10) NOT NULL ,

    INTG_GRP_CDNM VARCHAR2(10) ,

    INTG_GRCD_DESC VARCHAR2(500) ,

    UP_CD_EXST_YN VARCHAR2(1) DEFAULT 'N' ,

    FNC_ISU_NO NUMBER(10) DEFAULT 1 ,

    LOAN_AMT NUMBER(10) DEFAULT 0 NOT NULL ,

    EFVU_STDT DATE DEFAULT SYSDATE NOT NULL ,

    EFVU_ENDT DATE DEFAULT SYSDATE NOT NULL ,

    REG_DT DATE DEFAULT SYSDATE NOT NULL ,

    RGR_ID VARCHAR2(10) DEFAULT 'ADMIN' NOT NULL ,

    UPD_DT DATE DEFAULT SYSDATE NOT NULL ,

    UPDR_ID VARCHAR2(10) DEFAULT 'ADMIN' NOT NULL

)

;

CREATE UNIQUE INDEX PK_WEX002D ON WEX002D

 (INTG_GRCD,FNC_SNO)

;

ALTER TABLE WEX002D ADD (

 CONSTRAINT PK_WEX002D

 PRIMARY KEY (INTG_GRCD,FNC_SNO)

);

CREATE UNIQUE INDEX IX_WEX002D_N01 ON WEX002D

(FNC_SNO,FNC_ISU_NO)

;

CREATE INDEX IX_WEX002D_N02 ON WEX002D

(FNC_SNO,UP_CD_EXST_YN)

;

COMMENT ON TABLE WEX002D IS '통합그룹코드';

COMMENT ON COLUMN WEX002D.INTG_GRCD IS '통합그룹코드';

COMMENT ON COLUMN WEX002D.FNC_SNO IS '금융일련번호';

COMMENT ON COLUMN WEX002D.INTG_GRP_CDNM IS '통합그룹코드명';

COMMENT ON COLUMN WEX002D.INTG_GRCD_DESC IS '통합그룹코드설명';

COMMENT ON COLUMN WEX002D.UP_CD_EXST_YN IS '상위코드존재여부';

COMMENT ON COLUMN WEX002D.FNC_ISU_NO IS '금융발급번호';

COMMENT ON COLUMN WEX002D.LOAN_AMT IS '대출금액';

COMMENT ON COLUMN WEX002D.EFVU_STDT IS '유효값시작일자';

COMMENT ON COLUMN WEX002D.EFVU_ENDT IS '유효값종료일자';

COMMENT ON COLUMN WEX002D.REG_DT IS '등록일자';

COMMENT ON COLUMN WEX002D.RGR_ID IS '등록자ID';

COMMENT ON COLUMN WEX002D.UPD_DT IS '수정일자';

COMMENT ON COLUMN WEX002D.UPDR_ID IS '수정자ID';

--------------------------------------------------------------------------------

-- 테이블 : 통합코드

-- 테이블ID : WEX001M

--------------------------------------------------------------------------------

CREATE TABLE WEX001M

(

    INTG_CD VARCHAR2(10) ,

    INTG_GRCD VARCHAR2(10) ,

    FNC_SNO VARCHAR2(10) NOT NULL ,

    INTG_CDNM VARCHAR2(10) ,

    INTG_CD_DESC VARCHAR2(500) ,

    REG_DT DATE DEFAULT SYSDATE NOT NULL ,

    RGR_ID VARCHAR2(10) DEFAULT 'ADMIN' NOT NULL ,

    UPD_DT DATE DEFAULT SYSDATE NOT NULL ,

    UPDR_ID VARCHAR2(10) DEFAULT 'ADMIN' NOT NULL

)

;

CREATE UNIQUE INDEX PK_WEX001M ON WEX001M

 (INTG_CD,INTG_GRCD,FNC_SNO)

;

ALTER TABLE WEX001M ADD (

 CONSTRAINT PK_WEX001M

 PRIMARY KEY (INTG_CD,INTG_GRCD,FNC_SNO)

);

COMMENT ON TABLE WEX001M IS '통합코드';

COMMENT ON COLUMN WEX001M.INTG_CD IS '통합코드';

COMMENT ON COLUMN WEX001M.INTG_GRCD IS '통합그룹코드';

COMMENT ON COLUMN WEX001M.FNC_SNO IS '금융일련번호';

COMMENT ON COLUMN WEX001M.INTG_CDNM IS '통합코드명';

COMMENT ON COLUMN WEX001M.INTG_CD_DESC IS '통합코드설명';

COMMENT ON COLUMN WEX001M.REG_DT IS '등록일자';

COMMENT ON COLUMN WEX001M.RGR_ID IS '등록자ID';

COMMENT ON COLUMN WEX001M.UPD_DT IS '수정일자';

COMMENT ON COLUMN WEX001M.UPDR_ID IS '수정자ID';

--------------------------------------------------------------------------------

테이블 생성 3개

기본 테이블 : WEX001M

이력 테이블 : WEX001H

상세 테이블 : WEX002D