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

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