142 lines
8.5 KiB
MySQL
142 lines
8.5 KiB
MySQL
|
--创建dblink
|
||
|
|
|
||
|
|
--测试库1.0
|
||
|
|
create database link link_test connect to hzs_cn identified by "123456"
|
||
|
|
using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=8.140.54.234)(PORT=1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';
|
||
|
|
|
||
|
|
--测试库
|
||
|
|
create database link link_cloud connect to hzs_cloud identified by "123456"
|
||
|
|
using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=39.107.153.159)(PORT=1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';
|
||
|
|
|
||
|
|
--生产库1.0
|
||
|
|
create database link link_provider connect to hzs_cn identified by "HzSqQyzW20221208"
|
||
|
|
using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=39.106.11.76)(PORT=1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';
|
||
|
|
|
||
|
|
insert into sys_user (USER_ID, USER_NAME, NICK_NAME, PHONENUMBER, PASSWORD, STATUS, DEL_FLAG, LOGIN_IP, LOGIN_DATE, CREATION_TIME, MODIFIED_TIME, PK_COUNTRY, PK_CREATOR, PK_MODIFIED, AUTHORITY, SMS, ROLE_IDS, COUNTRY_IDS, VERTEX_IDS, PK_DEPT, HEAD_PATH, TEAM_IDS)
|
||
|
|
values (100000000, 'admin', '超级管理员', '13507321456', '$2a$10$7JB720yubVSZvUI0rEqK/.VqGOZTH.ulu33dHOiBE8ByOhJIrdAu2', 0, 0, null, null, to_date('01-01-2023 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('08-06-2023 11:13:25', 'dd-mm-yyyy hh24:mi:ss'), 0, 1, 1, 0, 1, '1,21', '1,23,24,25,26,27,28,29,30,31,32', null, 1, 'https://hzs-in.oss-cn-beijing.aliyuncs.com/test-new/systemHead/036934ba-689f-4361-b09b-62282fe52d5c.png', null);
|
||
|
|
|
||
|
|
-- 国家
|
||
|
|
--select max(pk_id) from BD_COUNTRY
|
||
|
|
insert into BD_COUNTRY (pk_id,CODE,NAME,SHORT_NAME,CODE_THREE,NATIONAL_FLAG1,national_flag2,ENABLE_STATE,NUMBER_PLACES,pk_creator,LANGUAGES,EFFECTIVE_DATE)
|
||
|
|
select PK_ID,CODE,SHORT_NAME_OTHER NAME,short_name_other SHORT_NAME,CODE_THREE,national_flag NATIONAL_FLAG1,
|
||
|
|
national_flag national_flag2,0 ENABLE_STATE,2 NUMBER_PLACES,100000000 PK_CREATOR,language,
|
||
|
|
to_date('2021-01-01,00:00:00','yyyy-MM-dd HH24:mi:ss') EFFECTIVE_DATE
|
||
|
|
from bd_country@link_test order by pk_id;
|
||
|
|
|
||
|
|
--汇率
|
||
|
|
--select max(pk_id) from BD_CURRENCY
|
||
|
|
insert into BD_CURRENCY (pk_id,NAME,ENABLE_STATE,IN_EXCHANGE_RATE,OUT_EXCHANGE_RATE,SYMBOL,IS_CURRENCY,
|
||
|
|
PK_CREATOR,EFFECTIVE_DATE,pk_country,CURRENCY_CODE)
|
||
|
|
select br.pk_id,br.name_cn,0 enable_state,br.IN_EXCHANGE_RATE,br.OUT_EXCHANGE_RATE,SYMBOL,1 IS_CURRENCY,
|
||
|
|
100000000 PK_CREATOR,to_date('2021-01-01,00:00:00','yyyy-MM-dd HH24:mi:ss') EFFECTIVE_DATE,br.pk_country,bc.code from bd_currency@link_test br
|
||
|
|
inner join bd_country@link_test bc
|
||
|
|
on br.pk_country=bc.pk_id;
|
||
|
|
|
||
|
|
update BD_CURRENCY set IS_CURRENCY=0 where CURRENCY_CODE = 'US';
|
||
|
|
|
||
|
|
--地区
|
||
|
|
--select max(pk_id) from bd_area
|
||
|
|
insert into bd_area(pk_id,name,code,PARENT,ENABLE_STATE,PK_COUNTRY,PK_CREATOR)
|
||
|
|
select bi.pkid pk_id,bi.mc name,bi.bh code,nvl(bf.pkid,0) PARENT,0 ENABLE_STATE,bi.PK_COUNTRY,100000000 PK_CREATOR from bs_area_info@link_test bi
|
||
|
|
left join bs_area_info@link_test bf
|
||
|
|
on bi.parent_bh=bf.bh
|
||
|
|
|
||
|
|
--银行
|
||
|
|
--select max(pk_id) from bd_bank
|
||
|
|
insert into bd_bank(pk_id,BANK_NAME,SORT,PK_CREATOR,CREATION_TIME,pk_country)
|
||
|
|
select bd_bank_SEQ.Nextval pk_id,dict_label BANK_NAME,dict_sort SORT,100000000 PK_CREATOR,
|
||
|
|
CREATE_TIME CREATION_TIME,pk_country from sys_dict_data@link_test
|
||
|
|
where dict_type='bs_bank'
|
||
|
|
--select pkid pk_id,yhmc BANK_NAME,xh SORT,100000000 PK_CREATOR,
|
||
|
|
--CREATE_TIME CREATION_TIME,pk_country from bs_bank@link_test;
|
||
|
|
|
||
|
|
--账户
|
||
|
|
--select max(pk_id) from bd_account
|
||
|
|
insert into bd_account(PK_ID,ACCOUNT_NAME,SORT,FIELD_VALUE,ENABLE_STATE,EFFECTIVE_DATE,BACKGROUND_IMG,pk_country,pk_creator)
|
||
|
|
select bd_account_seq.nextval PK_ID,a.* from(
|
||
|
|
select account_name,sort,field_value,0 enable_state,to_date('01-01-2023 01:00:00', 'dd-mm-yyyy hh24:mi:ss') EFFECTIVE_DATE,
|
||
|
|
background_img,106 pk_country,100000000 pk_creator from bd_account@link_cloud
|
||
|
|
where pk_id in (104,105,106,107,241,242)
|
||
|
|
order by pk_id)a;
|
||
|
|
update bd_account set sort=field_value;
|
||
|
|
|
||
|
|
--等级
|
||
|
|
--select max(pk_id) from bd_grade
|
||
|
|
insert into bd_grade(pk_id,grade_name,grade_value,CENSUS_VALUE,START_VALUE,PK_AWARDS,REGISTER_AUTHORITY,EXPAND_CAPPING,
|
||
|
|
EXPAND_RATIO,REPURCHASE_CAPPING,REPURCHASE_RATIO,PK_COUNTRY,PK_CREATOR,IMAGE,color,ENABLE_STATE,ICON)
|
||
|
|
select bd_grade_SEQ.nextval pk_id,a.* from(
|
||
|
|
select grade_name,grade_value,CENSUS_VALUE,START_VALUE,PK_AWARDS,REGISTER_AUTHORITY,EXPAND_CAPPING,EXPAND_RATIO,
|
||
|
|
REPURCHASE_CAPPING,REPURCHASE_RATIO,1 PK_COUNTRY,100000000 PK_CREATOR,IMAGE,COLOR,0 ENABLE_STATE,ICON
|
||
|
|
from bd_grade@link_cloud
|
||
|
|
where pk_country =1 and pk_id not in(7,8))a
|
||
|
|
--奖衔
|
||
|
|
--select max(pk_id) from bd_awards
|
||
|
|
insert into bd_awards(pk_id,AWARDS_NAME,awards_value,PK_COUNTRY,IMAGE,COLOR,ENABLE_STATE,pk_creator)
|
||
|
|
select BD_AWARDS_SEQ.nextval pk_id,a.* from(
|
||
|
|
select awards_name,awards_value,106 PK_COUNTRY,(case when awards_value=0 then null else image end) IMAGE,
|
||
|
|
COLOR,0 ENABLE_STATE,100000000 pk_creator from bd_awards@link_cloud
|
||
|
|
where pk_country =1
|
||
|
|
order by pk_country,awards_value ) a
|
||
|
|
--产品
|
||
|
|
--select max(pk_id) from bd_product
|
||
|
|
insert into bd_product (PK_ID,PRODUCT_CODE,PRODUCT_NAME,PK_CLASSIFY,UNIT,WEIGHT,LENGTH,WIDTH,HEIGHT,POSTAGE_WEIGHT,
|
||
|
|
RETAIL_PRICE,ENABLE_STATE,COVER,SHELF_LIFE,IS_FRESH,PK_CREATOR,SHIPPING_CHANNEL,PK_COUNTRY)
|
||
|
|
select pkid PK_ID,cpbh PRODUCT_CODE,cpmc PRODUCT_NAME,1 PK_CLASSIFY,1 UNIT,1 WEIGHT,1 LENGTH,
|
||
|
|
1 WIDTH,1 HEIGHT,1 POSTAGE_WEIGHT,cpjg RETAIL_PRICE,0 ENABLE_STATE,nvl(cpfm,'0') COVER,0 SHELF_LIFE,
|
||
|
|
1 IS_FRESH,100000000 PK_CREATOR,nvl(shipping_Channel,1) SHIPPING_CHANNEL,pk_country from bs_product@link_test
|
||
|
|
where cpmc not like '%测试%' and del_flag='0';
|
||
|
|
|
||
|
|
--产品扩展表
|
||
|
|
--select max(pk_id) from BD_PRODUCT_EXTEND
|
||
|
|
insert into BD_PRODUCT_EXTEND (pk_id,PK_PRODUCT,pk_country,SORT,PK_CREATOR)
|
||
|
|
select pkid pk_id,pkid PK_PRODUCT,pk_country,(case when xh >=999999 then 999999 else xh end) SORT,100000000 PK_CREATOR from bs_product@link_test
|
||
|
|
where cpmc not like '%测试%' and del_flag='0';
|
||
|
|
|
||
|
|
--商品分类
|
||
|
|
--select max(pk_id) from BD_AREA_CLASSIFY
|
||
|
|
insert into BD_AREA_CLASSIFY(pk_id,CLASSIFY_NAME,PK_TRANSACTION_KEY,SORT,ENABLE_STATE,PK_PARENT,PK_COUNTRY,PK_CREATOR)
|
||
|
|
select id pk_id,name CLASSIFY_NAME,-1 PK_TRANSACTION_KEY,SORT,0 ENABLE_STATE,0 PK_PARENT,
|
||
|
|
PK_COUNTRY,100000000 PK_CREATOR from BS_GOODS_CATEGORY@link_test where id in(select GOODS_CATEGORY_ID from bs_goods@link_test);
|
||
|
|
|
||
|
|
--商品
|
||
|
|
--select max(pk_id) from bd_wares
|
||
|
|
insert into bd_wares(PK_ID,WARES_CODE,WARES_NAME,WARES_PRICE,WARES_ACHIEVE,PK_AREA_CLASSIFY,COVER1,DETAILS,sort,
|
||
|
|
special_Area,IS_SINGLE,IS_FREE_MAIL,PK_COUNTRY,PK_CREATOR)
|
||
|
|
select pkid PK_ID,spbh WARES_CODE,spmc WARES_NAME,spjg WARES_PRICE,sppv WARES_ACHIEVE,1 PK_AREA_CLASSIFY,
|
||
|
|
nvl(nvl(SPFM, SPTW),'1') COVER1,nvl(nvl(SPTW, SPFM),'1') DETAILS,(case when xh >=999999 then 999999 else xh end) sort,sszq special_Area,(case when splx=1 then 0 else 1 end) IS_SINGLE,
|
||
|
|
IS_MAIL_FREE IS_FREE_MAIL,PK_COUNTRY,100000000 PK_CREATOR
|
||
|
|
from bs_goods@link_test;
|
||
|
|
|
||
|
|
--商品扩展表
|
||
|
|
--select max(pk_id) from bd_wares_extend
|
||
|
|
insert into bd_wares_extend(PK_ID,PK_WARES,IS_PUT_ON,PRE_SALE_STATUS,PUT_ON_TIME,PUT_OFF_TIME,OPERATE_SCOPE,SALES,
|
||
|
|
IS_SALE,PK_COUNTRY,PK_CREATOR)
|
||
|
|
select pkid PK_ID,pkid PK_WARES,(case when spzt=1 then 0 else 1 end) IS_PUT_ON,1 PRE_SALE_STATUS,
|
||
|
|
auto_Start_Time PUT_ON_TIME,auto_End_Time PUT_OFF_TIME,0 OPERATE_SCOPE,0 SALES,1 IS_SALE,
|
||
|
|
PK_COUNTRY,100000000 PK_CREATOR
|
||
|
|
from bs_goods@link_test;
|
||
|
|
|
||
|
|
--商品明细表
|
||
|
|
--select max(pk_id) from bd_wares_detail
|
||
|
|
insert into bd_wares_detail(pk_id,PK_WARES,PK_PRODUCT,PRICE,achieve,QUANTITY,IS_GIFT,PK_COUNTRY,PK_CREATOR)
|
||
|
|
select bp.pkid pk_id,bg.pkid PK_WARES,bt.pkid PK_PRODUCT,bt.cpjg PRICE,bt.cppv achieve,
|
||
|
|
bp.num QUANTITY,bg.isactgift IS_GIFT,bg.PK_COUNTRY,100000000 PK_CREATOR from BS_GOODS@link_test bg
|
||
|
|
inner join BS_GOODS_PRODUCT@link_test bp
|
||
|
|
on bg.pkid=bp.goods_id
|
||
|
|
inner join bs_product@link_test bt
|
||
|
|
on bp.product_id=bt.pkid
|
||
|
|
|
||
|
|
|
||
|
|
--奖金项设置
|
||
|
|
insert into bd_bonus_items(pk_id,bonus_name,bonus_value,enable_state,SETTLE_TYPE,PUBLISH_TIME,PK_COUNTRY,PK_CREATOR)
|
||
|
|
select rownum pk_id,a.* from(
|
||
|
|
select bonus_name,bonus_value,enable_state,SETTLE_TYPE,PUBLISH_TIME,PK_COUNTRY,100000000 PK_CREATOR
|
||
|
|
from bd_bonus_items@link_cloud where pk_country=1 and del_flag=0
|
||
|
|
order by pk_id)a;
|
||
|
|
|
||
|
|
insert into bd_bonus_items(pk_id,bonus_name,bonus_value,enable_state,SETTLE_TYPE,PUBLISH_TIME,PK_COUNTRY,PK_CREATOR)
|
||
|
|
select bd_bonus_items_seq.nextval pk_id,a.* from(
|
||
|
|
select bonus_name,bonus_value,enable_state,SETTLE_TYPE,PUBLISH_TIME,106 PK_COUNTRY,100000000 PK_CREATOR
|
||
|
|
from bd_bonus_items where pk_country=1 and del_flag=0
|
||
|
|
and pk_id in(2,3,4,5,6,7,8,9,10,11,16,17,23)
|
||
|
|
order by pk_id)a;
|