set serveroutput on
create or replace procedure vypis_priemer
is
  type zaznam is record (
    z_meno os_udaje.meno%type,
    z_priezvisko os_udaje.priezvisko%type,
    z_priemer number
  );
  type pole_zaznam is table of zaznam;
  pole_studentov pole_zaznam;
  i integer;
begin
  select ou.meno, ou.priezvisko,
    round((sum(zp.ects * decode(zp.vysledok, 'A', 1, 'B', 1.5, 'C', 2, 'D', 2.5, 'E', 3, 4))/
           sum(zp.ects)), 2) as vaz_priemer
    bulk collect into pole_studentov
    from os_udaje ou
    join student s on (ou.rod_cislo = s.rod_cislo)
    join zap_predmety zp on (s.os_cislo = zp.os_cislo)
    join predmet_bod pb on (zp.skrok = pb.skrok and zp.cis_predm = pb.cis_predm)
      where (pb.forma_kont = 's'
      or pb.forma_kont = 'e')
      group by ou.meno, ou.priezvisko, ou.rod_cislo, s.os_cislo
      order by vaz_priemer;

    for i in 1 .. pole_studentov.count loop
      dbms_output.put_line(pole_studentov(i).z_meno || '|' || pole_studentov(i).z_priezvisko || '|' || pole_studentov(i).z_priemer);
    end loop;
end;
/