Oracle WITH: uso della clausola

Oracle with: questa clausola, introdotta a partire dalla versione Oracle 9.2, consente di eseguire una query in una tabella utilizzando come parametro il risultato di un’altra query.

Come esempio immaginiamo di avere una tabella di rilievi di temperature e voler selezionare i record in cui i valori sono superiori alla media.

create table temperature(
  id number(10),
  valore number(10,2),
  data_rilievo timestamp
);

Aggiungiamo un po’ di dati:

Una possibile soluzione per questo problema è la query seguente:

select valore from temperature
where valore > (
  select avg(valore) from temperature
)

che da’ il seguente recordset

Con la clausola WITH costruiamo una tabella temporanea con una sola colonna e una sola riga che contiene il valore medio. Potremo successivamente riferirci al valore contenuto in questo recordset per ritornare il recordset ottenuto con la query standard di prima:

WITH tmpTemperature (tmpMedia) as
    (SELECT avg(valore) FROM Temperature)

SELECT valore
FROM Temperature, tmpTemperature
WHERE Temperature.valore > tmpTemperature.tmpMedia;

In sostanza si tratta di definire l’oggetto temporaneo che è un recordset con una riga e una colonna; poi si esegue la query sulla tabella delle temperature utilizzando la tabella temporanea definita in virtù della clausola WITH.

Il recordset ritornato è identico a quello di prima:

Quindi fondamentalmente la sintassi della clausola WITH è

WITH tabellaTemporanea (parametro) AS
    (select di calcolo del parametro)

SELECT * 
FROM tabella1, tabella2, ..., tabellaTemporanea 
WHERE 
    ... condizione su tabellaTemporanea.parametro...
;

Risorse esterne per Oracle WITH

Oracle Base