-- Nie można edytować nazwiska ucznia, który ma conajmniej 1 ocene

CREATE OR REPLACE TRIGGER trigblok
BEFORE UPDATE OF Nazwisko ON Uczen
FOR EACH ROW

DECLARE
v_oceny NUMBER;

BEGIN

    BEGIN
        SELECT NVL(COUNT(OCENA),0) INTO v_oceny FROM OCENY WHERE idUczen = (SELECT idUczen FROM Uczen WHERE NAZWISKO=NEW:Nazwisko);
        EXCEPTION WHEN NO_DATA_FOUND
        v_oceny := 0;
    END

    IF ({NEW:Nazwisko IS NOT NULL)&&( v_oceny > 0 )} THEN
         RAISE_APPLICATION_ERROR(-20001, 'NIE MOŻNA EDYTOWAĆ UCZNIA, KTÓRY MA JUŻ JAKĄŚ OCENĘ');
    END IF;

END;

--- LICZENIE ŚREDNIEJ UCZNIÓW I WYPISYWANIE NA EKRAN

CREATE OR REPLACE PROCEDURE licz_srednia(p_grupa IN NUMBER) IS

  v_avg NUMBER;

BEGIN

  IF (p_grupa=0) THEN


    SELECT AVG(OCENA) INTO v_avg FROM OCENY o join uczen u ON u.iduczen = o.iduczen;
    DBMS_OUTPUT.put_line('Srednia wszystkich uczniów to: ' || v_avg);

  ELSE

  BEGIN
    SELECT AVG(OCENA) INTO v_avg FROM OCENY o JOIN Uczen U ON o.iduczen = u.iduczen join sklad_grupy s ON s.idUCzen = u.iduczen
    join grupa g ON g.idgrupa = s.idgrupa WHERE g.numer_grupy = p_grupa;
    DBMS_OUTPUT.put_line('Srednia wszystkich uczniów grupy '|| p_grupa ||  'to: ' || v_avg);

    EXCEPTION WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.put_line('Nie ma takiej grupy');
   END;
  END IF;



END;


----Trigger kontrolujący liczby studentow w grupie

CREATE OR REPLACE TRIGGER max_osob_w_grupie
BEFORE INSERT ON Sklad_grupy
FOR EACH ROW

DECLARE
v_ilosc NUMBER;

BEGIN

  SELECT COUNT(idUczen) INTO v_ilosc FROM sklad_grupy s join grupa g ON s.idgrupa = g.idgrupa join kurs k ON k.idgrupa = g.idgrupa GROUP BY g.idgrupa;

  IF (v_ilosc >=30 ) THEN
     RAISE_APPLICATION_ERROR(-20001, 'PRZEKROCZONO MAKSYMALNA LICZBE STUDENTÓW');
  END IF;

END;

---Procedura pokazujaca telefony

CREATE OR REPLACE PROCEDURE pokaz_telefony(p_grupa IN NUMBER) IS

  CURSOR telefony
  IS
  SELECT Numer_telefonu, Nazwisko FROM Uczen U join Sklad_grupy s ON u.iduczen = s.iduczen WHERE s.idgrupa = p_grupa;

  v_number NUMBER;
  v_name VARCHAR2(20);

BEGIN



  OPEN telefony;

  LOOP
    FETCH
       telefony INTO v_number, v_name;
       DBMS_OUTPUT.put_line(v_name || ' ' || v_number);

    EXIT WHEN telefony%NotFOUND;

  END LOOP;

  CLOSE telefony;


END;