|
Тема
|
MYSQL5 - stored procedure - traversal trees
|
|
Автор | FEX (Нерегистриран) |
Публикувано | 05.10.06 16:26 |
|
Здравейте,
може ли някой да ни помогне в следната ситуация за обходжане и билдване на траверсно дърво със рекурсивно извикване на съхранена процедура (функция)
Имаме таблица с категории:
=======================================================
CREATE TABLE `objects` (
`ID` int(11) unsigned NOT NULL auto_increment,
`PARENT_ID` int(11) default NULL,
`Name` varchar(63) NOT NULL,
`Num` int(11) NOT NULL default '0',
`lft` int(11) unsigned default NULL,
`rgt` int(11) unsigned default NULL,
`lvl` int(11) NOT NULL default '0',
PRIMARY KEY (`ID`),
KEY `IDX_objects_by_PARENT_ID` (`PARENT_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO objects(ID, PARENT_ID, Name, Num) VALUES(1, NULL,'Main',1), (2, NULL, 'News',2), (3,2,'Events',1), (4,2,'Other',2), (5,3,'Future',1), (6,3,'Past',2);
CREATE FUNCTION `SP_rebuild_category_tree`(_parent INTEGER(11), _left INTEGER(11), _level INTEGER(11))
RETURNS int(11)
DETERMINISTIC
SQL SECURITY INVOKER
COMMENT 'Building traversal tree'
BEGIN
DECLARE _right INT;
DECLARE _id INT;
DECLARE done INT DEFAULT 0;
DECLARE c CURSOR FOR
SELECT ID
FROM objects
WHERE
CASE
WHEN _parent IS NULL THEN PARENT_ID IS NULL
ELSE PARENT_ID = _parent
END
ORDER BY num;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := 1;
SET _right := _left + 1;
OPEN c;
REPEAT
FETCH c INTO _id;
IF done = 0 THEN
SET _right := SP_rebuild_category_tree(_id, _right, _level+1);
END IF;
UNTIL done = 1 END REPEAT;
CLOSE c;
IF (_parent IS NOT NULL) THEN
UPDATE objects SET lft=_left, rgt=_right, lvl=_level WHERE ID=_parent;
END IF;
RETURN _right + 1;
END;
=======================================================
Проблемът е, че при извикване на функцията:
SELECT SP_rebuild_category_tree(NULL,0,0);
не влиза в рекурсия, не намерихме начин за репортване на статус съобщения, аналогично като в PostGreSQL RAISE NOTICE....,
Any ideas?
| |
Тема
|
RESHENIE ;)
[re: FEX]
|
|
Автор | FEX (Нерегистриран) |
Публикувано | 05.10.06 16:54 |
|
Намерихме решение: :))
Пренаписахме съхранената функция на съхранена процедура:
==========================================================
CREATE PROCEDURE `SP_rebuild_object_tree`(IN _parent INTEGER(11), IN _left INTEGER(11), IN _level INTEGER(11), OUT _right INTEGER(11))
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE _id INT;
DECLARE done INT DEFAULT 0;
DECLARE c CURSOR FOR
SELECT ID FROM objects
WHERE
CASE
WHEN _parent IS NULL THEN PARENT_ID IS NULL
ELSE PARENT_ID = _parent
END
ORDER BY num;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := 1;
SET _right := _left + 1;
OPEN c;
REPEAT
FETCH c INTO _id;
IF done = 0 THEN
CALL SP_rebuild_object_tree(_id, _right, _level+1, _right);
END IF;
UNTIL done = 1 END REPEAT;
CLOSE c;
IF (_parent IS NOT NULL) THEN
UPDATE objects SET lft=_left, rgt=_right, lvl=_level WHERE ID=_parent;
END IF;
SET _right := _right + 1;
END;
==========================================================
SET max_sp_recursion_depth = 256;
SELECT @r:= 0;
CALL SP_rebuild_object_tree(NULL, 0, 0, @r);
SELECT ID, CONCAT(REPEAT(' ',lvl),name ) as n FROM objects ORDER BY lft
| |
|
|
|
|