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

-- 創建用戶product
CREATE USER "PRODUCT"  PROFILE "DEFAULT" 
    IDENTIFIED BY "product" DEFAULT TABLESPACE "PRODUCT" 
    ACCOUNT UNLOCK;

-- 授予用戶權限
GRANT CREATE SESSION TO "PRODUCT";
GRANT "CONNECT" TO "PRODUCT";
GRANT "RESOURCE" TO "PRODUCT";

-- 使用PRODUCT用戶連接數據庫
CONNECT product/product

-- 創建主鍵信息表
CREATE TABLE tableseq
(
    tablename	VARCHAR(32),
    maxid	INTEGER,
    CONSTRAINTS pk_tableseq_tablename PRIMARY KEY ( tablename )	
);

-- 創建商品分類表
CREATE TABLE category
(
    categoryid	INTEGER,
    name	NVARCHAR2(32),
    CONSTRAINTS	pk_category_categoryid PRIMARY KEY ( categoryid )
);

-- 創建商品信息表
CREATE TABLE product
(
    productid	INTEGER,
    name	NVARCHAR2(32),
    manufacturer NVARCHAR2(64),
    description	NVARCHAR2(265),
    categoryid	INTEGER,
    CONSTRAINTS pk_product_productid PRIMARY KEY ( productid ),
    CONSTRAINTS fk_product_category FOREIGN KEY (categoryid) REFERENCES category( categoryid )
);