Announcement

**mary** · 02.08.2011, 00:24

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

Code:

-- Aufgabe 2
/*
Erstellen Sie ein PL/SQL Skript, dass für jeden Titel die Anzahl der Medien anzeigt.
Halten Sie sich an die folgende Ausgabe:

Film 5028: Sherlock Holmes gibt es auf 1 Medium: 77777
Film 5835: Avatar - Aufbruch nach Pandora gibt es auf 2 Medien: 26421,26422
Film 907: Selbst ist die Braut gibt es auf 1 Medium: 6647
Film 8670: Valentinstag gibt es auf 1 Medium: 99767
Film 9592: Die schönsten Eisenbahnen gibt es auf 1 Medium: 32206
Film 2893: Eisenbahn-Geschichte gibt es auf 1 Medium: 14989
Film 7402: Die Herzogin gibt es auf 1 Medium: 29879
Film 1752: Elizabeth - Das goldene Königreich gibt es auf 1 Medium: 66903
*/

-- bisheriger Lösungsansatz:

set serveroutput on;
declare
tid vl_titel.tid%type;
titel vl_titel.titel%type;
anzahl number;

begin
  select t.tid, t.titel, count(m.mid)
  into tid, titel, anzahl
  from vl_titel t, vl_medien m
  where t.tid = m.tid
  and t.tid = 5028
  group by tid;  
  dbms_output.put_line('Film '||tid||': '||titel||' gibt es auf '||anzahl||' Medien:');
end;
/

-- Test
set serveroutput on;
declare
mid vl_medien.mid%type;
tid number;
begin
  select m.mid, count(m.tid)
  into mid, tid  
  from vl_medien m  
  group by mid;
  dbms_output.put_line('Medium '': '||mid||' gibt es '||tid||' mal!');
end;
/

-- Lösung mit Cursor
set serveroutput on;
DECLARE
  CURSOR curTitel IS SELECT * FROM vl_titel;
  datensatz curTitel%ROWTYPE; -- Variable entspricht dem Cursor
  CURSOR curMedium IS SELECT * FROM vl_medien;
  datensatz2 curMedium%ROWTYPE;
  anzahl number;
  anzText CHAR(6);
  varmid vl_medien.mid%type;
BEGIN
  
  
  OPEN curTitel; -- Cursor öffnen
  FETCH curTitel INTO datensatz; -- nächste Zeile holen
  
  OPEN curMedium; -- Cursor öffnen
  FETCH curMedium INTO datensatz2; -- nächste Zeile holen
  
  WHILE curTitel%FOUND AND curMedium%FOUND LOOP -- Solange Zeile gefunden
    --SELECT mid into varmid FROM vl_medien where tid = datensatz.tid;
    
    SELECT count(*) into anzahl FROM vl_medien where tid = datensatz2.tid group by tid; --testweise 5028 oder 5835
  
  IF anzahl > 1 THEN    
    anzText := 'Medien';
  ELSE    
    anzText := 'Medium';
  END IF ;    
    
    DBMS_OUTPUT.PUT_LINE('Film ' || datensatz.tid || ': ' || datensatz.titel || ' gibt es auf ' || anzahl || ' ' || anzText || ': ' || datensatz2.mid);
    FETCH curTitel INTO datensatz; -- nächste Zeile holen
    FETCH curMedium INTO datensatz2; -- nächste Zeile holen
  END LOOP;
  
  CLOSE curTitel; -- Cursor schließen
  CLOSE curMedium; -- Cursor schließen
END;
/

-- FOR-Schleife mit SELECT-Anweisung ohne extra Cursor-Deklaration
set serveroutput on;
BEGIN
  FOR datensatz IN (SELECT * FROM vl_titel)
  LOOP
    DBMS_OUTPUT.PUT_LINE('Film ' || datensatz.tid || ': ' || datensatz.titel);
  END LOOP;
END;
/

-----------------------------------------------------------------------------------------

Code:

-- 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. */

-- bisheriger Lösungsansatz:

CREATE OR REPLACE FUNCTION aktueller_kunde(tidparam IN INT)
RETURN INT
IS
BEGIN
  FOR datensatz IN (SELECT * FROM vl_verschickt where tid = tidparam)
  LOOP
    DBMS_OUTPUT.PUT_LINE('Film ' || datensatz.tid || ': ' || datensatz.titel);
  END LOOP;
  RETURN tidparam;
END;
/


SELECT aktueller_kunde(5028)
FROM vl_titel;


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



BEGIN
  FOR datensatz IN (SELECT * FROM vl_titel)
  LOOP
    DBMS_OUTPUT.PUT_LINE('Film ' || datensatz.tid || ': ' || datensatz.titel);
  END LOOP;
END;
/

------------------------------------------------------------------------------------------

Code:

-- Aufgabe 4

/*
Erstellen Sie eine PL/SQL Funktion NAECHSTER_FREIER_FILM(...). Diese Funktion soll die
Wunschliste jedes Kunden durchsuchen und feststellen, welche Filme der Kunde noch zu sehen
wünscht. Aus diesen Filmen wird dann einfach der nächste ausgewählt, der zur Zeit vorhanden (also
nicht entliehen) ist.
Die Funktion erhält die Kundennummer (z.B. 182) und gibt eine Mediennummer zurück (z.B.: 2918).
Das Medium zu dieser MID muss natürlich einerseits vorhanden sein und andererseits auf der
Wunschliste des Kunden stehen. Falls es keinen solchen Film gibt, soll mit der Fehlermeldung '20001,
Kein Film mehr für Kunde 182!' abgebrochen werden. */

------------------------------------------------------------------------------------------

Code:

-- Aufgabe 5
/*
In der Tabelle Listen ist für jeden Kunden der Füllstand seiner Liste erfasst. Der Füllstand gibt an,
wie viele DVDs der Kunde auf seiner Liste hat.
Nun soll die Datenbank selbst dafür sorgen, dass der Wert des Attributs Füllstand immer den
aktuellen Wert anzeigt. Wenn also der Kunde einen neuen Film auf seine Liste setzt, oder einen
vorhandenen Film von der Liste löscht, soll das Attribut FÜLLSTAND automatisch angepasst
werden. Erstellen Sie einen datenbankseitigen Mechanismus, der diese Funktion übernimmt. */

-- bisheriger Lösungsansatz:

-- trigger ?!
CREATE OR REPLACE TRIGGER fuellstand
AFTER INSERT ON vl_liste
FOR EACH ROW

BEGIN

  IF DELETING THEN
  -- verringere Füllstand um 1
  END IF;
  
  IF INSERTING THEN
  -- erhöhe Füllstand um 1
  END IF;  
  
END;
/

**Falk Prüfer** · 02.08.2011, 09:29

Hallo,

wo genau hast du Probleme, bzw. was hast du selbst schon an Lösungen versucht? Niemand wird dir hier einfach deine Hausaufgaben machen und die fertigen Lösungen posten!

Gruß Falk

**Wernfried** · 02.08.2011, 12:41

Hallo

Auch wenn ich Falk absolut zustimme hier mal ein Anfang:

Erstellen Sie eine Datenbankfunktion LISTENFUELLSTAND(kid), die den Füllstand der Kundenliste für einen bestimmten Kunden anzeigt.

[highlight=sql]
SET SERVEROUTPUT ON SIZE 2500
DECLARE
FUNCTION LISTENFUELLSTAND(kid IN NUMBER)
RETURN NUMBER
IS
BEGIN
SELECT name FROM vl_kunden ;
END LISTENFUELLSTAND;
BEGIN
SELECT * FROM vl_kunden;
END;
/
[/highlight]

Damit erstellst du keine Datenbankfunktion, das ist ein anonymer PL/SQL Block.
Eine Funktion erstellst du mit
CREATE FUNCTION LISTENFUELLSTAND...

SELECT * FROM ... geht nicht, du musst das Ergebnis in eine Varialbe schreiben, also immer SELECT * INTO ... FROM ..., bzw. SELECT * BULK COLLECT INTO ... FROM ... wenn es mehrere Datensätze gibt.

Mir ist nicht ganz klar was der Unterschied zwischen "Datenbankfunktion" (Aufgabe 1), "STORED FUNCTION" (Aufgabe 3) und "PL/SQL Funktion" (Aufgabe 4) sein soll.

Gruss

**mary** · 02.08.2011, 23:08

Hallo!

Vorab erst einmal vielen Dank für das Verschieben der Themen in einen Thread. Ich wusste nicht, wie ich das hätte machen können. Bei der Erstellung des Themas waren es über 13000 Zeichen und nur 10000 sind pro Thema erlaubt, deswegen hatte ich es aufgeteilt.

Da habt ihr beide natürlich vollkommen Recht. Aber das war von mir auch nicht beabsichtigt.
Ich habe ja bereits eigene Lösungsansätze beigefügt, aber leider hakt es noch an diversen Stellen. Teilweise habe ich in meinen Lösungsansätzen ja auch nur zu Testzwecken Codefragmente eingefügt oder beispielsweise das Grundgerüst einer Funktion.

Zu Aufgabe 1 habe ich nun Folgendes entwickelt, was aber leider noch nicht ganz funktioniert:

Code:

CREATE OR REPLACE FUNCTION LISTENFUELLSTAND(kidparam IN NUMBER)
RETURN NUMBER
IS  
  CURSOR mycursor IS SELECT k.kid as KID, k.name as NAME, k.vorname as VORNAME, l.füllstand as FÜLLSTAND
    FROM vl_kunden k, vl_listen l
    where l.kid = k.kid
    and k.kid = kidparam; 
  myvariable mycursor%ROWTYPE;

BEGIN  
  OPEN mycursor; -- Cursor öffnen
  FETCH mycursor INTO myvariable; -- nächste Zeile holen
  WHILE mycursor%FOUND LOOP -- Solange Zeile gefunden
  DBMS_OUTPUT.PUT_LINE('--- ---------   -------  ---------');
  END LOOP;
  CLOSE mycursor;
  RETURN kidparam;
END;
/


SELECT kid, LISTENFUELLSTAND(kid)
FROM vl_kunden;

Skriptausgabe:

FUNCTION LISTENFUELLSTAND kompiliert

Abfrageergebnis:

ORA-20000: ORU-10027: buffer overflow, limit of 10000 bytes
ORA-06512: in "SYS.DBMS_OUTPUT", Zeile 32
ORA-06512: in "SYS.DBMS_OUTPUT", Zeile 97
ORA-06512: in "SYS.DBMS_OUTPUT", Zeile 112
ORA-06512: in "SYSTEM.LISTENFUELLSTAND", Zeile 14
20000. 00000 - "%s"
*Cause: The stored procedure 'raise_application_error'
was called which causes this error to be generated.
*Action: Correct the problem as described in the error message or contact
the application administrator or DBA for more information.

:-(

P.S. "Datenbankfunktion", "Stored Function" und "PL/SQL Function" bedeuten für den Aufgabensteller das gleiche. Hat mich zwar auch gewundert, aber okay dann sollen es eben alles Datenbankobjekte (gespeicherte Funktionen) werden. :-)

**Wernfried** · 03.08.2011, 08:23

Hallo

Code:

WHILE mycursor%FOUND LOOP -- Solange Zeile gefunden
   DBMS_OUTPUT.PUT_LINE('--- ---------   -------  ---------');
END LOOP;

Das ist eine Endlosschleife, in der Schleife machst du nichts ausser der DBMS Ausgabe. Irgendwann ist der Buffer voll und es kommt zum Fehler. Das FETCH muss in die Schleife!

Code:

RETURN kidparam;

Damit gibst du einfach wieder den Eingangsparameter aus. Das ist aber nicht gefordert, sondern der Füllstand.

In die Lösung für Aufgabe 1 gehört gar kein DBMS_OUTPUT, du musst den Wert für die Rückgabe (RETURN) finden.

Gruss

**Falk Prüfer** · 03.08.2011, 08:35

Hallo Mary,

Bleiben wir mal bei Erstens.
Zuerst ist mir nicht ganz klar was der "Füllstand der Kundenliste für einen bestimmten Kunden" ist. Mit ziemlicher Sicherheit ist es NICHT das Feld FÜLLSTAND aus der Tabelle vl_LISTEN. Mit den Daten aus diesem Feld lässt sich das gewünschte Ergebnis nämlich nicht erzielen.
Eher sieht es so aus, als wäre die Anzahl der Datensätze aus vl_DVDLISTEN zu ermitteln. Zumindes liese sich damit das gewünschte Ergebnis erzielen.

Dann zu deinem Ansatz: Diesen halte ich für grundsätzlich falsch. Du sollst eine Funktion erstellen, die einen EINZIGEN Wert zurückliefert, damit sie in einem Select verwendet werden kann. Eine Ausgabe via DBMS_OUTPUT hat dort also überhaupt nichts zu suchen.

Nach meinem Verständnis müsste diese Funktion einfach nur mit einem SELECT INTO die Anzahl der Datensätze aus vl_DVDLISTEN zugeordent zu einem Kunden (über vl_LISTEN) ermitteln und als Return-Wert zurückgeben. Einen CURSOR benötigst du dafür in dieser Funktion nicht.

Das Select zur Ermittlung des "Füllstandes" (was für ein verwirrender Begriff) könnte dann so aussehen:
[highlight=sql]
select count(dvd.DLID)
into dieRückgabeVariable
from vl_DVDLISTEN dvd
inner join vl_LISTEN list on list.LID = dvd.LID
where list.KID = dieÜbergebeneKundennummer
[/highlight]

Das Ganze nur noch entsprechend in eine Funktion verpacken und fertig.

Gruß Falk

**mary** · 03.08.2011, 14:49

Danke Wernfried, das war natürlich echt blöd.

Falk: Danke für die Hilfestellung. Ich habe es nun in eine Funktion gepackt und den richtigen Return eingebaut.

Code:

CREATE OR REPLACE FUNCTION LISTENFUELLSTAND(kidparam IN NUMBER)
RETURN NUMBER
IS
anzahl NUMBER;
BEGIN
  SELECT count(dvd.DLID)
  INTO anzahl
  FROM vl_DVDLISTEN dvd, vl_listen l  
  WHERE l.kid = kidparam
  and l.lid = dvd.lid;
  RETURN anzahl;
END;
/

SELECT kid, LISTENFUELLSTAND(kid) as FÜLLSTAND
FROM vl_kunden;

Statt des INNER JOINS habe ich einfach zwei equiJoins mit AND verknüpft, weil mich das weniger verwirrt. ;-) Ist aber im Prinzip dasselbe, nur mit anderer Syntax.

Abfrageergebnis:

Allerdings fehlen jetzt ja noch der Name sowie Vorname des jeweiligen Kunden aus der vl_kunden-Tabelle.

Dies habe ich folgendermaßen zu lösen probiert:

Code:

CREATE OR REPLACE FUNCTION LISTENFUELLSTAND(kidparam IN NUMBER)
RETURN NUMBER
IS
anzahl NUMBER;
name vl_kunden.name%type;
vorname vl_kunden.vorname%type;
BEGIN
  SELECT count(dvd.DLID), k.name, k.vorname
  INTO anzahl, name, vorname
  FROM vl_DVDLISTEN dvd, vl_listen l, vl_kunden k
  WHERE l.kid = kidparam
  and l.lid = dvd.lid
  and l.kid = k.kid
  and k.kid = kidparam
  GROUP BY vorname;
  RETURN anzahl;
  RETURN name;
  RETURN vorname;
END;
/

SELECT kid, LISTENFUELLSTAND(kid) as FÜLLSTAND
FROM vl_kunden;

Abfrageergebnisl:

ORA-00979: Kein GROUP BY-Ausdruck
ORA-06512: in "SYSTEM.LISTENFUELLSTAND", Zeile 8
00979. 00000 - "not a GROUP BY expression"
*Cause:
*Action:

GROUP BY macht irgendwie immer Zicken. Habe ihn schon mit anderen (kid, name) ausprobiert, aber das brachte nur in einem Fall ein Abfrageergebnis wie oben im Bild zu sehen, wo allerdings die beiden "0" durch "(null)" ersetzt wurden. Den GROUP BY benötige ich aber, weil ich ja zuvor mit COUNT arbeite und nun nicht mehr lediglich den COUNT habe, sondern noch weitere Spaltenbezeichner.

Zuvor hatte ich noch das Problem, dass ich eine Variable vergessen hatte und das Compiler-Protokoll dann sagte "Anzahl der Werte reicht nicht aus!".

Irgendwie habe ich den Select-Into noch nicht ganz verstanden, weil man bei diesem ja immer nur einen Wert in die Variable schreiben darf und für mehrere Zeilen einen CURSOR braucht. Ich verstehe nur grad nicht, wieso die KID´s alle ohne Probleme angezeigt werden und darüber hinaus nicht einmal eine Schleife benötigt wurde.

Ich verstehe echt nicht, wieso ich stundenlang an einer blöden Aufgabe sitze und dann doch nur Bruchstücke löse und gleichzeitig tausend neue Fragen aufwerfe.

**mary** · 03.08.2011, 15:28

Schon mal zur Aufgabe 2:

Code:

-- Lösung mit Cursor
set serveroutput on;
DECLARE
  CURSOR curTitel IS SELECT * FROM vl_titel;
  datensatz curTitel%ROWTYPE; -- Variable entspricht dem Cursor
  CURSOR curMedium IS SELECT * FROM vl_medien;
  datensatz2 curMedium%ROWTYPE;
  anzahl number;
  anzText CHAR(6);
  varmid vl_medien.mid%type;
BEGIN
  
  
  OPEN curTitel; -- Cursor öffnen
  FETCH curTitel INTO datensatz; -- nächste Zeile holen
  
  OPEN curMedium; -- Cursor öffnen
  FETCH curMedium INTO datensatz2; -- nächste Zeile holen
  
  WHILE curTitel%FOUND AND curMedium%FOUND LOOP -- Solange Zeile gefunden
    --SELECT mid into varmid FROM vl_medien where tid = datensatz.tid;
    
    SELECT count(*) into anzahl FROM vl_medien where tid = datensatz2.tid group by tid; --testweise 5028 oder 5835
  
  IF anzahl > 1 THEN    
    anzText := 'Medien';
  ELSE    
    anzText := 'Medium';
  END IF ;    
    
    DBMS_OUTPUT.PUT_LINE('Film ' || datensatz.tid || ': ' || datensatz.titel || ' gibt es auf ' || anzahl || ' ' || anzText || ': ' || datensatz2.mid);
    FETCH curTitel INTO datensatz; -- nächste Zeile holen
    FETCH curMedium INTO datensatz2; -- nächste Zeile holen
  END LOOP;
  
  CLOSE curTitel; -- Cursor schließen
  CLOSE curMedium; -- Cursor schließen
END;
/

Skriptausgabe:

anonymer Block abgeschlossen
Film 5028: Sherlock Holmes gibt es auf 1 Medium: 77777
Film 5835: Avatar - Aufbruch nach Pandora gibt es auf 2 Medien: 26421
Film 907: Selbst ist die Braut gibt es auf 2 Medien: 26422
Film 8670: Valentinstag gibt es auf 1 Medium: 6647
Film 9592: Die schönsten Eisenbahnen gibt es auf 1 Medium: 99767
Film 2893: Eisenbahn-Geschichte gibt es auf 1 Medium: 32206
Film 7402: Die Herzogin gibt es auf 1 Medium: 14989
Film 1752: Elizabeth - Das goldene Königreich gibt es auf 1 Medium: 29879

Hier ist es nun so, dass zwar korrekt zwischen Medium und Medien unterschieden wird, allerdings passt die Ausgabe der Medien-ID´s (mid) noch nicht.

Denn so sollte es ja wirklich aussehen:
Erstellen Sie ein PL/SQL Skript, dass für jeden Titel die Anzahl der Medien anzeigt.
Halten Sie sich an die folgende Ausgabe:

Film 5028: Sherlock Holmes gibt es auf 1 Medium: 77777
Film 5835: Avatar - Aufbruch nach Pandora gibt es auf 2 Medien: 26421,26422
Film 907: Selbst ist die Braut gibt es auf 1 Medium: 6647
Film 8670: Valentinstag gibt es auf 1 Medium: 99767
Film 9592: Die schönsten Eisenbahnen gibt es auf 1 Medium: 32206
Film 2893: Eisenbahn-Geschichte gibt es auf 1 Medium: 14989
Film 7402: Die Herzogin gibt es auf 1 Medium: 29879
Film 1752: Elizabeth - Das goldene Königreich gibt es auf 1 Medium: 66903

Die beiden fettgedruckten mid´s habe ich hier extra so gekennzeichnet, damit man sieht, dass diese beiden zum Film Avatar mit der tid 5835 gehören.

Leider ist bei mir offensichtlich alles um eine Zeile verrutscht.

**Wernfried** · 03.08.2011, 16:40

Zur Aufgabe 1:

Code:

Erstellen Sie eine Datenbankfunktion LISTENFUELLSTAND(kid), die den Füllstand der
Kundenliste für einen bestimmten Kunden anzeigt.
Die Abfrage SELECT kid,listenfuellstand(kid) from kunden; soll folgende Ausgabe
haben:
KID  NAME       VORNAME FÜLLSTAND
---- --------   ------- ----------------------
22   Gerlach    Heidi 3
23   Gerlach    Hilde 5
24   Meier      Kurt 3
25   Meier      Kurt 0
26   Brühbach   Rudolf 0

Das ist so nicht möglich, wenn 2 Attribute selektiert werden können im Ergebnis nicht 4 Spalten stehen, so wie Falk es bereits geschrieben hat. Die Abfrage laut Aufgabe müsste dieses hier ausgeben:

Code:

KID  LISTENFUELLSTAND(kid)
---- ----------------------
22   3
23   5
24   3
25   0
26   0

Ich würde mal behaupten die Aufgabe ist nicht sauber formuliert (genauso wie es später im realen Berufsleben sein wird

).

Gruss

**mary** · 03.08.2011, 18:32

Originally posted by Wernfried View Post

Zur Aufgabe 1:

Code:

Erstellen Sie eine Datenbankfunktion LISTENFUELLSTAND(kid), die den Füllstand der
Kundenliste für einen bestimmten Kunden anzeigt.
Die Abfrage SELECT kid,listenfuellstand(kid) from kunden; soll folgende Ausgabe
haben:
KID  NAME       VORNAME FÜLLSTAND
---- --------   ------- ----------------------
22   Gerlach    Heidi 3
23   Gerlach    Hilde 5
24   Meier      Kurt 3
25   Meier      Kurt 0
26   Brühbach   Rudolf 0

Das ist so nicht möglich, wenn 2 Attribute selektiert werden können im Ergebnis nicht 4 Spalten stehen, so wie Falk es bereits geschrieben hat. Die Abfrage laut Aufgabe müsste dieses hier ausgeben:

Code:

KID  LISTENFUELLSTAND(kid)
---- ----------------------
22   3
23   5
24   3
25   0
26   0

Ich würde mal behaupten die Aufgabe ist nicht sauber formuliert (genauso wie es später im realen Berufsleben sein wird

).

Gruss

Korrekt, die Ausgabe sieht so aus, wie Du es beschrieben hast und das habe ich ja weiter oben auch als Bildchen eingefügt gehabt.

Mir leuchtet auch nicht ein, wie es funktionieren soll, dass 4 Spalten angezeigt werden, obwohl nur 2 selektiert werden. Ich dachte halt, dass die Funktion die zu selektierenden Spalten ersetzt bzw. ergänzt. Aber so wie Du es ja hier beschreibst, scheint das so gar nicht möglich zu sein.

**Falk Prüfer** · 04.08.2011, 08:05

Originally posted by mary View Post

Code:

CREATE OR REPLACE FUNCTION LISTENFUELLSTAND(kidparam IN NUMBER)
RETURN NUMBER
IS
anzahl NUMBER;
BEGIN
  SELECT count(dvd.DLID)
  INTO anzahl
  FROM vl_DVDLISTEN dvd, vl_listen l  
  WHERE l.kid = kidparam
  and l.lid = dvd.lid;
  RETURN anzahl;
END;
/

SELECT kid, LISTENFUELLSTAND(kid) as FÜLLSTAND
FROM vl_kunden;

Damit hast du, zumindest für die Funktion, die KORREKTE Lösung gefunden! Der NAME und VORNAME haben in der Funktion nichts zu suchen und gehören in das abfragende Select. Wie schon gesagt ist das jedoch ein Fehler in der AUFGABENSTELLUNG.

Originally posted by mary View Post

...Statt des INNER JOINS habe ich einfach zwei equiJoins mit AND verknüpft, weil mich das weniger verwirrt. ;-) Ist aber im Prinzip dasselbe, nur mit anderer Syntax.

Mir ist die explizite Join-Syntax lieber und ich finde da nichts verwirrend. Eher im Gegenteil, man sieht viel besser was Join- und was reine Where-Bedingung ist.

Gruß Falk

**Falk Prüfer** · 04.08.2011, 08:30

Nun zur Aufgabe 2:
Dein Ansatz ist ja schonmal vielversprechend. Die Ermittlung der Medien ist jedoch fehlerhaft. Erstens gehört diese in die Cursor-Schleife der Titel. Und zum Zweiten fehlt der Bezug des Mediums zum Titel völlig.
Auch würde ich die Anzahl nicht über ein separates Select ermitteln, sondern dafür gleich eine entsprechende Gruppierung im Cursor verwenden.

[highlight=sql]
...
CURSOR curTitel IS
SELECT t.TID, T.TITEL, count(m.MID) anz_medien
FROM vl_titel t
LEFT JOIN vl_MEDIEN m ON m.TID = t.TID
[/highlight]

Auch gefällt mir prinzipiell die FOR Cursor LOOP besser, weil man sich da das explizite OPEN, FETCH und CLOSE spart

[highlight=sql]
declare
...
CURSOR curMedium(pTID in vl_medien.TID%TYPE) IS
SELECT MID, TID FROM vl_medien where TID = pTID
...
for datensatz in curTitel
loop
...
DBMS_OUTPUT.PUT_LINE('Film ' || ...

for datensatz2 in curMedium(datensatz.TID)
loop
// MIDs verketten und ausgeben
...
end loop;
end loop;
[/highlight]

Was da jetzt noch fehlt dürfte relativ einfach aus deinem bisherigen Versuch zu ersetzen sein.

Gruß Falk

**mary** · 04.08.2011, 17:26

an Falk:

Den FOR-CURSOR hatte ich zuvor auch lieber verwendet, dachte aber, dass die ausführlichere Aufdröselung Vorteile bieten könnte.

Stand der Dinge:
Aufgabe 1: gelöst
Aufgabe 2: zu 99 % gelöst
Aufgabe 3: zu 95 % gelöst
Aufgabe 4: zu 20 % gelöst
Aufgabe 5: gelöst
-------------------------------------------------------------------------------------------

Aufgabe 2:

Code:

set serveroutput on;
DECLARE  
  CURSOR curTitel IS
  SELECT t.TID, T.TITEL, count(m.MID) as anz_medien
  FROM vl_titel t
  LEFT JOIN vl_MEDIEN m ON m.TID = t.TID
  GROUP BY t.tid, t.titel;  
  
  CURSOR curMedium(pTID IN vl_medien.TID%TYPE) IS
    SELECT MID, TID FROM vl_medien WHERE TID = pTID;
  
  anzText CHAR(6);
  varmid vl_medien.mid%type;
BEGIN

  FOR datensatz IN curTitel
  loop
  IF datensatz.anz_medien > 1 THEN    
    anzText := 'Medien';
  ELSE    
    anzText := 'Medium';
  END IF;    
  DBMS_OUTPUT.PUT('Film ' || datensatz.tid || ': ' || datensatz.titel || ' gibt es auf ' || datensatz.anz_medien || ' ' || anzText || ': ');
    
    FOR datensatz2 IN curMedium(datensatz.TID)
    loop
      IF datensatz.anz_medien > 1 THEN    
      DBMS_OUTPUT.PUT(datensatz2.mid||', ');
      ELSE    
        DBMS_OUTPUT.PUT(datensatz2.mid);
      END IF;
      
    end loop;
    dbms_output.new_line;
  end loop;  
END;
/

Skriptausgabe:

anonymer Block abgeschlossen
Film 5028: Sherlock Holmes gibt es auf 1 Medium: 77777
Film 2893: Eisenbahn-Geschichte gibt es auf 1 Medium: 14989
Film 9592: Die schönsten Eisenbahnen gibt es auf 1 Medium: 32206
Film 5835: Avatar - Aufbruch nach Pandora gibt es auf 2 Medien: 26421, 26422,
Film 7402: Die Herzogin gibt es auf 1 Medium: 29879
Film 1752: Elizabeth - Das goldene Königreich gibt es auf 1 Medium: 66903
Film 907: Selbst ist die Braut gibt es auf 1 Medium: 6647
Film 8670: Valentinstag gibt es auf 1 Medium: 99767

Hier stört nur noch das Komma hinter der zweiten mid. Naja und irgendwie ist die Reihenfolge nun eine andere als sie vorgegeben war, was aber glaube ich nicht so schlimm ist.

-------------------------------------------------------------------------------------------

Aufgabe 3:

Code:

-- 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. 

NACHTRAG:
"Sie haben recht. Die Aufgabe 3 macht so keinen Sinn. Erstellen Sie die Funktion
einfach so, dass nicht die TID, sondern die MID übergeben wird."

ALSO:
Erstellen Sie eine STORED FUNCTION aktueller_kunde(mid), die für ein medium (mid)
zurückgibt, welcher Kunde dieses Medium zur Zeit entliehen hat. Ist das Medium nicht entliehen, so soll
NULL zurückgegeben werden.
*/

-- bisheriger Lösungsansatz:

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;
  
  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;
/

Skriptausgabe:

anonymer Block abgeschlossen
Das Medium mit der von Ihnen übergebenen MID: 66903 ist zur Zeit von Frau Heidi Gerlach entliehen.
Die zugehörige Kundennummer lautet:
22

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

------------------------------------------------------------------------------------------

Aufgabe 4:

Meine bisherigen Überlegungen:

KID 22 --> LID aus vl_listen ermitteln (l.kid = k.kid)
wähle aus vl_dvdlisten eine tid (where l.lid = dvd.lid), WENN
diese mindestens eine mid (bei mehreren müssen alle mid´s durchgeprüft werden)
in vl_medien hat und diese mid in vl_verschickt gar nicht steht
oder zurueck != NULL ist.
Wenn mid in vl_verschickt vorhanden, aber zurueck doch NULL ist, DANN
mit Fehlermeldung '20001, Kein Film mehr für Kunde 22!' abbrechen (Exception).

------------------------------------------------------------------------------------------

Aufgabe 5:

Code:

CREATE OR REPLACE TRIGGER fuellstand
AFTER INSERT OR DELETE ON vl_dvdlisten
FOR EACH ROW

BEGIN
  IF INSERTING THEN
  UPDATE vl_listen l SET füllstand = füllstand +1 where l.lid = :NEW.lid;
  END IF;  
  IF DELETING THEN
  UPDATE vl_listen l set füllstand = füllstand -1 where l.lid = :OLD.lid;
  END IF;  
END;
/

-- Testzwecke
INSERT INTO vl_DVDLISTEN VALUES (555522, 80891, 5028);
INSERT INTO vl_DVDLISTEN VALUES (555523, 80889, 907);
ROLLBACK;
DELETE FROM vl_dvdlisten WHERE dlid = 555522;

Läuft.

Allerdings habe ich die Füllwerte aus der vl_listen-Tabelle direkt auf die korrekten Werte gesetzt, damit es funktioniert. Sonst hätte ich statt "+1" oder "-1" jedesmal die Anzahl ermitteln müssen, was ich aber schwieriger finde.

**Wernfried** · 04.08.2011, 21:02

Originally posted by mary View Post

anonymer Block abgeschlossen
Film 5028: Sherlock Holmes gibt es auf 1 Medium: 77777
Film 2893: Eisenbahn-Geschichte gibt es auf 1 Medium: 14989
Film 9592: Die schönsten Eisenbahnen gibt es auf 1 Medium: 32206
Film 5835: Avatar - Aufbruch nach Pandora gibt es auf 2 Medien: 26421, 26422,
Film 7402: Die Herzogin gibt es auf 1 Medium: 29879
Film 1752: Elizabeth - Das goldene Königreich gibt es auf 1 Medium: 66903
Film 907: Selbst ist die Braut gibt es auf 1 Medium: 6647
Film 8670: Valentinstag gibt es auf 1 Medium: 99767

Hier stört nur noch das Komma hinter der zweiten mid. Naja und irgendwie ist die Reihenfolge nun eine andere als sie vorgegeben war, was aber glaube ich nicht so schlimm ist.

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

Announcement

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

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

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment