Zitat von MYSQL_REFERENZ

Posted by erick ringot on March 31 2011 6:26am [Delete] [Edit]

RECURSION / RECURSIVE SELECT :

1) Let `task` be a MySql table containing at least 2 columns: id (primary key), pid (parent id - may be NULL) so that the rows form a classic tree structure.

2) Suppose you want to extract the tree relative to a particular actual id (constituted by itself and all its spawns) so that you need a recursive select which is unfortunately not implemented in MySql.

You can proceed by writing a recursive function as below.

CREATE PROCEDURE `TASKTREE`(tid INT UNSIGNED, flag BOOLEAN)
BEGIN
--
-- TASKTREE(id,flag)
-- recursive function, must be called with flag=FALSE
-- tid is the task (row) id
-- creation of a temporary table `tasktree`
-- containing the tree relative to tid
--
declare xid,xpid INT UNSIGNED;
declare tend BOOLEAN DEFAULT FALSE;
declare tcur CURSOR FOR SELECT id,pid FROM `task`
WHERE ((tid>0 AND pid=tid) OR (tid=0 AND pid IS NULL));
declare CONTINUE HANDLER FOR NOT FOUND SET tend=TRUE;
--
-- external call : flag MUST be FALSE
-- -> creation of the temporary table `tasktree`
--
IF (NOT flag) THEN
DROP TEMPORARY TABLE IF EXISTS `tasktree`;
CREATE TEMPORARY TABLE `tasktree` (
`id` int(10) unsigned NOT NULL,
`pid` int(10) unsigned,
PRIMARY KEY (`id`));
SELECT pid FROM `task` WHERE id=tid INTO xpid;
INSERT `tasktree` SET id=tid,pid=xpid;
END IF;
--
-- recursive (internal) call: flag MUST be TRUE
--
OPEN tcur;
tscan:LOOP
FETCH tcur INTO xid,xpid;
IF tend THEN LEAVE tscan; END IF;
INSERT `tasktree` SET id=xid,pid=xpid;
CALL TASKTREE(xid,TRUE);
END LOOP;
CLOSE tcur;
END

-----------------
Note: Don't omit to set the global variable max_sp_recursion_depth to an adequate positive value (for instance in the file 'my.ini').

Zitat von tramp

Funktioniert die Rekursion überhaupt in diesen Versionen?