-- PRAK 7 

-- Aufgabe 1 
CREATE OR REPLACE VIEW first_view AS SELECT * FROM FUEHRERSCHEINKLASSEN ORDER BY KLASSENBEZEICHNUNG DESC;
CREATE OR REPLACE VIEW second_view AS SELECT * FROM KUNDEN WHERE PID = (SELECT PID FROM KUNDEN MINUS SELECT PID FROM PERSON_FUEHRERSCHEINKLASSE);
CREATE OR REPLACE VIEW third_view AS SELECT fk.KLASSENKENNUNG, fk.KLASSENBEZEICHNUNG, (SELECT count(*) FROM PERSON_FUEHRERSCHEINKLASSE pf WHERE pf.KLASSENKENNUNG = fk.KLASSENKENNUNG) COUNTER FROM FUEHRERSCHEINKLASSEN fk;
CREATE OR REPLACE VIEW fourth_view AS SELECT PID, Name, Strasse, PLZ, ORT, (SELECT count(*) FROM PERSON_FUEHRERSCHEINKLASSE WHERE PID = KUNDEN.PID) Klassen FROM KUNDEN ORDER BY Klassen DESC;

-- Aufgabe 2 
-- a) 
ALTER TABLE FUEHRERSCHEINKLASSEN ADD UNIQUE ( KLASSENBEZEICHNUNG );

-- b) 
ALTER TABLE KUNDEN MODIFY (ORT VARCHAR2(50) NULL, PLZ NUMBER(8) NULL);

CREATE OR REPLACE TRIGGER kunden_PLZ_ORT
AFTER INSERT ON KUNDEN 
FOR EACH ROW
BEGIN
   IF  :NEW.ORT is NULL AND :NEW.PLZ is NULL THEN   
        DBMS_OUTPUT.PUT_LINE('ahhhhh');
   END IF;
END kunden_PLZ_ORT;
/

INSERT INTO KUNDEN (PID, NAME, STRASSE, KONTONUMMER, BLZ) VALUES (kund_seq.NEXTVAL,'David', 'An der Mergelskaul 32' , 119191991, 37040366);
INSERT INTO KUNDEN VALUES (kund_seq.NEXTVAL,'David', 'An der Mergelskaul 32', 'Frechen', 50226, 119191991, 37040366);

-- Aufgabe 3 
CREATE OR REPLACE FUNCTION middle (x NUMBER, y NUMBER)
RETURN NUMBER
IS middle NUMBER;
BEGIN
    middle := x + y;
    middle := middle / 2;
    RETURN(middle);
END;
/

SELECT middle(4,8) FROM DUAL;

-- Aufgabe 4 
CREATE OR REPLACE PROCEDURE blub
(Strasse IN VARCHAR2, Nummer IN NUMBER, PLZ IN NUMBER, ORT IN VARCHAR2, NATION IN VARCHAR2, RUECK1 OUT VARCHAR2, RUECK2 OUT VARCHAR2)
IS
not_german EXCEPTION;
invalid_input EXCEPTION;
BEGIN
    IF Nummer is NULL OR PLZ is NULL OR ORT is NULL or NATION is NULL THEN
        RAISE invalid_input;
    END IF;
    IF NATION = 'D' THEN
        RUECK1 := NATION || ' ' || PLZ || ' ' || ORT;
        dbms_output.put_line(RUECK1);
    ELSE
        RAISE not_german;
    END IF;
    IF Strasse is NULL THEN
        RUECK2 := 'POSTFACH ' || Nummer;
        dbms_output.put_line(RUECK2);
    END IF;
EXCEPTION
    WHEN not_german THEN
        dbms_output.put_line('nicht in deutschland');
END;
/

-- Aufgabe 5 
CREATE OR REPLACE PROCEDURE bla(blub IN NUMBER) IS

cursor klassen is SELECT distinct KLASSENKENNUNG,
    (SELECT KLASSENBEZEICHNUNG FROM FUEHRERSCHEINKLASSEN WHERE KLASSENKENNUNG = pf.KLASSENKENNUNG) KLASSENBEZEICHNUNG,
    (SELECT BESCHREIBUNG FROM FUEHRERSCHEINKLASSEN WHERE KLASSENKENNUNG = pf.KLASSENKENNUNG) BESCHREIBUNG
    FROM PERSON_FUEHRERSCHEINKLASSE pf;

rec FUEHRERSCHEINKLASSEN%ROWTYPE;

BEGIN
    OPEN klassen;
    LOOP
        fetch klassen into rec;
        exit when klassen%notfound;
        dbms_output.put_line( rec.KLASSENKENNUNG || ' ' || rec.KLASSENBEZEICHNUNG || ' ' || rec.BESCHREIBUNG );
    END LOOP;
    CLOSE klassen;
END;
/