Funzioni in SQL

1. Funzioni in SQL

2. Tipi di Funzione • Funzioni numeriche • Funioni di stringhe • Funzioni di date • Funzioni di conversione • Funzioni di selezione

3. Funzioni in SQL Funzioni numeriche

4. Classi di funzioni numeriche • Le funzioni di Oracle si applicano a tre classi di numeri: • Valori singoli • O un numero vero e proprio 3365.4573 • O una variabile PL/SQL • Un numero tratto da una colonna o da una riga del database • Gruppi di valori Sono numeri di una colonna tratti da una serie di righe • Elenchi di valori serie di numeri che può comprendere • Elenchi di numeri veri e propri 34, 4, 23.45, 45.3 • elenchi di variabili PL/SQL • Colonne

5. Alcune funzioni numeriche a valori singoli valore1+valore2 valore1-valore2 valore1*valore2 valore1/valore2 ABS(valore) CEIL(valore) FLOOR(valore) LN(valore) LOG(valore) EXP(valore) POWER(Valore, esponente) SQRT(valore) MOD(valore, divisore) NVL(valore, sostituto) ROUND(valore, precisione) TRUNC(valore, precisione) Addizione Sottrazione Moltiplicazione Divisione Valore assoluto Parte intera superiore Parte intera inferiore Logaritmo naturale Logaritmo in base 10 Esponenziale in base e Valore elevato a esponente Radice quadrata Modulo Sostituto di valore se il valore è nullo Arrotondamento a valore di precisione Valore troncato a precisione

6. La tabella DUAL Oracle fornisce la definizione di una piccola tabella, chiamata DUAL, di una riga e una colonna che permette di calcolare il risultati delle funzioni su valori specifici. L’unico valore rappresentato nella tabella sarà il risultato della funzione (o composizione di funzioni) rappresentato come unica colonna Esempio: se si vuole calcolare Mod(63,5) basta scrivere Select mod(63,5) from DUAL In questo modo si può testare il funzionamento delle varie funzioni su dei valori particolari

7. Esercizi • Calcolare 34.8 modulo 4 • Calcolare il valore assoluto di -2349.25 • Calcolare la parte intera inferiore di 34.3 • Calcolare la parte intera inferiore di -34.3 • Calcolare la parte intera superiore di 234.534 • Calcolare la parte intera superiore di -3253.34245 • Arrotondare il valore 2345.5563 alla 1a cifra decimale • Arrotondare il valore -4686.3455 alla 2a cifra decimale • Calcolare 878.8712.3 • Calcolare -878.87-12.3 • Calcolare la radice quadrata di 45.67 • Calcolare e11.3 • Calcolare il logaritmo naturale di 11.3 • Calcolare il logaritmo in base 10 di 11.3

8. Esercizi Esercizio: calcolare per ogni articolo il prezzo lordo, inclusivo di spese di trasporto. Per gli elementi per cui l’IVA non è definita, imporre il valore del 10%, e considerare gratuite le spese di trasporto là dove non sono definite. Select art_nome, art_prezzo*(1+NVL(art_IVA, 10)/100)+NVL(art_spese_trasporto, 0), FROM Articoli

9. Funzioni di gruppo Già viste… COUNT, SUM,AVG,MAX,MIN + altre funzioni di tipo statistico (vedi guida Oracle 9) Nelle funzioni di gruppo i valori nulli non sono considerati, tranne che per la funzione COUNT dove si può richiedere (mediante la specifica (*)) di considerare anche le righe nulle.

10. Funzioni di gruppo e funzioni su valori singoli (1) Le funzioni su valori singoli possono essere composte fra di loro a piacimento, rispettando il numero dei parametri. Esempio: Mod(abs(34.5-56.32), ceil(sqrt(abs(234*(-24))))) L’argomento di una funzione di gruppo può essere una composizione di funzioni su valori singoli applicati alle righe. Esempio: AVG(power(round(sqrt(art_prezzo(1+IVA/100))))), 2) Non si possono invece comporre due funzioni di gruppo Sum(AVG(temperature)) (?????)

11. Funzioni numeriche di elenco Mentre le funzioni di gruppo operano su diverse righe, le funzioni di elenco operano su diverse colonne all’interno di un’unica riga. Le funzioni di elenco sono: Restituisce il primo valore non null nell’elenco delle espressioni Il valore più grande dell’elenco Il valore più piccolo nell’elenco Coalesce(valore1,valore2,…) Greatest(valore1,valore2,…) Least(valore1,valore2,…)

12. Calcolare per ogni giorno la temperatura massima, la temperatura minima e la prima temperatura non nulla nell’elenco Select data, Greatest (PA, CT, AG,ME) GST, Least (PA, CT, AG, ME) LST, Coalesce (PA, CT, AG , ME) COA From Temperature

13. Esercizio Stabilire per ogni riga della tabella articoli, il massimo e il minimo fra il prezzo dell’articolo o la somma del costo dell’IVA e delle spese di trasporto, e il primo valore non null tra la colonna art_prezzo e la colonna art_spese_trasporto Select Greatest (art_prezzo,art_prezzo*art_IVA/100+art_spese_trasporto), Least (art_prezzo,art_prezzo*art_IVA/100+art_spese_trasporto), Coalesce(art_prezzo, art_spese_trasporto) FROM Articoli

14. Funzioni in SQL Funzioni su stringhe

15. Funzioni su stringhe In Oracle le funzioni su stringhe operano in due modi: alcune creano oggetti nuovi a partire dai vecchi, modificando i valori di input, per esempio trasformando i valori minuscoli in maiuscoli. Altre forniscono un’informazione sulla stringa, come per esempio la sua lunghezza.

16. Alcune funzioni di stringa str1||str2, concat(str1, str2) ASCII(str) Chr(int) Instr(str, chr) Length(str) Lower (str) Upper(str) Initcap(str) • Concatenazione • Intero corrispondente codice ASCIIdel 1o carattere di str • Carattere corrispondente all’intero int dato in input nel codice ASCII • Trova la posizione del carattere chr nella stringa str. • Fornisce la lunghezza di una stringa str • Rende minuscoli tutti i caratteri di str • Rende maiuscoli tutti i caratteri di str • Rende maiuscolo il primo carattere di una stringa str

17. Alcune funzioni di stringa Substr (str, i,k) Instr(str, chr) LPAD(str,l [,car]) RPAD(str,l [,car]) LTRIM(str, chr) RTRIM(str, chr) TRIM(str, chr) • Estrae dalla stringa str la sottostringa che inizia nella posizione i ed è lunga k caratteri • Trova la posizione di un carattere chr in una stringa str • Riempie una stringa str fino a una certa lunghezza l aggiungendo a sinistra una serie di caratteri car • Riempie una stringa str fino a una certa lunghezza l aggiungendo a destra una serie di caratteri car • Elimina tutti i caratteri che rientrano in una serie specificata chr dalla parte sinistra di una stringa str • Elimina tutti i caratteri che rientrano in una serie specificata chr dalla parte sinistra di una stringa str • Elimina tutti i caratteri che rientrano in una serie specificata chr dalla parte da entrambe le estremità di una stringa str

18. Concatenazione di stringhe Per concatenare due stringhe si può utilizzare o l’operatore || oppure la funzione CONCAT. Questi operatori vengono applicati ai nomi di colonne e ad elementi letterali. Esempio: Visualizzare in un’unica colonna il nome e cognome di ogni Impiegato. Select Nome || ‘ ’ || Cognome From Impiegato Impiegato

19. Concatenazione di stringhe Alternativamente si può usare la funzione CONCAT. Select Concat(Concat(Nome, ‘ ’), Cognome) From Impiegato Impiegato

20. Esercizio Scrivere il nome di ogni componente, seguito dal suo codice fra parentesi Select (com_descrizione || ‘ (‘ ||com_cod || ‘)’) FROM Componenti

21. Rpad ed Lpad Sono due funzioni che permettono di aggiungere dei simboli rispettivamente alla destra e alla sinistra della stringa considerata. Sintassi: Rpad(str, lungh [,chr]) Lpad(str, lungh [,chr]) Dove str è la stringa considerata, lungh indica la lunghezza della stringa risultante (di conseguenza il numero di simboli aggiunti sarà lungh- length(str)) e chr indica il tipo di carattere che si vuole aggiungere. Se tale carattere viene omesso, il default è lo spazio. Questa funzione ha senso quando si utilizza l’interfaccia a riga di comando, dove le tabelle non sono graficamente ben definite come nella nostra interfaccia

22. Rpad, esempio Si vogliono aggiungere dei puntini alla fine di ogni nome per collegarli al cognome Impiegato Select Rpad(nome, 18, ‘.’) , Cognome From Impiegato Rpad(nome,18,‘.’) | Cognome ---------------------------- Luca..............|Rossi Mara..............|Bruni Ugo...............|Verdi Fabio.............|Neri

23. Lpad, esempio Si vogliono formattare i nomi, giustificandoli a destra Impiegato Select Lpad(nome, 18) , Cognome From Impiegato Lpad(nome,14) | Cognome ---------------------------- Luca|Rossi Mara|Bruni Ugo|Verdi Fabio|Neri

24. Rtrim, Ltrim, Trim Queste funzioni servono ad eliminare alcuni caratteri rispettivamente all’inizio, alla fine, e da entrambi i lati di una stringa. Sintassi: Rtrim(str, chr) Ltrim(str, chr) Trim(str,chr) Dove str è la stringa considerata e chr è un carattere o una sequenza di caratteri che si vuole eliminare rispettivamente dall’inizio, dalla fine e da entrambi i lati della lista.

25. Ltrim: esempio Libri Select Ltrim(titolo, '"' ) Titolo From Libri

26. Rtrim: esempio Libri Select Rtrim(titolo, '."') Titolo From Libri

27. Ltrim ed Rtrim: esempio Libri Select Ltrim(Rtrim(titolo, '."'), '"') Titolo From Libri

28. Ltrim, Rtrim: attenzione!! Libri Select Ltrim(titolo, '"Il ') Titolo From Libri Quello che c’è tra apici è un insieme e non una sequenza. Vengono eliminati dall’inizio tutti i simboli (case-sensitive) che appartengono all’insieme, indipendentemente dall’ordine

29. Trim, Esempio Libri Select Trim('"'FROM titolo) Titolo From Libri In questo modo si possono eliminare le virgolette ad entrambi i lati del titolo

30. Lower, Upper ed Initcap • Lower(str) rende minuscolo ogni carattere della stringa str. • Upper(str) rende maiuscolo ogni carattere della stringa str. • Initcap(str) rende maiuscola la prima lettera di ogni parola della stringa str.

31. Esempio Libri Select Upper(titolo), Lower(titolo), Initcap(titolo) From Libri

32. Substr Substr(str, pos, ncar) estrae dalla stringa str la sottostringa che inizia dalla posizione pos e che ha ncar caratteri. Se ncar non è specificata, si sottointendono tutti i caratteri fino alla fine E’ possibile anche inserire un numero negativo in pos, intendendo che il carattere di inizio viene contato a partire da pos elementi dalla fine.

33. Esempio Supponiamo che i primi due caratteri della collocazione rappresentino lo scaffale e che le ultime 4 rappresentino la collocazione nel suo scaffale Libri Select substr(collocazione, 1, 2) Scaffale substr(collocazione, -4) Colloc From Libri

34. Instr La funzione Instr(str, set [, inizio[, occorrenza]]) fornisce la posizione (se esiste) in cui la stringa set è presente nella stringa str a partire dalla posizione inizio. Se il parametro inizio non è specificato, assume valore 1 per default. Il parametro occorrenza indica l’occorrenza che si vuole trovare della stringa set. Se per esempio occorrenza è 3, vengono saltate le prime due occorrenze. Select Instr(titolo, 'Il', 1,1) from Libri Select titolo, Instr(titolo, 'di') Dove From Libri

35. Funzioni in SQL Funzioni di date e funzioni di conversione

36. Il tipo date In Oracle il tipo date è memorizzato in un formato che comprende giorno, mese, anno, ma anche ora, minuti e secondi. E’ poi possibile formattare la colonna in modo da mostrare solo la data, la data e l’ora, la data l’ora e i minuti, oppure la data l’ora i minuti e i secondi. Se non viene specificata nessuna formattazione, appare solo la data con giorno, mese e anno. Per le frazioni di secondo è possibile utilizzare il tipo timestamp

37. Funzioni di date Add_Months (n, data) Current_Date Current_Timestamp Extract (UT FROM data) Greatest(data1,data2,…) Least(data1, data2…) Last_day(data) Months_Between(data2,data1) Next_Day(data, ‘giorno’) Round(data, ‘formato’) Somma n mesi a data Data corrente nel fuso orario della sessione Indicatore orario nel fuso orario della sessione Estrae una porzione di data (UT=unità di tempo) da un valore data La più recente fra le date elencate La meno recente fra le date elencate La data dell’ultimo giorno del mese a cui appartiene la data Fornisce data2-data1 in mesi (eventualmente numero frazionario) Data del giorno successivo a date dove giorno=‘Monday’, ‘Tuesday’ etc Senza un formato specificato arrotonda una data a 12 A.M.se l’ora della data è prima di mezzogiorno, altrimenti alle 12 A.M del giorno successivo.

38. Sysdate, Current_date e Systimestamp Sysdate è una funzione che da come risultato la data e l’ora correnti Systimestamp segnala la data di sistema nel formato del tipo di dati timestamp Select Sysdate From Dual Select Systimestamp From Dual

39. Differenza fra due date La differenza fra due date restituisce il tempo, espresso in giorni, che intercorre fra le due date. Esercizio Per ogni coppia di ordini distinti, trovare il tempo che intercorre fra essi. Select o.ord_cod, d.ord_cod, o.ord_data-d.ord_data From ordini o, ordini d Where o.ord_cod>d.ord_cod

40. Aggiunta di mesi Add_Months(data,n)aggiungen mesi alla data considerata. Se n è un numero negativo, si ottiene la sottrazione di n mesi dalla data. Esercizio Nella tabella ordini, visualizzare una colonna “scadenza”, che avviene due mesi dopo la data in cui l’ordine è effettuato Select ordini.*, Add_months(ord_data,2) Scadenza From Ordini

41. Greatest e Least Greatest(data1, data2,…) seleziona la massima fra le date dell’elenco. Least(data1,data2,…) seleziona la minima fra le date dell’elenco.

42. Esempio Persone Creare la tabella descritta sopra e inserire i dati. Per ogni persona, trovare la data più antica e la data più recente tra quella di laurea, quella di matrimonio e quella di assunzione. Select Nome, Greatest(Laurea, Matrimonio, Assunzione) Least(Laurea, Matrimonio, Assunzione) From Persone

43. Greatest e least coi letterali Se si applicano le funzioni greatest e least sui letterali, insorge un problema. Select Least (’12-Gen-2004’, ‘12-Dic-2007’) From Dual La funzione least restituisce il valore 12-Dic-2007. Questo perché Oracle interpreta i valori tra virgolette come stringhe, e quindi utilizza l’ordine lessicografico. Per ottenere il risultato corretto occorre usare la funzione di conversione TO_DATE Select Least (To_date(‘12-Gen-2006’), To_date(‘12-Dic-2007’))

44. Next_day Next_day(data, ‘giorno sett’) calcola la data del prossimo giorno della settimana (giorno_sett) successivo alla data specificata. Esercizio: calcolare la data della prossima domenica Select next_day(sysdate, ‘domenica’) From Dual

45. Last_day Last_day (data) restituisce l’ultimo giorno del mese contenuto nella data. Esercizio: trovare quanti giorni aveva il mese di febbraio nell’anno 2004. E nel 2000? E nel 1900? Select Last_day(to_date(1-Feb-2004)) from Dual Select Last_day(to_date(1-Feb-2000)) from Dual Select Last_day(to_date(1-Feb-1900)) from Dual

46. Months Between Months_Between(data1, data2) calcola i mesi che intercorrono tra la data2 e la data1 Esercizio: creare la seguente tabella e trovare l’età delle persone nella tabella: Compleanni Select Compleanni.*, Floor(months_between(Sysdate, "data di nascita")/12) eta From Compleanni

47. Esercizio Determinare chi è la persona più anziana,specificandone nome, cognome, data di nascita ed età, tra quelle nella tabella precedente Select Compleanni.*, Floor(months_between(sysdate, "data di nascita")/12) eta From Compleanni Where Floor(months_between(sysdate,"data di nascita")/12) =(select max(Floor(months_between(sysdate, "data di nascita")/12)) From Compleanni)

48. Round e Trunc Una variabile di tipo date in genere contiene anche l’informazione su ora, minuti e secondi. A volte può essere importante “liberarsi” di questi dettagli per evitare la presenza di cifre decimali nell’utilizzo di alcune funzioni. Per questo a volte è utile fare degli arrotondamenti

49. Round e Trunc • A una data inserita come letterale è assegnata come ora predefinita le 12 A.M. dell’inizio della giornata • Sysdate contiene sia la data che l’ora. In questo caso secondo l’applicazione si può applicare: • ROUND, che arrotonda l’ora alla mezzanotte precedente, se l’ora è precedente a mezzogiorno, o alla mezzanotte seguente se l’ora è successiva a mezzogiorno. • Altrimenti si può usare la funzione TRUNC che imposta sempre l’ora alla mezzanotte precedente dello stesso giorno.

50. Esercizio Trovare quanti giorni mancano alla prossima domenica, arrotondando la data di sistema alla mezzanotte precedente Select Round(NEXT_DAY(sysdate, 'Domenica'))-Round(sysdate) From Dual