mysql--游标/递归/存储过程/临时表

    技术2022-05-11  10

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


    最新回复(0)