SET serveroutput ON SIZE 20000 word_wrapped;

CREATE OR REPLACE PROCEDURE adaugas(cods IN NUMBER, nume IN VARCHAR2, adresa IN VARCHAR2, datan IN DATE, bursa IN NUMBER, tel IN NUMBER, cnp IN NUMBER) AS
BEGIN
    INSERT INTO student VALUES (cods, nume, adresa, datan, bursa, tel, cnp);
EXCEPTION
    WHEN dup_val_on_index THEN
        dbms_output.put_line('Acel student exista deja.');
END;
/

CREATE OR REPLACE PROCEDURE afisn(cod_s IN NUMBER) AS

CURSOR c_curs IS SELECT c.*, n.nota, n.data FROM curs c, note n WHERE c.codc = n.codc AND n.cods = cod_s;
r_note c_curs%ROWTYPE; --pentru cursorul de mai sus
r_student student%ROWTYPE; --pentru informatii student
nr_curent NUMBER;
medie NUMBER;
suma_note NUMBER;

BEGIN
    --initializare
    nr_curent := 1;
    suma_note := 0;

    --informatii student
    SELECT * INTO r_student FROM student s WHERE s.cods = cod_s;

    --afisare
    dbms_output.put_line('Nume Student: ' || r_student.nume || ', Bursa: ' || r_student.bursa);

    --deschidere cursor, fetching
    OPEN c_curs;
    LOOP
        FETCH c_curs INTO r_note;
        EXIT WHEN c_curs%notfound;

        dbms_output.put_line('Nr. Crt: ' || nr_curent || ', CodC: ' || r_note.codc || ', Titlu Curs: ' || r_note.titlu || ', Nota: ' || r_note.nota || ', Data: ' || r_note.data || ', Profesor: ' || r_note.numeprof);

        nr_curent := nr_curent + 1;
        suma_note := suma_note + r_note.nota;
    END LOOP;
    CLOSE c_curs;

    medie := suma_note / (nr_curent - 1);
    dbms_output.put_line('Medie: ' || medie);
EXCEPTION
    WHEN no_data_found THEN
        dbms_output.put_line('Tabele nepopulate');
END;
/

CREATE OR REPLACE PROCEDURE acstatn(n IN NUMBER) AS
BEGIN
    FOR rand IN
        (SELECT s.cods, s.nume, s.adresa, COUNT(n.cods) AS nr_note, AVG(n.nota) AS medie
        FROM student s
        join note n ON s.cods = n.cods
        GROUP BY s.cods, s.nume, s.adresa, COUNT(n.cods), AVG(n.nota)
        HAVING medie > n)
    LOOP
        dbms_output.put_line('CodS: ' || rand.cods || ', Nume: ' || rand.nume || ', Adresa: ' || rand.adresa || ', Nr. Note: ' || rand.nr_note || ', Media: ' || rand.medie);
    END LOOP;
END;
/