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)//