数据库操作

1.创建表

create table SCM_GROUP_PURCHASE( GROUP_PURCHASE_ID varchar(10) primary key, productp_id varchar(10) not null, start_time date, end_time date, purchase_quantity number(14,4), purchase_type number(1), modify_time date, modify_user_id varchar(50), delete_flag number(1), delete_user_id varchar(50), delete_time date, spec_id varchar2(10), remark VARCHAR2(250), DEPT_ID varchar2(10), Class_Name varchar2(50) ); comment on table SCM_GROUP_PURCHASE is '带量维护表'; comment on column SCM_GROUP_PURCHASE.GROUP_PURCHASE_ID is 'GROUP_PURCHASE_ID'; comment on column SCM_GROUP_PURCHASE.productp_id is '规格厂商代码'; comment on column SCM_GROUP_PURCHASE.start_time is '开始日期'; comment on column SCM_GROUP_PURCHASE.end_time is '结束日期'; comment on column SCM_GROUP_PURCHASE.purchase_quantity is '采购量'; comment on column SCM_GROUP_PURCHASE.purchase_type is '带量类型（0省带量，1市带量）'; comment on column SCM_GROUP_PURCHASE.modify_user_id is '修改人ID'; comment on column SCM_GROUP_PURCHASE.modify_time is '修改时间'; comment on column SCM_GROUP_PURCHASE.delete_flag is '作废标志'; comment on column SCM_GROUP_PURCHASE.delete_user_id is '作废人'; comment on column SCM_GROUP_PURCHASE.delete_time is '作废时间'; comment on column SCM_GROUP_PURCHASE.spec_id is '规格id'; comment on column SCM_GROUP_PURCHASE.remark is '备注'; comment on column SCM_GROUP_PURCHASE.Class_Name is '分类';

2.加字段

alter table SCM_GROUP_PURCHASE add type varchar2(10); comment on column SCM_GROUP_PURCHASE.type is '维护类型';

3.修改字段大小

alter table SCM_GROUP_PURCHASE modify (SPEC_ID nvarchar2(500));

4.备份表

create table scm_stock_bak_20200903 as select * from scm_stock; insert into scm_stock_bak_20200903 select * from scm_stock;

5.两个表关联更新字段

update scm_stock a set a.agent_id=(select max(b.agent_id) from scm_product_produce_agent b where a.productp_id=b.productp_id ) where exists(select b.agent_id from scm_product_produce_agent b where a.productp_id=b.productp_id) and a.agent_id is null;