[MySQL] Stored Function für Zusammenstellung - wie?

[Panthrax]

Hallo.

Ich möchte mir mit einer Stored Function alle Blätter eines Knotens in einem Baum heraussuchen lassen. Ich habe vorher noch keine Stored Functions bzw. Procedures gebraucht. Die Dokumentation u.a. auf MySQL.com bringt mich nicht weiter.

Das habe ich schon:

SQL-Code:

CREATE FUNCTION GetLeaves(ParentNodeID INT) RETURNS ???
BEGIN
   DECLARE Result ??? DEFAULT ???; --Wie die leere Menge als Standardwert angeben?
   DECLARE Datasets ??? DEFAULT ???;

   -- So findet man Kindknoten eines Knotens:
   SELECT ID FROM Nodes WHERE ParentID=ParentNodeID;

   -- Hier müsste jetzt über das eben ermittelte Ergebnis iteriert werden, um von diesen Knoten wiederum die Kindknoten zu finden... Also irgendwie so:
   WHILE ... DO
      -- den aktuellen Knoten zu Result hinzufügen
      SET Result = Result und aktueller Knoten;

      -- die Kindknoten des aktuellen Knotens ermitteln
      SET Datasets = CALL GetLeaves(...);

      -- und zu Result hinzufügen...
      SET Result = Result und Datasets;
   END WHILE;

   -- und noch das Ergebnis zurückgeben
   RETURN Result;
END

Die Tabelle Nodes sieht wie folgt aus:

SQL-Code:

+----+----------+
| ID | ParentID |
+----+----------+
|  1 |        0 |
|  2 |        1 |
|  3 |        2 |
|  4 |        2 |
|  5 |        4 |
|  6 |        3 |
|  . |        . |
|  . |        . |
|  . |        . |
+----+----------+

Wie müssen die Stored Functions aussehen, damit ich (1) alle Unterknoten zum angegebenen Knoten aufgelistet bekomme und (2) nur die Blätter zum angegebenen Knoten aufgelistet bekomme?

Ich bin für jede Hilfe dankbar,
Panthrax.

[marabu]

Hallo Panthrax,

du hast die MySQL Version 4.1 angegeben - Stored Procedures (und Trigger) sind dort nicht implementiert.

Grüße vom marabu

[Panthrax]

Öhm... ja. Entschuldigung. Das würde ich an dieser Stelle gern berichtigen: Ich benutze MySQL 5.0. Die Falschangabe war die Macht der Gewohnheit. Ich habe MySQL 5.0 erst seit kurzem "dazuinstalliert". In der verwiesenen Doku stehts ja auch drin "MySQL 5.0 Reference Manual". Nur leider komm' ich nicht so richtig weiter...

Panthrax.

[omata]

Hallo Panthrax,

dir ist klar, dass das keine triviale Aufgabe ist?
Wie dem auch sei, ich habe mich mal dran versucht.


Folgende Tabelle habe ich für den Baum angelegt...

SQL-Code:

CREATE TABLE `nodes` (
  `id` int(10) unsigned NOT NULL,
  `parentid` int(10) unsigned default NULL,
  `bez` varchar(45) NOT NULL default '',
  `reihenfolge` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `FK_nodes_parentid` (`parentid`),
  CONSTRAINT `FK_nodes_parentid` FOREIGN KEY (`parentid`) REFERENCES `nodes` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Beispielinhalt...

SQL-Code:

+----+----------+-----+-------------+
| id | parentid | bez | reihenfolge |
+----+----------+-----+-------------+
| 1  | NULL     | A   | 1           |
| 2  | 1        | AA  | 1           |
| 3  | 1        | AB  | 2           |
| 4  | NULL     | B   | 2           |
| 5  | 4        | BA  | 1           |
| 6  | 4        | BB  | 2           |
| 7  | 1        | AC  | 3           |
| 8  | 7        | ACA | 1           |
+----+----------+-----+-------------+

Dann habe ich folgende Prozedur angelegt...

SQL-Code:

CREATE PROCEDURE `proc_node`(IN _ParentID INT, IN _NurBlaetter BIT)
BEGIN
  DECLARE _done INT DEFAULT 0;
  DECLARE _id INT;
  DECLARE _bezeichnung VARCHAR(10);

  DECLARE cursor_nodes CURSOR FOR
    SELECT id, bez
    FROM nodes
    WHERE COALESCE(parentid, 0) = _ParentID
    ORDER BY reihenfolge;

  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done = 1;
  OPEN cursor_nodes;

  REPEAT
    FETCH cursor_nodes INTO _id, _bezeichnung;
    IF _done = 0 THEN
      IF    (SELECT COUNT(*) FROM nodes WHERE parentid = _id) = 0
         OR _NurBlaetter = 0
      THEN
        INSERT INTO temp (ID, Bezeichnung) VALUES (_id, _bezeichnung);
      END IF;
      CALL proc_node(_id, _NurBlaetter);
    END IF;
  UNTIL _done END REPEAT;

  CLOSE cursor_nodes;
END

und dann diese Prozedur...

SQL-Code:

CREATE PROCEDURE `proc_main`(IN _ParentID INT, IN _NurBlaetter BIT)
BEGIN
  SET @@max_sp_recursion_depth = 100;
  CREATE TEMPORARY TABLE temp (
    ID  INT,
    Bezeichnung VARCHAR(10)
  );
  CALL proc_node(_ParentID, _NurBlaetter);
  SELECT *
  FROM temp;
  DROP TEMPORARY TABLE temp;
  SET @@max_sp_recursion_depth = 0;
END

Ausgabe des gesamten Baums...

SQL-Code:

CALL proc_main(0, 0);

+------+-------------+
| ID   | Bezeichnung |
+------+-------------+
| 1    | A           |
| 2    | AA          |
| 3    | AB          |
| 7    | AC          |
| 8    | ACA         |
| 4    | B           |
| 5    | BA          |
| 6    | BB          |
+------+-------------+

Ausgabe aller Elemente und Unterelemente, die zum Element mit der ID = 1 gehören...

SQL-Code:

CALL proc_main(1, 0);

+------+-------------+
| ID   | Bezeichnung |
+------+-------------+
| 2    | AA          |
| 3    | AB          |
| 7    | AC          |
| 8    | ACA         |
+------+-------------+

Ausgabe des gesamten Baums (nur die Blätter)...

SQL-Code:

CALL proc_main(0, 1);

+------+-------------+
| ID   | Bezeichnung |
+------+-------------+
| 2    | AA          |
| 3    | AB          |
| 8    | ACA         |
| 5    | BA          |
| 6    | BB          |
+------+-------------+

Ausgabe aller Blatt-Elemente, die zum Element mit der ID = 1 gehören...

SQL-Code:

CALL proc_main(1, 1);

+------+-------------+
| ID   | Bezeichnung |
+------+-------------+
| 2    | AA          |
| 3    | AB          |
| 8    | ACA         |
+------+-------------+

Wenn dein Baum sehr tief verschachtelt ist musst du eventuell die maximale Rekursionstiefe in proc_main erhöhen.

MfG
Thorsten

[edit=sakura] Auf Wunsch korrigiert. Mfg, sakura[/edit]

[omata]

Eine bessere (weil nicht rekursiv) Variante ist hier zufinden.