批量执行动态SQL语句

数据量很大时,需要对数据表做分表处理,比如按号码取模,日期等分表:TABLE_0_20131001,TABLE_99_20131031 公司为了所谓的可移植性不让使用数据库的分区表特性,就只能自己手工分表了.这样一来分表数量庞大,分表的管理维护是个问题,如变动表结构,批量建表之类的操

数据量很大时,需要对数据表做分表处理, 比如按号码取模,日期等分表: TABLE_0_20131001, TABLE_99_20131031
公司为了所谓的"可移植性"不让使用数据库的分区表特性, 就只能自己手工分表了. 这样一来分表数量庞大,分表的管理维护是个问题, 如变动表结构,批量建表之类的操作就会显得很麻烦.
为此,只好自己写个脚本以备不时之需.

写了两个版本的, ORACLE版的只写了一个匿名块, MySQL版的是存储过程(因为它不支持匿名块!!!)
功能一样, 简单地将原始SQL(代码中变量v_oriSql)中的[N]替换成号码, [D]替换成日期, 然后循环执行. 号码和日期的范围由入参指定.

-- exesql_batch
declare
    -- incomming param
    v_oriSql VARCHAR2(1024):= 'create table TABLE_[N]_[D] as select * from TABLE where 1=2';    -- original sql
    v_beg  NUMBER := 0;  -- begin of number
    v_end NUMBER := 9; -- end of number [beg, end]
    v_begDate DATE := to_date('20130701', 'YYYYMMDD');  -- begin date
    v_endDate DATE := to_date('20130731', 'YYYYMMDD');  -- end date, [beg, end]
    v_dateSw NUMBER := 1; -- date switch 1:day, others:month
    -- internel var
    v_dateNum NUMBER := 0;
    v_numNum NUMBER := 0;
    v_strDate VARCHAR2(8);
    v_destSql VARCHAR2(2000);
    V_DATE VARCHAR2(3) := '[D]';
    V_NUM VARCHAR2(3) := '[N]';
begin
    if INSTR(v_oriSql, V_DATE) <> 0 then
        if v_dateSw = 1 then
            v_dateNum := trunc(v_endDate, 'DD') - trunc(v_begDate, 'DD');
        else
            v_dateNum := MONTHS_BETWEEN(trunc(v_endDate, 'MM'), trunc(v_begDate, 'MM'));
        end if;
    end if;

    if INSTR(v_oriSql, V_NUM) <> 0 then
        v_numNum := v_end - v_beg;
    end if;

    -- loop
    for i in 0 .. v_numNum loop
        for j in 0 .. v_dateNum loop
            if v_dateSw = 1 then
                v_strDate := to_char(v_begDate + j, 'YYYYMMDD');
            else
                v_strDate := to_char(ADD_MONTHS(v_begDate, j), 'YYYYMM');
            end if;
            v_destSql := REPLACE(v_oriSql, V_NUM, v_beg + i);
            v_destSql := REPLACE(v_destSql, V_DATE, v_strDate);
            EXECUTE IMMEDIATE v_destSql;
        end loop;
    end loop;
end;

-- exesql_batch
-- 1.procedure define
delimiter $$
DROP PROCEDURE IF EXISTS exesql_batch$$
CREATE PROCEDURE exesql_batch(
    IN v_oriSql VARCHAR(1024),  -- original sql
    IN v_beg INT,   -- begin of number
    IN v_end INT,   -- end of number [beg, end]
    IN v_begDate DATE,  -- begin date
    IN v_endDate DATE,  -- end date, [beg, end]
    IN v_dateSw INT -- date switch 1:day, others:month
)

BEGIN
    DECLARE v_dateNum INT DEFAULT 0;
    DECLARE v_numNum INT DEFAULT 0;
    DECLARE v_strDate VARCHAR(8);
    DECLARE i INT;
    DECLARE j INT;
    DECLARE V_DATE VARCHAR(3) DEFAULT '[D]';
    DECLARE V_NUM VARCHAR(3) DEFAULT '[N]';

    if INSTR(v_oriSql, V_DATE) <> 0 then
        if v_dateSw = 1 then
            SET v_dateNum = DATEDIFF(v_endDate, v_begDate);
        else
            SET v_dateNum = (YEAR(v_endDate)-YEAR(v_begDate))*12 + (MONTH(v_endDate)-MONTH(v_begDate));
        end if;
    end if;

    if INSTR(v_oriSql, V_NUM) <> 0 then
        SET v_numNum = v_end - v_beg;
    end if;

    -- loop
    SET i=0;
    while i<=v_numNum do
        SET j=0;
        while j<=v_dateNum do
            if v_dateSw = 1 then
                SET v_strDate = DATE_FORMAT(DATE_ADD(v_begDate, INTERVAL j DAY), '%Y%m%d');
            else
                SET v_strDate = DATE_FORMAT(DATE_ADD(v_begDate, INTERVAL j MONTH), '%Y%m');
            end if;

            SET @v_destSql = REPLACE(v_oriSql, V_NUM, v_beg+i);
            SET @v_destSql = REPLACE(@v_destSql, V_DATE, v_strDate);
            PREPARE s1 FROM @v_destSql;
            EXECUTE s1;
            DEALLOCATE PREPARE s1;
            SET j=j+1;
        end while;
        SET i=i+1;
    end while;
END$$
delimiter ;

-- 2.demo
-- crate tables from TABLE_0_20131001 to TABLE_9_20131031
CALL exesql_batch(
    'create table TABLE_[N]_[D] like TABLE',    -- original sql
    0,  -- begin of number
    9,  -- end of number, [beg, end]
    str_to_date('20131001', '%Y%m%d'),  -- begin date
    str_to_date('20131031', '%Y%m%d'),  -- end date, [beg, end]
    1   -- date switch 1:day, others:month
);