MySQL树形结构查询

DROP PROCEDURE IF EXISTS query_node_deep;
CREATE PROCEDURE query_node_deep(IN rootId int(11))
BEGIN
    CREATE TEMPORARY TABLE IF NOT EXISTS tmptable(
        sno INT PRIMARY KEY auto_increment,
        nid INT(11),
        deep int(11)
    );
    DELETE FROM tmptable;

    CALL create_node_deep(rootId, 0, 1);
    select tmptable.*,api_node.* from tmptable,api_node where tmptable.nid=api_node.id order by tmptable.sno;

END;

DROP PROCEDURE IF EXISTS create_node_deep;
CREATE PROCEDURE create_node_deep(IN rootId int(11),IN nDepth INT, IN max_n INT)
    BEGIN
            DECLARE done INT DEFAULT 0;
            DECLARE b VARCHAR(20);
            DECLARE cur1 CURSOR FOR SELECT id FROM api_node where parent_id=rootId;
            DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
        IF (max_n > 0) THEN
            insert into tmptable values (null,rootId,nDepth);
            SET @@max_sp_recursion_depth = 7;
            OPEN cur1;

            FETCH cur1 INTO b;
            WHILE done=0 DO
                CALL create_node_deep(b,nDepth+1, max_n-1);
                FETCH cur1 INTO b;
            END WHILE;
            CLOSE cur1;
        END IF;
    END;


call query_node_deep(17);