业务批量数据的数据库操作

需求：在实际的业务处理过程中，有一些批处理数据的需求，不管是为了减轻数据库实时查询的压力还是为了业务数据归档留存。通常生成的批处理数据都是来源于各个业务表，之间通过某些字段关联。

--伪报表SQL举例
CREATE TABLE RPT(
    BAT_NO     VARCHAR(30),--批次号
    BUS_A_COL  VARCHAR(30),--业务表A的字段,假设此字段为关联字段
    BUS_B_COL  VARCHAR(30),--业务表B的字段
    BUS_C_COL  VARCHAR(30),--业务表C的字段
    ...
)

CREATE TABLE RPT_TMP(
    BAT_NO     VARCHAR(30),--批次号
    BUS_A_COL  VARCHAR(30),--业务表A的字段,假设此字段为关联字段
    BUS_B_COL  VARCHAR(30),--业务表B的字段
    BUS_C_COL  VARCHAR(30),--业务表C的字段
    ...
)

CREATE TABLE RPT_TMP2(
    BAT_NO     VARCHAR(30),--批次号
    BUS_A_COL  VARCHAR(30),--业务表A的字段,假设此字段为关联字段
    BUS_B_COL  VARCHAR(30),--业务表B的字段
    BUS_C_COL  VARCHAR(30),--业务表C的字段
    ...
)

分析：直接关联所有表一次插入，随着数据量的增大，未来的操作会变得不可控。要将步骤变得可持续，后续报表再增加其他业务信息也可以扩展。思路如下：

1. 先临时表操作，最后入正式表。
2. 临时表组装数据，分为若干步，每个业务可划分一或多步操作，即小步快跑。

Oracle数据库习惯使用游标方式Update操作：

--临时表操作，大体步骤如下
TRUNCATE TABLE RPT_TMP;

--插入主关联
INSERT INTO RPT_TMP(BAT_NO,BUS_A_COL) 
SELECT #{BAT_NO},BUS_A_COL 
  FROM BUS_A;

--更新业务字段BUS_B_COL
DECLARE
    Row_Num NUMBER := 0;
BEGIN
	FOR cr in(
		SELECT BUS_A_COL,BUS_B_COL
	      FROM BUS_B 
	) LOOP 
	    UPDATE RPT_TMP
	       SET BUS_B_COL = cr.BUS_B_COL
	     WHERE BUS_A_COL = cr.BUS_A_COL
	    Row_Num := Row_Num + 1;
        IF MOD(Row_Num, 10000) = 0 THEN
            COMMIT;
        END IF;
	END LOOP;
	COMMIT;
END;

--更新业务字段BUS_C_COL
DECLARE
    Row_Num NUMBER := 0;
BEGIN
	FOR cr in(
		SELECT BUS_A_COL,BUS_C_COL
	      FROM BUS_C 
	) LOOP 
	    UPDATE RPT_TMP
	       SET BUS_C_COL = cr.BUS_C_COL
	     WHERE BUS_A_COL = cr.BUS_A_COL
	    Row_Num := Row_Num + 1;
        IF MOD(Row_Num, 10000) = 0 THEN
            COMMIT;
        END IF;
	END LOOP;
	COMMIT;
END;
...

--拼装好的数据入正式表
INSERT INTO RPT(
    BAT_NO   ,
    BUS_A_COL,
    BUS_B_COL,
    BUS_C_COL,
    ...
)SELECT 
    BAT_NO   ,
    BUS_A_COL,
    BUS_B_COL,
    BUS_C_COL,
    ...
FROM RPT_TMP

Mysql数据库游标及联表更新对比效果不好，目前使用的方式为A/B表，即用查询插入代替Update操作。

--临时表操作，大体步骤如下
TRUNCATE TABLE RPT_TMP;

--插入主关联
INSERT INTO RPT_TMP(BAT_NO,BUS_A_COL) 
SELECT #{BAT_NO},BUS_A_COL 
FROM BUS_A;

--更新业务字段BUS_B_COL
TRUNCATE TABLE RPT_TMP2;
INSERT INTO RPT_TMP2(BAT_NO,BUS_A_COL,BUS_B_COL) 
SELECT tmp.BAT_NO,tmp.BUS_A_COL,bus.BUS_B_COL 
  FROM RPT_TMP tmp,BUS_A bus
 WHERE tmp.BUS_A_COL = bus.BUS_A_COL;

--更新业务字段BUS_C_COL
TRUNCATE TABLE RPT_TMP;
INSERT INTO RPT_TMP(BAT_NO,BUS_A_COL,BUS_B_COL,BUS_C_COL) 
SELECT tmp.BAT_NO,tmp.BUS_A_COL,tmp.BUS_B_COL,bus.BUS_C_COL 
  FROM RPT_TMP2 tmp,BUS_C bus
 WHERE tmp.BUS_A_COL = bus.BUS_A_COL;
...

--拼装好的数据入正式表
INSERT INTO RPT(
    BAT_NO   ,
    BUS_A_COL,
    BUS_B_COL,
    BUS_C_COL,
    ...
)SELECT 
    BAT_NO   ,
    BUS_A_COL,
    BUS_B_COL,
    BUS_C_COL,
    ...
FROM RPT_TMP