Как выполнить множество SELECT в одной хранимой процедуре?

Question

[Александр Коленчиц]

Пишу примерно сделующее, но понимаю что выполняется все равно всего один раз.
inCategoryId — входная переменная процедуры

BEGIN
	DECLARE cpi  INT;
	SET cpi = inCategoryId;
    

    WHILE cpi > 0 DO
    	SELECT category_id
        FROM category 
        WHERE category_id = cpi;
        
        SET cpi = cpi - 1;
    END WHILE;
END

В целом если упростить, то надо следующее — имеется входная цифра(номер категории), она может быть потомком какой либо категории, которая может тоже быть чьим либо потомком и так до бесконечности.

Хочу что бы из всех этих категорий выбиралось допустим по паре товаров — при этом уровень вложенности неизвестен, может быть и корневая.

Answer 1

[tigrenok]

nested set для этих целей очень удобны. и без вложеных процедур — все выбирается одним простым запросом.

Comments

[Александр Коленчиц]

Спасибо огромное, это и впрямь именно то что нужно

Answer 2

[balloon]

Вот пример функции для нахождения id корневой категории

CREATE DEFINER=`root`@`localhost` FUNCTION `FIND_CATEGORY_ROOT_ID`(category INT) RETURNS int(11)
    DETERMINISTIC
BEGIN
	DECLARE root INT;
	WHILE category IS NOT NULL DO
		SET root = category;
		SELECT parent_id INTO category FROM tree WHERE id = category;
	END WHILE;
	RETURN root;
END

Comments

[Александр Коленчиц]

ну так же мы найдем самую родительскую, а можно ли все родительские если древовидная структура с длинной веткой.
Средствами совместно с пхп это сделать легко, но тут потребовалось на стороне базы сделать…

[balloon]

Можно и средствами базы, но с моей точки зрения, гораздо проще денормализовать таблицу категорий и сохранять туда еще и полный путь. А функция вот она:

CREATE FUNCTION `FIND_CATEGORY_PATH`(category INT) RETURNS VARCHAR(200)
    DETERMINISTIC
BEGIN
    DECLARE path VARCHAR(200);
	WHILE category IS NOT NULL DO
        SET path = IF(path IS NULL, category, CONCAT(path, ",", category));
		SELECT parent_id INTO category FROM tree WHERE id = category;
	END WHILE;
	RETURN path;
END
// usage example: find all parent categories
SELECT * FROM tree WHERE FIND_IN_SET(id, FIND_CATEGORY_PATH(3))