通寶遷移流程注意 · 系統遷移步驟

# 通寶數據遷移 ***數據遷移步驟*** [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; ```