wiWPySnL

1delimiter |
2
3CREATE PROCEDURE WITH_EMULATOR(
4recursive_table varchar(100), # имя рекурсивной таблицы
5initial_SELECT varchar(65530), # источник рекурсии
6recursive_SELECT varchar(65530), # элемент рекурсии
7final_SELECT varchar(65530), # финальный SELECT, выполняющийся над объединенным результатом
8max_recursion int unsigned, # защита от бесконечного цикла, используйте 0 по умолчанию
9create_table_options varchar(65530) # тут можно задать опции, которые будут использоваться в
10# операторе CREATE TABLE при создании рекурсивной таблицы с целью ускорения
11# запросов (initial_SELECT/recursive_SELECT/final_SELECT; например:
12# "(KEY(some_column)) ENGINE=MEMORY"
13)
14
15BEGIN
16  declare new_rows int unsigned;
17  declare show_progress int default 0; # установите 1 при отладке выполнения
18  declare recursive_table_next varchar(120);
19  declare recursive_table_union varchar(120);
20  declare recursive_table_tmp varchar(120);
21  set recursive_table_next  = concat(recursive_table, "_next");
22  set recursive_table_union = concat(recursive_table, "_union");
23  set recursive_table_tmp   = concat(recursive_table, "_tmp");
24
25  # Удаление всех временных таблиц с именами, которые будут использоваться в этой процедуре
26  SET @str =
27    CONCAT("DROP TEMPORARY TABLE IF EXISTS ", recursive_table, ",",
28    recursive_table_next, ",", recursive_table_union,
29    ",", recursive_table_tmp);
30  PREPARE stmt FROM @str;
31  EXECUTE stmt;
32
33 # Если нужно ссылаться на recursive_table более
34  # одного раза в recursive_SELECT, удалите ключевое слово TEMPORARY .
35  SET @str = # создание и заполнение T0
36    CONCAT("CREATE TEMPORARY TABLE ", recursive_table, " ",
37    create_table_options, " AS ", initial_SELECT);
38  PREPARE stmt FROM @str;
39  EXECUTE stmt;
40  SET @str = # создание U
41    CONCAT("CREATE TEMPORARY TABLE ", recursive_table_union, " LIKE ", recursive_table);
42  PREPARE stmt FROM @str;
43  EXECUTE stmt;
44  SET @str = # создание T1
45    CONCAT("CREATE TEMPORARY TABLE ", recursive_table_next, " LIKE ", recursive_table);
46  PREPARE stmt FROM @str;
47  EXECUTE stmt;
48  if max_recursion = 0 then
49    set max_recursion = 100; # по умолчанию для предотвращения бесконечного цикла
50  end if;
51  recursion: repeat
52    # добавляем строки из T0 в U (всегда с помощью UNION ALL)
53    SET @str =
54      CONCAT("INSERT INTO ", recursive_table_union, " SELECT * FROM ", recursive_table);
55    PREPARE stmt FROM @str;
56    EXECUTE stmt;
57    # цикл будет прерван как только счетчик max_recursion уменьшится до 0
58    set max_recursion = max_recursion - 1;
59    if not max_recursion then
60      if show_progress then
61        select concat("max recursion exceeded"); # вероятно select "max recursion exceeded";
62      end if;
63      leave recursion;
64    end if;
65    # выполняем recursive_SELECT на основе T0 и помещаем результат в Т1
66    SET @str =
67      CONCAT("INSERT INTO ", recursive_table_next, " ", recursive_SELECT);
68    PREPARE stmt FROM @str;
69    EXECUTE stmt;
70    # прерываем цикл, если в T1 нет строк
71    select row_count() into new_rows;
72    if show_progress then
73      select concat(new_rows, " new rows found");
74    end if;
75    if not new_rows then
76      leave recursion;
77    end if;
78    # Подготовка к следующей итерации:
79    # T1 становится T0, чтобы стать источником получения новых строк при выполнении recursive_SELECT,
80    # T0 становится T1.
81    SET @str =
82      CONCAT("ALTER TABLE ", recursive_table, " RENAME ", recursive_table_tmp);
83    PREPARE stmt FROM @str;
84    EXECUTE stmt;
85    # используется ALTER TABLE RENAME, так как RENAME TABLE не поддерживает временные таблицы
86    SET @str =
87      CONCAT("ALTER TABLE ", recursive_table_next, " RENAME ", recursive_table);
88    PREPARE stmt FROM @str;
89    EXECUTE stmt;
90    SET @str =
91      CONCAT("ALTER TABLE ", recursive_table_tmp, " RENAME ", recursive_table_next);
92    PREPARE stmt FROM @str;
93    EXECUTE stmt;
94    # очищается T1
95    SET @str =
96      CONCAT("TRUNCATE TABLE ", recursive_table_next);
97    PREPARE stmt FROM @str;
98    EXECUTE stmt;
99  until 0 end repeat;
100  # удаляются T0 и T1
101  SET @str =
102    CONCAT("DROP TEMPORARY TABLE ", recursive_table_next, ", ", recursive_table);
103  PREPARE stmt FROM @str;
104  EXECUTE stmt;
105  # Объединению промежуточных результатов присваивается имя recursive_table
106  SET @str =
107    CONCAT("ALTER TABLE ", recursive_table_union, " RENAME ", recursive_table);
108  PREPARE stmt FROM @str;
109  EXECUTE stmt;
110  # Выполнение окончательного запроса
111  SET @str = final_SELECT;
112  PREPARE stmt FROM @str;
113  EXECUTE stmt;
114  # Удаление ненужных временных таблиц:
115  SET @str =
116    CONCAT("DROP TEMPORARY TABLE ", recursive_table);
117  PREPARE stmt FROM @str;
118  EXECUTE stmt;
119  # Мы сделали это :-)
120END|
121
122delimiter ;