petstore.sql.txt

SunJ2EE宠物店(含例子和参考书）经典购物例子

-- 连接系统帐号
conn sys/change_on_install;

-- Create the user 
create user petstore
  identified by petstore
  default tablespace SYSTEM
  temporary tablespace TEMP;
-- Grant/Revoke role privileges 
grant connect to petstore;
grant resource to petstore;

--连接业已创建的新用户
conn petstore/petstore;



-- 创建各表


drop sequence linenum;
drop sequence ordernum;

drop index productCat;
drop index productName;
drop index itemProd;

drop table lineitem;
drop table orderstatus;
drop table orders;
drop table bannerdata;
drop table profile;
drop table signon;
drop table inventory;
drop table item;
drop table product;
drop table account;
drop table category;
drop table supplier;
drop table sequence;

create table supplier (
    suppid int not null,
    name varchar(80) null,
    status varchar(2) not null,
    addr1 varchar(80) null,
    addr2 varchar2(80) null,
    city varchar2(80) null,
    state varchar2(80) null,
    zip varchar2(5) null,
    phone varchar2(80) null,
    constraint pk_supplier primary key (suppid)
);

grant all on supplier to public;

create table signon (
    username varchar2(25) not null,
    password varchar2(25)  not null,
    constraint pk_signon primary key (username)
);

grant all on signon to public;

create table account (
    userid varchar2(80) not null,
    email varchar2(80) not null,
    name varchar2(80) not null,
    status varchar2(2)  null,
    addr1 varchar2(80) not null,
    addr2 varchar2(40) null,
    city varchar2(80) not  null,
    state varchar2(80) not null,
    zip varchar2(20) not null,
    country varchar2(20) not null,
    phone varchar2(80) not null,
    constraint pk_account primary key (userid)
);

grant all on account to public;

create table profile (
    userid varchar2(80) not null,
    langpref varchar2(80) not null,
    favcategory varchar2(30),
    mylistopt int,
    banneropt int,
    constraint pk_profile primary key (userid)
);

grant all on profile to public;


--create table bannerdata (
--    favcategory varchar2(80) not null,
--    bannername varchar2(255)  null,
--    constraint pk_bannerdata primary key (favcategory)
--);

--grant all on bannerdata to public;

create table orders (
      orderid int not null,
      userid varchar2(80) not null,
      orderdate date not null,
      shipaddr1 varchar2(80) not null,
      shipaddr2 varchar2(80) null,
      shipcity varchar2(80) not null,
      shipstate varchar2(80) not null,
      shipzip varchar2(20) not null,
      shipcountry varchar2(20) not null,
      billaddr1 varchar2(80) not null,
      billaddr2 varchar2(80)  null,
      billcity varchar2(80) not null,
      billstate varchar2(80) not null,
      billzip varchar2(20) not null,
      billcountry varchar2(20) not null,
      courier varchar2(80) not null,
      totalprice number(10,2) not null,
      billtoname varchar2(80) not null,
      shiptoname varchar2(80) not null,
      creditcard varchar2(80) not null,
      exprdate varchar2(7) not null,
      cardtype varchar2(80) not null,
      locale varchar2(80) not null,
      constraint pk_orders primary key (orderid),
      constraint fk_orders_1 foreign key (userid)
	references account (userid)
);

create sequence ordernum increment by 1 cache 10000;

grant all on orders to public; 	

create sequence linenum increment by 1 cache 10000;

create table orderstatus (
      orderid int not null,
      linenum int not null,
      timestamp date not null,
      status varchar2(2) not null,
      constraint pk_orderstatus primary key (orderid, linenum),
      constraint fk_orderstatus_1 foreign key (orderid)
	references orders (orderid)
);

grant all on orderstatus to public;


create table category (
	catid varchar2(10) not null,
	name varchar2(80) null,
	descn varchar2(255) null,
	constraint pk_category primary key (catid)
);

grant all on category to public;

create table product (
    productid varchar2(10) not null,
    category varchar2(10) not null,
    name varchar2(80) null,
    descn varchar2(255) null,
    constraint pk_product primary key (productid),
        constraint fk_product_1 foreign key (category)
        references category (catid)
);

grant all on product to public;
create index productCat on product (category);
create index productName on product (name);

create table item (
    itemid varchar2(10) not null,
    productid varchar2(10) not null,
    listprice decimal(10,2) null,
    unitcost decimal(10,2) null,
    supplier int null,
    status varchar2(2) null,
    attr1 varchar2(80) null,
    attr2 varchar2(80) null,
    attr3 varchar2(80) null,
    attr4 varchar2(80) null,
    attr5 varchar2(80) null,
    constraint pk_item primary key (itemid),
        constraint fk_item_1 foreign key (productid)
        references product (productid),
        constraint fk_item_2 foreign key (supplier)
        references supplier (suppid)
);

grant all on item to public;
create index itemProd on item (productid);

create table inventory (
    itemid varchar2(10) not null,
    qty int not null,
    constraint pk_inventory primary key (itemid)
);

grant all on inventory to public;

create table lineitem (
      orderid int not null,
      linenum int not null,
      itemid varchar2(10) not null,
      quantity int not null,
      unitprice number(10,2) not null,
      constraint pk_lineitem primary key (orderid, linenum),
      constraint fk_lineitem_1 foreign key (orderid)
	references orders (orderid)
);

grant all on lineitem to public;

CREATE TABLE sequence
(
    name               varchar2(30)  not null,
    nextid             int          not null,
    constraint pk_sequence primary key (name)
);

grant all on sequence to public;