transaction-service/transaction-server/target/classes/sql/demo.sql

-- 交际委托子表序列
CREATE SEQUENCE seq_jycrkwt_sub
START WITH 1
INCREMENT BY 1
MINVALUE 1
NOMAXVALUE
NOCYCLE
CACHE 20;

-- 委托书添加字段
ALTER TABLE db_utf8:ty_jycrkwt ADD lscd varchar(100);
ALTER TABLE db_utf8:ty_jycrkwt ADD wtrq date;
ALTER TABLE db_utf8:ty_jycrkwt ADD bzq_first_date date;
ALTER TABLE db_utf8:ty_jycrkwt ADD bzq_last_date date;
ALTER TABLE db_utf8:ty_jycrkwt ADD wcsj date;
ALTER TABLE db_utf8:ty_jycrkwt ADD gxcj DECIMAL(9,2);
ALTER TABLE db_utf8:ty_jycrkwt ADD qybmyq varchar(200);
ALTER TABLE db_utf8:ty_jycrkwt ADD qyzz varchar(20);
ALTER TABLE db_utf8:ty_jycrkwt ADD commitment varchar(200);
ALTER TABLE db_utf8:ty_jycrkwt ADD fhbz varchar(200);
ALTER TABLE db_utf8:ty_jycrkwt ADD jyxmyq varchar(200);
ALTER TABLE db_utf8:ty_jycrkwt ADD jjcz DECIMAL(9,2);
ALTER TABLE db_utf8:ty_jycrkwt ADD bzyq varchar(200);
ALTER TABLE db_utf8:ty_jycrkwt ADD jhfs varchar(20);
ALTER TABLE db_utf8:ty_jycrkwt ADD jlfs varchar(20);
ALTER TABLE db_utf8:ty_jycrkwt ADD rkyq varchar(20);
ALTER TABLE db_utf8:ty_jycrkwt ADD sfbgyq varchar(200);
ALTER TABLE db_utf8:ty_jycrkwt ADD ccjyxm varchar(200);
ALTER TABLE db_utf8:ty_jycrkwt ADD crksx varchar(20);
ALTER TABLE db_utf8:ty_jycrkwt ADD jsfs varchar(200);
ALTER TABLE db_utf8:ty_jycrkwt ADD slr varchar(100);
ALTER TABLE db_utf8:ty_jycrkwt ADD slryj varchar(200);
ALTER TABLE db_utf8:ty_jycrkwt ADD slrq date;
ALTER TABLE db_utf8:ty_jycrkwt ADD jyzxshr varchar(100);
ALTER TABLE db_utf8:ty_jycrkwt ADD jyzxshryj varchar(200);
ALTER TABLE db_utf8:ty_jycrkwt ADD jyzxshrq date;

ALTER TABLE db_utf8:ty_jycrkwt ADD wtshr varchar(100);
ALTER TABLE db_utf8:ty_jycrkwt ADD wtfqz text;
ALTER TABLE db_utf8:ty_jycrkwt ADD wtfgzsj date;
ALTER TABLE db_utf8:ty_jycrkwt ADD wtshsj date;

comment on column db_utf8:ty_jycrkwt.lscd is '产地';
comment on column db_utf8:ty_jycrkwt.wtrq is '委托日期';
comment on column db_utf8:ty_jycrkwt.bzq_first_date is '保质期限 最早一批';
comment on column db_utf8:ty_jycrkwt.bzq_last_date is '保质期限 最后一批';
comment on column db_utf8:ty_jycrkwt.wcsj is '完成时间';
comment on column db_utf8:ty_jycrkwt.gxcj is '购销价差';
comment on column db_utf8:ty_jycrkwt.qybmyq is '报名企业要求';
comment on column db_utf8:ty_jycrkwt.qyzz is '资质<br/> 1生产 2经营 3应急 4进口';
comment on column db_utf8:ty_jycrkwt.commitment is '承诺';
comment on column db_utf8:ty_jycrkwt.fhbz is '符合标准';
comment on column db_utf8:ty_jycrkwt.jyxmyq is '检验项目要求';
comment on column db_utf8:ty_jycrkwt.jjcz is '竞价差值';
comment on column db_utf8:ty_jycrkwt.bzyq is '包装要求';
comment on column db_utf8:ty_jycrkwt.jhfs is '交货方式<br/> 1库内价 2汽车车板价';
comment on column db_utf8:ty_jycrkwt.jlfs is '计量方式<br/> 1地磅记录 2检斤计价 3其他';
comment on column db_utf8:ty_jycrkwt.rkyq is '入库要求<br/> 1第三方报告 2出厂报告';
comment on column db_utf8:ty_jycrkwt.sfbgyq is '第三方报告要求';
comment on column db_utf8:ty_jycrkwt.ccjyxm is '出厂检验项目';
comment on column db_utf8:ty_jycrkwt.crksx is '出入库顺序<br/> 1先出后入 2先入后出 3其他';
comment on column db_utf8:ty_jycrkwt.jsfs is '结算方式';
comment on column db_utf8:ty_jycrkwt.slr is '受理人';
comment on column db_utf8:ty_jycrkwt.slryj is '受理人意见';
comment on column db_utf8:ty_jycrkwt.slrq is '受理日期';
comment on column db_utf8:ty_jycrkwt.jyzxshr is '审核人';
comment on column db_utf8:ty_jycrkwt.jyzxshryj is '审核人意见';
comment on column db_utf8:ty_jycrkwt.jyzxshrq is '审核人日期';
comment on column db_utf8:ty_jycrkwt.wtshr is '委托审核人';
comment on column db_utf8:ty_jycrkwt.wtfgzsj is '委托审核时间';
comment on column db_utf8:ty_jycrkwt.wtfqz is '委托方盖章';
comment on column db_utf8:ty_jycrkwt.wtshsj is '委托方盖章时间';

-- 安排添加字段
ALTER TABLE db_utf8:ty_jycrkap ADD sm varchar(20);
ALTER TABLE db_utf8:ty_jycrkap ADD jgjd varchar(20);
ALTER TABLE db_utf8:ty_jycrkap ADD hf varchar(20);
ALTER TABLE db_utf8:ty_jycrkap ADD wcsj date;
ALTER TABLE db_utf8:ty_jycrkap ADD rknl DECIMAL(9,3);
ALTER TABLE db_utf8:ty_jycrkap ADD sfkc integer;

comment on column db_utf8:ty_jycrkap.sm is '碎米';
comment on column db_utf8:ty_jycrkap.jgjd is '加工精度';
comment on column db_utf8:ty_jycrkap.hf is '灰分';
comment on column db_utf8:ty_jycrkap.wcsj is '完成时间';
comment on column db_utf8:ty_jycrkap.rknl is '入库能力';
comment on column db_utf8:ty_jycrkap.sfkc is '是否空仓<br/> 0 否 1 是';



lock table db_utf8:ty_jycrkwt in exclusive mode ;

RENAME COLUMN db_utf8:ty_jycrkwt.jsfx TO jyfs ;
ALTER TABLE db_utf8:ty_jycrkwt MODIFY jyfs INTEGER ;
comment on column db_utf8:ty_jycrkwt.jyfs is '交易方式' ;
RENAME COLUMN db_utf8:ty_jycrkwt.jylx TO jyfx ;
ALTER TABLE db_utf8:ty_jycrkwt MODIFY jyfx INTEGER ;
comment on column db_utf8:ty_jycrkwt.jyfx is '交易方向' ;

commit work ;
RENAME COLUMN db_utf8:ty_jycrkwt.jsfxmc TO jyfsmc ;
RENAME COLUMN db_utf8:ty_jycrkwt.jylxmc TO jyfxmc ;

ALTER TABLE db_utf8:ty_jycrkap ADD bd_group varchar(5);
comment on column db_utf8:ty_jycrkap.bd_group is '标的分组';
ALTER TABLE db_utf8:ty_jycrkap ADD bdh varchar(32);
ALTER TABLE db_utf8:ty_jycrkap ADD bdbm varchar(32);
comment on column db_utf8:ty_jycrkap.bdh is '标的号';
comment on column db_utf8:ty_jycrkap.bdbm is '标的编码';
ALTER TABLE db_utf8:ty_jycrkap ADD jydj DECIMAL(9,2);
comment on column db_utf8:ty_jycrkap.jydj is '交易底价';

ALTER TABLE db_utf8:ty_jycrkwt ADD hwggbt varchar(200) ;
ALTER TABLE db_utf8:ty_jycrkwt ADD hwggzw text ;

ALTER TABLE db_utf8:ty_jycrkwt ADD wcm_id1 int ;
ALTER TABLE db_utf8:ty_jycrkwt ADD wcm_id2 int ;
comment on column db_utf8:ty_jycrkwt.wcm_id1 is 'wcm反写的docid' ;
comment on column db_utf8:ty_jycrkwt.wcm_id2 is 'wcm反写的docid' ;