Benötige Hilfe bei anspruchsvollen PL/SQL-Aufgaben TEIL1 + TEIL2

[Wernfried]

-- Aufgabe 3
/*
Erstellen Sie eine STORED FUNCTION aktueller_kunde(tid), die für einen titel (TID)
zurückgibt, welcher Kunde diesen Film zur Zeit entliehen hat. Ist der Film nicht entliehen, so soll
NULL zurückgegeben werden.

Hier fehlt mir nur noch die Umsetzung von: "Ist der Film nicht entliehen, so soll
NULL zurückgegeben werden."

Du machst das gleiche (falsch) wie bei den ersten Aufgaben, benutze RETURN für den Rückgabewert anstatt das Ergebnis direkt via DBMS_OUTPUT auszugeben.

Wenn du NULL zurückgeben möchtest kannst du das mit einer Exception machen:
[highlight=sql]
BEGIN
SELECT ..
INTO var
FROM ...;
RETURN var;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;
[/highlight]

Man kann jetzt natürlich darüber streiten ob ein nicht ausgeliehener Film tatsächlich eine Ausnahme (EXCEPTION, d. h. einen Fehler) darstellt und man die Abfrage nicht eleganter über einen expliziten Cursor macht und mit %NOTFOUND arbeitet aber das ist zum Teil auch eine philosophische Frage.

Gruss

[mary]

Das kannst du so machen:

[highlight=sql]
medien_liste := NULL;
FOR datensatz2 IN curMedium(datensatz.TID) LOOP
medien_liste := medien_liste || datensatz2.mid||', ';
END LOOP;
dbms_output.put_line(REGEXP_REPLACE(medien_liste, ', $'));

[/highlight]

Gruss

Habe das gerade mal ausprobiert, aber erst meckerte Oracle, dass medien_liste noch deklariert werden müsse. Also habe ich "medien_liste := NULL;" in den DECLARE-Teil gepackt, nur leider sagt Oracle nun:
Fehlerbericht:
ORA-06550: Zeile 14, Spalte 16:
PLS-00103: Fand das Symbol "=" als eines der folgenden erwartet wurde:

constant exception <an identifier>
<a double-quoted delimited-identifier> table LONG_ double ref
char time timestamp interval date binary national character
nchar
Das Symbol "<an identifier>" ersetzte "=", um fortzufahren.
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

In der beanstandeten Zeile 14 ist die Deklaration der "medien_liste"-Variable.

Meine If-Abfrage mit dem Komma habe ich durch Deinen Code ersetzt, aber ich habe diesen auch noch nicht ganz verstanden.

Wieso weist Du der medien_liste mittels := medien_liste nochmals denselben Wert zu, den sie bereits durch die Deklaration erhalten hat?

[mary]

Du machst das gleiche (falsch) wie bei den ersten Aufgaben, benutze RETURN für den Rückgabewert anstatt das Ergebnis direkt via DBMS_OUTPUT auszugeben.

Wenn du NULL zurückgeben möchtest kannst du das mit einer Exception machen:
[highlight=sql]
BEGIN
SELECT ..
INTO var
FROM ...;
RETURN var;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;
[/highlight]

Man kann jetzt natürlich darüber streiten ob ein nicht ausgeliehener Film tatsächlich eine Ausnahme (EXCEPTION, d. h. einen Fehler) darstellt und man die Abfrage nicht eleganter über einen expliziten Cursor macht und mit %NOTFOUND arbeitet aber das ist zum Teil auch eine philosophische Frage.

Gruss

Also mit dem RETURN habe ich noch meine Probleme. Ich möchte ja eine hübsche Ausgabe erzeugen und bislang gab es immer Probleme, wenn ich mehrere Return´s versucht habe, auszugeben.

Ich lasse doch die Werte extra in spezielle Variablen schreiben, die ich später dann verkettet ausgebe.

DBMS_OUTPUT.PUT_LINE('Die zugehörige Kundennummer lautet: ');
RETURN varkid;

Diesen Return gebe ich z.B. nur aus, weil ich es muss (Oracle meckert sonst, dass nichts zurückgegeben wird). Damit dann nicht nur beispielsweise "22" ganz alleine so da steht, habe ich darüber noch eine Ausgabezeile erzeugt, die klarstellt, worum es sich bei der 22 handelt.

Folgender Code mit Return direkt nach der Abfrage:

CREATE OR REPLACE FUNCTION aktueller_kunde(midparam IN INT)
RETURN INT
IS
varlid vl_verschickt.lid%TYPE;
varzurueck vl_verschickt.zurueck%TYPE;
varkid vl_listen.kid%TYPE;
varanrede vl_kunden.anrede%TYPE;
varname vl_kunden.name%TYPE;
varvorname vl_kunden.vorname%TYPE;
BEGIN
  SELECT v.lid, v.zurueck
  INTO varlid, varzurueck
  FROM vl_verschickt v  
  WHERE v.mid = midparam
  AND v.zurueck IS NULL;
  RETURN varlid;
  EXCEPTION
   WHEN NO_DATA_FOUND THEN
      RETURN NULL;
  
  SELECT l.kid
  INTO varkid
  FROM vl_listen l  
  WHERE varlid = l.lid;
  
  SELECT k.anrede, k.name, k.vorname
  INTO varanrede, varname, varvorname
  FROM vl_kunden k  
  WHERE varkid = k.kid;
  
  --IF zurueck IS NOT NULL THEN RETURN NULL;
  DBMS_OUTPUT.PUT_LINE('Das Medium mit der von Ihnen übergebenen MID: ' ||midparam|| ' ist zur Zeit von '||varanrede|| ' ' ||varvorname|| ' ' ||varname|| ' entliehen.');
  DBMS_OUTPUT.PUT_LINE('Die zugehörige Kundennummer lautet: ');
  RETURN varkid;  
  --RETURN zurueck;
  /*
  FOR datensatz IN (SELECT * FROM vl_verschickt where mid = midparam)
  LOOP
    DBMS_OUTPUT.PUT_LINE('Film ' || datensatz.lid || ': ' || datensatz.mid || ' ' || datensatz.verschickt);
  END LOOP;
  RETURN lid;*/
END;
/

set serveroutput on;
BEGIN
  DBMS_OUTPUT.PUT_LINE(aktueller_kunde(66903));
END;
/

hat nun folgende Skriptausgabe:

FUNCTION aktueller_kunde kompiliert
anonymer Block abgeschlossen
80888

Also scheint Oracle schon nach dem ersten RETURN die Abarbeitung des Codes abzubrechen.

[Wernfried]

Habe das gerade mal ausprobiert, aber erst meckerte Oracle, dass medien_liste noch deklariert werden müsse. Also habe ich "medien_liste := NULL;" in den DECLARE-Teil gepackt, nur leider sagt Oracle

Die Variable wird so deklariert (alles wollte ich dir hier eigentlich nicht vorkauen):

[highlight=plsql]DECLARE
medien_liste VARCHAR2(1000);
BEGIN
....
[/highlight]

Wieso weist Du der medien_liste mittels := medien_liste nochmals denselben Wert zu, den sie bereits durch die Deklaration erhalten hat?

Ich weise nicht noch einmal den gleichen Wert zu, sondern

medien_liste := medien_liste  || datensatz2.mid||', ';

Damit hänge ich den Wert für datensatz2.mid an die existierende medien_liste hinten ran.

Also mit dem RETURN habe ich noch meine Probleme. Ich möchte ja eine hübsche Ausgabe erzeugen und bislang gab es immer Probleme, wenn ich mehrere Return´s versucht habe, auszugeben.

Sobald das Programm auf ein RETURN stösst wird die Funktion, bzw. Prozedur verlassen, das ist auch in anderen Programiersprachen so.

Ich lasse doch die Werte extra in spezielle Variablen schreiben, die ich später dann verkettet ausgebe.

DBMS_OUTPUT.PUT_LINE('Die zugehörige Kundennummer lautet: ');
RETURN varkid;

Der richtige Ansatz sieht so aus:

set serveroutput on;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Die zugehörige Kundennummer lautet: '||aktueller_kunde(66903));
END;
/

Die Funktion selber gibt nur die Zahl zurück.

Gruss

[mary]

Die Variable wird so deklariert (alles wollte ich dir hier eigentlich nicht vorkauen):

[highlight=plsql]DECLARE
medien_liste VARCHAR2(1000);
BEGIN
....
[/highlight]




Ich weise nicht noch einmal den gleichen Wert zu, sondern

medien_liste := medien_liste  || datensatz2.mid||', ';

Damit hänge ich den Wert für datensatz2.mid an die existierende medien_liste hinten ran.




Sobald das Programm auf ein RETURN stösst wird die Funktion, bzw. Prozedur verlassen, das ist auch in anderen Programiersprachen so.



Der richtige Ansatz sieht so aus:

set serveroutput on;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Die zugehörige Kundennummer lautet: '||aktueller_kunde(66903));
END;
/

Die Funktion selber gibt nur die Zahl zurück.

Gruss

Die Variable hatte ich deklariert, allerdings als NUMBER. Es war wohl doch schon zu spät...

Offensichtlich habe ich aber noch einige Wissenslücken, die ich unbedingt noch füllen muss.

Danke für Deine Hilfe. Nun muss ich aber erstmal noch Aufgabe 4 zuende basteln.

Gruß