3R4FLTi9

1USE test;
2
3DROP PROCEDURE IF EXISTS for_each_table;
4DELIMITER $$
5CREATE PROCEDURE for_each_table
6(
7  IN in_sql_prefix VARCHAR(32),
8  IN in_table_schema VARCHAR(64),
9  IN in_table_partten VARCHAR(128),
10  IN in_sql_suffix VARCHAR(4096)
11)
12BEGIN
13  DECLARE fetch_end INT DEFAULT FALSE;
14  DECLARE sql_error INT DEFAULT FALSE;
15  DECLARE ok_count INT DEFAULT 0;
16  DECLARE error_count INT DEFAULT 0;
17  DECLARE dest_tablename VARCHAR(128);
18  DECLARE ok_list TEXT DEFAULT '';
19  DECLARE error_list TEXT DEFAULT '';
20  DECLARE str_report TEXT DEFAULT '';
21
22  DECLARE cur_get_table CURSOR FOR
23    SELECT CONCAT(t.table_schema, '.', t.table_name) FROM information_schema.tables AS t WHERE t.table_schema = in_table_schema AND t.table_name LIKE in_table_partten;
24  
25  DECLARE CONTINUE HANDLER FOR SQLWARNING
26    BEGIN
27      -- do nothing
28    END;
29  DECLARE CONTINUE HANDLER FOR NOT FOUND
30    BEGIN
31      SET fetch_end = TRUE;
32    END;
33  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
34    BEGIN
35      SET sql_error = TRUE;
36    END;
37
38  OPEN cur_get_table;
39  read_loop: LOOP
40    FETCH cur_get_table INTO dest_tablename;
41    IF fetch_end THEN
42      LEAVE read_loop;
43    END IF;
44    SET @strSql = concat(in_sql_prefix, ' ', dest_tablename, ' ', in_sql_suffix);
45    PREPARE s1 FROM @strSql;
46    EXECUTE s1;
47    DEALLOCATE PREPARE s1;
48    IF sql_error THEN
49      SET error_count = error_count + 1;
50      SET error_list = concat(error_list, dest_tablename, '\n');
51      SET sql_error = FALSE;
52      ITERATE read_loop;
53    END IF;
54    SET ok_count = ok_count + 1;
55    SET ok_list = concat(ok_list, dest_tablename, '\n');
56  END LOOP;
57  CLOSE cur_get_table;
58  /* report result */
59  IF error_count > 0 THEN
60    SET str_report = CONCAT('/* SQL: ', in_sql_prefix, ' ', in_table_schema, '.', in_table_partten, ' ', in_sql_suffix, '; */\n');
61    SET str_report = CONCAT(str_report, '/* total:', ok_count + error_count, ' (ok:', ok_count, ' error:', error_count, ') */\n');
62    SET str_report = CONCAT(str_report, '/* ok tables */\n', ok_list, '/* error tables */\n', error_list, '/* the end */\n');
63    SELECT str_report;
64  END IF;
65END$$
66DELIMITER ;
67
68-- test
69-- CALL for_each_table('drop table if exists', 'test', 'test\_for\_each\_table%', '');
70-- create table test.test_for_each_table (id bigint, v varchar(32));
71-- create table test.test_for_each_table_1 like test.test_for_each_table;
72-- create table test.test_for_each_table_2 like test.test_for_each_table;
73-- create table test.test_for_each_table_3 like test.test_for_each_table;
74-- select * from information_schema.columns where table_schema = 'test' and table_name like 'test\_for\_each\_table%';
75-- CALL for_each_table('alter table', 'test', 'test\_for\_each\_table%', 'add column (v1 varchar(32), v2 varchar(32))');
76-- select * from information_schema.columns where table_schema = 'test' and table_name like 'test\_for\_each\_table%';
77-- CALL for_each_table('drop table if exists', 'test', 'test\_for\_each\_table%', '');