oracle脚本规范

1、新建普通表

CREATE TABLE WMSSERVER.T_DPC_BEFOREHAND_EXTEND

(

  ID               NUMBER NOT NULL,

  BEFOR_ID      NUMBER,

  CODE        VARCHAR2(100),

  ORDER_CODE         VARCHAR2(100),

  DELIVERY_CODE      VARCHAR2(100),

  WAYBILL_CODE              VARCHAR2(100),

  CHANNEL_CODE   VARCHAR2(100),

  WAREHOUSE_LOCATION         VARCHAR2(100),

  WAREHOUSE_STATE VARCHAR2(20),

  SITE_CODE   VARCHAR2(50),

  SITE_NAME  VARCHAR2(50),

  ASN_TIME    DATE,

  ASN_NAME           VARCHAR2(20),

  TRACKING_RECORD      VARCHAR2(500),

  CREATE_TIME    DATE,

  UPDATE_TIME    DATE,

  EXPRESS_TIME   DATE

)

--DPC表空间为WMSSERVER_DATA

TABLESPACE WMSSERVER_DATA;

1.1、每个字段必须添加注释，包括表名

COMMENT ON TABLE WMSSERVER.T_DPC_BEFOREHAND_EXTEND IS '预配载单扩展表';

COMMENT ON COLUMN WMSSERVER.T_DPC_BEFOREHAND_EXTEND.ID IS 'ID';

COMMENT ON COLUMN WMSSERVER.T_DPC_BEFOREHAND_EXTEND.BEFOR_ID IS '预配载单ID';

COMMENT ON COLUMN WMSSERVER.T_DPC_BEFOREHAND_EXTEND.CODE IS '调度单号';

COMMENT ON COLUMN WMSSERVER.T_DPC_BEFOREHAND_EXTEND.ORDER_CODE IS '订单号';

COMMENT ON COLUMN WMSSERVER.T_DPC_BEFOREHAND_EXTEND.DELIVERY_CODE IS '配载单号';

COMMENT ON COLUMN WMSSERVER.T_DPC_BEFOREHAND_EXTEND.WAYBILL_CODE IS '运单号';

COMMENT ON COLUMN WMSSERVER.T_DPC_BEFOREHAND_EXTEND.CHANNEL_CODE IS '渠道编号';

COMMENT ON COLUMN WMSSERVER.T_DPC_BEFOREHAND_EXTEND.WAREHOUSE_LOCATION IS '库位';

COMMENT ON COLUMN WMSSERVER.T_DPC_BEFOREHAND_EXTEND.WAREHOUSE_STATE IS '库存状态';

COMMENT ON COLUMN WMSSERVER.T_DPC_BEFOREHAND_EXTEND.SITE_CODE IS '站点编码';

COMMENT ON COLUMN WMSSERVER.T_DPC_BEFOREHAND_EXTEND.SITE_NAME IS '站点名称';

COMMENT ON COLUMN WMSSERVER.T_DPC_BEFOREHAND_EXTEND.ASN_TIME IS '入库时间';

COMMENT ON COLUMN WMSSERVER.T_DPC_BEFOREHAND_EXTEND.ASN_NAME IS '入库操作人';

COMMENT ON COLUMN WMSSERVER.T_DPC_BEFOREHAND_EXTEND.TRACKING_RECORD IS '跟踪记录';

COMMENT ON COLUMN WMSSERVER.T_DPC_BEFOREHAND_EXTEND.CREATE_TIME IS '创建时间';

COMMENT ON COLUMN WMSSERVER.T_DPC_BEFOREHAND_EXTEND.UPDATE_TIME IS '更新时间';

COMMENT ON COLUMN WMSSERVER.T_DPC_BEFOREHAND_EXTEND.EXPRESS_TIME IS '发运时间';

 

1.2、ID索引为必须，添加索引空间名WMSSERVER_INDEX

ALTER TABLE WMSSERVER.T_DPC_BEFOREHAND_EXTEND

  ADD CONSTRAINT PK_T_DPC_BEFOREHAND_EXTEND PRIMARY KEY (ID)

  USING INDEX TABLESPACE WMSSERVER_INDEX;

2、创建分区表（根据数据量的不同，存储业务不同，按照实际情况考虑是否需要建立分区）

CREATE TABLE WMSSERVER.T_DPC_FEE_DETAIL

(

  ID                 INTEGER NOT NULL,

  BATCH_CODE         VARCHAR2(50),

  WAY_BILL_CODE      VARCHAR2(50),

  CHANNEL_CODE       VARCHAR2(50),

  CUSTOMER_CODE      VARCHAR2(200),

  CUSTOMER_NAME      VARCHAR2(200),

  WAREHOUSE_CODE     VARCHAR2(100),

  WAREHOUSE_NAME     VARCHAR2(100),

  CARRIER_CODE       VARCHAR2(200),

  CARRIER_NAME       VARCHAR2(200),

  CONSIGNER_PROVINCE VARCHAR2(100),

  CONSIGNER_CITY     VARCHAR2(100),

  CONSIGNER_AREA     VARCHAR2(100),

  RECEIVING_PROVINCE VARCHAR2(100),

  RECEIVING_CITY     VARCHAR2(100),

  RECEIVING_AREA     VARCHAR2(100),

  COST_TYPE          VARCHAR2(50),

  SOURCE             VARCHAR2(50),

  BILL_DATE          DATE,

  CACULATE_DATE      DATE,

  CACULATE_NUM       NUMBER(9),

  CACULATE_WEIGHT    NUMBER,

  CACULATE_VOLUME    NUMBER,

  FEE_ALL            NUMBER(38,6),

  PACKAGES_NUM       NUMBER(9),

  WEIGHT             NUMBER,

  VOLUME             NUMBER,

  FEE                NUMBER(38,6),

  WAYBILL_STATUS     VARCHAR2(50),

  FEE_STATUS         VARCHAR2(50),

  REMARK             VARCHAR2(700),

  MODIFY_TIME        DATE,

  MODIFY_NAME        VARCHAR2(50),

  ORDER_CODE         VARCHAR2(50),

  CREATE_TIME        DATE,

  CREATE_NAME        VARCHAR2(50),

  CREATE_ID          VARCHAR2(50),

  MODIFY_ID          VARCHAR2(50),

  ABNORMAL_CAUSE     VARCHAR2(500),

  P_ID               NUMBER,

  CACULATE_TYPE      VARCHAR2(50),

  DELIVERY_CODE      VARCHAR2(100),

  FEE_SERIAL_CODE    VARCHAR2(100),

  PRICE              NUMBER,

  VEHICLE            NUMBER,

  ADJUNCT            VARCHAR2(700),

  AUDIT_OPINION      VARCHAR2(500),

  CONSIGNER_ADDRESS  VARCHAR2(255),

  RECEIVING_ADDRESS  VARCHAR2(255),

  SHIPPER_NAME       VARCHAR2(100),

  CONSIGNER_NAME     VARCHAR2(100),

  CONSIGNER_TEL      VARCHAR2(50),

  RECEIVING_NAME     VARCHAR2(100),

  RECEIVING_TEL      VARCHAR2(50),

  RECEIVER_NAME      VARCHAR2(100),

  DELIVERY_TIME      DATE,

  ORDER_SEND_TIME    DATE,

  ORDER_SHELF_TIME   DATE,

  FEE_WAY            VARCHAR2(20),

  CONTRACT_NUM       VARCHAR2(255)

)

PARTITION BY RANGE (CREATE_TIME) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))

(PARTITION T_DPC_FEE_DETAIL_P01 VALUES LESS THAN(TO_DATE('2020-08-01', 'YYYY-MM-DD')))

TABLESPACE WMSSERVER_DATA;

2.1、为表添加注释

COMMENT ON TABLE WMSSERVER.T_DPC_FEE_DETAIL

  IS '方案管理-费用明细';

2.2、为字段添加注释

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.ID

  IS 'ID';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.BATCH_CODE

  IS '批次号';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.WAY_BILL_CODE

  IS '运单号';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.CHANNEL_CODE

  IS '渠道单号';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.CUSTOMER_CODE

  IS '客户编码';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.CUSTOMER_NAME

  IS '客户名称';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.WAREHOUSE_CODE

  IS '仓库编码';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.WAREHOUSE_NAME

  IS '仓库名称';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.CARRIER_CODE

  IS '承运商编码';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.CARRIER_NAME

  IS '承运商名称';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.CONSIGNER_PROVINCE

  IS '发货省';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.CONSIGNER_CITY

  IS '发货市';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.CONSIGNER_AREA

  IS '发货区';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.RECEIVING_PROVINCE

  IS '收货省';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.RECEIVING_CITY

  IS '收货市';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.RECEIVING_AREA

  IS '收货区';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.COST_TYPE

  IS '费用类型';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.SOURCE

  IS '来源';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.BILL_DATE

  IS '单据日期';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.CACULATE_DATE

  IS '计费日期';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.CACULATE_NUM

  IS '计费件数';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.CACULATE_WEIGHT

  IS '计费重量';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.CACULATE_VOLUME

  IS '计费体积';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.FEE_ALL

  IS '总费用';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.PACKAGES_NUM

  IS '件数';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.WEIGHT

  IS '重量';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.VOLUME

  IS '体积';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.FEE

  IS '费用';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.WAYBILL_STATUS

  IS '运单/订单状态';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.FEE_STATUS

  IS '费用状态';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.REMARK

  IS '备注/异常原因';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.MODIFY_TIME

  IS '修改时间';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.MODIFY_NAME

  IS '修改人';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.ORDER_CODE

  IS '订单号';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.CREATE_TIME

  IS '创建时间';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.CREATE_NAME

  IS '创建人';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.CREATE_ID

  IS '创建人工号';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.MODIFY_ID

  IS '修改人工号';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.ABNORMAL_CAUSE

  IS '异常原因';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.P_ID

  IS '关联ID';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.CACULATE_TYPE

  IS '计费单据类型';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.DELIVERY_CODE

  IS '配载单号';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.FEE_SERIAL_CODE

  IS '费用明细流水号';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.PRICE

  IS '单价';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.VEHICLE

  IS '整车价';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.ADJUNCT

  IS '附件';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.AUDIT_OPINION

  IS '审核意见';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.CONSIGNER_ADDRESS

  IS '发货详细地址';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.RECEIVING_ADDRESS

  IS '收货详细地址';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.SHIPPER_NAME

  IS '发货方';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.CONSIGNER_NAME

  IS '发货人';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.CONSIGNER_TEL

  IS '发货人联系方式';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.RECEIVING_NAME

  IS '收货人';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.RECEIVING_TEL

  IS '收货人联系方式';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.RECEIVER_NAME

  IS '收货方';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.DELIVERY_TIME

  IS '配载时间';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.ORDER_SEND_TIME

  IS '订单发运时间';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.ORDER_SHELF_TIME

  IS '订单上架时间';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.FEE_WAY

  IS '计费方式';

COMMENT ON COLUMN WMSSERVER.T_DPC_FEE_DETAIL.CONTRACT_NUM

  IS '合同序号';

2.3、id为主键，必须添加

ALTER TABLE WMSSERVER.T_DPC_FEE_DETAIL ADD CONSTRAINT PK_T_DPC_FEE_DETAIL

  PRIMARY KEY (ID) USING INDEX TABLESPACE WMSSERVER_INDEX;

3、脚本示例

3.1、Oracle新增字段示例

--表名前面必须添加数据库用户名

alter table wmsserver.t_dpc_beforehand_extend add (VALUE_ADDED_FEE_TYPE varchar2(200));

--每个字段必须添加字段注解

COMMENT ON COLUMN wmsserver.t_dpc_beforehand_extend.VALUE_ADDED_FEE_TYPE IS '已计费的增值服务费类型';

3.2、Oracle新增序列示例

--序列名前面需要加用户名,序列从1开始

create sequence WMSSERVER.SEQ_WMS_DELIVERY_ORDER

start with 1

cache 200;

3.3、Oracle新增索引示例

--索引名以及表名前必须添加数据库用户名,DPC索引空间固定为WMSSERVER_INDEX(WMS不同版本空间名不同,添加之前需要问清楚)

CREATE INDEX WMSSERVER.IDX1_T_DPC_FEE_DETAIL ON WMSSERVER.T_DPC_FEE_DETAIL (ORDER_CODE)

  TABLESPACE WMSSERVER_INDEX;

 

--组合索引字段小于或者等于5个

CREATE INDEX WMSSERVER.IDX2_T_DPC_FEE_DETAIL ON WMSSERVER.T_DPC_FEE_DETAIL (CREATE_TIME, BILL_DATE, CUSTOMER_CODE, WAREHOUSE_CODE)

  TABLESPACE WMSSERVER_INDEX;

4、注意事项

4.1、大表新增字段索引的脚本必须使用online关键字

示例：

CREATE INDEX WMSSERVER.IDX5_T_DPC_TRAIL_LOG ON WMSSERVER.T_DPC_TRAIL_LOG (WAYBILL_CODE)  online

TABLESPACE WMSSERVER_INDEX;