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;