SQL Oracle PL/SQL

FUNCTIONS.sql

-- EDB 1
-- Schreiben Sie eine PL/SQL-Funktion mit dem Namen "schaltjahr_check", die, wenn ein übergebenes Jahr ein Schaltjahr ist, den booleschen Wert TRUE zurückgibt und wenn nicht, den Wert FALSE. Schaltjahre mit 366 Tagen sind solche, die durch 400 teilbar sind und außerdem solche, die durch 4, aber nicht durch 100 teilbar sind. Alle anderen Jahre haben 365 Tage. Hinweis: Benutzen Sie die Modulo-Funktion MOD(n,m), die den Rest beim Teilen von n durch m liefert.
CREATE OR REPLACE FUNCTION schaltjahr_check(
    jahr IN NUMBER
) RETURN BOOLEAN
IS
BEGIN
    IF MOD(JAHR, 400) = 0 OR (MOD(JAHR, 4) = 0 AND MOD(JAHR, 100) != 0) THEN
        RETURN TRUE;
    end if;
    RETURN FALSE;
END;

-- EDB 2
-- Es soll eine Funktion "eur_to_usd" erstellt werden, der ein Wert in € übergeben wird und ihn umgerechnet in Dollar zurückgibt. Der Wechselkurs für diese Aufgabe ist 1.13 (1€ = 1,13$)
CREATE OR REPLACE FUNCTION eur_to_usd
(
    EUR IN NUMBER
) RETURN NUMBER
IS
BEGIN
    RETURN EUR * 1.13;
END;

-- EDB 3
-- Schreiben Sie eine PL/SQL-Funktion mit dem Namen "tage_im_jahr", die zu einem gegebenen Jahr die Zahl der Tage zurückliefert. Schaltjahre mit 366 Tagen sind solche, die durch 400 teilbar sind und außerdem solche, die durch 4, aber nicht durch 100 teilbar sind. Alle anderen Jahre haben 365 Tage. Hinweis: Benutzen Sie die Modulo-Funktion MOD(n,m), die den Rest beim Teilen von n durch m liefert.
CREATE OR REPLACE FUNCTION tage_im_jahr
(
    JAHR IN NUMBER
) RETURN NUMBER
IS
BEGIN
    IF MOD(JAHR, 400) = 0 OR (MOD(JAHR, 4) = 0 AND MOD(JAHR, 100) != 0) THEN
        RETURN 366;
    end if;
    RETURN 365;
END;

-- EDB 4
-- Schreiben Sie eine Taschenrechner-Funktion "taschenrechner", die die vier Grundrechenarten kann: Addition „+“, Subtraktion „-“, Division „/“, Multiplikation „*“! Der Funktion werden drei Parameter übergeben, den ersten Operanden, das Symbol der Rechenoperation und den zweiten Operanden. Das berechnete Ergebnis ist der Rückgabewert.
CREATE OR REPLACE FUNCTION taschenrechner
(
    OP1 IN NUMBER,
    OP IN CHAR,
    OP2 IN NUMBER
) RETURN NUMBER
IS
    arithmetic_exception EXCEPTION;
BEGIN
    IF OP = '+' THEN
        RETURN OP1 + OP2;
    ELSIF OP = '-' THEN
        RETURN OP1 - OP2;
    ELSIF OP = '*' THEN
        RETURN OP1 * OP2;
    ELSIF OP = '/' THEN
        RETURN OP1 / OP2;
    ELSE
        RAISE arithmetic_exception;
    END IF;
end;

-- EDB 5
-- Schreiben Sie eine Funktion mit dem Namen "anrede_bestimmen", der als Übergabeparameter ein Geschlecht und ein Nachname mitgegeben wird. Das Ergebnis soll wie folgt aussehen: "Sehr geehrte Frau Meier", bzw. "Sehr geehrter Herr Müller". Beim Geschlecht sind die zulässigen Werte "leer/NULL", "w" und "m", unabhängig von der Groß/Kleinschreibung, beim Nachnamen kann auch ein Nullwert übergeben werden. Wird kein Geschlecht oder kein Nachname übergeben (NULL), oder wird als Geschlecht ein ungültiger Wert übergeben, dann wird nur die Anrede "Sehr geehrte Damen und Herren" zurückgegeben, unabhängig von den Werten des anderen Parameters.
CREATE OR REPLACE FUNCTION anrede_bestimmen(
    geschlecht IN CHAR,
    nachname IN VARCHAR2
) RETURN VARCHAR2
IS
BEGIN
    IF geschlecht IS NOT NULL THEN
        IF nachname IS NOT NULL THEN
            IF upper(geschlecht) = 'M' THEN
                RETURN 'Sehr geehrter Herr ' || nachname;
            ELSE
                RETURN 'Sehr geehrte Frau ' || nachname;
            END IF;
        ELSE
            RETURN 'Sehr geehrte Damen und Herren';
        END IF;
    ELSE
        RETURN 'Sehr geehrte Damen und Herren';
    END IF;
END;

-- EDB 6
-- Schreiben Sie eine PL/SQL-Funktion "gehaltsklasse", die zu einem Angestellten die Gehaltsklasse ermittelt. Übergeben werden soll die Ang-Nr, ermittelt werden soll die Gehaltsklasse, d.h. die Geh_Klasse, so dass das Gehalt größer als das Min_Gehalt und kleiner oder gleich dem Max_Gehalt ist.
CREATE OR REPLACE FUNCTION gehaltsklasse(
    angNr IN NUMBER
) RETURN NUMBER
IS
    geh NUMBER;
    geh_kl NUMBER;
BEGIN
    SELECT GEHALT INTO geh FROM ANGESTELLTE WHERE ANG_NR = angNr;
    SELECT GEH_KLASSE INTO geh_kl FROM GEH_KLASSEN WHERE MIN_GEHALT < geh AND MAX_GEHALT > geh;
    RETURN geh_kl;
END;

-- EDB 7
-- Schreiben Sie eine PL/SQL-Funktion "stueckliste", die zu einer Teile_Nr die Stücklistentiefe bestimmt, d.h. die Länge des maximalen Wegs in der Stückliste, die zu einem Blatt führt. Die Stücklisten werden in der Struktur-Tabelle verwaltet, d.h. z.B. die Beziehung des Teils mit der Nummer 60 zum Teil 2 wird in der Struktur-Tabelle in der Zeile mit dem OTEIL = 60 und dem UTEIL = 2 gespeichert. Die Tiefe ist in diesem Beispiel 3. Verwenden Sie dazu das CONNECT-BY-Statement von Oracle!

-- EDB 8
-- Schreiben Sie eine PL/SQL-Funktion "zeichen_pruefen", die für einen übergebenen Text prüft, an welcher Stelle im Text ein Doppelpunkt enthalten ist! Der Rückgabewert soll vom Typ INTEGER sein und die Position enthalten, an der das gesuchte Zeichen, nämlich der Doppelpunkt, zum ersten Mal auftritt! Falls kein Doppelpunkt auftritt, soll der Wert 0 zurückgegeben werden. Sie können die Oracle-Funktion INSTR nutzen • instr( string1, string2 [, start_position [, nth_appearance ] ] ) ◦ string1: zu durchsuchender Text., ◦ string2: Substring, der in string 1 gesucht wird. ◦ Start_position: ist optional ◦ nth_appearance Wie oft der string 1 in string 2 auftritt, ist optional Instr gibt 0 zurück, wenn der string2 nicht gefunden wird.

PROCEDURES.sql

-- EDB 1
-- Schreiben Sie eine Prozedur mit dem Namen "gehaltsnachteile", die alle Nachnamen von Frauen ausgibt, die bei Ausübung des gleichen Berufs ein geringeres Gehalt als ein Mann haben. Die Prozedur sollte zeilenweise "Frauenname" "Männername" ausgeben.
CREATE OR REPLACE PROCEDURE gehaltsnachteile
IS
    CURSOR CUR_W IS SELECT NACHNAME, GEHALT, BERUF FROM ANGESTELLTE WHERE upper(GESCHLECHT) = 'W';
    w_nachname ANGESTELLTE.NACHNAME%TYPE;
    w_gehalt ANGESTELLTE.GEHALT%TYPE;
    w_beruf ANGESTELLTE.BERUF%TYPE;
    m_nachname ANGESTELLTE.NACHNAME%TYPE;
BEGIN
    OPEN CUR_W;
    LOOP
        FETCH CUR_W INTO w_nachname, w_gehalt, w_beruf;
        EXIT WHEN CUR_W%NOTFOUND;
        SELECT NACHNAME INTO m_nachname FROM ANGESTELLTE WHERE BERUF = w_beruf AND GEHALT > w_gehalt AND upper(GESCHLECHT) = 'M';
            IF m_nachname IS NOT NULL THEN
                DBMS_OUTPUT.PUT_LINE(w_nachname || ' ' || m_nachname);
                m_nachname := NULL;
            END IF;
    END LOOP;
END;

-- EDB 2
-- Schreiben Sie eine Prozedur mit dem Namen "angestellte", die zuerst den Text "Nachnamen" ausgibt, und danach alle Nachnamen aus der Tabelle "angestellte".
CREATE OR REPLACE PROCEDURE nachnamen
IS
    CURSOR CUR_ANGESTELLTE IS SELECT NACHNAME FROM ANGESTELLTE;
    nachname ANGESTELLTE.NACHNAME%TYPE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Nachnamen');
    OPEN CUR_ANGESTELLTE;
    LOOP
        FETCH CUR_ANGESTELLTE INTO nachname;
        EXIT WHEN CUR_ANGESTELLTE%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(nachname);
    END LOOP;
END;

-- EDB 3
-- Schreiben Sie eine Prozedur mit dem Namen "jahre", die alle Angestellten (Vorname, Nachname ) der letzten 25 Jahre getrennt durch ein Komma und ein Blank, ausgibt. Benutzte Spalte: Eintrittsdatum. Benutzen Sie dabei einen Cursor und berücksichtigen Sie, dass die Differenz von zwei Date-Spalten in Tagen ausgegeben wird!
CREATE OR REPLACE PROCEDURE jahre
IS
    vorname ANGESTELLTE.VORNAME%TYPE;
    nachname ANGESTELLTE.NACHNAME%TYPE;
    CURSOR CUR_ANGESTELLTE IS SELECT VORNAME, NACHNAME FROM ANGESTELLTE WHERE EINTRITTSDATUM BETWEEN ADD_MONTHS(CURRENT_DATE, -12*25) AND CURRENT_DATE;
BEGIN
    OPEN CUR_ANGESTELLTE;
    LOOP
        FETCH CUR_ANGESTELLTE INTO vorname, nachname;
        EXIT WHEN CUR_ANGESTELLTE%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(nachname || ', ' || vorname);
    END LOOP;
END;

-- EDB 4
-- Schreiben Sie eine Prozedur mit dem Namen "parameter", die einen ihr übergebenen String ausgibt.
CREATE OR REPLACE PROCEDURE parameter(
    parameter IN VARCHAR2
)
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE(parameter);
END;

-- EDB 5
-- Schreiben Sie eine Prozedur mit dem Namen "transform_name", der zwei Textvariablen für Nachname und Vorname übergeben werden und die als Ergebnis beide Variable hintereinandergeschrieben (Nachname, Vorname), getrennt durch ein Komma und in Großbuchstaben umgewandelt wieder ausgibt!
CREATE OR REPLACE PROCEDURE transform_name(
    vorname IN VARCHAR2,
    nachname IN VARCHAR2)
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE(upper(nachname) || ', ' || upper(vorname));
END;

-- EDB 6
-- Schreiben Sie eine PL/SQL-Prozedur mit dem Namen "hello_world", die "Hello World" ausgibt.
CREATE OR REPLACE PROCEDURE hello_world
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello World');
END;

TRIGGER.sql

-- EDB 1
-- Die Summer aller Gehälter darf ein bestimmtes Budget nicht überschreiten. Es soll ein Trigger "trg_budget_check" erstellt werden, der beim Hinzufügen eines neuen Mitarbeiters in der Tabelle "Angestellte" und gleichzeitigem Überschreitung eines Gesamtbudgets von 100.000€ der Abteilung angestossen wird. In diesem Fall sollen alle Gehälter, die höher als 5000€ sind, auf 5000€ gekürzt werden.
CREATE OR REPLACE TRIGGER trg_budget_check
    BEFORE INSERT
    ON ANGESTELLTE
    FOR EACH ROW
    DECLARE
        SUM_GEHALT INT;
    BEGIN
        SUM_GEHALT := 0;
        SELECT SUM(ANGESTELLTE.GEHALT) INTO SUM_GEHALT FROM ANGESTELLTE;
        IF SUM_GEHALT > 100000 THEN
            UPDATE ANGESTELLTE SET GEHALT = 5000 WHERE GEHALT > 5000;
        END IF;
    END;

-- EDB 2
-- Abteilungsleiter müssen einer Mindestgehaltsgruppe angehören. Beim Hinzufügen einer neuen Abteilung oder wenn sich der Leiter einer Abteilung ändert soll mit dem Trigger "trg_leiter_gehalt" geprüft werden, ob das Gehalt des Abteilungleiters mindestens in die Gehaltsgruppe 3 fällt. Ist das Gehalt zu niedrig, so soll es auf das Mindestgehalt dieser Gehaltsgruppe erhöht werden.
CREATE OR REPLACE TRIGGER trg_leiter_gehalt
    BEFORE INSERT OR UPDATE
    ON ABTEILUNGEN
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    DECLARE
        geh_leiter INT;
        geh_gr_leiter INT;
        min_geh_kl INT;
    BEGIN
        IF :NEW.LEITER <> :OLD.LEITER THEN
            SELECT GEHALT INTO geh_leiter FROM ANGESTELLTE WHERE ANG_NR = :NEW.LEITER;
            SELECT GEH_KLASSE INTO geh_gr_leiter FROM GEH_KLASSEN WHERE MIN_GEHALT < geh_leiter AND MAX_GEHALT > geh_leiter;

            IF geh_gr_leiter < 3 THEN
                SELECT MIN_GEHALT INTO min_geh_kl FROM GEH_KLASSEN WHERE GEH_KLASSE = 3;
                UPDATE ANGESTELLTE SET GEHALT = min_geh_kl WHERE ANG_NR = :NEW.LEITER;
            END IF;
        END IF;
    END;

-- EDB 3
-- Folgeverarbeitung bei Bestandsänderung. Wenn sich der Bestand eines Teils in der Tabelle Lagerbestand ändert, soll der Bestand in der Tabelle Teile automatisch mit angepasst werden. Der Trigger soll unter dem Namen "trg_bestand_update" erstellt werden.
CREATE OR REPLACE TRIGGER trg_bestand_update
    BEFORE UPDATE 
    ON LAGERBESTAND
    FOR EACH ROW 
    DECLARE 
    BEGIN
        IF NEW.BESTAND <> OLD.BESTAND THEN
            UPDATE TEILE SET BESTAND = NEW.BESTAND WHERE TNR = NEW.TNR;
        END IF;
    END;

-- EDB 4
-- Folgeverarbeitung vollständig bezahlter Rechnungen. Der Trigger "trg_umsatz_update" soll, sobald ein Kunde den Rechnungsbetrag eines Auftrags vollständig gezahlt hat, in der Tabelle Artikel die im Auftrag umgesetzten Mengen zum Jahresumsatz hinzu addieren.
CREATE OR REPLACE TRIGGER trg_umsatz_update
    AFTER UPDATE
    ON AUFTRAEGE
    REFERENCING NEW AS NEW
    FOR EACH ROW
    DECLARE
        menge INT;
        t_nr INT;
        n_jahresumsatz INT;
        CURSOR CUR_ARTIKEL IS SELECT MENGE, TNR, JAHRESUMSATZ FROM AUFTRAGSPOSITIONEN NATURAL JOIN ARTIKEL WHERE AUFTRAGSNR = :NEW.AUFTRAGSNR;
    BEGIN
        IF :NEW.BEREITS_GEZAHLT = 1 THEN
            OPEN CUR_ARTIKEL;
            LOOP
                FETCH CUR_ARTIKEL INTO menge, t_nr, n_jahresumsatz;
                EXIT WHEN CUR_ARTIKEL%NOTFOUND;
                n_jahresumsatz := n_jahresumsatz + menge;
                UPDATE ARTIKEL SET JAHRESUMSATZ = n_jahresumsatz WHERE TNR = t_nr;
            END LOOP;
        END IF;
    END;
    
-- EDB 5
-- Gehaltserhöhungen müssen mindestens in einer Höhe von 10% erfolgen. Bei Erhöhung des Gehalts eines Angestellten soll durch den Trigger "trg_erh_gehalt" geprüft werden, ob diese Regelung eingehalten wurde. Sollte der neue Wert zu niedrig sein, soll das Gehalt des Angestellten automatisch um dieses Minimum erhöht werden.
CREATE OR REPLACE TRIGGER trg_erh_gehalt
    BEFORE UPDATE
    ON ANGESTELLTE
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    DECLARE
        pr_gehalt DECIMAL(10, 2) := 0.0;
    BEGIN
        pr_gehalt := (:NEW.GEHALT / :OLD.GEHALT) * 100;
        IF pr_gehalt < 110 THEN
            :NEW.GEHALT := :OLD.GEHALT * 1.1;
        end if;
    END;

-- EDB 6
-- Archivierung gelöschter Auftragspositionen. Wenn ein Datensatz aus der Tabelle "Auftragspositionen" gelöscht wird, sollen die gelöschten Datensätz durch den Trigger "trg_pos_archiv" in der Tabelle "Positionsarchiv" archiviert werden.
CREATE OR REPLACE TRIGGER trg_pos_archiv
    BEFORE DELETE
    ON AUFTRAGSPOSITIONEN
    FOR EACH ROW
    BEGIN
        INSERT INTO POSITIONSARCHIV(TNR, AUFTRAGSNR, MENGE) VALUES (:OLD.MENGE, :OLD.AUFTRAGSNR, :OLD.MENGE);
    END;

-- EDB 7
-- Protokollierung von Gehaltsänderungen. Wenn in der Tabelle Angestellte Änderungen in der Spalte "Gehalt" vorgenommen werden, soll die betreffende Ang_Nr, die Gehaltsveränderung d.h. altes Gehalt und neues Gehalt sowie das aktuelle Tagesdatum, durch den Trigger "trg_geh_prot", in der Tabelle "Gehaltsprotokoll" protokolliert werden. Info: Aktuelles Tagesdatum über TO_CHAR(sysdate) einfügen.
CREATE OR REPLACE TRIGGER trg_geh_prot
    BEFORE UPDATE
    ON ANGESTELLTE
    FOR EACH ROW
    BEGIN
        IF :OLD.GEHALT <> :NEW.GEHALT THEN
            INSERT INTO GEHALTSPROTOKOLL(ANG_NR, ALT_GEHALT, NEU_GEHALT, DATUM) VALUES (:NEW.ANG_NR, :OLD.GEHALT, :NEW.GEHALT, TO_CHAR(sysdate));
        END IF;
    END;

-- EDB 8
-- Gehälter dürfen nicht sinken. Durch den Trigger "trg_geh_check" soll sichergestellt werden, dass in der Spalte Gehalt in der Tabelle "Angestellte" kein niedrigerer Wert als der bisherige eingetragen wird. In diesem Fall soll eine Ausnahme mit der ID:-20001 und dem Text:"Gehälter dürfen nicht sinken" ausgelöst werden.
CREATE OR REPLACE TRIGGER trg_geh_check
    BEFORE UPDATE
    ON ANGESTELLTE
    FOR EACH ROW
    BEGIN
        IF :OLD.GEHALT > :NEW.GEHALT THEN
            RAISE_APPLICATION_ERROR(-20001,'Gehälter dürfen nicht sinken');
        END IF;
    END;

-- EDB 9
-- Personalnummern dürfen nicht geändert werden. Durch den Trigger "trg_ang_nr_check" soll verhindert werden, dass die Spalte "Ang_Nr" in der Tabelle Angestellte nachträglich geändert wird. In diesem Fall soll eine Ausnahme mit der ID:-20001 und dem Text:"Personalnummern dürfen nicht geändert werden" ausgelöst werden.
CREATE OR REPLACE TRIGGER trg_ang_nr_check
    BEFORE UPDATE
    ON ANGESTELLTE
    FOR EACH ROW
    BEGIN
        IF :OLD.ANG_NR <> :NEW.ANG_NR THEN
            RAISE_APPLICATION_ERROR(-20001, 'Personalnummern dürfen nicht geändert werden');
        END IF;
    END;

-- EDB 10
-- Gehaltsklassen dürfen sich nicht überschneiden. Der Trigger "trg_geh_klassen" soll beim Einfügen/Ändern eines Datensatzes in der Tabelle geh_klassen die neuen bzw. geänderten Zeilen prüfen, ob sich hierdurch Überschneidungen zu anderen Gehaltsklassen ergeben. Sollte dies der Fall sein, soll der Trigger eine Exception mit der ID: -20001 und dem Text: "Gehaltsklassen dürfen sich nicht überschneiden" auslösen. (Tipp: Verwenden Sie zur Lösung einen autonomen Row-Trigger)
CREATE OR REPLACE TRIGGER trg_geh_klassen
    BEFORE INSERT OR UPDATE
    ON GEH_KLASSEN
    FOR EACH ROW
    DECLARE
        CURSOR CUR_MIN IS SELECT * FROM GEH_KLASSEN WHERE MIN_GEHALT < :NEW.MIN_GEHALT AND MAX_GEHALT > :NEW.MIN_GEHALT;
        geh GEH_KLASSEN%ROWTYPE;
    BEGIN
        OPEN CUR_MIN;
            FETCH CUR_MIN INTO geh;
        IF NOT CUR_MIN%NOTFOUND THEN
            RAISE_APPLICATION_ERROR(-20001, 'Gehälter dürfen sich nicht überschneiden');
        END IF;
    END;

-- EDB 11
-- Erstellen Sie einen Trigger mit dem Namen "trg_view_rechnung" auf der View "Rechnungen", welcher die Menge in der Tabelle "Auftragspositionen" anpasst, wenn versucht wird, dieses Feld in der View selber zu ändern.
CREATE OR REPLACE TRIGGER trg_view_rechnung
    INSTEAD OF UPDATE 
    ON RECHNUNGEN
    FOR EACH ROW 
    BEGIN 
        UPDATE AUFTRAGSPOSITIONEN SET MENGE = :NEW.MENGE WHERE AUFTRAGSNR = :NEW.AUFTRAGSNR AND TNR = :NEW.TNR;
    END;

-- EDB 12
-- Erstellen Sie einen Trigger mit dem Namen "trg_view_auftrag" auf der View "Rechnungen", welcher einen neuen Eintrag in der Tabelle Auftragspositionen macht, wenn versucht wird, in der View einen neuen Datensatz anzulegen.
CREATE OR REPLACE TRIGGER trg_view_auftrag
    INSTEAD OF INSERT
    ON RECHNUNGEN
    FOR EACH ROW 
    BEGIN 
        INSERT INTO AUFTRAGSPOSITIONEN(TNR, AUFTRAGSNR, MENGE) VALUES (:NEW.TNR, :NEW.AUFTRAGSNR, :NEW.MENGE);
    END;