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