Pasted

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;