1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
|
CREATE TABLE QTE (
ID NUMBER,
ID_OPERATEUR CHAR(7),
ID_ACTION CHAR(13),
DOCUMENT VARCHAR2(10),
SS_DOCUMENT VARCHAR2(10),
CHAPITRE VARCHAR2(150),
JOUR DATE DEFAULT TRUNC(SYSDATE) NOT NULL,
HEURE CHAR(6) DEFAULT TO_CHAR(SYSDATE,'HH24MISS') NOT NULL,
QUANTITE NUMBER(6,0),
TRT NUMBER(4,0),
ID_TYPE_TRT_QTE CHAR(2) DEFAULT '0',
ID_TYPE_TRT_PTG CHAR(2) DEFAULT 'NT',
LIB_ERR VARCHAR2(200)
) STORAGE(INITIAL 60M) PCTFREE 10 TABLESPACE GEO;
CREATE SEQUENCE SEQ_QTE_ID INCREMENT BY 1 START WITH 1;
CREATE OR REPLACE TRIGGER TRIG_QTE_ID BEFORE INSERT ON QTE FOR EACH ROW BEGIN SELECT SEQ_QTE_ID.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/
ALTER TABLE QTE ADD CONSTRAINT PK_QTE_ID PRIMARY KEY(ID) USING INDEX PCTFREE 10 STORAGE(INITIAL 9M) TABLESPACE INDEX;
ALTER TABLE QTE ADD CONSTRAINT FK_QTE_TYPE_TRT_PTG FOREIGN KEY (ID_TYPE_TRT_PTG) REFERENCES TYPE_TRT_PTG (ID_TYPE_TRT_PTG);
ALTER TABLE QTE ADD CONSTRAINT FK_QTE_TYPE_TRT_QTE FOREIGN KEY (ID_TYPE_TRT_QTE) REFERENCES TYPE_TRT_QTE (ID_TYPE_TRT_QTE);
CREATE INDEX QTE ON QTE(ID_OPERATEUR,ID_ACTION,DOCUMENT,SS_DOCUMENT,CHAPITRE,JOUR) STORAGE(INITIAL 70M) TABLESPACE INDEX;
CREATE INDEX QTE_QTE ON QTE(JOUR,TRT,ID_ACTION) STORAGE(INITIAL 42M) TABLESPACE INDEX; |
Partager