Захотелось вот сделать рекурсивную процедуру, которая возвращала бы все узлы дерева, хранящегося в таблице вида tree_container(node_id, parent_id). Первым на ум приходит процедура getNodes(family_name varchar(255), node_id int default null), на входе в процедуру family_name для определения корневого узла из служебной таблицы и node_id для рекурсии, которая выглядит примерно так:
BEGIN
declare root_id int;
declare t_node int;
declare done int default 0;
declare cur cursor for
select tc.node_id from tree_container tc
declare cur cursor for
select tc.node_id from tree_container tc
where tc.parent_id = node_id;
declare continue handler for not found set done = 1;
set @@session.max_sp_recursion_depth = 255;
create temporary table if not exists tmp_nodes(node_id int);
if (node_id is null) then
select tf.root_node into root_id from tree_families tf where tf.family_name = family_name;
insert into tmp_nodes(node_id) values (root_id);
call getNodes(family_name, root_id);
else
open cur;
nodes: loop
fetch cur into t_node;
if done = 1 then
leave nodes;
end if;
insert into tmp_nodes(node_id) values (t_node);
call getNodes(family_name, t_node);
end loop nodes;
close cur;
end if;
select * from tmp_nodes;
drop table tmp_nodes;
END
Вот ведь хорошо же всё, ан нет! Оказывается, при входе в рекурсию новой именной облатси для курсоров не создаётся, а open cur в рекурсивном вызове закрывает курсор породившей процедуры, поэтому такая процедура возвращает только по одному «левому» (который первым в выборке окажется) элементу на каждом уровне (если самая «левая» ветка проходит через все уровни, конечно же) вложенности дерева.
В итоге пришлось реализовывать процедуру getNodes(family_name varchar(255), in_cnt int) весьма неэлегантным методом с помощью двух временных таблиц, отказавшись от курсоров в пользу подсчёта количества элементов в одной из этих таблиц:
BEGIN
declare root_id int;
declare t_node int;
declare cnt int;
set @@session.max_sp_recursion_depth = 255;
create temporary table if not exists tmp_nodes(node_id int, primary key(node_id));
create temporary table if not exists tmp_nodes_sub(node_id int, primary key(node_id));
if (in_cnt = 0) then
select tf.root_node into root_id from tree_families tf where tf.family_name = family_name;
replace into tmp_nodes(node_id) values (root_id);
call getNodes(family_name, 1);
else
replace into tmp_nodes_sub(node_id) select tc.node_id from tree_container tc where tc.parent_id in (select node_id from tmp_nodes);
replace into tmp_nodes(node_id) select node_id from tmp_nodes_sub;
select count(*) into cnt from tmp_nodes;
if (cnt <> in_cnt) then
call getNodes(family_name, cnt);
end if;
end if;
drop table tmp_nodes_sub;
select * from tmp_nodes
drop table tmp_nodes;
END