Hochschule Fulda 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; 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; 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.