- //Oracle版
- -- 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;
- //MySQL版
- -- 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
- );
- --该片段来自于http://www.codesnippet.cn/detail/111120137047.html
来源: http://www.codesnippet.cn/detail/111120137047.html