DDL

DDL

 

※ DDL은 오토커밋이다. (커밋을 날리지 않아도 됨)

————————————————————————————————————————

CHAR, VARCHAR2, CLOB(오라클10g) ===> String (getString())
NUMBER ===> int (getInt())
DATE,TIMESTAMP ==> Date (getDate())
BLOB,BFILE ===> InputStream (getInputStream())

————————————————————————————————————————

 

CREATE TABLE sawon_dept(
 deptno NUMBER(4),
 dname VARCHAR2(34) CONSTRAINT sd_nn_dname NOT NULL,
 loc VARCHAR2(100) CONSTRAINT sd_nn_loc NOT NULL,
 CONSTRAINT sd_pk_deptno PRIMARY KEY(deptno)
 );

CREATE TABLE sawon(
 empno NUMBER(4),
 ename VARCHAR2(34) CONSTRAINT sawon_nn_ename NOT NULL,
 job VARCHAR2(20) CONSTRAINT sawon_nn_job NOT NULL,
 mgr NUMBER(4),
 hiredate DATE CONSTRAINT sawon_nn_hiredate NOT NULL,
 sal NUMBER CONSTRAINT sawon_nn_sal NOT NULL,
 comm NUMBER,
 deptno NUMBER(2),
 CONSTRAINT sawon_pk_empno PRIMARY KEY(empno),
 CONSTRAINT sawon_ck_job CHECK(job IN(‘개발부’,’영업부’,’자재부’,’총무부’,’홍보부’)),
 CONSTRAINT sawon_fk_mgr FOREIGN KEY(mgr) REFERENCES sawon(empno),
 CONSTRAINT sawon_fk_deptno FOREIGN KEY(deptno) REFERENCES sawon_dept(deptno)
 );

————————————————————————————————————————

 

DROP TABLE reply;

DROP TABLE board;

CREATE TABLE board(
 no NUMBER,
 name VARCHAR2(34) CONSTRAINT board_nn_name NOT NULL,
 email VARCHAR2(50),
 subject VARCHAR2(1000) CONSTRAINT board_nn_sub NOT NULL,
 content CLOB  CONSTRAINT board_nn_cont NOT NULL,
 pwd VARCHAR2(10) CONSTRAINT board_nn_pwd NOT NULL,
 regdate DATE DEFAULT SYSDATE,
 hit NUMBER DEFAULT 0,
 CONSTRAINT board_pk_no PRIMARY KEY(no)
);

CREATE TABLE reply(
 no NUMBER,
 bno NUMBER,
 id VARCHAR2(20) CONSTRAINT reply_nn_id NOT NULL,
 name VARCHAR2(34) CONSTRAINT reply_nn_name NOT NULL,
 content VARCHAR2(4000) CONSTRAINT reply_nn_cont NOT NULL,
 regdate DATE DEFAULT SYSDATE,
 CONSTRAINT reply_pk_no PRIMARY KEY(no),
 CONSTRAINT reply_fk_bno FOREIGN KEY(bno) REFERENCES board(no)
);

————————————————————————————————————————

 

DROP TABLE 판매전표;
DROP TABLE 제품;
DROP TABLE 전표상세;

CREATE TABLE 제품(
  제품번호 VARCHAR2(12),
  제품명 VARCHAR2(50),
  제품단가 NUMBER CONSTRAINT 제품_ck_제품단가 CHECK(제품단가>0),
  CONSTRAINT 제품_pk_제품번호 PRIMARY KEY(제품번호),
  CONSTRAINT 제품_uk_제품명 UNIQUE(제품명)
);

CREATE TABLE 판매전표(
  전표번호 VARCHAR2(12),
  판매일자 DATE CONSTRAINT 판매전표_nn_판매일자 NOT NULL,
  고객명 VARCHAR2(34) CONSTRAINT 판매전표_nn_고객명 NOT NULL,
  총액 NUMBER CONSTRAINT 판매전표_ck_총액 CHECK(총액>0),
  CONSTRAINT 판매전표_pk_전표번호 PRIMARY KEY(전표번호)
);

CREATE TABLE 전표상세(
  전표번호 VARCHAR2(12),
  제품번호 VARCHAR2(12) CONSTRAINT 전표상세_nn_제품번호 NOT NULL,
  수량 NUMBER CONSTRAINT 전표상세_nn_수량 NOT NULL,
  단가 NUMBER CONSTRAINT 전표상세_nn_단가 NOT NULL,
  금액 NUMBER CONSTRAINT 전표상세_nn_금액 NOT NULL,
  CONSTRAINT 전표상세_pk_전표번호 PRIMARY KEY(전표번호),
  CONSTRAINT 전표상세_fk_전표번호 FOREIGN KEY(전표번호)
                     REFERENCES 판매전표(전표번호),
  CONSTRAINT 전표상세_fk_제품번호 FOREIGN KEY(제품번호)
                     REFERENCES 제품(제품번호),
  CONSTRAINT 전표상세_ck_금액 CHECK(금액>0)
);

—————————————————————