[SQL/Oracle] rekursive stored procedure

[bluezac]

moin,

ich habe ein tabelle mit folgenden spalten:
kind, vater, menge

die menge ist eher zu vernachlässigen.

in dieser SP soll nun rekursiv von einem vater über die kinder gegangen werden und mir am ende eine liste der kinder ausgeben.

für mich ist das problem, dass ein vater mehr als 1 sohn haben kann, weswegen ich das ergebnis einer einfachen select abfrage nicht einfach in eine variable stecken kann.

wäre für ideen und lösungsvorschläge sehr dankbar !

 

[Elgar]

Willst Du das Ergebnis der Abfrage in deiner stored procedure weiter verarbeiten, oder an den Aufrufenden Prozess zurückgeben?

Ersteres sollte mit einem cursor for loop funktionieren. Innerhalb der Schleife dann halt die Verarbeitung pro Datensatz.

Letzteres sollte über einen ref cursor gehen, den man halt für die Abfrage öffnet und dann zurückgibt.

 

[bluezac]

nein, es langt wenn es ausgegen wird, wenn die rekursion komplett durch ist.

ich habe soweit auch schon was geschrieben, allerdings sagt er mir beim versuch der ausführung, dass zu viele cursor offen sind

das ganze sieht im moment so aus:

create or replace
procedure viewArticles(ArtID in number) as
  art_id number;
  art_name varchar2(50);
  cursor csr is select father from articles_table at where at.father = ArtID;
begin
  open csr;
  loop
    fetch csr into art_id;
    exit when csr%notfound;
    select titel into art_name from articles where articles.tid=art_id;
    dbms_output.put_line(art_name);
     viewArticles(art_id);
  end loop;
  close csr;
  return;
end;

 

[Elgar]

nein, es langt wenn es ausgegen wird, wenn die rekursion komplett durch ist.

ich habe soweit auch schon was geschrieben, allerdings sagt er mir beim versuch der ausführung, dass zu viele cursor offen sind

das ganze sieht im moment so aus:

create or replace
procedure viewArticles(ArtID in number) as
  art_id number;
  art_name varchar2(50);
  cursor csr is select father from articles_table at where at.father = ArtID;
begin
  open csr;
  loop
    fetch csr into art_id;
    exit when csr%notfound;
    select titel into art_name from articles where articles.tid=art_id;
    dbms_output.put_line(art_name);
     viewArticles(art_id);
  end loop;
  close csr;
  return;
end;

Da ist aber was doppeltgemoppelt.

Mal sehen ob ich das so richtig verstanden habe:

- Du übergibst die ID des Fathers als ArtID
- in Deinem Cursor ermittelst Du genau diese ID nochmals
- und die ermittelte ID nutzt Du dann in deiner Abfrage für die Artikel.

Da kannst Du doch gleich den übergebenen Parameter in der Artikelabfrage nutzen.

Also z.B. so:

create or replace
procedure viewArticles(ArtID in number) as
begin

  for c in (
    select titel from articles where tid in = ArtId
  ) loop
  
    dbms_output.put_line(c.titel);

  end loop;
end;

 

[bluezac]

mhm es ist so, dass ein vater ein oder mehrere söhne haben kann.
diese söhne allerdings weitere söhne. (ich hatte diesen punkt vergessen zu erwähnen)

also wie eine baumstruktur

deshalb muss er sich halt mit den gefundenen ids wieder aufrufen.

 

[Elgar]

Ah, ok, ich habe den rekursiven Aufruf übersehen.

So wie er im Moment in Deinem Code drinsteht, ist es klar das irgendwann zu viele Cursor geöffnet sind. Du übergibst ja da nicht die ID des Kindes erneut an Deine Procedure, sondern immer wieder die ID des Vaters, also jene ID mit der Du die Procedure ganz zu Anfang aufgerufen hast. Und so kommst Du natürlich nie aus Deiner Schleife raus. Das ist also eigentlich keine Rekursion, sondern eine endlose Wiederholung.

Da ich Deine Tabellenstruktur jetzt nicht kenne, kann ich nur mutmaßen:

create or replace
procedure viewArticles(ArtID in number) as
begin

  for c in (
    select id, titel from articles where tid in = ArtId
  ) loop
  
    dbms_output.put_line(c.titel);

    viewArticles(c.id);

  end loop;
end;

Damit übergibst Du die ArtikelID des Kindes als neue VaterID an Deine Procedure.

 

[little_skunk]

Dafür nimmt man keine Stored Procedure. Die Lösung trägt den Namen "Hierarchische Abfrage".