Cartesius — метод хранения и извлечения древовидных структур в реляционных базах данных или SQL деревья без червей и тараканов

CREATE  PROCEDURE `sp_move`(
                                                      IN id_move_menu INT,
                                                      IN id_menu INT,
                                                      IN direct INT
                                                     )
BEGIN

DECLARE menu_ord INT;
DECLARE menu_dep INT;
DECLARE move_ord INT;
DECLARE move_dep INT;
DECLARE count_id INT;
DECLARE max_ord INT;
DECLARE rezult_ord INT;
DECLARE rezult_dep INT;
DECLARE max_ord_n INT;
DECLARE point_to_plus_ord INT;


    SELECT COUNT(id) INTO count_id FROM tree
            WHERE ord >= (SELECT ord FROM tree WHERE id =id_move_menu)
            AND ord < (SELECT MIN(ord) FROM tree WHERE ord>(SELECT ord FROM tree WHERE id=id_move_menu) AND dep<=(SELECT dep FROM tree WHERE id=id_move_menu));

 		SELECT ord, dep INTO move_ord, move_dep FROM tree WHERE id = id_move_menu;

 		SELECT MIN(ord) INTO max_ord FROM tree WHERE ord>(SELECT ord FROM tree WHERE id=id_move_menu) AND dep<=(SELECT dep FROM tree WHERE id=id_move_menu);

 		UPDATE tree SET m_i = 1 WHERE ord >= move_ord AND ord < max_ord;

 		UPDATE tree SET ord = ord-count_id WHERE ord >= max_ord AND m_i = 0;

 		SELECT ord, dep INTO menu_ord, menu_dep FROM tree WHERE id=id_menu;

    IF (direct=5) THEN
 		    SET point_to_plus_ord=menu_ord;
    ELSEIF (direct=6) THEN
 		    SELECT MIN(ord) INTO max_ord FROM tree WHERE ord>(SELECT ord FROM tree WHERE id=id_menu) AND dep<=(SELECT dep FROM tree WHERE id=id_menu) AND m_i=0;
        SET point_to_plus_ord=max_ord;
     ELSEIF (direct=7) THEN
             SET point_to_plus_ord=menu_ord+1;
     END IF;

     UPDATE tree SET ord = ord+count_id WHERE ord >= point_to_plus_ord AND m_i = 0;

     IF (direct=5 || direct=6) THEN
 		 UPDATE tree SET ord=ord-move_ord+point_to_plus_ord, dep=dep-move_dep+menu_dep WHERE m_i = 1;
     ELSEIF (direct=7) THEN
         UPDATE tree SET ord=ord-move_ord+point_to_plus_ord, dep=dep-move_dep+menu_dep+1 WHERE m_i = 1;
    END IF;

    UPDATE tree SET m_i=0 WHERE m_i=1;

END$$