-- 創(chuàng)建用戶表空間SHOPPING
CREATE TABLESPACE "SHOPPING" 
    LOGGING 
    DATAFILE 'E:\ORACLE\ORADATA\TEST\SHOPPING.ora' SIZE 10M EXTENT
    MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO;

-- 創(chuàng)建用戶shopping
CREATE USER "SHOPPING"  PROFILE "DEFAULT" 
    IDENTIFIED BY "shopping" DEFAULT TABLESPACE "SHOPPING" 
    ACCOUNT UNLOCK;

-- 授予用戶權(quán)限
GRANT CREATE SESSION TO "SHOPPING";
GRANT "CONNECT" TO "SHOPPING";
GRANT "RESOURCE" TO "SHOPPING";

-- 使用shopping用戶連接數(shù)據(jù)庫(kù)
CONNECT shopping/shopping

-- 創(chuàng)建OJB使用的主鍵信息表
CREATE TABLE OJB_HL_SEQ (
    TABLENAME VARCHAR(175) NOT NULL,
    FIELDNAME VARCHAR(70) NOT NULL,
    MAX_KEY int NULL,
    GRAB_SIZE int NULL,
    VERSION int NULL,
    CONSTRAINT OJB_HL_SEQ_PK PRIMARY KEY (TABLENAME,FIELDNAME)
);

-- 創(chuàng)建商品分類(lèi)表
CREATE TABLE category
(
    id	        INTEGER,
    name	NVARCHAR2(32) NOT NULL,
    desccn	NVARCHAR2(256),
    CONSTRAINTS	pk_category_id PRIMARY KEY ( id )
);

-- 創(chuàng)建商品信息表
CREATE TABLE product
(
    id		INTEGER,
    name	NVARCHAR2(32) NOT NULL,
    categoryid	INTEGER NOT NULL,
    desccn	NVARCHAR2(256),
    price	NUMERIC(10, 2) NOT NULL,
    producer	NVARCHAR2(128),
    status	INTEGER,
    picture	BLOB 	DEFAULT EMPTY_BLOB(),
    CONSTRAINT pk_product_id PRIMARY KEY ( id ),
    CONSTRAINT fk_product_category FOREIGN KEY ( categoryid )
		REFERENCES category( id )
);

-- 創(chuàng)建用戶信息表
CREATE TABLE userinfo
(
    id		INTEGER,
    logname	VARCHAR(32) NOT NULL,
    password    VARCHAR(32) NOT NULL,
    name	NVARCHAR2(16) NOT NULL,
    idnumber	VARCHAR(32),
    postcode	VARCHAR(16),
    address	NVARCHAR2(128),
    email	VARCHAR(64),
    telp	VARCHAR(64),
    type	INTEGER
);

-- 訂單信息
CREATE TABLE orders
(
    id		INTEGER,
    orderdate	DATE	NOT NULL,
    userinfoid	INTEGER,
    name	NVARCHAR2(16) NOT NULL,
    postcode	VARCHAR(16),
    address	NVARCHAR2(128) NOT NULL,
    email	VARCHAR(64),
    telp	VARCHAR(64),
    totalfee	NUMERIC(10, 2),
    status	INTEGER,
    CONSTRAINT pk_orders_id PRIMARY KEY ( id ),
    CONSTRAINT fk_orders_userinfo FOREIGN KEY ( userinfoid )
		REFERENCES userinfo( id )
);

-- 訂單詳細(xì)信息
CREATE TABLE orderitem
(
    id		INTEGER,
    ordersid	INTEGER NOT NULL,
    productid   INTEGER NOT NULL,
    quantity 	INTEGER,
    price 	NUMERIC(10, 2),
    totalfee	NUMERIC(10, 2),
    CONSTRAINT pk_orderitem_id PRIMARY KEY ( id ),
    CONSTRAINT fk_orderitem_order FOREIGN KEY ( ordersid )
		REFERENCES orders( id ),
    CONSTRAINT fk_orderitem_product FOREIGN KEY ( productid )
		REFERENCES product( id )
);