# 通宝数据迁移
***数据迁移步骤***
[TOC]
## 1，整体迁移数据
* 不负责此部分
## 2，修改部分表明，含金额部分乘以100
```
ALTER TABLE whitelist RENAME TO zz_whitelist;
```
## 3，添加缺失表与序列
* 添加通宝账户表
```
CREATE TABLE "CARD"."ZZ_COIN_ACCOUNT"
( "EHIDDEN" NUMBER(22,0) DEFAULT 0,
"ISSUE_ITEM" VARCHAR2(100 BYTE),
"TB_VERSION" VARCHAR2(1 BYTE),
"RECIPIENT_ITEM" VARCHAR2(50 BYTE),
"TRIGGER_PANTERID" VARCHAR2(50 BYTE),
"TRIGGER_RULES" VARCHAR2(100 BYTE),
"CHECKDATE" VARCHAR2(20 BYTE),
"CHECKID" VARCHAR2(20 BYTE),
"PANTERCHECK" VARCHAR2(2 BYTE),
"ENDDATE" VARCHAR2(20 BYTE),
"CARDPURCHASEID" VARCHAR2(20 BYTE),
"SOURCEORDER" VARCHAR2(60 BYTE),
"COINID" VARCHAR2(8 BYTE),
"PANTERID" VARCHAR2(8 BYTE),
"PLACEDTIME" VARCHAR2(8 BYTE),
"PLACEDDATE" VARCHAR2(8 BYTE),
"REMINDAMOUNT" NUMBER(20,0) DEFAULT 0,
"RECHARGEAMOUNT" NUMBER(20,0) DEFAULT 0,
"CUSTOMID" VARCHAR2(8 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "CARD" ;
COMMENT ON COLUMN "CARD"."ZZ_COIN_ACCOUNT"."EHIDDEN" IS '控制隐藏一家赠送明细+0显示1隐藏';
COMMENT ON COLUMN "CARD"."ZZ_COIN_ACCOUNT"."ISSUE_ITEM" IS '';
COMMENT ON COLUMN "CARD"."ZZ_COIN_ACCOUNT"."TB_VERSION" IS '通宝版本';
COMMENT ON COLUMN "CARD"."ZZ_COIN_ACCOUNT"."RECIPIENT_ITEM" IS '领取公司名称';
COMMENT ON COLUMN "CARD"."ZZ_COIN_ACCOUNT"."TRIGGER_PANTERID" IS '领取公司商户号';
COMMENT ON COLUMN "CARD"."ZZ_COIN_ACCOUNT"."TRIGGER_RULES" IS '赠送规则';
COMMENT ON COLUMN "CARD"."ZZ_COIN_ACCOUNT"."CHECKDATE" IS '审核时间';
COMMENT ON COLUMN "CARD"."ZZ_COIN_ACCOUNT"."CHECKID" IS '审核人编号';
COMMENT ON COLUMN "CARD"."ZZ_COIN_ACCOUNT"."PANTERCHECK" IS '审核状态：0未审核1已审核';
COMMENT ON COLUMN "CARD"."ZZ_COIN_ACCOUNT"."ENDDATE" IS '有效期';
COMMENT ON COLUMN "CARD"."ZZ_COIN_ACCOUNT"."CARDPURCHASEID" IS '充值编号';
COMMENT ON COLUMN "CARD"."ZZ_COIN_ACCOUNT"."SOURCEORDER" IS '赠送合同编号';
COMMENT ON COLUMN "CARD"."ZZ_COIN_ACCOUNT"."COINID" IS '通宝赠送编号';
COMMENT ON COLUMN "CARD"."ZZ_COIN_ACCOUNT"."PANTERID" IS '赠送方商户编号';
COMMENT ON COLUMN "CARD"."ZZ_COIN_ACCOUNT"."PLACEDTIME" IS '赠送时间';
COMMENT ON COLUMN "CARD"."ZZ_COIN_ACCOUNT"."PLACEDDATE" IS '赠送日期';
COMMENT ON COLUMN "CARD"."ZZ_COIN_ACCOUNT"."REMINDAMOUNT" IS '剩余金额';
COMMENT ON COLUMN "CARD"."ZZ_COIN_ACCOUNT"."RECHARGEAMOUNT" IS '赠送金额';
COMMENT ON COLUMN "CARD"."ZZ_COIN_ACCOUNT"."CUSTOMID" IS '会员编号';
REM INSERTING into CARD.ZZ_COIN_ACCOUNT
SET DEFINE OFF;
--------------------------------------------------------
-- DDL for Index ZZ_COIN_ACCOUNT_PK
--------------------------------------------------------
CREATE UNIQUE INDEX "CARD"."ZZ_COIN_ACCOUNT_PK" ON "CARD"."ZZ_COIN_ACCOUNT" ("COINID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "CARD" ;
--------------------------------------------------------
-- DDL for Index ZZ_COIN_ACCOUNT_CUSTOMID
--------------------------------------------------------
CREATE INDEX "CARD"."ZZ_COIN_ACCOUNT_CUSTOMID" ON "CARD"."ZZ_COIN_ACCOUNT" ("CUSTOMID" DESC)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "CARD" ;
--------------------------------------------------------
-- DDL for Index ZZ_COIN_ACCOUNT_PLACEDDATE
--------------------------------------------------------
CREATE INDEX "CARD"."ZZ_COIN_ACCOUNT_PLACEDDATE" ON "CARD"."ZZ_COIN_ACCOUNT" ("PLACEDDATE" DESC)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "CARD" ;
--------------------------------------------------------
-- DDL for Index ZZ_COIN_ACCOUNT_SU
--------------------------------------------------------
CREATE INDEX "CARD"."ZZ_COIN_ACCOUNT_SU" ON "CARD"."ZZ_COIN_ACCOUNT" ("SOURCEORDER" DESC)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "CARD" ;
--------------------------------------------------------
-- Constraints for Table ZZ_COIN_ACCOUNT
--------------------------------------------------------
ALTER TABLE "CARD"."ZZ_COIN_ACCOUNT" ADD CONSTRAINT "ZZ_COIN_ACCOUNT_PK" PRIMARY KEY ("COINID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "CARD" ENABLE;
ALTER TABLE "CARD"."ZZ_COIN_ACCOUNT" MODIFY ("RECHARGEAMOUNT" NOT NULL ENABLE);
ALTER TABLE "CARD"."ZZ_COIN_ACCOUNT" MODIFY ("REMINDAMOUNT" NOT NULL ENABLE);
ALTER TABLE "CARD"."ZZ_COIN_ACCOUNT" MODIFY ("COINID" NOT NULL ENABLE);
ALTER TABLE "CARD"."ZZ_COIN_ACCOUNT" MODIFY ("EHIDDEN" NOT NULL ENABLE);
--序列号
CREATE SEQUENCE "CARD"."SEQ_CARD_COINID" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 26 NOCACHE NOORDER NOCYCLE ;
```
* 添加通宝明细表
```
CREATE TABLE "CARD"."ZZ_COIN_CONSUME"
( "CHECKID" VARCHAR2(20 BYTE),
"CHECKDATE" VARCHAR2(20 BYTE),
"PANTERCHECK" VARCHAR2(1 BYTE),
"FLAG" VARCHAR2(2 BYTE),
"STATUS" VARCHAR2(20 BYTE),
"PANTERID" VARCHAR2(10 BYTE),
"PLACEDTIME" VARCHAR2(10 BYTE),
"PLACEDDATE" VARCHAR2(20 BYTE),
"AMOUNT" VARCHAR2(18 BYTE),
"COINID" VARCHAR2(10 BYTE),
"CUSTOMID" VARCHAR2(10 BYTE),
"TRADEID" VARCHAR2(60 BYTE),
"COINCONSUMEID" VARCHAR2(15 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "CARD" ;
COMMENT ON COLUMN "CARD"."ZZ_COIN_CONSUME"."CHECKID" IS '审核人';
COMMENT ON COLUMN "CARD"."ZZ_COIN_CONSUME"."CHECKDATE" IS '审核时间';
COMMENT ON COLUMN "CARD"."ZZ_COIN_CONSUME"."PANTERCHECK" IS '审核状态：0未审核1已审核';
COMMENT ON COLUMN "CARD"."ZZ_COIN_CONSUME"."FLAG" IS '兑换状态：1正常消费2退款';
COMMENT ON COLUMN "CARD"."ZZ_COIN_CONSUME"."STATUS" IS '结算状态0未结算1已结算';
COMMENT ON COLUMN "CARD"."ZZ_COIN_CONSUME"."PANTERID" IS '消费商户';
COMMENT ON COLUMN "CARD"."ZZ_COIN_CONSUME"."PLACEDTIME" IS '添加时间';
COMMENT ON COLUMN "CARD"."ZZ_COIN_CONSUME"."PLACEDDATE" IS '添加日期';
COMMENT ON COLUMN "CARD"."ZZ_COIN_CONSUME"."AMOUNT" IS '消费金额';
COMMENT ON COLUMN "CARD"."ZZ_COIN_CONSUME"."COINID" IS '通宝兑换账户编号';
COMMENT ON COLUMN "CARD"."ZZ_COIN_CONSUME"."CUSTOMID" IS '会员编号';
COMMENT ON COLUMN "CARD"."ZZ_COIN_CONSUME"."TRADEID" IS '消费明细表消费流水';
COMMENT ON COLUMN "CARD"."ZZ_COIN_CONSUME"."COINCONSUMEID" IS '通宝兑换';
REM INSERTING into CARD.ZZ_COIN_CONSUME
SET DEFINE OFF;
--------------------------------------------------------
-- DDL for Index ZZ_COIN_CONSUME_PK
--------------------------------------------------------
CREATE UNIQUE INDEX "CARD"."ZZ_COIN_CONSUME_PK" ON "CARD"."ZZ_COIN_CONSUME" ("COINCONSUMEID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "CARD" ;
--------------------------------------------------------
-- DDL for Index ZZ_COIN_CONSUME_CUSTOMID
--------------------------------------------------------
CREATE INDEX "CARD"."ZZ_COIN_CONSUME_CUSTOMID" ON "CARD"."ZZ_COIN_CONSUME" ("CUSTOMID" DESC)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "CARD" ;
--------------------------------------------------------
-- DDL for Index ZZ_COIN_CONSUME_TRADEID
--------------------------------------------------------
CREATE INDEX "CARD"."ZZ_COIN_CONSUME_TRADEID" ON "CARD"."ZZ_COIN_CONSUME" ("TRADEID" DESC)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "CARD" ;
--------------------------------------------------------
-- Constraints for Table ZZ_COIN_CONSUME
--------------------------------------------------------
ALTER TABLE "CARD"."ZZ_COIN_CONSUME" ADD CONSTRAINT "ZZ_COIN_CONSUME_PK" PRIMARY KEY ("COINCONSUMEID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "CARD" ENABLE;
ALTER TABLE "CARD"."ZZ_COIN_CONSUME" MODIFY ("COINCONSUMEID" NOT NULL ENABLE);
-- 序列号
CREATE SEQUENCE "CARD"."SEQ_CARD_COINCONSUMEID" MINVALUE 1 MAXVALUE 9999999999 INCREMENT BY 1 START WITH 1662227 CACHE 20 NOORDER NOCYCLE ;
```
* 添加通宝池表
```
CREATE TABLE "CARD"."ZZ_TB_POOL"
( "ID" VARCHAR2(50 BYTE),
"CELLIN_TIME" NUMBER(22,0) DEFAULT 0,
"TIME" NUMBER(22,0) DEFAULT 0,
"TB_STOCK" NUMBER(22,0) DEFAULT 0,
"ISSUE_ITEM" VARCHAR2(100 BYTE),
"ISSUE_COMPANY" VARCHAR2(100 BYTE),
"PANTERID" VARCHAR2(8 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "JYCARD" ;
COMMENT ON COLUMN "CARD"."ZZ_TB_POOL"."ID" IS '主键';
COMMENT ON COLUMN "CARD"."ZZ_TB_POOL"."CELLIN_TIME" IS '第一次入池时间';
COMMENT ON COLUMN "CARD"."ZZ_TB_POOL"."TIME" IS '更新时间';
COMMENT ON COLUMN "CARD"."ZZ_TB_POOL"."TB_STOCK" IS '库存';
COMMENT ON COLUMN "CARD"."ZZ_TB_POOL"."ISSUE_ITEM" IS '商户简称';
COMMENT ON COLUMN "CARD"."ZZ_TB_POOL"."ISSUE_COMPANY" IS '商户名称';
COMMENT ON COLUMN "CARD"."ZZ_TB_POOL"."PANTERID" IS '商户号';
REM INSERTING into CARD.ZZ_TB_POOL
SET DEFINE OFF;
--------------------------------------------------------
-- Constraints for Table ZZ_TB_POOL
--------------------------------------------------------
ALTER TABLE "CARD"."ZZ_TB_POOL" MODIFY ("CELLIN_TIME" NOT NULL ENABLE);
ALTER TABLE "CARD"."ZZ_TB_POOL" MODIFY ("TIME" NOT NULL ENABLE);
ALTER TABLE "CARD"."ZZ_TB_POOL" MODIFY ("TB_STOCK" NOT NULL ENABLE);
```
* 添加通宝结算表
```
CREATE TABLE "CARD"."ZZ_OUT_DALIY_JYCOIN"
( "SYNC" CHAR(1 BYTE),
"SYNCDATE" VARCHAR2(20 BYTE),
"PLACEDDATE" VARCHAR2(20 BYTE),
"POUNDAGE" NUMBER(20,0) DEFAULT 0,
"TRADEAMOUNT" NUMBER(20,0) DEFAULT 0,
"SETTLEAMOUNT" NUMBER(20,0) DEFAULT 0,
"PANTERID" CHAR(8 BYTE),
"TRADEQUANTITY" NUMBER(20,0)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "JYCARD" ;
COMMENT ON COLUMN "CARD"."ZZ_OUT_DALIY_JYCOIN"."SYNC" IS '是否同步';
COMMENT ON COLUMN "CARD"."ZZ_OUT_DALIY_JYCOIN"."SYNCDATE" IS '同步日期';
COMMENT ON COLUMN "CARD"."ZZ_OUT_DALIY_JYCOIN"."PLACEDDATE" IS '结算日期';
COMMENT ON COLUMN "CARD"."ZZ_OUT_DALIY_JYCOIN"."POUNDAGE" IS '手续费';
COMMENT ON COLUMN "CARD"."ZZ_OUT_DALIY_JYCOIN"."TRADEAMOUNT" IS '交易金额';
COMMENT ON COLUMN "CARD"."ZZ_OUT_DALIY_JYCOIN"."SETTLEAMOUNT" IS '结算金额';
COMMENT ON COLUMN "CARD"."ZZ_OUT_DALIY_JYCOIN"."PANTERID" IS '商户号';
COMMENT ON COLUMN "CARD"."ZZ_OUT_DALIY_JYCOIN"."TRADEQUANTITY" IS '交易笔数';
REM INSERTING into CARD.ZZ_OUT_DALIY_JYCOIN
SET DEFINE OFF;
ALTER TABLE "CARD"."ZZ_OUT_DALIY_JYCOIN" MODIFY ("POUNDAGE" NOT NULL ENABLE);
ALTER TABLE "CARD"."ZZ_OUT_DALIY_JYCOIN" MODIFY ("TRADEAMOUNT" NOT NULL ENABLE);
ALTER TABLE "CARD"."ZZ_OUT_DALIY_JYCOIN" MODIFY ("SETTLEAMOUNT" NOT NULL ENABLE);
ALTER TABLE "CARD"."ZZ_OUT_DALIY_JYCOIN" MODIFY ("TRADEQUANTITY" NOT NULL ENABLE);
```
* 添加通宝消费表
```
CREATE TABLE "CARD"."ZZ_COIN_PAY"
( "FLAG" NUMBER(22,0) DEFAULT 0,
"EORDERID" VARCHAR2(60 BYTE),
"AMOUNT" NUMBER(12,0) DEFAULT 0,
"PLACEDTIME" VARCHAR2(10 BYTE),
"PLACEDDATE" VARCHAR2(10 BYTE),
"TRADEID" VARCHAR2(60 BYTE),
"PANTERID" VARCHAR2(8 BYTE),
"TERMPOSNO" VARCHAR2(10 BYTE),
"ORDERID" VARCHAR2(60 BYTE),
"CUSTOMID" VARCHAR2(8 BYTE),
"REFUND_AMOUNT" NUMBER(12,0) DEFAULT 0,
"COINID" VARCHAR2(20 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "CARD" ;
COMMENT ON COLUMN "CARD"."ZZ_COIN_PAY"."FLAG" IS '是否结算1是0否';
COMMENT ON COLUMN "CARD"."ZZ_COIN_PAY"."EORDERID" IS '外部交易号';
COMMENT ON COLUMN "CARD"."ZZ_COIN_PAY"."AMOUNT" IS '交易金额';
COMMENT ON COLUMN "CARD"."ZZ_COIN_PAY"."PLACEDTIME" IS '交易时间';
COMMENT ON COLUMN "CARD"."ZZ_COIN_PAY"."PLACEDDATE" IS '交易日期';
COMMENT ON COLUMN "CARD"."ZZ_COIN_PAY"."TRADEID" IS '订单号';
COMMENT ON COLUMN "CARD"."ZZ_COIN_PAY"."PANTERID" IS '商户号';
COMMENT ON COLUMN "CARD"."ZZ_COIN_PAY"."TERMPOSNO" IS 'pos编号';
COMMENT ON COLUMN "CARD"."ZZ_COIN_PAY"."ORDERID" IS '内部交易号';
COMMENT ON COLUMN "CARD"."ZZ_COIN_PAY"."CUSTOMID" IS '会员编号';
COMMENT ON COLUMN "CARD"."ZZ_COIN_PAY"."REFUND_AMOUNT" IS '可退金额';
COMMENT ON COLUMN "CARD"."ZZ_COIN_PAY"."COINID" IS '通宝赠送编号';
REM INSERTING into CARD.ZZ_COIN_PAY
SET DEFINE OFF;
--------------------------------------------------------
-- DDL for Index ZZ_COIN_PAY_CUSTOMID
--------------------------------------------------------
CREATE INDEX "CARD"."ZZ_COIN_PAY_CUSTOMID" ON "CARD"."ZZ_COIN_PAY" ("CUSTOMID" DESC)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "CARD" ;
--------------------------------------------------------
-- DDL for Index ZZ_COIN_PAY_EORDERID
--------------------------------------------------------
CREATE INDEX "CARD"."ZZ_COIN_PAY_EORDERID" ON "CARD"."ZZ_COIN_PAY" ("EORDERID" DESC)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "CARD" ;
--------------------------------------------------------
-- DDL for Index ZZ_COIN_PAY_TRADEID
--------------------------------------------------------
CREATE UNIQUE INDEX "CARD"."ZZ_COIN_PAY_TRADEID" ON "CARD"."ZZ_COIN_PAY" ("TRADEID" DESC)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "CARD" ;
--------------------------------------------------------
-- Constraints for Table ZZ_COIN_PAY
--------------------------------------------------------
ALTER TABLE "CARD"."ZZ_COIN_PAY" MODIFY ("REFUND_AMOUNT" NOT NULL ENABLE);
ALTER TABLE "CARD"."ZZ_COIN_PAY" MODIFY ("AMOUNT" NOT NULL ENABLE);
ALTER TABLE "CARD"."ZZ_COIN_PAY" MODIFY ("FLAG" NOT NULL ENABLE);
```
* 添加通宝退款表
```
CREATE TABLE "CARD"."ZZ_COIN_REFUND"
( "PRETRADEID" VARCHAR2(60 BYTE),
"FLAG" NUMBER(22,0) DEFAULT 0,
"EORDERID" VARCHAR2(60 BYTE),
"AMOUNT" NUMBER(12,0) DEFAULT 0,
"PLACEDTIME" VARCHAR2(10 BYTE),
"PLACEDDATE" VARCHAR2(10 BYTE),
"TRADEID" VARCHAR2(60 BYTE),
"PANTERID" VARCHAR2(8 BYTE),
"TERMPOSNO" VARCHAR2(10 BYTE),
"ORDERID" VARCHAR2(60 BYTE),
"CUSTOMID" VARCHAR2(20 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "CARD" ;
COMMENT ON COLUMN "CARD"."ZZ_COIN_REFUND"."PRETRADEID" IS '原订单编号';
COMMENT ON COLUMN "CARD"."ZZ_COIN_REFUND"."FLAG" IS '是否结算1是0否';
COMMENT ON COLUMN "CARD"."ZZ_COIN_REFUND"."EORDERID" IS '外部交易号';
COMMENT ON COLUMN "CARD"."ZZ_COIN_REFUND"."AMOUNT" IS '交易金额';
COMMENT ON COLUMN "CARD"."ZZ_COIN_REFUND"."PLACEDTIME" IS '交易时间';
COMMENT ON COLUMN "CARD"."ZZ_COIN_REFUND"."PLACEDDATE" IS '交易日期';
COMMENT ON COLUMN "CARD"."ZZ_COIN_REFUND"."TRADEID" IS '订单号';
COMMENT ON COLUMN "CARD"."ZZ_COIN_REFUND"."PANTERID" IS '商户号';
COMMENT ON COLUMN "CARD"."ZZ_COIN_REFUND"."TERMPOSNO" IS 'pos编号';
COMMENT ON COLUMN "CARD"."ZZ_COIN_REFUND"."ORDERID" IS '内部交易号';
COMMENT ON COLUMN "CARD"."ZZ_COIN_REFUND"."CUSTOMID" IS '会员编号';
REM INSERTING into CARD.ZZ_COIN_REFUND
SET DEFINE OFF;
--------------------------------------------------------
-- DDL for Index ZZ_COIN_REFUND_TRADEID
--------------------------------------------------------
CREATE INDEX "CARD"."ZZ_COIN_REFUND_TRADEID" ON "CARD"."ZZ_COIN_REFUND" ("TRADEID" DESC)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "CARD" ;
--------------------------------------------------------
-- DDL for Index ZZ_COIN_REFUND_CUSTOMID
--------------------------------------------------------
CREATE INDEX "CARD"."ZZ_COIN_REFUND_CUSTOMID" ON "CARD"."ZZ_COIN_REFUND" ("CUSTOMID" DESC)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "CARD" ;
--------------------------------------------------------
-- DDL for Index ZZ_COIN_REFUND_EORDERID
--------------------------------------------------------
CREATE INDEX "CARD"."ZZ_COIN_REFUND_EORDERID" ON "CARD"."ZZ_COIN_REFUND" ("EORDERID" DESC)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "CARD" ;
--------------------------------------------------------
-- Constraints for Table ZZ_COIN_REFUND
--------------------------------------------------------
ALTER TABLE "CARD"."ZZ_COIN_REFUND" MODIFY ("AMOUNT" NOT NULL ENABLE);
ALTER TABLE "CARD"."ZZ_COIN_REFUND" MODIFY ("FLAG" NOT NULL ENABLE);
```
* 通宝池明细表
```
CREATE TABLE "TB_POOL_DETAILS"
( "ID" VARCHAR2(30 BYTE),
"ISSUE_COMPANY" VARCHAR2(100 BYTE),
"ISSUE_ITEM" VARCHAR2(100 BYTE),
"TB_NUMS" NUMBER(20,0),
"CONTRACT_NUM" VARCHAR2(100 BYTE),
"STATE" VARCHAR2(2 BYTE),
"CELLIN_TIME" NUMBER(20,0),
"TB_NUMBER" VARCHAR2(100 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "NEWJYCARD" ;
REM INSERTING into TB_POOL_DETAILS
SET DEFINE OFF;
ALTER TABLE "NEWJYCARD"."TB_POOL_DETAILS" MODIFY ("ID" NOT NULL ENABLE);
```
## 4，删除多余表
```
drop table coin_account;
drop table coin_consume;
drop table tb_pool;
drop table test;
drop table OUT_DALIY_JYCOIN
```
## 5，迁移缺失数据表
1，通宝发行记录,分页式导出，导出时注意过滤numrow字段
```
SELECT * FROM(SELECT tp.*,rownum as numrow FROM (SELECT (nvl(ca.RECHARGEAMOUNT,0)*100) RECHARGEAMOUNT,(nvl(ca.REMINDAMOUNT,0)*100) REMINDAMOUNT,ca.PLACEDDATE,ca.PLACEDTIME,ca.PANTERID,nvl(ca.COINID,0)COINID,ca.SOURCEORDER,ca.CARDPURCHASEID,ca.ENDDATE,ca.PANTERCHECK,ca.CHECKID,ca.CHECKDATE,ca.TRIGGER_RULES,ca.TRIGGER_PANTERID,ca.RECIPIENT_ITEM,nvl(ca.TB_VERSION,1)TB_VERSION,ca.ISSUE_ITEM,ca.EHIDDEN,cc.CUSTOMID FROM coin_account ca left join customs_c cc on cc.cid=ca.cardid where ca.placeddate<'20190807') tp) where numrow>0 and numrow <=380000;
SELECT * FROM(SELECT tp.*,rownum as numrow FROM (SELECT (nvl(ca.RECHARGEAMOUNT,0)*100) RECHARGEAMOUNT,(nvl(ca.REMINDAMOUNT,0)*100) REMINDAMOUNT,ca.PLACEDDATE,ca.PLACEDTIME,ca.PANTERID,nvl(ca.COINID,0)COINID,ca.SOURCEORDER,ca.CARDPURCHASEID,ca.ENDDATE,ca.PANTERCHECK,ca.CHECKID,ca.CHECKDATE,ca.TRIGGER_RULES,ca.TRIGGER_PANTERID,ca.RECIPIENT_ITEM,nvl(ca.TB_VERSION,1)TB_VERSION,ca.ISSUE_ITEM,ca.EHIDDEN,cc.CUSTOMID FROM coin_account ca left join customs_c cc on cc.cid=ca.cardid where ca.placeddate<'20190807') tp) where numrow>380000 and numrow <=760000;
SELECT * FROM(SELECT tp.*,rownum as numrow FROM (SELECT (nvl(ca.RECHARGEAMOUNT,0)*100) RECHARGEAMOUNT,(nvl(ca.REMINDAMOUNT,0)*100) REMINDAMOUNT,ca.PLACEDDATE,ca.PLACEDTIME,ca.PANTERID,nvl(ca.COINID,0)COINID,ca.SOURCEORDER,ca.CARDPURCHASEID,ca.ENDDATE,ca.PANTERCHECK,ca.CHECKID,ca.CHECKDATE,ca.TRIGGER_RULES,ca.TRIGGER_PANTERID,ca.RECIPIENT_ITEM,nvl(ca.TB_VERSION,1)TB_VERSION,ca.ISSUE_ITEM,ca.EHIDDEN,cc.CUSTOMID FROM coin_account ca left join customs_c cc on cc.cid=ca.cardid where ca.placeddate<'20190807') tp) where numrow>760000 and numrow <=1140000;
SELECT * FROM(SELECT tp.*,rownum as numrow FROM (SELECT (nvl(ca.RECHARGEAMOUNT,0)*100) RECHARGEAMOUNT,(nvl(ca.REMINDAMOUNT,0)*100) REMINDAMOUNT,ca.PLACEDDATE,ca.PLACEDTIME,ca.PANTERID,nvl(ca.COINID,0)COINID,ca.SOURCEORDER,ca.CARDPURCHASEID,ca.ENDDATE,ca.PANTERCHECK,ca.CHECKID,ca.CHECKDATE,ca.TRIGGER_RULES,ca.TRIGGER_PANTERID,ca.RECIPIENT_ITEM,nvl(ca.TB_VERSION,1)TB_VERSION,ca.ISSUE_ITEM,ca.EHIDDEN,cc.CUSTOMID FROM coin_account ca left join customs_c cc on cc.cid=ca.cardid where ca.placeddate<'20190807') tp) where numrow>1140000 and numrow <=1520000;
```
2，通宝消费明细，分页式导出，导出时注意过滤numrow字段
```
SELECT * FROM(SELECT tp.*,rownum as numrow FROM (SELECT ca.COINCONSUMEID,trim(ca.TRADEID) TRADEID,ca.COINID,ca.AMOUNT*100 AMOUNT,ca.PLACEDDATE,ca.PLACEDTIME,ca.PANTERID,ca.STATUS,ca.FLAG,ca.PANTERCHECK,ca.CHECKDATE,ca.CHECKID,cc.CUSTOMID FROM coin_consume ca left join customs_c cc on cc.cid=ca.cardid where ca.placeddate<'20190807' ) tp) where numrow>0 and numrow <=380000;
SELECT * FROM(SELECT tp.*,rownum as numrow FROM (SELECT ca.COINCONSUMEID,trim(ca.TRADEID) TRADEID,ca.COINID,ca.AMOUNT*100 AMOUNT,ca.PLACEDDATE,ca.PLACEDTIME,ca.PANTERID,ca.STATUS,ca.FLAG,ca.PANTERCHECK,ca.CHECKDATE,ca.CHECKID,cc.CUSTOMID FROM coin_consume ca left join customs_c cc on cc.cid=ca.cardid where ca.placeddate<'20190807') tp) where numrow>380000 and numrow <=760000;
SELECT * FROM(SELECT tp.*,rownum as numrow FROM (SELECT ca.COINCONSUMEID,trim(ca.TRADEID) TRADEID,ca.COINID,ca.AMOUNT*100 AMOUNT,ca.PLACEDDATE,ca.PLACEDTIME,ca.PANTERID,ca.STATUS,ca.FLAG,ca.PANTERCHECK,ca.CHECKDATE,ca.CHECKID,cc.CUSTOMID FROM coin_consume ca left join customs_c cc on cc.cid=ca.cardid where ca.placeddate<'20190807') tp) where numrow>760000 and numrow <=1140000;
SELECT * FROM(SELECT tp.*,rownum as numrow FROM (SELECT ca.COINCONSUMEID,trim(ca.TRADEID) TRADEID,ca.COINID,ca.AMOUNT*100 AMOUNT,ca.PLACEDDATE,ca.PLACEDTIME,ca.PANTERID,ca.STATUS,ca.FLAG,ca.PANTERCHECK,ca.CHECKDATE,ca.CHECKID,cc.CUSTOMID FROM coin_consume ca left join customs_c cc on cc.cid=ca.cardid where ca.placeddate<'20190807') tp) where numrow>1140000 and numrow <=1520000;
```
3，通宝消费订单主体，全部导出
```
SELECT tw.flag,tw.eorderid,(tw.tradepoint*100) amount,tw.placedtime,tw.placeddate,trim(tw.tradeid)tradeid,tw.panterid,tw.termposno,cc.customid,tw.tradepoint*100-nvl(xx.tradepoint,0)*100 refund_amount,ca.coinid,nvl(tw.exdate,'orderid')orderid FROM coin_consume ca left join customs_c cc on cc.cid=ca.cardid left join trade_wastebooks tw on tw.tradeid=ca.tradeid left join (select x.pretradeid,sum(x.tradepoint) tradepoint from trade_wastebooks x where x.pretradeid is not null and x.tradepoint>0 group by x.pretradeid ) xx on tw.tradeid=xx.pretradeid where ca.placeddate<'20190807' and ca.status=0 and tw.flag is not null order by tw.eorderid;
```
4, 通宝退款，全部导出
```
SELECT trim(tw.pretradeid) pretradeid,tw.flag,tw.eorderid,(tw.tradepoint*100) amount,tw.placedtime,tw.placeddate,trim(tw.tradeid)tradeid,tw.panterid,tw.termposno,cc.customid,nvl(tw.exdate,'orderid')orderid FROM coin_consume ca left join customs_c cc on cc.cid=ca.cardid left join trade_wastebooks tw on tw.tradeid=ca.tradeid where ca.placeddate<'20190807' and tw.flag is not null and ca.status=1 order by tw.pretradeid;
```
5，通宝池详情，全部导出
```
select ID,ISSUE_COMPANY,ISSUE_ITEM,TB_NUMS*100 TB_NUMS,CONTRACT_NUM,STATE, CELLIN_TIME ,TB_NUMBER from tb_pool_details;
```
6,命名规范
```
1，导出表名为ZZ_COIN_ACCOUNT ,文件命名为分别为1，2，3，4 统一放到ZZ_COIN_ACCOUNT文件夹中
2，导出表名为ZZ_COIN_CONSUME ,文件命名为分别为1，2，3，4 统一放到ZZ_COIN_CONSUME文件夹中
3，导出表名为ZZ_COIN_PAY ,文件命名为1 统一放到ZZ_COIN_PAY文件夹中
4，导出表名为ZZ_COIN_REFUND ,文件命名为1 统一放到ZZ_COIN_REFUND文件夹中
5，导出表名为ZZ_TB_POOL_DETAILS ,文件命名为1 统一放到ZZ_TB_POOL_DETAILS文件夹中
```
7，脚本
### 三，脚本运行
1，通宝发行
```
php think coins:table account --uid 1
php think coins:table account --uid 2
php think coins:table account --uid 3
php think coins:table account --uid 4
```
2，通宝消费明细
```
php think coins:table consume --uid 1
php think coins:table consume --uid 2
php think coins:table consume --uid 3
php think coins:table consume --uid 4
```
3，通宝消费
```
php think coins:table pay
```
4，通宝退款
```
php think coins:table refund
```
5，通宝池详情
```
php think coins:table pool
```
* 脚本平均耗时为1小时
* 每次执行插入操作条数为500条
* 完成之后会发送钉钉
## 6，创建物化视图
**物化视图 zz_view_coin_account**
```
select
zc.namechinese username,zc.linktel,
zca.CUSTOMID,zca.RECHARGEAMOUNT,zca.REMINDAMOUNT,zca.PANTERID,zca.CARDPURCHASEID,zca.SOURCEORDER,zca.TB_VERSION,zca.COINID,zca.TRIGGER_RULES,zca.ENDDATE,zca.PLACEDTIME,
p1.NAMECHINESE,
p1.NAMEENGLISH,
p1.PARENT,
nvl(p2.NAMECHINESE,p1.NAMECHINESE) PANTER1_NAMECHINESE,
nvl(p2.NAMEENGLISH,p1.NAMEENGLISH) PANTER1_NAMEENGLISH,
nvl(p2.PARENT,p1.PARENT) PANTER1_PARENT,
nvl(zca.TRIGGER_PANTERID,zca.panterid) TRIGGER_PANTERID
from zz_coin_account zca
left join zz_customs zc on zc.customid=zca.customid
left join panters p1 on p1.panterid=zca.panterid
left join panters p2 on p2.panterid=zca.TRIGGER_PANTERID;
```
**物化视图 zz_view_coin_pay**
```
select zcp.*,
zca.RECIPIENT_ITEM COINACCOUNT_RECIPIENT_ITEM,
zca.panterid COINACCOUNT_PANTERID,
zca.RECHARGEAMOUNT COINACCOUNT_RECHARGEAMOUNT,
zca.REMINDAMOUNT COINACCOUNT_REMINDAMOUNT,
zca.COINID COINACCOUNT_COINID,
zca.TB_VERSION COINVERSION,
za.coin ACCOUNT_COIN,
za.accountid ACCOUNT_ACCOUNTID,
za.customid ACCOUNT_CUSTOMID,
p1.panterid PANTER_PANTERID,
p1.NAMECHINESE PANTER_NAMECHINESE,
p1.NAMEENGLISH PANTER_NAMEENGLISH,
p2.panterid PANTER1_PANTERID,
p2.NAMECHINESE PANTER1_NAMECHINESE,
p2.NAMEENGLISH PANTER1_NAMEENGLISH
from zz_coin_pay zcp
left join zz_coin_account zca on zca.coinid=zcp.coinid
left join zz_account za on za.customid=zcp.customid
left join panters p1 on p1.panterid=zcp.panterid
left join panters p2 on p2.panterid=zca.panterid;
```
**物化视图 zz_view_coin_refund**
```
select zcp.*,
zca.RECIPIENT_ITEM COINACCOUNT_RECIPIENT_ITEM,
zca.panterid COINACCOUNT_PANTERID,
zca.RECHARGEAMOUNT COINACCOUNT_RECHARGEAMOUNT,
zca.REMINDAMOUNT COINACCOUNT_REMINDAMOUNT,
zca.COINID COINACCOUNT_COINID,
zca.TB_VERSION COINVERSION,
za.coin ACCOUNT_COIN,
za.accountid ACCOUNT_ACCOUNTID,
za.customid ACCOUNT_CUSTOMID,
p1.panterid PANTER_PANTERID,
p1.NAMECHINESE PANTER_NAMECHINESE,
p1.NAMEENGLISH PANTER_NAMEENGLISH,
p2.panterid PANTER1_PANTERID,
p2.NAMECHINESE PANTER1_NAMECHINESE,
p2.NAMEENGLISH PANTER1_NAMEENGLISH
from zz_coin_refund zcp
left join zz_coin_consume zcc on zcc.tradeid=zcp.tradeid
left join zz_coin_account zca on zca.coinid=zcc.coinid
left join zz_account za on za.customid=zcp.customid
left join panters p1 on p1.panterid=zcp.panterid
left join panters p2 on p2.panterid=zca.panterid;
```
