CREATE or REPLACE PROCEDURE BestMatch (p_utente SPECIFICHE.Utente %type,p_citta HOTEL.Citta %type,p_categoria HOTEL.Categoria %type) IS

cursor crs_hotel is
select H.Cod, Sum (Valore) as Somma
from HOTEL H, HOTEL_CRITERI HC, SPECIFICHE S
where H.Cod = HC.Cod and S.Criterio = HC.Nome and S.Utente = p_utente and H.citta = p_citta and H.Categoria = p_categoria
group by H.Cod;

v_hotel crs_hotel %ROWTYPE;
NumPreferenzeUtente integer;
v_valore HOTEL_CRITERI.Valore %type;
v_valore_max HOTEL_CRITERI.Valore %type;
nome_hotel HOTEL.Nome %type;

begin
  select count(Criterio) into NumPreferenzeUtente 
  from SPECIFICHE 
  where Utente = p_utente;

  if(NumPreferenzeUtente >0) then
    open crs_hotel;
    v_valore_max :=0;
    loop
      fetch crs_hotel into v_hotel;
      exit when crs_hotel %notfound;
      v_valore := v_hotel.Somma / NumPreferenzeUtente;
      if(v_valore > v_valore_max) then
        v_valore_max := v_valore;
        nome_hotel := v_hotel.Cod;
      end if;
    end loop;
    close crs_hotel;
  else
    select Nome into nome_hotel
    from HOTEL
    where Citta = p_citta and Categoria = p_categoria
    and Prezzo = (select Min(Prezzo) from HOTEL where Citta = p_citta and Categoria = p_categoria);
  end if;

  DBMS_OUTPUT.PUT_LINE ('Il miglior hotel per le pecifiche inserite risulata essere: ' || nome_hotel);
end;








--------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |          |     1K|  142K|  90451 |       |       |
|  SORT GROUP BY            |          |     1K|  142K|  90451 |       |       |
|   HASH JOIN               |          |     1K|  142K|  90370 |       |       |
|    TABLE ACCESS FULL      |NATION    |     1 |   29 |      1 |       |       |
|    MERGE JOIN             |          |    40K|    2M|  90327 |       |       |
|     SORT JOIN             |          |    40K|    2M|  87050 |       |       |
|      MERGE JOIN           |          |    40K|    2M|  84867 |       |       |
|       SORT JOIN           |          |    40K|    1M|  44564 |       |       |
|        NESTED LOOPS       |          |    40K|    1M|  42695 |       |       |
|         TABLE ACCESS BY IN|PART      |     1K|   32K|      7 |       |       |
|          INDEX RANGE SCAN |P_TYPE    |     1K|      |      1 |       |       |
|         TABLE ACCESS BY IN|LINEITEM  |     6M|  114M|     32 |       |       |
|          INDEX RANGE SCAN |IX_PART_L |     6M|      |      2 |       |       |
|       SORT JOIN           |          |     1M|   12M|  40303 |       |       |
|        TABLE ACCESS FULL  |ORDERS    |     1M|   12M|   7147 |       |       |
|     SORT JOIN             |          |   150K|    1M|   3277 |       |       |
|      TABLE ACCESS FULL    |CUSTOMER  |   150K|    1M|   1059 |       |       |
--------------------------------------------------------------------------------