Heim > Datenbank > MySQL-Tutorial > 批量执行动态SQL语句

批量执行动态SQL语句

WBOY
Freigeben: 2016-06-07 14:57:08
Original
1631 Leute haben es durchsucht

数据量很大时,需要对数据表做分表处理,比如按号码取模,日期等分表: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;
Nach dem Login kopieren
-- 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
);
Nach dem Login kopieren
Verwandte Etiketten:
Quelle:php.cn
Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Beliebte Tutorials
Mehr>
Neueste Downloads
Mehr>
Web-Effekte
Quellcode der Website
Website-Materialien
Frontend-Vorlage