Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE PROCEDURE WITH_EMULATOR(
- recursive_table TEXT, # имя рекурсивной таблицы
- initial_SELECT TEXT, # источник рекурсии
- recursive_SELECT TEXT, # элемент рекурсии
- final_SELECT TEXT, # финальный SELECT, выполняющийся над объединенным результатом
- max_recursion int unsigned, # защита от бесконечного цикла, используйте 0 по умолчанию
- create_table_options TEXT # тут можно задать опции, которые будут использоваться в
- # операторе CREATE TABLE при создании рекурсивной таблицы с целью ускорения
- # запросов (initial_SELECT/recursive_SELECT/final_SELECT; например:
- # "(KEY(some_column)) ENGINE=MEMORY"
- )
- BEGIN
- declare new_rows int unsigned;
- declare show_progress int default 0; # установите 1 при отладке выполнения
- declare recursive_table_next varchar(120);
- declare recursive_table_union varchar(120);
- declare recursive_table_tmp varchar(120);
- set recursive_table_next = concat(recursive_table, "_next");
- set recursive_table_union = concat(recursive_table, "_union");
- set recursive_table_tmp = concat(recursive_table, "_tmp");
- # Удаление всех временных таблиц с именами, которые будут использоваться в этой процедуре
- SET @str =
- CONCAT("DROP TEMPORARY TABLE IF EXISTS ", recursive_table, ",",
- recursive_table_next, ",", recursive_table_union,
- ",", recursive_table_tmp);
- PREPARE stmt FROM @str;
- EXECUTE stmt;
- # Если нужно ссылаться на recursive_table более
- # одного раза в recursive_SELECT, удалите ключевое слово TEMPORARY .
- SET @str = # создание и заполнение T0
- CONCAT("CREATE TEMPORARY TABLE ", recursive_table, " ",
- create_table_options, " AS ", initial_SELECT);
- PREPARE stmt FROM @str;
- EXECUTE stmt;
- SET @str = # создание U
- CONCAT("CREATE TEMPORARY TABLE ", recursive_table_union, " LIKE ", recursive_table);
- PREPARE stmt FROM @str;
- EXECUTE stmt;
- SET @str = # создание T1
- CONCAT("CREATE TEMPORARY TABLE ", recursive_table_next, " LIKE ", recursive_table);
- PREPARE stmt FROM @str;
- EXECUTE stmt;
- if max_recursion = 0 then
- set max_recursion = 100; # по умолчанию для предотвращения бесконечного цикла
- end if;
- recursion: repeat
- # добавляем строки из T0 в U (всегда с помощью UNION ALL)
- SET @str =
- CONCAT("INSERT INTO ", recursive_table_union, " SELECT * FROM ", recursive_table);
- PREPARE stmt FROM @str;
- EXECUTE stmt;
- # цикл будет прерван как только счетчик max_recursion уменьшится до 0
- set max_recursion = max_recursion - 1;
- if not max_recursion then
- if show_progress then
- select concat("max recursion exceeded"); # вероятно select "max recursion exceeded";
- end if;
- leave recursion;
- end if;
- # выполняем recursive_SELECT на основе T0 и помещаем результат в Т1
- SET @str =
- CONCAT("INSERT INTO ", recursive_table_next, " ", recursive_SELECT);
- PREPARE stmt FROM @str;
- EXECUTE stmt;
- # прерываем цикл, если в T1 нет строк
- select row_count() into new_rows;
- if show_progress then
- select concat(new_rows, " new rows found");
- end if;
- if not new_rows then
- leave recursion;
- end if;
- # Подготовка к следующей итерации:
- # T1 становится T0, чтобы стать источником получения новых строк при выполнении recursive_SELECT,
- # T0 становится T1.
- SET @str =
- CONCAT("ALTER TABLE ", recursive_table, " RENAME ", recursive_table_tmp);
- PREPARE stmt FROM @str;
- EXECUTE stmt;
- # используется ALTER TABLE RENAME, так как RENAME TABLE не поддерживает временные таблицы
- SET @str =
- CONCAT("ALTER TABLE ", recursive_table_next, " RENAME ", recursive_table);
- PREPARE stmt FROM @str;
- EXECUTE stmt;
- SET @str =
- CONCAT("ALTER TABLE ", recursive_table_tmp, " RENAME ", recursive_table_next);
- PREPARE stmt FROM @str;
- EXECUTE stmt;
- # очищается T1
- SET @str =
- CONCAT("TRUNCATE TABLE ", recursive_table_next);
- PREPARE stmt FROM @str;
- EXECUTE stmt;
- until 0 end repeat;
- # удаляются T0 и T1
- SET @str =
- CONCAT("DROP TEMPORARY TABLE ", recursive_table_next, ", ", recursive_table);
- PREPARE stmt FROM @str;
- EXECUTE stmt;
- # Объединению промежуточных результатов присваивается имя recursive_table
- SET @str =
- CONCAT("ALTER TABLE ", recursive_table_union, " RENAME ", recursive_table);
- PREPARE stmt FROM @str;
- EXECUTE stmt;
- # Выполнение окончательного запроса
- SET @str = final_SELECT;
- PREPARE stmt FROM @str;
- EXECUTE stmt;
- # Удаление ненужных временных таблиц:
- SET @str =
- CONCAT("DROP TEMPORARY TABLE ", recursive_table);
- PREPARE stmt FROM @str;
- EXECUTE stmt;
- # Мы сделали это :-)
- END;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement