批量执行动态SQL语句

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

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

写了两个版本的, ORACLE版的只写了一个匿名块, MySQL版的是存储过程(因为它不支持匿名块!!!)
功能一样, 简单地将原始SQL(代码中变量v_oriSql)中的[N]替换成号码, [D]替换成日期, 然后循环执行. 号码和日期的范围由入参指定. <style> .CodeEntity .code_pieces ul.piece_anchor{width:25px;position:absolute;top:25px;left:-30px;z-index:1000;} .CodeEntity .code_pieces ul.piece_anchor li{width:25px;background: #efe;margin-bottom:2px;} .CodeEntity .code_pieces ul.piece_anchor li{border-left:3px #40AA63 solid;border-right:3px #efe solid;} .CodeEntity .code_pieces ul.piece_anchor li:hover{border-right:3px #40AA63 solid;border-left:3px #efe solid;} .CodeEntity .code_pieces ul.piece_anchor li a{color: #333;padding: 3px 10px;} .CodeEntity .code_pieces .jump_to_code{visibility:hidden;position:relative;} .CodeEntity .code_pieces .code_piece:hover .jump_to_code{visibility:visible;} .CodeEntity .code_pieces .code_piece:hover .jump_to_code a{text-decoration:none;} .CodeEntity .code_pieces h2 i{float:right;font-style:normal;font-weight:normal;} .CodeEntity .code_pieces h2 i a{font-size:9pt;background: #FFFFFF;color:#00A;padding: 2px 5px;text-decoration:none;} </style> <!—ecms -ecms

–> <!—ecms -ecms
• $velocityCount

–> <!—ecms -ecms

–>

-- exesql_batchdeclare	-- 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_dateNu<span>本文来源gaodai#ma#com搞*!代#%^码$网*</span>m 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 definedelimiter $$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_20131031CALL 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);

喜欢 (0)赏

[搞代码]

分享 (0)