create or replace
PACKAGE VOETBAL_PKG_2008 AS

  e_does_not_exist exception;
  pragma exception_init(e_does_not_exist, -2291);
  
  TYPE type_rec_ploeg IS RECORD (naam ploeg.naam%type, aantal_wedstrijden ploeg.aantal_wedstrijden%type, gewonnen ploeg.gewonnen%type, 
  verloren ploeg.verloren%type, gelijkgespeeld ploeg.gelijkgespeeld%type, score_voor ploeg.score_voor%type, 
  score_tegen ploeg.score_tegen%type, punten number(5));
  TYPE type_tab_ploeg IS TABLE OF type_rec_ploeg INDEX BY pls_integer;
  t_ploeg type_tab_ploeg;
  
  PROCEDURE KLASSEMENT(p_afdeling IN ploeg.afdeling%type);
  PROCEDURE OVERZICHT_SPELERS_PLOEG(p_ploeg in speler.ploeg%type);
  PROCEDURE PLOEGOPSTELLINGWEDSTRIJD (p_wedstrijd in selectie.wedstrijd%type);
  PROCEDURE VOEG_SPELER_TOE(p_bondnr in number, p_familienaam in varchar2, p_voornaam in varchar2, p_straat in varchar2, p_huisnr in varchar2, p_postcode in number, p_gemeente in varchar2, p_telefoon in varchar2, p_gebdat in date, p_type in varchar2, p_ploeg in varchar2);
  PROCEDURE TOPSCOORDERS (p_afdeling in varchar2);
  PROCEDURE SPEELDAG_KALENDER (p_afdeling in afdeling.rang%type, p_speeldag in wedstrijd.speeldag%type);
  PROCEDURE KLASSEMENT_CURSOR (p_afdeling IN ploeg.afdeling%type);
  PROCEDURE MEESTE_SELECTIES;
  
END VOETBAL_PKG_2008;



create or replace
PACKAGE BODY VOETBAL_PKG_2008 AS

  PROCEDURE KLASSEMENT (p_afdeling IN ploeg.afdeling%type) IS
  BEGIN
    select naam, aantal_wedstrijden, gewonnen, verloren, gelijkgespeeld, score_voor, score_tegen, gewonnen * 3 + gelijkgespeeld punten BULK COLLECT INTO t_ploeg
      from ploeg
      where p_afdeling = afdeling
      order by 8 desc;
    FOR i IN 1.. t_ploeg.count
    LOOP
      dbms_output.put_line(t_ploeg(i).naam || ' ' || t_ploeg(i).aantal_wedstrijden || ' ' || t_ploeg(i).gewonnen || ' ' || t_ploeg(i).gelijkgespeeld|| ' ' || t_ploeg(i).verloren || ' ' || t_ploeg(i).punten);
    END LOOP;
  END klassement;
  
  PROCEDURE OVERZICHT_SPELERS_PLOEG(p_ploeg in speler.ploeg%type)
  IS 
    TYPE type_rec_spelers IS RECORD (familienaam speler.familienaam%type, voornaam speler.voornaam%type, speler_type speler.type%type);
    TYPE type_tab_spelers IS TABLE OF type_rec_spelers INDEX BY pls_integer;
    t_spelers type_tab_spelers;
    
    BEGIN
      select familienaam, voornaam, type BULK COLLECT INTO t_spelers
        from speler s
        where ploeg = p_ploeg order by type;
      
      FOR i IN 1 .. t_spelers.count
      LOOP
        dbms_output.put_line(t_spelers(i).familienaam || ' ' || t_spelers(i).voornaam || ' ' || t_spelers(i).speler_type);
      END LOOP;
  END overzicht_spelers_ploeg;
  
  PROCEDURE PLOEGOPSTELLINGWEDSTRIJD (p_wedstrijd in selectie.wedstrijd%type)
  IS
    TYPE type_rec_spelers IS RECORD (familienaam speler.familienaam%type, voornaam speler.voornaam%type, speler2 selectie.speler%type, positie selectie.positie%type, ploeg speler.ploeg%type);
    TYPE type_tab_spelers IS TABLE OF type_rec_spelers INDEX BY pls_integer;
    TYPE type_tab_ploegen IS TABLE OF number(4) INDEX BY pls_integer;
    
    t_spelers type_tab_spelers;
    t_ploegen type_tab_ploegen;
    
    BEGIN
      select sp.familienaam, sp.voornaam, se.speler, se.positie, sp.ploeg BULK COLLECT INTO t_spelers
        from selectie se, speler sp where se.wedstrijd = p_wedstrijd and se.speler = sp.bondnr;
      
      select sp.ploeg BULK COLLECT INTO t_ploegen
        from selectie se, speler sp where se.wedstrijd = p_wedstrijd and se.speler = sp.bondnr
        group by sp.ploeg;
      
      FOR v_teller1 IN 1 .. 2
      LOOP
        dbms_output.put_line('Ploeg ' || v_teller1 || ': ');
        FOR v_teller2 IN 1 .. t_spelers.count
        LOOP
          IF t_spelers(v_teller2).ploeg = t_ploegen(v_teller1)
          THEN
            dbms_output.put_line(t_spelers(v_teller2).familienaam || ' ' || t_spelers(v_teller2).voornaam || ': ' || t_spelers(v_teller2).positie);
          END IF;
        END LOOP;
      END LOOP;
  END ploegopstellingwedstrijd;
  
  PROCEDURE VOEG_SPELER_TOE(p_bondnr in number, p_familienaam in varchar2, p_voornaam in varchar2, p_straat in varchar2, p_huisnr in varchar2, p_postcode in number, p_gemeente in varchar2, p_telefoon in varchar2, p_gebdat in date, p_type in varchar2, p_ploeg in varchar2)
  IS
    e_type exception;
    e_ploeg exception;
    e_naam exception;
    
    pragma exception_init(e_type, -2290);
    
    BEGIN
      if p_familienaam is null or length(p_familienaam) = 0 then
        raise e_naam;
      end if;
      insert into speler (bondnr, familienaam, voornaam, straat, huisnr, postcode, gemeente, telefoon, gebdat, type, ploeg)
      values (p_bondnr, p_familienaam, p_voornaam, p_straat, p_huisnr, p_postcode, p_gemeente, p_telefoon, p_gebdat, p_type, p_ploeg);
    EXCEPTION
      when dup_val_on_index then
        dbms_output.put_line('Bondnr reeds in gebruik!');
      when e_naam then
        dbms_output.put_line('Naam mag niet leeg zijn!');
      when e_type then
        dbms_output.put_line('Het type speler is niet geldig!');
      when e_does_not_exist then
        dbms_output.put_line('Ploeg bestaat niet!');
  END VOEG_SPELER_TOE; 
  
  PROCEDURE LOG_ERROR(p_tekst in varchar2) 
  IS
    pragma autonomous_transaction;
  begin
    insert into log_tabel (tijd, gebruiker, event) values (sysdate, user, p_tekst);
    commit;
  END;

  PROCEDURE TOPSCOORDERS (p_afdeling in varchar2) IS
    
    cursor c_topscoorders is
      select s.voornaam, s.familienaam, s.gebdat, count(*) score
        from speler s, doelpunt d, wedstrijd w
        where s.bondnr = d.speler and d.wedstrijd = w.nummer and w.afdeling = p_afdeling
        group by s.voornaam, s.familienaam, s.gebdat
        order by score desc;
    r_topscoorder c_topscoorders%rowtype;
    v_vorige_score NUMBER := 0;
    
    BEGIN
      open c_topscoorders;
      fetch c_topscoorders into r_topscoorder;
      while c_topscoorders%found and (c_topscoorders%rowcount <= 3 or r_topscoorder.score = v_vorige_score)
      loop
        dbms_output.put_line(r_topscoorder.voornaam || ' ' || r_topscoorder.familienaam || ' ' || r_topscoorder.score);
        v_vorige_score := r_topscoorder.score;
        fetch c_topscoorders into r_topscoorder;
      end loop;
      close c_topscoorders;
  END TOPSCOORDERS;

  PROCEDURE SPEELDAG_KALENDER (p_afdeling in afdeling.rang%type, p_speeldag in wedstrijd.speeldag%type) IS
    cursor c_wedstrijden is
      select tp.naam thuisploeg, bp.naam bezoekers, score_thuisploeg, score_bezoekers
      from wedstrijd w, ploeg tp, ploeg bp
      where speeldag = p_speeldag and w.afdeling = p_afdeling and tp.stamnr = w.thuisploeg and bp.stamnr = w.bezoekers;
  BEGIN
    for r_wedstrijd in c_wedstrijden
    loop
      dbms_output.put_line(r_wedstrijd.thuisploeg || ' - ' || r_wedstrijd.bezoekers || ': ' || r_wedstrijd.score_thuisploeg
        || ' - ' || r_wedstrijd.score_bezoekers);
    end loop;
  END SPEELDAG_KALENDER;

  PROCEDURE KLASSEMENT_CURSOR (p_afdeling IN ploeg.afdeling%type) IS
    cursor c_ploegen is
     select naam, aantal_wedstrijden, gewonnen, verloren, gelijkgespeeld, score_voor, score_tegen, gewonnen * 3 + gelijkgespeeld punten
      from ploeg
      where p_afdeling = afdeling
      order by 8 desc;
  
    BEGIN
     
      FOR r_ploeg IN c_ploegen
      LOOP
        dbms_output.put_line(r_ploeg.naam || ' ' || r_ploeg.aantal_wedstrijden || ' ' || r_ploeg.gewonnen || ' '
        || r_ploeg.gelijkgespeeld|| ' ' || r_ploeg.verloren || ' ' || r_ploeg.punten);
      END LOOP;
  END klassement_cursor;
  
  PROCEDURE MEESTE_SELECTIES
  IS
    cursor c_spelers is
      select sp.familienaam, sp.voornaam, count(*) selecties
        from speler sp, selectie se
        where se.speler = sp.bondnr
        group by sp.familienaam, sp.voornaam
        order by 3 desc;
    v_hoogste NUMBER := 0;
    r_speler c_spelers%rowtype;
    
    BEGIN
      open c_spelers;
      fetch c_spelers into r_speler;
      while c_spelers%found
      loop
        if v_hoogste <= r_speler.selecties then
          v_hoogste := r_speler.selecties;
          dbms_output.put_line(r_speler.familienaam || ' ' || r_speler.voornaam || ' ' || r_speler.selecties);
        end if;
        fetch c_spelers into r_speler;
      end loop;
      close c_spelers;
  END MEESTE_SELECTIES;


END VOETBAL_PKG_2008;