Oracle + Firebird + Rekursion

[fatima]

Hallo Leute,
durch Zufall bin ich auf dieses Forum gestoßen und muss sagen es gefällt mir außerordentlich gut!

Ich habe auch gleich ein Problem: Es geht um Rekursion in einer SP bei Firebird.

Sagen wir wir haben folgende einfache Tabelle mit folgendem Inhalt:

Code:

CREATE TABLE EMPLOYEE
(
  EMP_ID    NUMBER,
  CHEF_ID   NUMBER,
  EMP_NAME  VARCHAR2(40 BYTE)
)
CREATE UNIQUE INDEX UQ_EMP_ID ON EMPLOYEE
(EMP_ID)


INSERT INTO EMPLOYEE (EMP_ID, CHEF_ID, EMP_NAME) VALUES (20, NULL, 'Grosser Chef');
INSERT INTO EMPLOYEE (EMP_ID, CHEF_ID, EMP_NAME) VALUES (10, 20, 'Abteilungsleiter Entwicklung');
INSERT INTO EMPLOYEE (EMP_ID, CHEF_ID, EMP_NAME) VALUES (5, 20, 'Abteilungsleiter Verkauf');
INSERT INTO EMPLOYEE (EMP_ID, CHEF_ID, EMP_NAME) VALUES (12, 10, 'Projektleiter');
INSERT INTO EMPLOYEE (EMP_ID, CHEF_ID, EMP_NAME) VALUES (1, 12, 'Programmierer');
INSERT INTO EMPLOYEE (EMP_ID, CHEF_ID, EMP_NAME) VALUES (2, 5, 'Sachbearbeiter A ');
INSERT INTO EMPLOYEE (EMP_ID, CHEF_ID, EMP_NAME) VALUES (3, 5, 'Sachbearbeiter B');
INSERT INTO EMPLOYEE (EMP_ID, CHEF_ID, EMP_NAME) VALUES (18, 5, 'Azubi Verkauf');

Um eine strukturierte Uebersicht zu erhalten kann ich in Oracle einfach folgendes SQl absetzen:

Code:

SELECT  E.CHEF_ID, E.EMP_ID , E.EMP_NAME , LEVEL
  FROM EMPLOYEE E
  START WITH E.CHEF_ID IS NULL
  CONNECT BY PRIOR E.EMP_ID = E.CHEF_ID

Mit Ergebnis in Preorder des Baumes:

Code:

CHEF_ID   EMP_ID   EMP_NAME                             LEVEL

NULL   20   Grosser Chef                     1
20   10   Abteilungsleiter Entwicklung   2
10   12   Projektleiter                     3
12   1   Programmierer                     4
20   5   Abteilungsleiter Verkauf              2
5   2   Sachbearbeiter A                      3
5   3   Sachbearbeiter B                     3
5   18   Azubi Verkauf                     3

Mit Firebird muss ich mir da sicher eine rekursive SP bauen, aber ich versuche nun schon seit Std ohne Erfolg, vlt kann mir jemand dabei helfen.

liebe Grüße
fatima

[Stefan Hueg]

Also was mir zuerst auffällt ist folgendes:

SQL-Code:

SELECT  E.CHEF_ID, E.EMP_ID , E.EMP_NAME , LEVEL
  FROM EMPLOYEE E
  START WITH E.CHEF_ID IS NULL
  CONNECT BY PRIOR E.EMP_ID = E.CHEF_ID

Da könntest du auch einfach ein Select * from ... machen da du ja eh alles an Daten selektierst. Aber wo kriegst du das Feld Level her, es wird ja bei der Create Anweisung nicht erstellt.

Nur: Was ist überhaupt deine Frage?

PS: Und ja, ich arbeite mit PL/SQL

[Bitworm]

Wenn ich die Frage richtig verstanden habe suchst Du nach einer Alternative der
hierarchischen Abfrage, die in Oracle sehr komfortabel gelöst wurde.
Das im Select erscheinende Feld LEVEL wird dabei von Oracle generiert.
Soviel ich weiß, ist diese schöne Sonderfunktion des Oracle-SQL nicht in anderen
SQL-Dialekten vorhanden.

[Robert_G]

@Fatima
Da brauchst du gar nicht mehr suchen.
Solche fancy features wirst du nur in Oracle finden.
Falls du Gruppierungen wie Cube, Rollup,... suchst -> ebenfalls Fehlanzeige: Die sind in allen anderen DBs entweder nicht vorhanden oder einfach zu billig implementiert sind um sinnvoll zu sein

[fatima]

Ja ich auche einen Ersatz fuer o.a. Oracle SQL in Firebird

Kenne mich mit Firebird leider nicht so gut aus wie mit Oracle.
Bisher habe ich es mit folgender SP probiert:

Code:

CREATE PROCEDURE HIERARCHIE (
    A_ID BIGINT,
    A_LEVEL INTEGER)
RETURNS (
    A BIGINT,
    L BIGINT)
AS
DECLARE VARIABLE NR BIGINT;
BEGIN
   NR = -1;
   FOR SELECT E.EMP_ID FROM EMPLOYEE E
   WHERE (E.CHEF_ID = :A_ID)
   INTO :NR
   DO
   BEGIN
     IF (NR <> -1) THEN
     BEGIN
       A_LEVEL = A_LEVEL +1;
       EXECUTE PROCEDURE HIERARCHIE(NR,:A_LEVEL) RETURNING_VALUES(:A, :A_LEVEL);
       A = :A_ID;
       L = :A_LEVEL;
       SUSPEND;
     END
   END
END

Ich rufe die SP mit (20,0) auf das Ding geht auch in die Rekursion aber die Daten werden irgendwie
falsch ausgegeben.

BTW: Kann man eigentlich bei Firebird SP's debuggen?

[urs.liska]

Zitat von fatima:

BTW: Kann man eigentlich bei Firebird SP's debuggen?

Mit dem entsprechenden Administrationsprogramm schon. Ich weiß nicht, welche es sind, die das können. IBExpert kann es jedenfalls.
MfG
Urs

[Edit]Eine Liste von Admin-Progs gibts auf der Firebird Homepage[/Edit]

[Lemmy]

Guten Morgen,

es ist durchaus möglich so etwas in Firebird zu machen!

In der Entwickler-Ausgabe 1/2003 ist ein Artikel von Karsten Strobel der die Entwicklung von Rekursiven SP's sehr gut erklärt!

Grüße
Lemmy

[lume96]

Moin,

hier mal 'n kleiner Vorschlag :

SQL-Code:

CREATE PROCEDURE HIERARCHIE (
    C_ID INTEGER,
    C_LEVEL INTEGER)
RETURNS (
    O_CHEF_ID INTEGER,
    O_EMP_ID INTEGER,
    O_EMP_NAME VARCHAR (50),
    O_LEVEL INTEGER)
AS
BEGIN

     if (c_level is null) then
        c_level = 0;

     FOR
        SELECT emp_ID, chef_ID, emp_name
        FROM employee
        WHERE (chef_ID = :C_ID) OR (chef_ID IS NULL AND :C_ID IS NULL)
        INTO :O_EMP_ID, :O_CHEF_ID, :O_EMP_NAME
      DO
      BEGIN
           o_level = c_level;
           SUSPEND;

           FOR
              SELECT o_emp_ID, o_chef_ID, o_emp_name, o_level FROM HIERARCHIE(:O_EMP_ID,:c_level+1)
              INTO :O_EMP_ID, :O_CHEF_ID, :O_EMP_NAME, :o_level
          DO
          BEGIN
               SUSPEND;
          END
      END
END

Wie es scheint, bekommt man bei EXECUTE PROCEDURE ... RETURNING VALUES immer nur das letzte Resultat zurück; die "Zwischenergebnisse" gehen leider flöten.

Ach ja, aufrufen kannst Du das Ganze mit
select * from HIERARCHIE(x,y) wobei :
X = chef_id (oder null für die gesamte Hierarchie)
Y = start_level (= gewünschte zahl für den höchsten Level)


Tschüss,
Lutz

[fatima]

salut Lutz,
deine Lösung ist super und perfekt. DANKE!
Ist zwar viel komplizierter als bei ora aber es funzt.
Mein Fehler war wohl, dass ich nicht auf die Idee kam im For Select in die Rekursion zu gehen.

Gibt es zum PSQL von Firebird eigentlich ein paar schöne Seiten im inet?


gruss
fatima

[DelphiDeveloper]

Hallo Fatima,
ja der Trick liegt im For Select in die Rekusrion zu gehen.

Du schriebst etwas von preorder Traversierung, dass ist nicht moeglich da dein
Baum ja keine Ordnung auf LevelEbene hat.

da die Bäume

Code:

    20
   |   |
   10  5

und 
   20 
  |    |
  5    10

ja äquivalent sind.

Hier noch ein alternativer Vorschlag von mir zu der gesuchten Stored proc:

Code:

CREATE PROCEDURE TRAVERSIERE (
    ACHEF_ID INTEGER)
RETURNS (
    EMPID INTEGER)
AS
DECLARE VARIABLE NR INTEGER;
BEGIN
  EMPID = :ACHEF_ID;
  SUSPEND;
  FOR SELECT E.EMP_ID FROM EMPLOYEE E
  WHERE E.CHEF_ID =:ACHEF_ID
  ORDER BY E.EMP_ID DESC
  INTO :NR DO
  BEGIN
    EMPID = :NR;
    SUSPEND;
    FOR SELECT EMPID
        FROM TRAVERSIERE(:NR)
        WHERE EMPID <> :NR
        INTO :EMPID DO
      SUSPEND;
  END
END

Diese Proc liefert die Knoten hierarchisch:
Die Proc kannst du dann in einer Query nutzen und deine Spalten
individuell setzen.
etwa so:

Code:

SELECT P.EMPID , E.CHEF_ID, E.EMP_NAME ,
(SELECT EE.EMP_NAME FROM EMPLOYEE EE
  WHERE EE.EMP_ID = E.CHEF_ID) AS SEIN_DIREKTER_CHEF_IST
FROM TRAVERSIERE(20)  P, EMPLOYEE E
WHERE P.EMPID = E.EMP_ID

Richtige gute Firebird Seiten kenne ich nicht! Lernt man wohl am besten wenn man sich
selbst damit auseinander setzt.
Im übrigen ist die SP Language von firebird ja nicht so mächtig wie bei Oracle,
die 20-30 Befehle die es bei Firebird gibt, beherrscht man schnell.

Noch ein Tip für deine Datenstruktur:
Sie sieht es leider nicht vor, dass ein Untergebener 2 direkte Vorgesetzte hat,
bspw. eine Sekräterin morgens in Abteilung A und nachmittags in Abteilung B.
Aber da wirst du sicherlich schon drauf gekommen sein.

mfg
DD

[edit=MrSpock]Doppelpost gelöscht. Mfg, MrSpock[/edit]