PL/SQL recalculate tree

gistfile1.sql

# SET @@session.max_sp_recursion_depth = 255;
# CALL recalculateTree(NULL, 0, 0, @index);

CREATE PROCEDURE recalculateTree(IN parent INT, IN i INT, IN depth INT, OUT ri INT)

BEGIN

DECLARE done INT DEFAULT FALSE;
DECLARE n_id INT;
DECLARE n_parent_id INT;
DECLARE n_lft INT;
DECLARE n_rgt INT;
DECLARE tmp_i INT;
DECLARE cur CURSOR FOR
    SELECT id, parent_id, lft, rgt
    FROM category
    WHERE
        COALESCE(parent_id, -1) = COALESCE(parent, -1)
;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;

read_loop: LOOP
    FETCH cur INTO n_id, n_parent_id, n_lft, n_rgt;
  IF done THEN
        LEAVE read_loop;
  END IF;

  IF n_parent_id IS NULL THEN
        SET depth = 0;
    END if;

    SET i = i + 1;
  SET tmp_i = i;
    CALL recalculateTree(n_id, i, depth + 1, @ii);

    SET i = @ii + 1;
    UPDATE category set lft = tmp_i, rgt = i, lvl = depth where id = n_id;

  END LOOP;

CLOSE cur;
SET ri = i;

END;