批量执行动态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
);