Batch execution of dynamic SQL statements

When the data volume is large, you need to perform table sharding for the data table, such as number modulo and date table sharding: TABLE_0_20131001, TABLE_99_20131031 for the sake of portability, the database partition table feature is not allowed, you can only manually split the table. as a result, the number of sub-tables is huge, and the management and maintenance of sub-tables is a problem, such as changing the table structure and batch tabulation.

When the data volume is large, you need to perform table sharding for the data table, such as number modulo and date table sharding: TABLE_0_20131001, TABLE_99_20131031 for the sake of portability, the database partition table feature is not allowed, you can only manually split the table. as a result, the number of sub-tables is huge, and the management and maintenance of sub-tables is a problem, such as changing the table structure and batch tabulation.

When the data volume is large, you need to perform table sharding for the data table, such as number modulo and date table sharding: TABLE_0_20131001, TABLE_99_20131031
For the so-called "portability", the company does not allow the use of the database partition table feature, you can only manually split the table. as a result, the number of sub-tables is huge, and the management and maintenance of sub-tables is a problem, such as changing the table structure and batch tabulation operations.
Therefore, you have to write a script for emergency purposes.

Two versions are written. ORACLE only writes one anonymous block, and MySQL stores the Stored Procedure (because it does not support anonymous blocks !!!)
Function, replace [N] in the original SQL statement (Code variable v_oriSql) with a number, [D] with a date, and then execute it cyclically. the number and date range are specified by the input parameter. <无>

-- exesql_batchdeclare-- incomming paramv_oriSql VARCHAR2(1024):= 'create table TABLE_[N]_[D] as select * from TABLE where 1=2';-- original sqlv_beg  NUMBER := 0;  -- begin of numberv_end NUMBER := 9; -- end of number [beg, end]v_begDate DATE := to_date('20130701', 'YYYYMMDD');-- begin datev_endDate DATE := to_date('20130731', 'YYYYMMDD');-- end date, [beg, end]v_dateSw NUMBER := 1; -- date switch 1:day, others:month-- internel varv_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]';beginif INSTR(v_oriSql, V_DATE) <> 0 thenif v_dateSw = 1 thenv_dateNum := trunc(v_endDate, 'DD') - trunc(v_begDate, 'DD');elsev_dateNum := MONTHS_BETWEEN(trunc(v_endDate, 'MM'), trunc(v_begDate, 'MM'));end if;end if;if INSTR(v_oriSql, V_NUM) <> 0 thenv_numNum := v_end - v_beg;end if;-- loopfor i in 0 .. v_numNum loopfor j in 0 .. v_dateNum loopif v_dateSw = 1 thenv_strDate := to_char(v_begDate + j, 'YYYYMMDD');elsev_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 sqlIN v_beg INT,-- begin of numberIN v_end INT,-- end of number [beg, end]IN v_begDate DATE,-- begin dateIN v_endDate DATE,-- end date, [beg, end]IN v_dateSw INT-- date switch 1:day, others:month)BEGINDECLARE v_dateNum INT DEFAULT 0;DECLARE v_numNum INT DEFAULT 0;DECLARE v_strDate VARCHAR(8);DECLARE i INT;DECLARE j INT;DECLAREV_DATE VARCHAR(3) DEFAULT '[D]';DECLAREV_NUM VARCHAR(3) DEFAULT '[N]';if INSTR(v_oriSql, V_DATE) <> 0 thenif v_dateSw = 1 thenSET v_dateNum = DATEDIFF(v_endDate, v_begDate);elseSET 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 thenSET v_numNum = v_end - v_beg;end if;-- loopSET i=0;while i<=v_numNum doSET j=0;while j<=v_dateNum doif v_dateSw = 1 thenSET v_strDate = DATE_FORMAT(DATE_ADD(v_begDate, INTERVAL j DAY), '%Y%m%d');elseSET 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 sql0,-- begin of number9,-- end of number, [beg, end]str_to_date('20131001', '%Y%m%d'),-- begin datestr_to_date('20131031', '%Y%m%d'),-- end date, [beg, end]1-- date switch 1:day, others:month);