mysql 临时表递归_mysql–游标／递归／存储过程／临时表 | 学步园

delimiter $$

drop PROCEDURE if exists node_change_parent $$

create procedure node_change_parent(

in p_node_id int unsigned,

in p_node_parent int unsigned

)

sql security definer

begin

#改变父节点

declare m_node_level smallint unsigned default 0;

declare m_node_parent_xpath varchar(255) default '//';

declare m_have int default 0;

declare m_sub_node_id int unsigned default 0;

declare stop int default 0;

declare cur cursor for select node_id from nodes where node_parent=p_node_id;

#declare continue handler for 1329 set stop = 1;

#声明游标的异常处理，设置一个终止标记

declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop=1;

#判断父节点是否存在

select count(*) into m_have from nodes where node_id=p_node_parent;

if m_have>0 then

select node_level+1,concat(node_parent_xpath,trim(convert(p_node_parent,nchar(12))),'/') into m_node_level,m_node_parent_xpath

from nodes

where node_id=p_node_parent;

update nodes set node_level=m_node_level,node_parent=p_node_parent,node_parent_xpath=m_node_parent_xpath

where node_id=p_node_id;

#递归调用

#注意要设置递归最大层级

SET @@max_sp_recursion_depth = 20;

open cur;

fetch cur into m_sub_node_id;

while stop<>1 do

call node_change_parent(m_sub_node_id,p_node_id);

fetch cur into m_sub_node_id;

end while;

close cur;

end if;

end

$$

delimiter ;

创建临时表

create temporary table if not exists tmp_table(id bigint(20),fid bigint(20),lvl int)//