首先停止业务，保证数据不再变化

1. 新建汇总表：TAB_BASE_MONEYLIST_ACCOUNT

CREATE TABLE TOPUP.TAB_BASE_MONEYLIST_ACCOUNT
(
GROUP_ID       NUMBER(19)                     NOT NULL,
MONEY_ACCOUNT NUMBER
)
TABLESPACE ORDER_DATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          50M
            NEXT             50M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

CREATE UNIQUE INDEX TOPUP.PK_TAB_BASE_MONEYLIST_ACCOUNT ON TOPUP.TAB_BASE_MONEYLIST_ACCOUNT
(GROUP_ID)
LOGGING
TABLESPACE ORDER_INDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          50M
            NEXT             50M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

ALTER TABLE TOPUP.TAB_BASE_MONEYLIST_ACCOUNT ADD (
CONSTRAINT PK_TAB_BASE_MONEYLIST_ACCOUNT
PRIMARY KEY
(GROUP_ID)
    USING INDEX
    TABLESPACE ORDER_INDX
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          50M
                NEXT             50M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));
2.导入数据

INSERT INTO TOPUP.TAB_BASE_MONEYLIST_ACCOUNT SELECT GROUP_ID,SUM(MONEY) MONEY_ACCOUNT FROM TOPUP.TAB_BASE_MONEYLIST GROUP BY

GROUP_ID;
COMMIT;

3. 设置TAB_BASE_MONEYLIST.GROUP_ID 为非空属性

ALTER TABLE TOPUP.TAB_BASE_MONEYLIST MODIFY(GROUP_ID NOT NULL);

4. 新建触发器：

CREATE OR REPLACE TRIGGER TOPUP.TRG_TAB_BASE_MONEYLIST
AFTER DELETE OR INSERT OR UPDATE
ON TOPUP.TAB_BASE_MONEYLIST
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
VAR_IS_EXIST NUMBER;
BEGIN
   CASE
   WHEN INSERTING THEN
   SELECT COUNT(GROUP_ID) INTO VAR_IS_EXIST FROM TOPUP.TAB_BASE_MONEYLIST_ACCOUNT WHERE GROUP_ID=:NEW.GROUP_ID;
   IF VAR_IS_EXIST > 0 THEN
   UPDATE TOPUP.TAB_BASE_MONEYLIST_ACCOUNT SET MONEY_ACCOUNT=MONEY_ACCOUNT+:NEW.MONEY WHERE GROUP_ID=:NEW.GROUP_ID;
   ELSE
   INSERT INTO TOPUP.TAB_BASE_MONEYLIST_ACCOUNT VALUES(:NEW.GROUP_ID,:NEW.MONEY);
   END IF;
   WHEN UPDATING THEN
   IF :NEW.MONEY>:OLD.MONEY THEN
   UPDATE TOPUP.TAB_BASE_MONEYLIST_ACCOUNT SET MONEY_ACCOUNT=MONEY_ACCOUNT+(:NEW.MONEY-:OLD.MONEY) WHERE GROUP_ID=:OLD.GROUP_ID;
   ELSE
   UPDATE TOPUP.TAB_BASE_MONEYLIST_ACCOUNT SET MONEY_ACCOUNT=MONEY_ACCOUNT-(:OLD.MONEY-:NEW.MONEY) WHERE GROUP_ID=:OLD.GROUP_ID;
   END IF;
   WHEN DELETING THEN
   UPDATE TOPUP.TAB_BASE_MONEYLIST_ACCOUNT SET MONEY_ACCOUNT=MONEY_ACCOUNT-:OLD.MONEY WHERE GROUP_ID=:OLD.GROUP_ID;
   END CASE;
   EXCEPTION
     WHEN OTHERS THEN
       dbms_output.put_line('PLEASE OPERATION AGAIN,THANKS!');
       RAISE;
END TRG_TAB_BASE_MONEYLIST;
/

最后启动业务，监控数据同步情况(先观察一两天，确认没有问题则改查询)

ITPub博客

统计商户账户上有多少余额的优化--方案实施

最新文章