orders

ALTER TABLE PTC.ORDERS
 DROP PRIMARY KEY CASCADE;
DROP TABLE PTC.ORDERS CASCADE CONSTRAINTS;

CREATE TABLE PTC.ORDERS
(
  ORDERSID    VARCHAR2(10 BYTE),
  USERID      VARCHAR2(10 BYTE),
  ORDERDATE   DATE                              DEFAULT sysdate,
  PAYMODE     INTEGER                           DEFAULT 1,
  ISPAYED     INTEGER                           DEFAULT 0,
  TOTALPRICE  FLOAT(126)
)
TABLESPACE PDM_DATA_01
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          80K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;

COMMENT ON COLUMN PTC.ORDERS.PAYMODE IS '1:貨到付款,2:網上付款';

COMMENT ON COLUMN PTC.ORDERS.ISPAYED IS '1:已付,0:未付';

CREATE UNIQUE INDEX PTC.ORDERS_PK ON PTC.ORDERS
(ORDERSID)
LOGGING
TABLESPACE PDM_DATA_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          80K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE TRIGGER PTC.ORDERS_TRI
BEFORE INSERT
ON PTC.ORDERS
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
BEGIN

   SELECT ORDER_SEQ.NEXTVAL INTO :NEW.ORDERSID FROM dual;
END ORDERS_TRI;
/

ALTER TABLE PTC.ORDERS ADD (
  CONSTRAINT ORDERS_PK
 PRIMARY KEY
 (ORDERSID)
    USING INDEX
    TABLESPACE PDM_DATA_01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          80K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

ordersdetails

DROP TABLE PTC.ORDERSDETAILS CASCADE CONSTRAINTS;

CREATE TABLE PTC.ORDERSDETAILS
(
  ORDERSID  VARCHAR2(10 BYTE),
  GOODSID   VARCHAR2(20 BYTE),
  NUMS      INTEGER
)
TABLESPACE PDM_DATA_01
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          80K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;

ALTER TABLE PTC.ORDERSDETAILS ADD (
  FOREIGN KEY (ORDERSID)
 REFERENCES PTC.ORDERS (ORDERSID));