BÜCHER [No4 ist Mist]

SET serveroutput ON;

--aufgabe buch no1 'Dicke Bücher'
CREATE OR REPLACE FUNCTION f1(zahl IN NUMBER) RETURN NUMBER AS

num_berd NUMBER := zahl;
zahl_2 NUMBER := 0;

BEGIN

    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM buch WHERE anz_seiten > ' || num_berd INTO zahl_2;
    RETURN zahl_2;

END;

DECLARE
num_berd NUMBER := 0;
BEGIN
    num_berd := f1(500);
    DBMS_OUTPUT.put_line(num_berd);
END;


--aufgabe buch no2 'Meiste Kopien'
CREATE OR REPLACE FUNCTION f2(isbn OUT VARCHAR2) RETURN NUMBER AS

anz NUMBER := 0;

CURSOR c1 IS SELECT COUNT(*) anz_in_cur, buch_kopien.isbn FROM buch_kopien GROUP BY buch_kopien.isbn;
c2 c1%ROWTYPE;

BEGIN
OPEN c1;
    LOOP

            FETCH c1 INTO c2;
            EXIT WHEN c1%notfound;

            IF anz < c2.anz_in_cur THEN
                anz := c2.anz_in_cur;
                isbn := c2.isbn;
            END IF;

    END LOOP;

    RETURN anz;
CLOSE c1;
END;


DECLARE
isbn VARCHAR2(255) := '';
anz NUMBER := -1;
BEGIN

    anz := f2(isbn);
    DBMS_OUTPUT.put_line('Anzahl: ' || anz || ', ISBN: ' || isbn);

END;

--aufgabe buch no3 'Überfällige bücher'

CREATE OR REPLACE FUNCTION f3(datum IN DATE) RETURN NUMBER AS

check_this_date DATE := datum;
NAME_des_BUCHES VARCHAR2(255) := '';
num_bernd NUMBER := 0;

CURSOR c1 IS SELECT COUNT(*) count_youre_books, KUNDE.AUSWEISNR, kunde.Vorname, AUSLEIHE.barcode_id, AUSLEIHE.ENTLEIHENDE FROM kunde, ausleihe WHERE AUSLEIHE.ENTLEIHENDE < check_this_date GROUP BY KUNDE.AUSWEISNR, kunde.Vorname, AUSLEIHE.barcode_id, AUSLEIHE.ENTLEIHENDE ORDER BY KUNDE.AUSWEISNR;

c2 c1%ROWTYPE;

BEGIN
OPEN c1;
LOOP

    FETCH c1 INTO c2;
    EXIT WHEN c1%notfound;

    num_bernd := c2.count_youre_books;
    EXECUTE IMMEDIATE 'SELECT BUCH.TITEL from buch, buch_kopien WHERE buch_kopien.barcode_id = ' || c2.barcode_id || ' AND buch.isbn = buch_kopien.isbn' INTO NAME_des_BUCHES;

    DBMS_OUTPUT.put_line('K_ID: ' || c2.AUSWEISNR || ', Vorname: ' || c2.vorname || ', Buch: ' || NAME_des_BUCHES || ', Termin: ' || c2.ENTLEIHENDE);

END LOOP;
CLOSE c1;

RETURN num_bernd;
END;

DECLARE
datum DATE := SYSDATE;
zahl NUMBER := 0;
BEGIN
zahl := f3(datum);
END;

--aufgabe buch no4 'buchkopien eintragen'

CREATE OR REPLACE FUNCTION f4(isbn IN VARCHAR2, bar_code IN VARCHAR2) RETURN VARCHAR2 AS

ins_isbn VARCHAR2(13) := isbn;
ins_barcode VARCHAR2(255) := bar_code;
check_corr_val VARCHAR2(255) := 'Ihre Daten wurden nicht korrekt eingetragen!';

CURSOR c1 IS SELECT BUCH.ISBN FROM BUCH;
c2 c1%ROWTYPE;

BEGIN
OPEN c1;
    LOOP

        FETCH c1 INTO c2;
        EXIT WHEN c1%notfound;

        IF ins_barcode IS NOT NULL AND ins_isbn = c2.isbn THEN
            EXECUTE IMMEDIATE 'INSERT INTO buch_kopien(BARCODE_ID, ISBN) values(' || ins_barcode || ', ' || ins_isbn || ')';
            check_corr_val := 'Ihre Daten wurden korrekt eingetragen!';
        END IF;

    END LOOP;
CLOSE c1;

RETURN check_corr_val;

    EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
        RAISE_APPLICATION_ERROR(-20001, 'Der Barcode ist schon vergeben! [' || SQLERRM || ']');
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20001, 'Ein Fehler ist Aufgetreten! [' || SQLERRM || ']');
END;


DECLARE
    checker VARCHAR2(255) := '';

    isbn VARCHAR2(255) := '1-56592-335-9';
    bar_code VARCHAR2(255) := '100000212';
BEGIN
    checker := f4(isbn, bar_code);
    DBMS_OUTPUT.put_line(checker);
END;


SELECT COUNT(buch.isbn) FROM buch WHERE buch.isbn = '1-56592-335-9';
INSERT INTO buch_kopien(BARCODE_ID, ISBN) VALUES('100000212', '1-56592-335-9');

--aufgabe buch no 5 'bücher auslesen'

CREATE OR REPLACE FUNCTION f5(counter IN NUMBER, titel IN VARCHAR2) RETURN NUMBER AS

n_buecher NUMBER := counter;
titel_buch VARCHAR2(255) := titel;
komplett_buecher NUMBER := 0;
i_z NUMBER := 0;

CURSOR c1(tit VARCHAR2) IS SELECT BUCH.TITEL FROM BUCH WHERE BUCH.TITEL LIKE tit GROUP BY BUCH.TITEL;
c2 c1%ROWTYPE;

BEGIN

EXECUTE IMMEDIATE 'SELECT count(*) from BUCH' INTO komplett_buecher;

OPEN c1(titel_buch);
    LOOP
        i_z := i_z + 1;
        FETCH c1 INTO c2;
        EXIT WHEN c1%notfound OR i_z = n_buecher;

        DBMS_OUTPUT.put_line('Titel: ' || c2.TITEL);

    END LOOP;
CLOSE c1;

RETURN komplett_buecher;
END;


DECLARE
zahl NUMBER := 5;
wort VARCHAR2(255) := '%PL%';
gesamt NUMBER := 0;
BEGIN
gesamt := f5(zahl, wort);
DBMS_OUTPUT.put_line('Anzahl der Bücher: ' || gesamt);
END;