MySQL recursive sorting query + Tree node generation

Mysql recursive sorting Query

Note: producttype sorting table, producttype. Ptype parent node, producttype. Id Primary Key, showTreeNodes (IN rootid INT) function. The parameter is of the starting type.

Drop procedure if exists showTreeNodes;

Create procedure showTreeNodes (IN rootid INT)
BEGIN
DECLARE Level int;
Drop table if exists tmpLst;
Create table tmpLst (
Id int,
NLevel int,
SCort varchar (8000)
);
Set Level = 0;
INSERT into tmpLst SELECT id, Level, id from producttype WHERE ptype = rootid;
WHILE ROW_COUNT ()> 0 DO
SET Level = Level + 1;
INSERT into tmpLst
Select a. ID, Level, concat (B. sCort, A. ID) FROM producttype A, tmpLst B
Where a. ptype = B. id and B. nLevel = Level-1;
End while;
END;
CALL showTreeNodes (-1 );

SELECT concat (SPACE (B. nLevel * 2), 'signature', A. name)
FROM producttype A, tmpLst B
Where a. ID = B. ID
Order by B. sCort;