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

生成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