Lösung

Fachbereich Angewandte Informatik
Dipl.-Inf. Christian Pape
Datenbanksysteme 2
WS 11/12
7. Übung
Aufgabe 1:
In dieser Übung sollen Sie sowohl eine kleine Funktion als auch einen Trigger
in PL/SQL erstellen. Dafür verwenden wir die Studenten-Datenbank. Falls
Sie diese noch nicht in der 5. Übung generiert haben, können Sie das jetzt
nachholen, indem Sie die Datei
http://wi-labor.informatik.hs-fulda.de/dbs2/studenten-db.sql
herunterladen und im Oracle SQL*Worksheet ausführen. Die Tabelle PRUEFUNG sollte dann 33 Einträge, die Tabelle STUDENT 11 Einträge enthalten.
a.) Erstellen Sie eine Funktion bestanden(MATRIKELNR, LEHRVERANSTALTUNG)
die den Status eines Studenten in Bezug auf eine Lehrveranstaltung
ausgibt. Dabei sollen folgende Werte ausgegeben werden:
• ”bestanden”, falls der Student bzgl. der Lehrveranstaltung in
einem Versuch eine Note besser als 5.0 hat
• ”noch nicht bestanden”, falls der Student noch keine Note besser
als 5.0 hat, aber mindestens einen Versuch (von 3) übrig hat
• ”endgültig durchgefallen”, falls der Student alle drei Versuche mit
5.0 als Note absolviert hat
Die Funktion soll auf folgende Art und Weise zu verwenden sein:
SELECT NAME, BESTANDEN(MATRIKELNR, ’Prog I’) FROM STUDENT;
NAME
BESTANDEN(MATRIKELNR,’PROGI’)
–––––––––––––––––– –––––––––––––––––––––––––––––
Dall
noch nicht bestanden
Olm
bestanden
von Sinnen
bestanden
Barth
bestanden
Mittermeier
bestanden
Pastewka
bestanden
Engelke
bestanden
Schroeder
bestanden
Stratmann
bestanden
Frier
bestanden
Hoecker
endgültig durchgefallen
11 rows selected
Benutzen Sie für Ihre Implementierung folgendes Code-Gerüst:
CREATE OR REPLACE FUNCTION bestanden
(matrikelnr_in IN NUMBER, lehrveranstaltung_in IN VARCHAR2)
RETURN VARCHAR2 IS
-- Variablen
BEGIN
-- Quelltext
RETURN ’unbekannt’;
END;
/
SHOW ERRORS FUNCTION BESTANDEN;
CREATE OR REPLACE FUNCTION bestanden
(matrikelnr_in IN NUMBER, lehrveranstaltung_in IN VARCHAR2)
RETURN VARCHAR2 IS
countTotal NUMBER;
countPassed NUMBER;
BEGIN
SELECT COUNT(*) INTO countPassed FROM PRUEFUNG
WHERE PRUEFUNG.MATRIKELNR=matrikelnr_in
AND PRUEFUNG.LEHRVERANSTALTUNG=lehrveranstaltung_in
AND PRUEFUNG.NOTE < 5.0;
SELECT COUNT(*) INTO countTotal FROM PRUEFUNG
WHERE PRUEFUNG.MATRIKELNR=matrikelnr_in
AND PRUEFUNG.LEHRVERANSTALTUNG=lehrveranstaltung_in;
IF (countPassed>=1) THEN
RETURN ’bestanden’;
ELSE
IF (countTotal=3) THEN
RETURN ’endgültig durchgefallen’;
ELSE
RETURN ’noch nicht bestanden’;
END IF;
END IF;
END;
/
SHOW ERRORS FUNCTION BESTANDEN;
Variablen werden mit dem Namen und dem Datentyp deklariert. Füllen
kann man diese beispielsweise durch eine SELECT-Anfrage:
CREATE OR REPLACE FUNCTION ...
ZAHL NUMBER;
.
.
.
IS
BEGIN
.
.
.
SELECT COUNT(*) INTO ZAHL FROM TABELLE WHERE ...
.
.
.
IF (ZAHL>5) THEN
;
.
.
.
END IF;
END;
b.) Bis jetzt ist es ohne Einschränkungen möglich einen Prüfungsversuch für
einen Studenten einzufügen solange der Wert für Versuch zwischen 1
und 3 liegt. Daher ist es möglich, beispielsweise einen zweiten Versuch
einzufügen, auch wenn noch kein erster Versuch in der Tabelle vorhanden ist.
Aus diesem Grund soll nun ein Trigger TRIGGER_VERSUCH entwickelt
werden, der bei einer Einfügeoperation eines Versuches v in die Tabelle
PRUEFUNG prüft, ob bereits der Versuch v − 1 in der Tabelle vorhanden ist. Falls nicht soll mit der Prozedur RAISE_APPLICATION_ERROR
ein Fehler generiert werden.
Benutzen Sie für Ihre Implementierung folgendes Code-Gerüst:
CREATE OR REPLACE TRIGGER TRIGGER_VERSUCH
BEFORE INSERT ON PRUEFUNG FOR EACH ROW
DECLARE
-- Variablen
BEGIN
-- Quelltext
-- bei Fehler
RAISE_APPLICATION_ERROR(-20000, ’Fehler:
END;
/
Ungültiger Versuch’);
SHOW ERRORS FUNCTION BESTANDEN;
CREATE OR REPLACE TRIGGER TRIGGER_VERSUCH
BEFORE INSERT ON PRUEFUNG FOR EACH ROW
DECLARE
versuche number;
BEGIN
SELECT COUNT(*) INTO versuche FROM PRUEFUNG
WHERE MATRIKELNR=:new.MATRIKELNR AND LEHRVERANSTALTUNG=:new.LEHRVERANSTALTUNG;
IF (versuche+1 != :new.VERSUCH) THEN
RAISE_APPLICATION_ERROR(-20000, ’Fehler:
END IF;
END;
/
Ungültiger Versuch’);
SHOW ERRORS TRIGGER TRIGGER_VERSUCH;
Auf die einzufügenden Daten können Sie innerhalb des Triggers mit
:new.SPALTENNAME zugreifen. Also beispielsweise:
IF (:new.MATRIKELNR=100000) THEN
-- tue dies
ELSE
-- tue das
END IF;
Überprüfen Sie die Funktionsfähigkeit Ihres Triggers, indem Sie versuchen für den Studenten Karl Dall einen zweiten oder dritten Versuch
für die Lehrveranstaltung ”Prog I” einzufügen.