Già in un precedente articolo ho affrontato come vedere i lock Oracle con una query. Con questo articolo vorrei approfondire l’argomento proponendo una query più evoluta per visualizzare i lock.

La limitazione di quanto presentato nel precedente articolo era dovuta alla difficoltà di analizzare la risorsa loccata.

Per poter fare un’analisi più dettagliata entrano in gioco più tabelle e viste di sistema: continua a leggere »

Oracle mette a isposizione la potentissima clausola WITH che permette di assegnare un nome a una subquery e di poterla referenziare in altre subquery o nella query principale con l’alias definito.

In altri termini la clausola WITH permette di creare delle tabelle temporanee frutto dell’esecuzione di una query e di poter usare queste tabelle temporanee, referenziate con un nome, nella query di ricerca.

La sintassi della clausola WITH è la seguente:

WITH
  alias1 AS 
  (subquery1),
  alias2 AS
  (subquery2),
     .
     .
     .  
  aliasN AS 
  (subqueryN)
SELECT lista_colonne
FROM lista_tabelle
WHERE lista_condizioni;

alias: sono i nomi con i queli vengono identificate le subquery e con i quali verranno referenziate nelle altre subquery e in quella principale

subquery: sono gli statement SQL di selezione che potranno essere refrenziati successivamente

Gli alias delle subquery potranno comparire nella lista tabelle e i loro campi nella lista_condizioni.

Una clausola di WITH è molto utile quando i risultati delle query di WITH sono richiesti più di una volta nel corpo della query principale come ad esempio nei valori delle clausole di where.

Oracle mette a disposizione innumerevoli funzioni analitiche che agevolano la scrittura si query particolarmente complesse e spesso migliorandone le performance.

In questo articolo saranno descritte, con un breve caso d’uso, le funzioni che permettono di elaborare matematicamente dati numerici:

SUM(campo_1) OVER (PARTITION BY campo_2)

Questa funzione calcola la somma cumulativa dei valori del campo_1 raggruppando i record da sommare per il campo_2. la parola chiave partition serve per partizionare il result set in insiemi sui quali viene poi applicata la funzione analitica di somma.

SUM(campo_1) OVER (ORDER BY campo_2, campo_3)

Questa funzione serve per calcolare i totali parziali del campo_1. Dopo che i dati sono stati ordinati per campo_2, campo_3 viene sommado il dato in esame con il precedente.

SUM(campo_1) OVER ()

Calcola il totale assoluto. Questa funzione è equivalente a SUM(campo_1) che restituisce un solo valore, a differenza di questa la funzione analitica associa il valore ritornato ad ogni record risultante.

SUM(campo_1) OVER (PARTITION BY campo_2 ORDER BY campo_3, campo_4)

Esegue un mix delle prime due, ovvero calcola un totale parziale del campo_1 raggruppando per il campo_2 e ordinando per i campi campo_3 e campo_4.

Vediamo un esempio:

SELECt ufficio,
  nome,
  salario,
  SUM(salario) OVER (PARTITION BY ufficio
    ORDER BY salario,nome) CUM_SAL_UFFI,
  SUM(salario) OVER (PARTITION BY ufficio) TOT_UFFI,
  SUM(salario) OVER (ORDER BY ufficio,salario) CUM,
  SUM(salario) OVER () TOT
FROM impiegati
ORDER BY ufficio, salario;

UFFICIO     NOME  SALARIO  CUM_SAL_UFFI   TOT_UFFI        CUM        TOT
------- --------  -------  ------------ ---------- ---------- ----------
10      BIANCHI      1300          1300       8750       1300      29025
10      FERRARI      2450          3750       8750       3750      29025
10      ROSSI        5000          8750       8750       8750      29025
20      BANFI         800           800      10875       9550      29025
20      DE AMICIS    1100          1900      10875      10650      29025
20      DEL PIERO    2975          4875      10875      13625      29025
20      SACCHI       3000          7875      10875      19625      29025
20      VERDI        3000         10875      10875      19625      29025
30      ANASTASI      950           950       9400      20575      29025
30      GABBANA      1250          2200       9400      23075      29025
30      GUCCI        1250          3450       9400      23075      29025
30      MIRRI        1500          4950       9400      24575      29025
30      PISANO       1600          6550       9400      26175      29025
30      RICCI        2850          9400       9400      29025      29025

14 righe selezionate.

Dalla tabella degli impiegati andiamo a selezionare:

• UFFICIO, NOME, SALARIO
• CUM_SAL_UFFI: il salario cumulativo di ogni ufficio
• TOT_UFFI: il salario totale di ogni ufficio
• CUM: i salari cumulativi degli impiegati
• TOT: il salario totale di tutti gli impiegati