Mysql procedure to get depth first ordered data from adjacent hierarchical tree

adjacent_fetch.sql

BEGIN
  DECLARE id INT DEFAULT NULL;
  DECLARE pid INT DEFAULT NULL;
  DECLARE idx INT DEFAULT NULL;
    
  CREATE TEMPORARY TABLE `temp_table` (
    `ord` int NOT NULL AUTO_INCREMENT,
    `id` int NOT NULL,
    `pid` int,
    `idx` int NOT NULL,
    `stage` int DEFAULT 0,
    PRIMARY KEY (`ord`),
    KEY (`id`)
  ) ENGINE=MEMORY;
  
  INSERT INTO temp_table (id, pid, idx)
  SELECT t.id, t.pid, t.idx
  FROM sample_tree t
  WHERE t.pid IS NULL
  ORDER BY t.idx
  LIMIT 1;
    
  daloop: WHILE TRUE DO
    SELECT t.id, t.pid, t.idx INTO id, pid, idx
    FROM (
      SELECT tmp.ord,
      tmp.id,
      tmp.pid,
      tmp.idx
      FROM temp_table tmp
      WHERE stage=0
      ORDER BY ord DESC
      LIMIT 1) tmp
    JOIN sample_tree t
    ON t.pid=tmp.id
    LEFT JOIN sample_tree t2
    ON t2.pid=tmp.id
    AND t2.idx<t.idx
    WHERE t2.id IS NULL
    ORDER BY tmp.ord DESC,
    t.idx
    LIMIT 1;
    
    UPDATE temp_table tmp
    SET tmp.stage=1
    WHERE tmp.id=pid;
    
    IF (id IS NOT NULL) THEN
        INSERT INTO temp_table (id, pid, idx)
        SELECT id, pid, idx;
    ELSE
      SELECT t.id, t.pid, t.idx INTO id, pid, idx
      FROM (
      SELECT tmp.ord,
      tmp.id,
      tmp.pid,
      tmp.idx
      FROM temp_table tmp
      WHERE stage<2
      ORDER BY ord DESC) tmp
      JOIN sample_tree t
      ON t.pid=tmp.pid
      AND t.idx>tmp.idx
      ORDER BY tmp.ord DESC,
      t.idx
      LIMIT 1;
      
      UPDATE temp_table tmp
      SET tmp.stage=2
      WHERE tmp.pid=pid;

      IF (id IS NOT NULL) THEN
        INSERT INTO temp_table (id, pid, idx)
        SELECT id, pid, idx;
      ELSE
        LEAVE daloop;
      END IF;
    END IF;
    
    SET id := NULL;
    
  END WHILE daloop;
  
  SELECT t.*
  FROM sample_tree t
  JOIN temp_table tmp
  ON tmp.id=t.id
  ORDER BY tmp.ord;
  
END

sample_tree.sql

DROP TABLE IF EXISTS `sample_tree`;
CREATE TABLE `sample_tree` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `pid` int DEFAULT NULL,
  `idx` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `sample_tree` (`id`, `name`, `pid`, `idx`) VALUES
(1,	'ELECTRONICS',	NULL,	0),
(2,	'TELEVISIONS',	1,	0),
(3,	'TUBE',	2,	0),
(4,	'LCD',	2,	1),
(5,	'PLASMA',	2,	2),
(6,	'PORTABLE ELECTRONICS',	1,	1),
(7,	'MP3 PLAYERS',	6,	0),
(8,	'FLASH',	7,	0),
(9,	'CD PLAYERS',	6,	1),
(10,	'2 WAY RADIOS',	6,	2);