oracle 购物系统（包括表，视图，序列，索引，触发器，程序包，等等）

--11张表------------- -- 重建表之前，首先删除表 drop table ZHANGXU.CATEGORY cascade constraints; --建表（商品类别）--------------------- create table ZHANGXU.CATEGORY ( CATEGORYID NUMBER(8) not null, CATEGORYNAME VARCHAR2(20) not null ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 1K next 1K minextents 1 maxextents unlimited pctincrease 0 ); -- 为表添加注释 comment on table ZHANGXU.CATEGORY is '商品类别表'; -- 为列添加注释 comment on column ZHANGXU.CATEGORY.CATEGORYID is '类别编号'; comment on column ZHANGXU.CATEGORY.CATEGORYNAME is '类别名称'; -- primary, unique and foreign key 约束（商品类别）--------------------- alter table ZHANGXU.CATEGORY add constraint CATEGORYID primary key (CATEGORYID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 1K next 16K minextents 1 maxextents unlimited pctincrease 0 ); alter table ZHANGXU.CATEGORY add constraint CATEGORYNAME unique (CATEGORYNAME) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 1K minextents 1 maxextents unlimited ); alter table ZHANGXU.CATEGORY CACHE PARALLEL; -- 重建表之前，首先删除表 drop table ZHANGXU.GOODS cascade constraints; --建表（商品信息）--------------------- create table ZHANGXU.GOODS ( GOODSID NUMBER(8) not null, GOODSNAME VARCHAR2(20) not null, STOREID NUMBER(8) not null, PRICE NUMBER(5,1) default 0 not null, CATEGORYID NUMBER(8) not null, PIC VARCHAR2(255), JIESHAO VARCHAR2(255), MIAOSHU VARCHAR2(255) ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 16K minextents 1 maxextents unlimited ); -- 为表添加注释 comment on table ZHANGXU.GOODS is '商品信息表'; -- 为列添加注释 comment on column ZHANGXU.GOODS.GOODSID is '商品编号'; comment on column ZHANGXU.GOODS.GOODSNAME is '商品名称'; comment on column ZHANGXU.GOODS.STOREID is '库存编号'; comment on column ZHANGXU.GOODS.PRICE is '售价'; comment on column ZHANGXU.GOODS.CATEGORYID is '类别编号'; comment on column ZHANGXU.GOODS.PIC is '图片'; comment on column ZHANGXU.GOODS.JIESHAO is '介绍'; comment on column ZHANGXU.GOODS.MIAOSHU is '描述'; -- primary, unique and foreign key 约束（商品信息）--------------------- alter table ZHANGXU.GOODS add constraint GOODSID primary key (GOODSID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 1K minextents 1 maxextents unlimited ); alter table ZHANGXU.GOODS add constraint GOODSNAME unique (GOODSNAME) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 1K minextents 1 maxextents unlimited ); alter table ZHANGXU.GOODS add constraint uni_store unique (STOREID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 1K minextents 1 maxextents unlimited ); alter table ZHANGXU.GOODS add constraint GOOD_CATEGORYID foreign key (CATEGORYID) references ZHANGXU.CATEGORY (CATEGORYID); -- Create/Recreate check constraints alter table ZHANGXU.GOODS add constraint PRICE check (price>=0); alter table ZHANGXU.GOODS CACHE PARALLEL; -- 重建表之前，首先删除表 drop table ZHANGXU.STORE cascade constraints; --建表（库存信息）--------------------- create table ZHANGXU.STORE ( STOREID NUMBER(8) not null, COUNT NUMBER(5) default 0 not null ) tablespace USERS pctfree 20 --更新预留空间 initrans 1 --初始事务数 maxtrans 255 --最大事务数 storage ( initial 1K --第一盘区大小 next 16K --第二盘区大小 minextents 1 --最小盘区数 maxextents unlimited --最大盘区数 pctincrease 0 --盘区增长百分比 ); -- 为表添加注释 comment on table ZHANGXU.STORE is '库存信息'; -- 为列添加注释 comment on column ZHANGXU.STORE.STOREID is '库存编号'; comment on column ZHANGXU.STORE.COUNT is '库存数量'; -- primary, unique and foreign key 约束（库存信息）--------------------- alter table ZHANGXU.STORE add constraint STOREID primary key (STOREID) using index --索引 tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 1K next 16K minextents 1 maxextents unlimited pctincrease 0 ); alter table ZHANGXU.STORE add constraint storeid_goods foreign key (STOREID) references zhangxu.goods (STOREID); alter table ZHANGXU.STORE CACHE PARALLEL; -- 重建表之前，首先删除表 drop table ZHANGXU.MANAGER cascade constraints; -- 建表（操作人员）--------------------- create table ZHANGXU.MANAGER ( MANAGERID NUMBER(8) not null, USERNAME VARCHAR2(20) not null, PASSWORD VARCHAR2(20) not null, LIMIT NUMBER(2) default 0 not null ) tablespace USERS pctfree 20 initrans 1 maxtrans 255 storage ( initial 1K next 1K minextents 1 maxextents unlimited pctincrease 0 ); -- 为表添加注释 comment on table ZHANGXU.MANAGER is '操作人员信息表'; -- 为列添加注释 comment on column ZHANGXU.MANAGER.MANAGERID is '操作人员编号'; comment on column ZHANGXU.MANAGER.USERNAME is '用户名'; comment on column ZHANGXU.MANAGER.PASSWORD is '密码'; comment on column ZHANGXU.MANAGER.LIMIT is '权限'; -- primary, unique and foreign key 约束（操作人员）--------------------- alter table ZHANGXU.MANAGER add primary key (MANAGERID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 1K minextents 1 maxextents unlimited ); alter table ZHANGXU.MANAGER add unique (USERNAME) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 1K minextents 1 maxextents unlimited ); alter table ZHANGXU.MANAGER CACHE PARALLEL; -- 重建表之前，首先删除表 drop table ZHANGXU.MEMBER cascade constraints; -- 建表（会员）--------------------- create table ZHANGXU.MEMBER ( MEMBERID NUMBER(8) not null, USERNAME VARCHAR2(20) not null, PASSWORD VARCHAR2(20) not null, SEX VARCHAR2(2), AGE NUMBER(3) default 0 not null, EMAIL VARCHAR2(50), PHONE VARCHAR2(14), PIC VARCHAR2(255), LOGTIMES NUMBER(3) default 0 not null ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 16K next 16K minextents 1 maxextents unlimited pctincrease 0 ); -- 为表添加注释 comment on table ZHANGXU.MEMBER is '会员信息表'; -- 为列添加注释 comment on column ZHANGXU.MEMBER.MEMBERID is '会员编号'; comment on column ZHANGXU.MEMBER.USERNAME is '用户名'; comment on column ZHANGXU.MEMBER.PASSWORD is '密码'; comment on column ZHANGXU.MEMBER.SEX is '性别'; comment on column ZHANGXU.MEMBER.AGE is '年龄'; comment on column ZHANGXU.MEMBER.EMAIL is '电子邮件'; comment on column ZHANGXU.MEMBER.PHONE is '电话'; comment on column ZHANGXU.MEMBER.PIC is '头像'; comment on column ZHANGXU.MEMBER.LOGTIMES is '登录次数'; -- primary, unique and foreign key 约束（会员）--------------------- alter table ZHANGXU.MEMBER add constraint MEMBERID primary key (MEMBERID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 16K minextents 1 maxextents unlimited ); alter table ZHANGXU.MEMBER add constraint USERNAME unique (USERNAME) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 16K minextents 1 maxextents unlimited ); -- Create/Recreate check constraints alter table ZHANGXU.MEMBER add check (sex in('男','女')); alter table ZHANGXU.MEMBER add check (age>=0 an