angelo
/
java-store-retail-app
forked from angelo/java-retail-app
3
0
Fork 0
Code Pull Requests Activity
java-store-retail-app/文档/sql/order.sql

535 lines
14 KiB
SQL
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

-- Create table 销售订单主表
create table SA_ORDER
(
pk_id NUMBER(20) not null,
order_code VARCHAR2(30) not null,
order_type number(6) not null,
pk_rate NUMBER(6) not null,
order_amount NUMBER(17,6) default 0 not null,
order_achieve NUMBER(17,6) default 0 not null,
postage NUMBER(15,6) default 0 not null,
member_code VARCHAR2(20) not null,
rec_name VARCHAR2(100) not null,
rec_phone VARCHAR2(20) not null,
rec_province NUMBER(6) not null,
rec_city NUMBER(6) not null,
rec_county NUMBER(6) not null,
rec_address VARCHAR2(200) not null,
pay_time DATE,
remark VARCHAR2(200),
reference VARCHAR2(20),
placement VARCHAR2(20),
delivery_way NUMBER(2),
order_status NUMBER(2),
vertex VARCHAR2(20) not null,
del_flag NUMBER(1) default 0 not null,
pkCreator VARCHAR2(50) not null,
creation_time DATE default sysdate not null,
pkModified VARCHAR2(50),
modified_time DATE,
pk_country NUMBER(6) not null
)
tablespace CLOUD
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);
-- Add comments to the columns
comment on column SA_ORDER.pk_id
is '主键';
comment on column SA_ORDER.order_code
is '订单编号';
comment on column SA_ORDER.order_type
is '订单类型 来源于枚举：所属专区';
comment on column SA_ORDER.pk_rate
is '汇率 来源于汇率';
comment on column SA_ORDER.order_amount
is '订单金额';
comment on column SA_ORDER.order_achieve
is '订单业绩';
comment on column SA_ORDER.postage
is '邮费';
comment on column SA_ORDER.member_code
is '会员编号';
comment on column SA_ORDER.rec_name
is '收货人';
comment on column SA_ORDER.rec_phone
is '收货电话';
comment on column SA_ORDER.rec_province
is '省';
comment on column SA_ORDER.rec_city
is '市';
comment on column SA_ORDER.rec_county
is '县';
comment on column SA_ORDER.rec_address
is '收货地址';
comment on column SA_ORDER.pay_time
is '支付时间';
comment on column SA_ORDER.remark
is '备注';
comment on column SA_ORDER.reference
is '推荐人编号';
comment on column SA_ORDER.placement
is '安置人编号';
comment on column SA_ORDER.delivery_way
is '发货方式 (1=快递,2=公司自提,3=店铺自提,4=店铺配送)';
comment on column SA_ORDER.order_status
is '订单状态 (0=待支付,1=已支付,2=已发货,3=部分发货)';
comment on column SA_ORDER.vertex
is '顶点,用于统计六合';
comment on column SA_ORDER.del_flag
is '逻辑删除 (0=未删除,1已删除)';
comment on column SA_ORDER.pkCreator
is '创建人';
comment on column SA_ORDER.creation_time
is '创建时间';
comment on column SA_ORDER.pkModified
is '修改人';
comment on column SA_ORDER.modified_time
is '修改时间';
comment on column SA_ORDER.pk_country
is '国家';
-- Create/Recreate indexes
create unique index SA_ORDER_ORDER_CODE on SA_ORDER (ORDER_CODE)
tablespace CLOUD
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);
-- Create/Recreate primary, unique and foreign key constraints
alter table SA_ORDER
add constraint SA_ORDER_PK primary key (PK_ID)
using index
tablespace CLOUD
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);
create sequence SA_ORDER_SEQ
minvalue 1
maxvalue 999999999999999999999999
start with 1
increment by 1
cache 20;
-- Create table 销售订单明细表
create table SA_ORDER_ITEMS
(
pk_id NUMBER(20) not null,
pk_order NUMBER(20) not null,
pk_product NUMBER(6) not null,
pk_wares NUMBER(6) not null,
pk_specs NUMBER(6) not null,
price NUMBER(17,6) default 0 not null,
pk_rate NUMBER(6) not null,
achievement NUMBER(17,6) default 0 not null,
quantity NUMBER(10) default 0 not null,
pk_grade NUMBER(6) default 0 not null,
pk_awards NUMBER(6) default 0 not null,
order_status NUMBER(2) default 0 not null,
del_flag NUMBER(1) default 0 not null,
pkCreator VARCHAR2(50) not null,
creation_time DATE default sysdate not null,
pkModified VARCHAR2(50),
modified_time DATE,
pk_country NUMBER(6) not null
)
tablespace CLOUD
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);
-- Add comments to the table
comment on table SA_ORDER_ITEMS
is '销售订单-明细表';
-- Add comments to the columns
comment on column SA_ORDER_ITEMS.pk_id
is '主键';
comment on column SA_ORDER_ITEMS.pk_order
is '销售订单主表';
comment on column SA_ORDER_ITEMS.pk_product
is '产品主键';
comment on column SA_ORDER_ITEMS.pk_wares
is '商品主键';
comment on column SA_ORDER_ITEMS.pk_specs
is '规格主键';
comment on column SA_ORDER_ITEMS.price
is '价格';
comment on column SA_ORDER_ITEMS.pk_rate
is '汇率';
comment on column SA_ORDER_ITEMS.achievement
is '业绩';
comment on column SA_ORDER_ITEMS.quantity
is '数量';
comment on column SA_ORDER_ITEMS.pk_grade
is '等级';
comment on column SA_ORDER_ITEMS.pk_awards
is '奖衔';
comment on column SA_ORDER_ITEMS.order_status
is '订单状态 (0=待支付,1=已支付,2=已发货,3=部分发货)';
comment on column SA_ORDER_ITEMS.del_flag
is '逻辑删除 (0=未删除,1已删除)';
comment on column SA_ORDER_ITEMS.pkCreator
is '创建人';
comment on column SA_ORDER_ITEMS.creation_time
is '创建时间';
comment on column SA_ORDER_ITEMS.pkModified
is '修改人';
comment on column SA_ORDER_ITEMS.modified_time
is '修改时间';
comment on column SA_ORDER_ITEMS.pk_country
is '国家';
-- Create/Recreate primary, unique and foreign key constraints
alter table SA_ORDER_ITEMS
add constraint SA_ORDER_ITEMS_PK primary key (PK_ID)
using index
tablespace CLOUD
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);
create sequence SA_ORDER_ITEMS_SEQ
minvalue 1
maxvalue 999999999999999999999999
start with 1
increment by 1
cache 20;
-- Create table
create table SA_DELIVER
(
pk_id NUMBER(20) not null,
deliver_code VARCHAR2(50) not null,
logistics_code VARCHAR2(50),
logistics_company NUMBER(2),
rec_name VARCHAR2(100) not null,
rec_phone VARCHAR2(20) not null,
rec_province NUMBER(6) not null,
rec_city NUMBER(6) not null,
rec_county NUMBER(6) not null,
deliver_status NUMBER(2) not null,
deliver_time DATE,
del_flag NUMBER(1) default 0 not null,
pkCreator VARCHAR2(50) not null,
creation_time DATE default sysdate not null,
pkModified VARCHAR2(50),
modified_time DATE,
pk_country NUMBER(6) not null
)
tablespace CLOUD
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);
-- Add comments to the table
comment on table SA_DELIVER
is '销售订单-发货单';
-- Add comments to the columns
comment on column SA_DELIVER.pk_id
is '主键';
comment on column SA_DELIVER.deliver_code
is '发货单号';
comment on column SA_DELIVER.logistics_code
is '物流单号';
comment on column SA_DELIVER.logistics_company
is '物流公司';
comment on column SA_DELIVER.rec_name
is '收货姓名';
comment on column SA_DELIVER.rec_phone
is '收货电话';
comment on column SA_DELIVER.rec_province
is '收货省';
comment on column SA_DELIVER.rec_city
is '收货市';
comment on column SA_DELIVER.rec_county
is '收货县';
comment on column SA_DELIVER.deliver_status
is '发货状态(1=未发货,2=已发货,3=部分发货,4=已收货，5=已退货)';
comment on column SA_DELIVER.deliver_time
is '发货时间';
comment on column SA_DELIVER.del_flag
is '逻辑删除 (0=未删除,1已删除)';
comment on column SA_DELIVER.pkCreator
is '创建人';
comment on column SA_DELIVER.creation_time
is '创建时间';
comment on column SA_DELIVER.pkModified
is '修改人';
comment on column SA_DELIVER.modified_time
is '修改时间';
comment on column SA_DELIVER.pk_country
is '国家';
-- Create/Recreate indexes
create unique index SA_DELIVER_DELIVER_CODE on SA_DELIVER (DELIVER_CODE)
tablespace CLOUD
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);
-- Create/Recreate primary, unique and foreign key constraints
alter table SA_DELIVER
add constraint SA_DELIVER_PK primary key (PK_ID)
using index
tablespace CLOUD
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);
-- Create table 发货单
create table SA_DELIVER
(
pk_id NUMBER(20) not null,
deliver_code VARCHAR2(50) not null,
logistics_code VARCHAR2(50),
logistics_company NUMBER(2),
rec_name VARCHAR2(100) not null,
rec_phone VARCHAR2(20) not null,
rec_province NUMBER(6) not null,
rec_city NUMBER(6) not null,
rec_county NUMBER(6) not null,
deliver_status NUMBER(2) not null,
deliver_time DATE,
del_flag NUMBER(1) default 0 not null,
pkCreator VARCHAR2(50) not null,
creation_time DATE default sysdate not null,
pkModified VARCHAR2(50),
modified_time DATE,
pk_country NUMBER(6) not null
)
tablespace CLOUD
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);
-- Add comments to the table
comment on table SA_DELIVER
is '发货单';
-- Add comments to the columns
comment on column SA_DELIVER.pk_id
is '主键';
comment on column SA_DELIVER.deliver_code
is '发货单号';
comment on column SA_DELIVER.logistics_code
is '物流单号';
comment on column SA_DELIVER.logistics_company
is '物流公司';
comment on column SA_DELIVER.rec_name
is '收货姓名';
comment on column SA_DELIVER.rec_phone
is '收货电话';
comment on column SA_DELIVER.rec_province
is '收货省';
comment on column SA_DELIVER.rec_city
is '收货市';
comment on column SA_DELIVER.rec_county
is '收货县';
comment on column SA_DELIVER.deliver_status
is '发货状态(1=未发货,2=已发货,3=部分发货,4=已收货，5=已退货)';
comment on column SA_DELIVER.deliver_time
is '发货时间';
comment on column SA_DELIVER.del_flag
is '逻辑删除 (0=未删除,1已删除)';
comment on column SA_DELIVER.pkCreator
is '创建人';
comment on column SA_DELIVER.creation_time
is '创建时间';
comment on column SA_DELIVER.pkModified
is '修改人';
comment on column SA_DELIVER.modified_time
is '修改时间';
comment on column SA_DELIVER.pk_country
is '国家';
-- Create/Recreate indexes
create unique index SA_DELIVER_DELIVER_CODE on SA_DELIVER (DELIVER_CODE)
tablespace CLOUD
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);
-- Create/Recreate primary, unique and foreign key constraints
alter table SA_DELIVER
add constraint SA_DELIVER_PK primary key (PK_ID)
using index
tablespace CLOUD
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);
create sequence SA_DELIVER_SEQ
minvalue 1
maxvalue 999999999999999999999999
start with 1
increment by 1
cache 20;
-- Create table 发货单明细表
create table SA_DELIVER_ITEMS
(
pk_id NUMBER(20) not null,
pk_order NUMBER(20) not null,
pk_product NUMBER(20) not null,
pk_specs NUMBER(6) not null,
pk_storehouse NUMBER(6) not null,
quantity NUMBER(10) default 0 not null,
del_flag NUMBER(1) default 0 not null,
pkCreator VARCHAR2(50) not null,
creation_time DATE default sysdate not null,
pkModified VARCHAR2(50),
modified_time DATE,
pk_country NUMBER(6) not null
)
tablespace CLOUD
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);
-- Add comments to the table
comment on table SA_DELIVER_ITEMS
is '发货单明细表';
-- Add comments to the columns
comment on column SA_DELIVER_ITEMS.pk_id
is '主键';
comment on column SA_DELIVER_ITEMS.pk_order
is '订单主键';
comment on column SA_DELIVER_ITEMS.pk_product
is '产品主键';
comment on column SA_DELIVER_ITEMS.pk_specs
is '产品规格主键';
comment on column SA_DELIVER_ITEMS.pk_storehouse
is '仓库';
comment on column SA_DELIVER_ITEMS.quantity
is '产品数量';
comment on column SA_DELIVER_ITEMS.del_flag
is '逻辑删除 (0=未删除,1已删除)';
comment on column SA_DELIVER_ITEMS.pkCreator
is '创建人';
comment on column SA_DELIVER_ITEMS.creation_time
is '创建时间';
comment on column SA_DELIVER_ITEMS.pkModified
is '修改人';
comment on column SA_DELIVER_ITEMS.modified_time
is '修改时间';
comment on column SA_DELIVER_ITEMS.pk_country
is '国家';
-- Create/Recreate primary, unique and foreign key constraints
alter table SA_DELIVER_ITEMS
add constraint SA_DELIVER_ITEMS_PK primary key (PK_ID)
using index
tablespace CLOUD
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);
create sequence SA_DELIVER_ITEMS_ID_SEQ
minvalue 1
maxvalue 999999999999999999999999
start with 1
increment by 1
cache 20;
View Git Blame Copy Permalink