利用MySQL排序将树结构表数据封装成树结构对象(三)

来源：互联网发布：信用证是什么意思知乎编辑：程序博客网时间：2024/06/06 19:14

生成Tree通用的存储过程（Mysql）

CREATE DEFINER=`root`@`localhost` PROCEDURE `generateTreeNode`(IN rootId INT,IN tableName varchar(50),IN idField varchar(50),IN orderField varchar(50),IN parentIdField varchar(50),IN nameField varchar(50))BEGIN        DECLARE Level int;        drop TABLE IF EXISTS treeNodeList;        CREATE TABLE treeNodeList (                id int,                name varchar(50),                nLevel int,                sCort varchar(100)        );        Set Level=0;        SET @sqlcmd = CONCAT('INSERT into treeNodeList SELECT ',idField,', ',nameField,', ',Level,', CONCAT(',orderField ,'+10,"/",',idField,') FROM ',tableName,' WHERE ',parentIdField,' = ',rootId);        PREPARE stmt FROM @sqlcmd;        EXECUTE stmt;--      DEALLOCATE PREPARE stmt;        WHILE ROW_COUNT()>0 DO                SET Level=Level+1 ;                SET @sqlcmd2 = CONCAT('INSERT into treeNodeList SELECT A.',idField,',A.', nameField,', ',Level, ', concat(B.sCort, "/",',orderField ,'+10,"/", A.',idField,') FROM ',tableName,' A, treeNodeList B WHERE A.',parentIdField,'=B.ID AND B.nLevel=',Level-1);                PREPARE stmt2 FROM @sqlcmd2;                EXECUTE stmt2;                -- DEALLOCATE PREPARE stmt;        END WHILE;        DEALLOCATE PREPARE stmt;--      DEALLOCATE PREPARE stmt2;END

0 0