Stored procedures: Variablen im Cursor

[Peter Thomassen]

Hallo,

ich bastle grade erstmals mit Stored Procedures. Grundlage ist eine Tabelle contract mit zwei Spalten id und parent_id, wodurch eine Hierarchie abgebildet wird. Ich möchte durch wiederholtes Abfragen von parent_id an die Wurzel des Baums kommen ... Adam sozusagen *g*

Ich hab folgendes:

  
CREATE FUNCTION contractMain (id INT)  
 RETURNS INT  
 BEGIN  
  DECLARE parent_id INT;  
  DECLARE cur1 CURSOR FOR SELECT parent_id FROM contract WHERE id = ?;  
  DECLARE CONTINUE HANDLER FOR NOT FOUND  
   BEGIN  
    CLOSE cur1;  
    RETURN parent_id;  
   END;  
  REPEAT  
   OPEN cur1;  
   FETCH cur1 INTO parent_id;  
   CLOSE cur1;  
  END REPEAT;  
 END;  

Wie man sieht, möchte ich denselben Cursor mehrfach verwenden, allerdings in der WHERE-Klausel verschiedene Werte einsetzen (da wo jetzt das Fragezeichen ist), nämlich den jeweils aktuellen Wert der Variablen parent_id. Wie geht das?

Ich verwende MySQL 5.0.16.

BTW, das ist meine erste Stored Procedure, und ich hab sie noch nicht getestet. Wenn noch was falsch sein sollte, bitte mitteilen :-)

Danke!
Peter

[Frank (no reg)]

Hi!

Wenn ich richtig liege

• du hast in deiner Tabelle mehrere Bäume
• du willst die Baum-Wurzel für ein beliebiges Element in deinem
    Baum herausfinden?

Dennoch solltest du vielleicht an dieser Stelle keinen Cursor
verwenden sondern eine einfachere WHILE Schleife. Ein Cursor ist
einer dazu gedacht ein einmaliges Result Set zu erzeugen um es
dann Record für Record zu verarbeiten.

In deiner Schleife fragst du dann jeweils nach der ParentId

  
SELECT [ParentId] FROM [contract] WHERE [Id] = id  

Wenn dabei irgendwann NULL zurückgegeben wird, weil [ParentId] für
das Wurzelelement eben NULL sein muss, dann hast du den gewünschten
Datensatz.

  
DECLARE oldParentId int  
DECLARE newParentId int  
  
SELECT oldParentId = id   -- dein Funktions-Eingabe-Parameter  
  
WHILE 1 = 1 BEGIN  
  SELECT newParentId = [ParentId] FROM [contract] WHERE [Id] = oldParentId  
  IF newParent IS NULL  
    BREAK  
  ELSE  
    SELECT oldParentId = newParentId -- damit die Tabellenabfrage von oben auch wieder ne neue Id benutzt  
END  
  
RETURN oldParentId  

Da ich nicht viel mit MySQL arbeite kann ich auch nicht garantieren,
dass meine Syntax vollständig korrekt ist :) Konsultiere ggf dein
MySQL Manual.

Grüße,
Frank

[Peter Thomassen]

Hallo,

Wenn ich richtig liege

• du hast in deiner Tabelle mehrere Bäume
• du willst die Baum-Wurzel für ein beliebiges Element in deinem
    Baum herausfinden?

Korrekt.

Dennoch solltest du vielleicht an dieser Stelle keinen Cursor
verwenden sondern eine einfachere WHILE Schleife. Ein Cursor ist
einer dazu gedacht ein einmaliges Result Set zu erzeugen um es
dann Record für Record zu verarbeiten.

Das Gefühl hatte ich auch, mir war allerdings nicht bekannt, dass man das Ergebnis einer SELECT-Abfrage einfach in eine Variable verfrachten kann; so ist es natürlich einfacher. Die Syntax mit den eckigen Klammern wäre mir neu, ich werde es aber mal ausprobieren. Danke!

Bye,
Peter

[Frank (no reg)]

Hallo,

Sicher dass in MySQL5 die Variablen in einer Stored Procedure ohne
Präfix wie zum Bleistift "@" deklariert werden? Was passiert, wenn
deine Variable genauso heißt wie eine Spalte die du abfragst?

... aber eben, wie gesagt, Infos zur Syntax unter MySQL bekommst von
deinem "Handbuch", in welcher Form du es auch immer vorliegen hast.

Grüße,
Frank