IDAS2_CV6_homefork

CREATE OR REPLACE PACKAGE pck_cv6 AS

FUNCTION student_prumer(p_id_studenta VARCHAR2) RETURN NUMBER;
PROCEDURE ucitel_seznam(p_id_ucitele NUMBER);
FUNCTION seznam_predmetu(p_id_studenta VARCHAR2) RETURN VARCHAR2;

END pck_cv6;

CREATE OR REPLACE PACKAGE BODY pck_cv6 AS

FUNCTION student_prumer(p_id_studenta VARCHAR2)
  RETURN NUMBER
  AS v_prumer NUMBER;
BEGIN
SELECT ROUND(AVG(znamka),2) AS prumer INTO v_prumer FROM A_SKOLA.uci
join A_SKOLA.studenti using (ID_STUDENTA)
WHERE ID_studenta LIKE p_id_studenta
GROUP BY ID_STUDENTA;
RETURN (v_prumer);
END student_prumer;

PROCEDURE ucitel_seznam(p_id_ucitele IN NUMBER)
AS
v_jmeno VARCHAR2(30);
v_prijmeni VARCHAR2(30);
BEGIN
SELECT DISTINCT jmeno, prijmeni INTO v_jmeno,v_prijmeni
FROM A_skola.ucitele
join A_SKOLA.uci using(id_ucitele)
WHERE id_ucitele=p_id_ucitele;
DBMS_OUTPUT.put_line('Ucitel:' || v_jmeno || ' ' || v_prijmeni );
FOR z IN (SELECT DISTINCT predmet,id_ucitele FROM A_skola.uci WHERE id_ucitele=p_id_ucitele)
LOOP
DBMS_OUTPUT.put_line('');
DBMS_OUTPUT.put_line('Predmet: ' || z.predmet);
DBMS_OUTPUT.put_line('*****');
  FOR x IN (
  SELECT DISTINCT predmet, id_ucitele, id_studenta, Studenti.jmeno, Studenti.prijmeni, znamka
  FROM A_skola.uci
  join A_skola.ucitele using(ID_UCITELE)
  join A_skola.studenti using(id_studenta)
  WHERE id_ucitele=p_id_ucitele
  )
    LOOP
      IF x.znamka IS NULL
      THEN
      DBMS_OUTPUT.put_line(x.jmeno || ' ' || x.prijmeni || '(' || 'X' || ')');
      ELSE
      DBMS_OUTPUT.put_line(x.jmeno || ' ' || x.prijmeni || '(' || x.znamka || ')');
      END IF;
    END LOOP;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.put_line('Error 404, no such teacher found!');
END;


 FUNCTION seznam_predmetu(p_id_studenta VARCHAR2)
RETURN VARCHAR2
AS
v_seznamFkinPredmetu VARCHAR2(200);
BEGIN
FOR X IN (SELECT ID_studenta, ID_ucitele, jmeno, prijmeni, predmet
FROM A_skola.ucitele
join A_skola.uci using (id_ucitele)
WHERE id_studenta = p_id_studenta
ORDER BY prijmeni)
LOOP
v_seznamFkinPredmetu:= v_seznamFkinPredmetu || x.predmet || '('|| x.jmeno || ' ' || x.prijmeni || ')'||';';
END LOOP;
IF(v_seznamFkinPredmetu IS NOT NULL)
THEN
RETURN v_seznamFkinPredmetu;
ELSE
     v_seznamFkinPredmetu:='Student je debil jako muj spolubydlici a tak misto toho aby si zapsal predmety radsi pari STAR WARS battlefront';
    RETURN v_seznamFkinPredmetu;
END IF;
END seznam_predmetu;

END pck_cv6;