fFhYjnEY

1USE test;
2
3DROP PROCEDURE IF EXISTS exe_sql_batch;
4delimiter $$
5CREATE PROCEDURE exe_sql_batch(
6  IN ori_sql VARCHAR(4096),
7  IN num_from INT,
8  IN num_to INT,  -- number [from, to]
9  IN date_from DATE,
10  IN date_to DATE,  -- date [from, to]
11  IN date_type VARCHAR(8)  -- 'day', 'month', 'no'
12)
13BEGIN
14  DECLARE DATE_MARK VARCHAR(3) DEFAULT '[D]';
15  DECLARE NUMBER_MARK VARCHAR(3) DEFAULT '[N]';
16  DECLARE date_count INT DEFAULT 0;
17  DECLARE num_count INT DEFAULT 0;
18  DECLARE str_date VARCHAR(8);
19  DECLARE i INT DEFAULT 0;
20  DECLARE j INT DEFAULT 0;
21  DECLARE sql_error INT DEFAULT FALSE;
22  DECLARE ok_count INT DEFAULT 0;
23  DECLARE error_count INT DEFAULT 0;
24  DECLARE error_list TEXT DEFAULT '';
25  DECLARE str_report TEXT DEFAULT '';
26  
27  DECLARE CONTINUE HANDLER FOR SQLWARNING
28    BEGIN
29      -- do nothing
30    END;
31  DECLARE CONTINUE HANDLER FOR NOT FOUND
32    BEGIN
33      SET sql_error = TRUE;
34    END;
35  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
36    BEGIN
37      SET sql_error = TRUE;
38    END;
39  
40  IF INSTR(ori_sql, DATE_MARK) <> 0 THEN
41    IF LOWER(date_type) = 'day' then
42      SET date_count = DATEDIFF(date_to, date_from);
43    ELSE
44      SET date_count = (YEAR(date_to) - YEAR(date_from))*12 + (MONTH(date_to) - MONTH(date_from));
45    END IF;
46  END IF;
47  
48  IF INSTR(ori_sql, NUMBER_MARK) <> 0 then
49    SET num_count = num_to - num_from;
50  END IF;
51  
52  -- loop
53  SET i = 0;
54  WHILE i <= num_count DO
55    SET j = 0;
56    WHILE j <= date_count DO
57      IF LOWER(date_type) = 'day' then
58        SET str_date = DATE_FORMAT(DATE_ADD(date_from, INTERVAL j DAY), '%Y%m%d');
59      ELSE
60        SET str_date = DATE_FORMAT(DATE_ADD(date_from, INTERVAL j MONTH), '%Y%m');
61      END IF;
62      SET @dest_sql = REPLACE(ori_sql, NUMBER_MARK, num_from + i);
63      SET @dest_sql = REPLACE(@dest_sql, DATE_MARK, str_date);
64      PREPARE s1 FROM @dest_sql;
65      EXECUTE s1;
66      DEALLOCATE PREPARE s1;
67      IF sql_error THEN
68        SET error_count = error_count + 1;
69        SET error_list = concat(error_list, @dest_sql, '\n');
70        SET sql_error = FALSE;
71      ELSE
72        SET ok_count = ok_count + 1;
73      END IF;
74      SET j = j + 1;
75    END WHILE; 
76    SET i = i + 1;
77  END WHILE;
78  
79  IF error_count > 0 THEN
80    SET str_report = concat('/* SQL: ', ori_sql, '; */\n');
81    SET str_report = concat(str_report, '/* (', NUMBER_MARK, ':[', num_from, ', ', num_to, '] ', DATE_MARK, ':[', date_from, ', ', date_to, ']) */\n');
82    SET str_report = concat(str_report, '/* total:', ok_count + error_count, ' (ok:', ok_count, ' error:', error_count, ') */\n');
83    SET str_report = concat(str_report, '/* error sqls */\n', error_list, '/* the end */\n');
84    SELECT str_report;
85  END IF;
86END$$
87delimiter ;
88
89-- test
90-- DROP TABLE IF EXISTS test.test_exe_sql_batch;
91-- CREATE TABLE test.test_exe_sql_batch (id bigint, value varchar(32));
92-- CALL exe_sql_batch('create table test.test_exe_sql_batch_[N]_[D] like test.test_exe_sql_batch', 0, 3, '2016-12-30', '2017-01-01', 'day');
93-- SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema = 'test' AND table_name LIKE 'test\_exe\_sql\_batch%';
94-- CALL exe_sql_batch('drop table if exists test.test_exe_sql_batch_[N]_[D]', 0, 3, '2016-12-30', '2017-01-01', 'day');
95-- SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema = 'test' AND table_name LIKE 'test\_exe\_sql\_batch%';
96-- DROP TABLE IF EXISTS test.test_exe_sql_batch;