хранимая процедура mysql, которая вызывает себя рекурсивно
У меня есть следующая таблица:
id | parent_id | quantity
-------------------------
1 | null | 5
2 | null | 3
3 | 2 | 10
4 | 2 | 15
5 | 3 | 2
6 | 5 | 4
7 | 1 | 9
Теперь мне нужна хранимая процедура в mysql, которая рекурсивно вызывает себя и возвращает вычисленное количество.
Например, идентификатор 6 имеет 5 в качестве родителя, а 3 в качестве родителя, который имеет 2 в качестве родителя.
Поэтому мне нужно вычислить 4 * 2 * 10 * 3 (= 240).
Я довольно плохо знаком с хранимыми процедурами и не буду использовать их очень часто в будущем, потому что я предпочитаю иметь мою бизнес-логику в моем программном коде, а не в базе данных. Но в этом случае я не могу избежать этого.
Может быть, MySQL-гуру (это вы) может за пару секунд собрать рабочее заявление.
2 ответа
его работа только в mysql версии> = 5
объявление хранимой процедуры таково,
вы можете немного улучшить его, но это работает:
DELIMITER $$
CREATE PROCEDURE calctotal(
IN number INT,
OUT total INT
)
BEGIN
DECLARE parent_ID INT DEFAULT NULL ;
DECLARE tmptotal INT DEFAULT 0;
DECLARE tmptotal2 INT DEFAULT 0;
SELECT parentid FROM test WHERE id = number INTO parent_ID;
SELECT quantity FROM test WHERE id = number INTO tmptotal;
IF parent_ID IS NULL
THEN
SET total = tmptotal;
ELSE
CALL calctotal(parent_ID, tmptotal2);
SET total = tmptotal2 * tmptotal;
END IF;
END$$
DELIMITER ;
вызов похож на (важно установить эту переменную):
SET @@GLOBAL.max_sp_recursion_depth = 255;
SET @@session.max_sp_recursion_depth = 255;
CALL calctotal(6, @total);
SELECT @total;
Взгляните на Управление иерархическими данными в MySQL Майка Хиллера.
Он содержит полностью проработанные примеры работы с иерархическими данными.