08.sql

来自「JDBC数据库高级编程源代码」· SQL 代码 · 共 46 行

-- 创建用户表空间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 )
);