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 definerbegin #改变父节点 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)//
