[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:

zusammenfalten · markieren

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:

zusammenfalten · markieren

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...

markieren

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...

zusammenfalten · markieren

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...

zusammenfalten · markieren

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...

zusammenfalten · markieren

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...

zusammenfalten · markieren

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...

markieren

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)...

zusammenfalten · markieren

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...

markieren

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.

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

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

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

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

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

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

Forumregeln

marabu Registriert seit: 6. Apr 2005 10.109 Beiträge	#2 Re: [MySQL] Stored Function für Zusammenstellung - wie? 1. Apr 2006, 17:20 Hallo Panthrax, du hast die MySQL Version 4.1 angegeben - Stored Procedures (und Trigger) sind dort nicht implementiert. Grüße vom marabu
	Zitat

omata Registriert seit: 26. Aug 2004 Ort: Nebel auf Amrum 3.154 Beiträge Delphi 7 Enterprise	#5 Re: [MySQL] Stored Function für Zusammenstellung - wie? 23. Dez 2008, 16:09 Eine bessere (weil nicht rekursiv) Variante ist hier zufinden.
	Zitat