CREATE OR REPLACE PACKAGE BODY unterricht_pkg_tw04n166 AS
-- Prozedur, um die Vortragenden eines Faches zu entfernen
PROCEDURE drop_fachzuordnung(mykbz IN VARCHAR)
IS
CURSOR GET_VORTRAGENDE IS -- Cursor ueber alle Vortragenden des Faches vom Studienjahr
SELECT personal_tw04n166.personen_id, personal_tw04n166.stunden
FROM personal_tw04n166
JOIN unterricht ON personal_tw04n166.personen_id = unterricht.personen_id
JOIN faecher ON unterricht.fach_id = faecher.fach_id
WHERE faecher.kbz = mykbz AND unterricht.studienjahr = p_studienjahr;
myfach_id faecher.fach_id%TYPE := 0;
mystunden INT := 0;
result_votragende GET_VORTRAGENDE%ROWTYPE;
myname VARCHAR(50);
BEGIN
-- Ueberpruefung, ob ein gueltiges Studienjahr angegeben wurde
IF NOT check_studienjahr THEN
DBMS_OUTPUT.PUT_LINE('Package besitzt kein gueltiges Studienjahr!');
RETURN;
END IF;
-- pruefe, ob uebergebenes Fach existiert
BEGIN
SELECT fach_id INTO myfach_id
FROM faecher
WHERE kbz = mykbz;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Das Fach "' || mykbz || '" existiert nicht.');
RETURN;
END;
--DBMS_OUTPUT.PUT_LINE(mykbz);
OPEN GET_VORTRAGENDE;
LOOP
FETCH GET_VORTRAGENDE INTO result_votragende;
EXIT WHEN GET_VORTRAGENDE%NOTFOUND;
-- Update der Stunden
update_stunden(result_votragende.personen_id);
-- Ueberpruefung der gefundenen Lektoren:
/*DBMS_OUTPUT.PUT_LINE(result_votragende.personen_id);
SELECT personen.name INTO myname
FROM personen WHERE personen.personen_id = result_votragende.personen_id;
DBMS_OUTPUT.PUT_LINE(myname);*/
END LOOP;
CLOSE GET_VORTRAGENDE;
-- loeschen der Lektor-Relation in der Unterricht-Tabelle
DELETE FROM unterricht_tw04n166
WHERE fach_id = myfach_id AND studienjahr = p_studienjahr;
END drop_fachzuordnung;
-- Prozedur, um das Studienjahr zu setzen
PROCEDURE set_studienjahr(jahr IN INT) IS
BEGIN
p_studienjahr := jahr;
EXCEPTION
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Fehler beim Setzen des Studienjahres!');
END set_studienjahr;
-- Funktion, die das gesetzte Studienjahr zurueckliefert
FUNCTION get_studienjahr RETURN INT
IS
BEGIN
RETURN p_studienjahr;
END get_studienjahr;
-- Funktion, die ueberprueft, ob das gesetzte Studienjahr verfuegbar ist
FUNCTION check_studienjahr RETURN BOOLEAN
IS
CURSOR GET_STUDIENJAHRE IS
SELECT studienjahr FROM unterricht;
mystudienjahr unterricht.studienjahr%TYPE;
BEGIN
IF p_studienjahr = 0 THEN -- studienjahr ist auf default-Wert --> wurde noch nicht gesetzt
RETURN FALSE;
END IF;
OPEN GET_STUDIENJAHRE;
LOOP
FETCH GET_STUDIENJAHRE INTO mystudienjahr;
EXIT WHEN GET_STUDIENJAHRE%NOTFOUND;
IF mystudienjahr = p_studienjahr THEN -- gueltiges studienjahr
CLOSE GET_STUDIENJAHRE;
RETURN TRUE;
END IF;
END LOOP;
CLOSE GET_STUDIENJAHRE;
RETURN FALSE; -- kein gueltiges studienjahr gefunden
END check_studienjahr;
-- aktualisiert die Anzahl der unterrichtenden Stunden fuer einen Vortragenden
PROCEDURE update_stunden(vortragender_id IN INT)
IS
mystunden faecher.stunden%TYPE;
myid personal_tw04n166.personen_id%TYPE;
BEGIN
-- Ueberpruefung, ob ein gueltiges Studienjahr angegeben wurde
IF NOT check_studienjahr THEN
DBMS_OUTPUT.PUT_LINE('Das Package besitzt kein gueltiges Studienjahr!');
RETURN;
END IF;
-- pruefe, ob zu uebergebener ID ein Vortragender existiert
BEGIN
SELECT personen_id INTO myid
FROM personal_tw04n166
WHERE personen_id = vortragender_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Ein Vortragender mit der ID: ' || vortragender_id || ' existiert nicht!');
RETURN;
END;
BEGIN
-- Stunden berechnen
SELECT sum(faecher.stunden) INTO mystunden
FROM personal_tw04n166
JOIN unterricht_tw04n166 ON personal_tw04n166.personen_id = unterricht_tw04n166.unterricht_id
JOIN faecher ON unterricht_tw04n166.fach_id = faecher.fach_id
WHERE unterricht_tw04n166.studienjahr = p_studienjahr AND personal_tw04n166.personen_id = vortragender_id;
IF mystunden IS NULL THEN
--DBMS_OUTPUT.PUT_LINE('Stunden fuer den Vortragenden mit der ID: ' || vortragender_id || ' konnten nicht aktualisiert werden!');
--RETURN;
mystunden := 0;
END IF;
--DBMS_OUTPUT.PUT_LINE('Stunden: ' || mystunden);
-- Stunden aktualisieren
UPDATE personal_tw04n166
SET stunden = mystunden
WHERE personen_id = vortragender_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Stunden fuer den Vortragenden mit der ID: ' || vortragender_id || ' konnten nicht aktualisiert werden!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Fehler bei der Aktualisierung der Stunden des Vortragenden mit der ID: ' || vortragender_id || '!');
END;
END update_stunden;
-- Prozedur, die einen Lektor einem zu unterrichtenden Fach zuordnet (sofern nicht voll besetzt)
PROCEDURE assign_lektor_to_fach(myname IN VARCHAR, mykbz IN VARCHAR)
IS
mylektor_id personal_tw04n166.personen_id%TYPE;
myfach_id faecher.fach_id%TYPE;
lektor_summe INT := 0;
BEGIN
-- Ueberpruefung, ob ein gueltiges Studienjahr angegeben wurde
IF NOT check_studienjahr THEN
DBMS_OUTPUT.PUT_LINE('Das Package besitzt kein gueltiges Studienjahr!');
RETURN;
END IF;
-- ID des Vortragenden ermitteln
BEGIN
SELECT personal_tw04n166.personen_id INTO mylektor_id
FROM personal_tw04n166
JOIN personen ON personal_tw04n166.personen_id = personen.personen_id
WHERE personen.name = myname;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Der Vortragende "' || myname || '" konnte nicht gefunden werden!');
RETURN;
END;
-- ID des Faches ermitteln
BEGIN
SELECT fach_id INTO myfach_id
FROM faecher
WHERE kbz = mykbz;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Das Fach "' || mykbz || '" konnte nicht gefunden werden!');
RETURN;
END;
-- Ueberpruefung, ob der Vortragende bereits dem Fach zugeordnet ist
DECLARE
zuordnung_exists INT := 0;
BEGIN
SELECT COUNT(*) INTO zuordnung_exists
FROM unterricht_tw04n166
WHERE personen_id = mylektor_id AND fach_id = myfach_id;
IF zuordnung_exists > 0 THEN
DBMS_OUTPUT.PUT_LINE('Der Vortragende "' || myname || '" ist dem Fach "' || mykbz || '" bereits zugeteilt!');
RETURN;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN null;
END;
-- Anzahl der Vortragenden ermitteln
BEGIN
SELECT COUNT(personen_id) INTO lektor_summe
FROM unterricht_tw04n166
WHERE fach_id = myfach_id AND studienjahr = p_studienjahr;
--DBMS_OUTPUT.PUT_LINE('Summe: ' || lektor_summe);
IF lektor_summe > 3 THEN -- max. Anzahl kontrollieren
DBMS_OUTPUT.PUT_LINE('Die maximale Anzahl der Vortragenden (4) vom Fach "' || mykbz || '" ist bereits erreicht!');
RETURN;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Fehler beim Ermitteln der Anzahl der Vortragenden zum Fach "' || mykbz || '"!');
END;
-- Tupel in die Unterricht-Tabelle einfuegen
DECLARE
max_id INT := 0;--unterricht_tw04n166.unterricht_id%TYPE;
myreihung unterricht_tw04n166.reihung%TYPE;
BEGIN
-- hoechsten PK-Wert in der Tabelle unterricht_tw04n166 fuer das Einfuegen vormerken und dann um eins erhoehen (neuer Index fuer den PK)
SELECT MAX(unterricht_id) INTO max_id
FROM unterricht_tw04n166;
max_id := max_id + 1;
myreihung := lektor_summe + 1;
-- Zuordnung erstellen
INSERT INTO unterricht_tw04n166(unterricht_id, personen_id, fach_id, studienjahr, reihung)
VALUES (max_id, mylektor_id, myfach_id, p_studienjahr, myreihung);
-- Stunden updaten
update_stunden(mylektor_id);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Fehler beim Erstellen der Zuordnung zwischen dem Vortragenden "' || myname || '" und dem Fach "' || mykbz || '"!');
END;
END assign_lektor_to_fach;
-- Prozedur, die die Anzahl der im Studienjahr unzugeordneten Faecher ausgibt
PROCEDURE print_unassigned_faecher
IS
summe_gesamt INT := 0;
summe_assigned INT := 0;
diff INT := 0;
BEGIN
-- Ueberpruefung, ob ein gueltiges Studienjahr angegeben wurde
IF NOT check_studienjahr THEN
DBMS_OUTPUT.PUT_LINE('Das Package besitzt kein gueltiges Studienjahr!');
RETURN;
END IF;
SELECT COUNT(*) INTO summe_gesamt -- alle Faecher
FROM faecher;
SELECT DISTINCT COUNT(faecher.kbz) INTO summe_assigned -- zugeordnete Faecher
FROM faecher
JOIN unterricht_tw04n166 ON faecher.fach_id = unterricht_tw04n166.fach_id
WHERE unterricht_tw04n166.studienjahr = p_studienjahr;
diff := summe_gesamt - summe_assigned;
DBMS_OUTPUT.PUT_LINE('Summe der unzugeordneten Faecher im Jahr ' || p_studienjahr || ': ' || diff);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Fehler beim ermitteln der unzugeordneten Stunden vom Studienjahr ' || p_studienjahr || '!');
END print_unassigned_faecher;
-- Prozedur, um die Arbeitstabelle, mit der die Lehrfaecherverteilung generiert werden kann, mit den notwendigen Daten des aktuelle Studienjahres zu fuellen
PROCEDURE fill_arbeitstabelle
IS
BEGIN
-- Ueberpruefung, ob ein gueltiges Studienjahr angegeben wurde
IF NOT check_studienjahr THEN
DBMS_OUTPUT.PUT_LINE('Das Package besitzt kein gueltiges Studienjahr!');
RETURN;
END IF;
-- alte Eintraege des Studienjahres zuerst entfernen
DELETE FROM arbeit_tw04n166 WHERE studienjahr = p_studienjahr;
-- Arbeitstabelle fuellen
INSERT INTO arbeit_tw04n166(personen_id, fach_id, semester, studienjahr, stunden)
SELECT u.personen_id, u.fach_id, u.semester, u.studienjahr, faecher.stunden
FROM unterricht_tw04n166 u
JOIN faecher ON u.fach_id = faecher.fach_id
WHERE u.studienjahr = p_studienjahr;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Fehler beim Fuellen der Arbeitstabelle!');
END fill_arbeitstabelle;
-- Prozedur, um die Stunden aus der Arbeitstabelle in die Kreuztabelle einzufuegen
PROCEDURE create_verteilung
IS
CURSOR GET_FAECHER IS
SELECT DISTINCT arbeit_tw04n166.fach_id, faecher.kbz
FROM arbeit_tw04n166
JOIN faecher ON arbeit_tw04n166.fach_id = faecher.fach_id
WHERE studienjahr = p_studienjahr;
CURSOR GET_VORTRAGENDE IS
SELECT DISTINCT personen_id
FROM arbeit_tw04n166;
result_faecher GET_FAECHER%ROWTYPE;
mylektor_id arbeit_tw04n166.personen_id%TYPE;
mystunden arbeit_tw04n166.stunden%TYPE;
TYPE stunden_array IS TABLE OF FLOAT INDEX BY BINARY_INTEGER; -- Array mit allen Stunden eines Faches (5 Lektoren)
lektor_stunden stunden_array;
i INT := 0; -- Indexvariable fuer das Array
BEGIN
-- Ueberpruefung, ob ein gueltiges Studienjahr angegeben wurde
IF NOT check_studienjahr THEN
DBMS_OUTPUT.PUT_LINE('Das Package besitzt kein gueltiges Studienjahr!');
RETURN;
END IF;
DELETE FROM kreuztabelle_tw04n166; -- Tabelle leeren
-- alle Faecher durchlaufen
OPEN GET_FAECHER;
LOOP
FETCH GET_FAECHER INTO result_faecher;
EXIT WHEN GET_FAECHER%NOTFOUND;
i := 0; -- Indexvariable fuer das Array zuruecksetzen
-- alle Lektoren durchlaufen
OPEN GET_VORTRAGENDE;
LOOP
FETCH GET_VORTRAGENDE INTO mylektor_id;
EXIT WHEN GET_VORTRAGENDE%NOTFOUND;
BEGIN
-- Stunden ermitteln
SELECT stunden INTO mystunden
FROM arbeit_tw04n166
WHERE fach_id = result_faecher.fach_id AND personen_id = mylektor_id;
-- wenn beim Fach zu dem Lektor keine Stunden gefunden wurden, Stunden auf 0 setzen
EXCEPTION
WHEN NO_DATA_FOUND THEN
mystunden := 0;
END;
-- Stunden des aktuellen Lektors ins Array schreiben
i := i + 1;
lektor_stunden(i) := mystunden;
END LOOP;
CLOSE GET_VORTRAGENDE;
-- Werte in die Kreuztabelle einfuegen
INSERT INTO kreuztabelle_tw04n166
VALUES(result_faecher.fach_id, result_faecher.kbz, lektor_stunden(1), lektor_stunden(2), lektor_stunden(3), lektor_stunden(4), lektor_stunden(5));
END LOOP;
CLOSE GET_FAECHER;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Die Lehrfaecherverteilung konnte nicht erstellt werden!');
END create_verteilung;
END unterricht_pkg_tw04n166;