Funzioni in SQL
Tipi di Funzione





Funzioni numeriche
Funioni di stringhe
Funzioni di date
Funzioni di conversione
Funzioni di selezione
Funzioni in SQL
Funzioni numeriche
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
Alcune funzioni numeriche a
valori singoli
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
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)
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
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
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
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.
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)) (?????)
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:
Coalesce(valore1,valore2,…)
Restituisce il primo valore
non null nell’elenco delle
espressioni
Greatest(valore1,valore2,…) Il valore più grande
dell’elenco
Least(valore1,valore2,…)
Il valore più piccolo
nell’elenco
Data
PA CT AG ME
14-Mar-07
17
18
15
13
15-Mar-07
20
18
17
15
16-Mar-07
18
21
19
19
17-Mar-07
--- ---
20
18
18-Mar-07
---
19
15
17
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
Data
GST LST COA
14-Mar-07
18
13
17
15-Mar-07
20
15
20
16-Mar-07
21
18
18
17-Mar-07
20
18
20
18-Mar-07
19
15
17
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
Funzioni in SQL
Funzioni su stringhe
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.
Alcune funzioni di stringa
str1||str2, concat(str1, str2) • Concatenazione
ASCII(str)
• Intero corrispondente codice
ASCIIdel 1o carattere di str
Chr(int)
• Carattere corrispondente all’intero int
dato in input nel codice ASCII
Instr(str, chr)
• Trova la posizione del carattere chr
nella stringa str.
Length(str)
• Fornisce la lunghezza di una stringa str
Lower (str)
• Rende minuscoli tutti i caratteri di str
Upper(str)
• Rende maiuscoli tutti i caratteri di str
Initcap(str)
• Rende maiuscolo il primo carattere di
una stringa str
Alcune funzioni di stringa
Substr (str, i,k) • Estrae dalla stringa str la sottostringa che inizia
nella posizione i ed è lunga k caratteri
Instr(str, chr) • Trova la posizione di un carattere chr in una stringa
str
LPAD(str,l [,car]) • Riempie una stringa str fino a una certa lunghezza l
aggiungendo a sinistra una serie di caratteri car
RPAD(str,l [,car]) • Riempie una stringa str fino a una certa lunghezza l
aggiungendo a destra una serie di caratteri car
LTRIM(str, chr) • Elimina tutti i caratteri che rientrano in una serie
specificata chr dalla parte sinistra di una stringa str
RTRIM(str, chr) • Elimina tutti i caratteri che rientrano in una serie
specificata chr dalla parte sinistra di una stringa str
TRIM(str, chr) • Elimina tutti i caratteri che rientrano in una serie
specificata chr dalla parte da entrambe le estremità
di una stringa str
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
Matricola Nome Cognome Settore
Nome || ‘ ’ || Cognome
123456
Luca
Rossi
segreteria
Luca Rossi
564387
Mara Bruni
produzione
Mara Bruni
776213
Ugo
marketing
Ugo Verdi
342091
Fabio Neri
produzione
Fabio Neri
Verdi
Concatenazione di stringhe
Alternativamente si può usare la funzione CONCAT.
Select Concat(Concat(Nome, ‘ ’), Cognome)
From Impiegato
Impiegato
Matricola Nome Cognome Settore
Concat(Nome, ‘ ’, Cognome)
123456
Luca
Rossi
segreteria
Luca Rossi
564387
Mara Bruni
produzione
Mara Bruni
776213
Ugo
marketing
Ugo Verdi
342091
Fabio Neri
produzione
Fabio Neri
Verdi
Esercizio
Scrivere il nome di ogni componente, seguito dal suo
codice fra parentesi
Select (com_descrizione || ‘ (‘ ||com_cod || ‘)’)
FROM Componenti
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
Rpad, esempio
Si vogliono aggiungere dei puntini alla fine di ogni nome
per collegarli al cognome
Impiegato
Matricola Nome Cognome Settore
123456
Luca
Rossi
segreteria
564387
Mara Bruni
produzione
776213
Ugo
marketing
342091
Fabio Neri
Verdi
produzione
Select Rpad(nome, 18, ‘.’) , Cognome
From Impiegato
Rpad(nome,18,‘.’) | Cognome
---------------------------Luca..............|Rossi
Mara..............|Bruni
Ugo...............|Verdi
Fabio.............|Neri
Lpad, esempio
Si vogliono formattare i nomi, giustificandoli a destra
Impiegato
Matricola Nome Cognome Settore
123456
Luca
Rossi
segreteria
564387
Mara Bruni
produzione
776213
Ugo
marketing
342091
Fabio Neri
Verdi
produzione
Select Lpad(nome, 18) , Cognome
From Impiegato
Lpad(nome,14) | Cognome
---------------------------Luca|Rossi
Mara|Bruni
Ugo|Verdi
Fabio|Neri
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.
Ltrim: esempio
Libri
Autore
Titolo
Collocazione
D. Alighieri
“la divina Commedia”
483291
D. Brown
Il codice Da Vinci.
532244
G. Garcia Marquez Cent’anni di solitudine
432457
A. Camilleri
“Il ladro di merendine.” 887625
E. Morante
La storia.
443213
E. Hemingway
“Il vecchio e il mare”
534853
Titolo
Select Ltrim(titolo, '"' ) Titolo
From Libri
la divina Commedia”
Il codice Da Vinci.
Cent’anni di solitudine
Il ladro di merendine.”
La storia.
il vecchio e il mare”
Rtrim: esempio
Libri
Autore
Titolo
Collocazione
D. Alighieri
“la divina Commedia”
483291
D. Brown
Il codice Da Vinci.
532244
G. Garcia Marquez Cent’anni di solitudine
432457
A. Camilleri
“Il ladro di merendine.” 887625
E. Morante
La storia.
443213
E. Hemingway
“Il vecchio e il mare”
534853
Titolo
Select Rtrim(titolo, '."') Titolo
From Libri
“la divina Commedia
Il codice Da Vinci
Cent’anni di solitudine
“Il ladro di merendine
La storia
“Il vecchio e il mare
Ltrim ed Rtrim: esempio
Libri
Autore
Titolo
Collocazione
D. Alighieri
“la divina Commedia”
483291
D. Brown
Il codice Da Vinci.
532244
G. Garcia Marquez Cent’anni di solitudine
432457
A. Camilleri
“Il ladro di merendine.” 887625
E. Morante
La storia.
443213
E. Hemingway
“Il vecchio e il mare”
534853
Titolo
la divina Commedia
Select Ltrim(Rtrim(titolo, '."'), '"') Titolo
From Libri
Il codice Da Vinci
Cent’anni di solitudine
Il ladro di merendine
La storia
Il vecchio e il mare
Ltrim, Rtrim: attenzione!!
Libri
Autore
Titolo
Collocazione
D. Alighieri
“la divina Commedia”
483291
D. Brown
Il codice Da Vinci.
532244
G. Garcia Marquez Cent’anni di solitudine
432457
A. Camilleri
“Il ladro di merendine.” 887625
E. Morante
La storia.
443213
E. Hemingway
“Il vecchio e il mare”
534853
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
Titolo
a divina Commedia
codice Da Vinci
Cent’anni di solitudine
adro di merendine
La storia
vecchio e il mare
Trim, Esempio
Libri
Autore
Titolo
Collocazione
D. Alighieri
“la divina Commedia”
483291
D. Brown
Il codice Da Vinci.
532244
G. Garcia Marquez Cent’anni di solitudine
432457
A. Camilleri
“Il ladro di merendine.” 887625
E. Morante
La storia.
443213
E. Hemingway
“Il vecchio e il mare”
534853
Select Trim('"'FROM titolo) Titolo
From Libri
In questo modo si possono eliminare le
virgolette ad entrambi i lati del titolo
Titolo
La divina Commedia
Il codice Da Vinci.
Cent’anni di solitudine
Il ladro di merendine.
La storia.
Il vecchio e il mare
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.
Esempio
Libri
Autore
Titolo
Collocazione
D. Alighieri
la divina commedia
483291
D. Brown
Il codice Da Vinci
532244
G. Garcia Marquez Cent’anni di solitudine 432457
A. Camilleri
Il ladro di merendine
887625
E. Morante
La storia
443213
Select Upper(titolo),
Lower(titolo),
Initcap(titolo)
From Libri
Upper(titolo)
Lower(Titolo)
Initcap(titolo)
LA DIVINA COMMEDIA
La divina commedia
La Divina Commedia
IL CODICE DA VINCI
Il codice Da Vinci
Il Codice Da Vinci
CENT’ANNI DI SOLITUDINE Cent’anni di solitudine Cent’Anni Di Solitudine
IL LADRO DI MERENDINE
Il ladro di merendine
Il Ladro Di Merendine
LA STORIA
La storia
La Storia
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.
Esempio
Supponiamo che i primi due caratteri della
collocazione rappresentino lo scaffale e che le ultime
4 rappresentino la collocazione nel suo scaffale
Libri
Autore
Titolo
Collocazione
D. Alighieri
“La divina Commedia”
483291
D. Brown
Il codice Da Vinci.
532244
G. Garcia Marquez Cent’anni di solitudine
432457
A. Camilleri
“Il ladro di merendine.” 887625
E. Morante
La storia.
443213
Select substr(collocazione, 1, 2) Scaffale
substr(collocazione, -4) Colloc
From Libri
Scaffale Colloc
48
3291
53
2244
43
2457
88
7625
44
3213
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.
Titolo
Select Instr(titolo, 'Il', 1,1) from Libri
Select titolo, Instr(titolo, 'di') Dove
From Libri
Dove
“La divina Commedia”
5
Il codice Da Vinci.
6
Cent’anni di solitudine
11
“Il ladro di merendine.” 11
La storia.
0
Funzioni in SQL
Funzioni di date e
funzioni di conversione
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
Funzioni di date
Add_Months (n, data)
Current_Date
Current_Timestamp
Somma n mesi a data
Data corrente nel fuso orario della sessione
Indicatore orario nel fuso orario della
sessione
Extract (UT FROM data)
Estrae una porzione di data (UT=unità di
tempo) da un valore data
Greatest(data1,data2,…)
La più recente fra le date elencate
Least(data1, data2…)
La meno recente fra le date elencate
Last_day(data)
La data dell’ultimo giorno del mese a cui
appartiene la data
Months_Between(data2,data1) Fornisce data2-data1 in mesi
(eventualmente numero frazionario)
Next_Day(data, ‘giorno’)
Data del giorno successivo a date dove
giorno=‘Monday’, ‘Tuesday’ etc
Round(data, ‘formato’)
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.
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
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
Aggiunta di mesi
Add_Months(data,n) aggiunge n 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
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.
Esempio
Persone
Nome
Laurea
Matrimonio
Assunzione
Rossi
14-Lug-2003 23-Set-2005 12-Mar-2003
Verdi
16-Apr-2005 4-Gen-2006
Bruni
2-Dic-2002
24-Set-2001 19-Gen-2003
Gialli
18-Dic-2002
7-Feb-2004
13-Set-2000
11-Lug-2003
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
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’))
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
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
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
Nome
Cognome Data di Nascita
Piero
Viola
25-Apr-1970
Giorgio Rossi
30-Giu-1966
Marco
Neri
16-Set-1977
Luca
Verdi
12-Gen-1972
Select Compleanni.*,
Floor(months_between(Sysdate, "data di nascita")/12) eta
From Compleanni
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)
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
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.
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
Funzioni in SQL
Funzioni di conversione
Formattazione
La funzione to_date converte una stringa di caratteri in
una data
La funzione to_char converte una data o un numero in
una stringa di caratteri
La funzione to_number trasforma una stringa in un
numero
Sintassi
To_char(data [, ‘formato’]) o To_char(numero)
To_date(stringa [, ‘formato’])
To_number(stringa)
Nella conversione to_date, stringa è una stringa
letterale, un numero vero e proprio o una colonna di un
database che contiene una stringa o un numero. In ogni
caso il formato di stringa deve essere quello definito da
formato. Solo se stringa è nel formato predefinito, si
può evitare di descrivere il formato. Il valore
predefinito è DD-MON-YYYY
Modificare il formato
Il formato della sessione può essere modificato mediante
l’istruzione
Alter session set NLS_DATE_FORMAT = “nuovo_formato”
I vari tipi di formato si trovano nel manuale ORACLE
9 nella tabella a pagina 171-172
To_char, Esempio
Compleanni
Nome
Cognome Data di Nascita
Piero
Viola
25-Apr-1970
Giorgio Rossi
30-Giu-1966
Marco
Neri
16-Set-1977
Luca
Verdi
12-Gen-1972
Select "data di nascita",
to_char("data di nascita", 'MM/DD/YY') Formattato
From Compleanni
Data di Nascita
Formattato
25-Apr-1970
04/25/70
30-Giu-1966
06/30/66
16-Set-1977
09/16/77
12-Gen-1972
01/12/72
Trasformazione di data come
letterale
Per trasformare il formato di una data inserita come
letterale possiamo scrivere:
Select to_char(to_date('12-Mar-1970'), 'DD Month YY') formattato
From dual
Dual
Formattato
12 Marzo 70
Visualizzare l’orario
La funzione to_char permette anche di visualizzare
l’orario.
Infatti una variabile date contiene anche l’orario, anche
se per default viene visualizzato solo giorno mese e anno.
Un modo per visualizzare l’orario è di trasformarlo in
formato carattere, specificando nella formattazione che
si intende visualizzare l’orario
Select to_char(sysdate, ‘hh:mi:ss’) from dual
Select to_char("data di nascita", 'hh24, mi, ss')
From Compleanni
N.B. Minuti è indicato da mi e non da mm (mese)
To_char sui numeri
La funzione to_char può anche essere applicata a un
oggetto di tipo number. In questo caso un tipo numerico
viene trasformato in una stringa. Questo permetterà di
applicare a un tipo numerico funzioni definite su stringhe
Esempio Supponiamo che in una tabella rubrica il numero
civico e il CAP siano definiti come number.
Per visualizzare l’indirizzo completo comprensivo di
numero civico e cap si può scrivere
Select Via || ', ' ||to_char(num) ||' - '|| to_char(cap)
From Rubrica
In realtà…
In realtà se noi scriviamo
Select Via || ', ' ||num ||' - '||cap
From Rubrica
Otteniamo lo stesso risultato.
Questo perché spesso Oracle opera queste conversioni in
maniera automatica
To date
L’istruzione to_date serve a convertire una stringa o un
numero in una data.
Ovviamente la stringa o la data deve essere in un formato
compatible con quello della data.
Sintassi:
To_date(string [,formato])
In questo caso formato può essere omesso solo nel caso in
cui la stringa rappresenta una data nel formato
predefinito
Select to_date(’15-Mag-2007’) from Dual
Date non in formato
predefinito
Se, per esempio, la stringa contiene una data nella forma
MM/GG/AA, allora devo specificare in quale formato
inserisco la stringa da trasformare in data.
Esempio:
Select to_date( ‘05/15/07’, ‘MM/GG/AA’) from dual
La data sarà comunque visualizzata nel formato
predefinito ‘15-Mag-2007’
n.b. 07 può significare 2007 o 1907. Questo dipenderà
dalle impostazioni predefinite del database
Trasformazione di un numero
in data
Anche un numero, se espresso in una certa forma, può
rappresentare una data. La funzione to_date, applicata
a un numero, e specificandone il formato, converte il
numero in una data
Select to_date((120370), 'DDMMYY') Data
From dual
Dual
Data
12-Mar-1970
Limitazioni della stringa
Ovviamente non qualunque stringa può essere trasformata
in data.
La stringa è infatti soggetta alle seguenti limitazioni:
1. Non sono consentite stringhe letterali che non hanno a
che fare con date. Esempio to_date(‘Donald Duck’);
2. I giorni non possono essere scritti in lettere. Devono
essere dei numeri;
3. Sono consentiti i segni di interpunzione (per separare le
varie unità di misura)
4. Se viene utilizzata Month il mese nella stringa deve
essere scritto per esteso in lettere. Se è utilizzato
Mon, deve essere un’abbreviazione di tre lettere. Le
lettere maiuscole o minuscole vengono ignorate.
To number
To number trasforma una stringa in un numero, se la
stringa contiene solo cifre.
select to_number('243434534')+1 from dual
Conversione automatica
Spesso Oracle effettua automaticamente alcune
conversioni.
Ci sono comunque certi casi in cui le conversioni
automatiche non vengono effettuate.
Conversione automatica,
regole



Qualsiasi number o date può essere convertito in
stringa di caratteri. Dunque una qualsiasi funzione di
stringa può essere applicata a una colonna di tipo date
o number, senza bisogno di convertirle.
Un valore char o varchar è convertito in number solo
se contiene cifre, un punto decimale e un segno –
(meno) prima delle cifre
Un valore char è convertito in data solo se è espresso
nel formato predefinito ‘DD-Mon-YYYY’. Questo vale
per tutte le funzioni tranne che per GREATEST e
LEAST per cui vengono considerati in ogni caso come
stringhe e vale per Between solo se la colonna a
sinistra di between è di tipo date
Funzioni in SQL
Decode e Case
La funzione decode
La funzione Decode realizza in SQL il costrutto
if…then…else presente nei vari linguaggi di
programmazione.
Decode, sintassi
DECODE(valore, if1,then1,if2,then2,…, ifn,thenn, else)
Qui valore rappresenta un qualsiasi attributo della
tabella, o qualsiasi risultato di un calcolo. Il valore viene
calcolato per ogni riga. Se è uguale a if1 allora il risultato
è then1, se è uguale a if2, il risultato è then2, etc. Se il
valore non è uguale a nessuno dei valori, allora il risultato
è else
Ciascuno degli if, then, else può essere una colonna o il
risultato di una funzione o calcolo.
Fra parentesi è possibile racchiudere fino a 255 elementi
Esempio
Biblio-Prestiti
Nome Titolo Dataprest Datarest
Biblioteca
Autore Titolo Editore Genere Pagine Anno Collocazione
Si vuole dare una diversa scadenza per i prestiti in base al
genere di libro, in quanto ci sono dei libri che si leggono più
in fretta e degli altri per cui occorre piu tempo.
La penale da pagare dipenderà anche dal genere
Esempio
Prestiti
Nome Titolo Dataprest Datarest
Biblioteca
Autore Titolo Editore Genere Pagine Anno Collocazione
Per ogni persona che prende in prestito un libro, elencare
nome, ritardo rispetto alla scadenza e mora,
considerando che il prestito per i romanzi scade dopo 14
giorni, per i libri per bambini dopo 25 giorni, per i saggi
dopo 20 giorni e per tutti gli altri libri dopo 18 giorni, e
che la mora è di 0.5 euro al giorno per i romanzi, di 0.2
euro al giorno per i libri per bambini, di 0.5 per i saggi e
di 0.8 per tutti gli altri libri
Esempio, soluzione
Prestiti
Nome Titolo Dataprest Datarest
Biblioteca
Autore Titolo Editore Genere Pagine Anno Collocazione
Select nome, titolo,
Decode(Genere, ‘Romanzo’, Datarest-Dataprest-14,
‘Bambini’, Datarest-Dataprest-25,
‘Saggio’, Datarest-Dataprest-20,
Datarest-Dataprest-18) Ritardo,
Decode(Genere, ‘Romanzo’, (Datarest-Dataprest-14)*0.5
‘Bambini’ (Datarest-Dataprest-25)*0.3,
‘Saggio’, (Datarest-Dataprest-20)*0.5,
(Datarest-dataprest-18)*0.8) Mora
From prestiti, Biblioteca
Where Prestiti.titolo=Biblioteca.titolo and datarest-dataprest>
Decode(Genere, ‘Romanzo’,14, ‘Bambini’, 25,
‘Saggio’, 20, 18)
Decode annidati
Così come nei linguaggi di programmazione, le funzioni
decode possono essere annidate una dentro l’altra.
Supponiamo per esempio che fra i libri della stessa
categoria vogliamo discriminare il ritardo rispetto al
nome dell’autore. Infatti potrebbero esserci degli autori
che sono più difficili da leggere.
In tal caso ci sarà un decode che dipende dal genere e
degli altri che dipendono dal nome dell’autore.
Esempio
Select nome, titolo,
Decode (Genere, ‘Romanzo’, Decode(Autore, ‘A. Camilleri’,
Datarest-Dataprest-10,
‘Dante’,
Datarest-Dataprest-30,
Datarest-Dataprest-15)
‘Bambini’, Decode (Autore, ‘G. Stilton’,
Datarest-Dataprest-20
‘J.K. Rowling’,
Datarest-Dataprest-45,
Datarest – Dataprest -25)
‘Saggio’, Datarest-Dataprest-20,
Datarest-Dataprest-18) Ritardo,
Decode (Genere, ……………) Mora
From Prestiti, Biblioteca
Where Prestiti.titolo=Biblioteca.titolo and datarest-dataprest>
Decode(Genere, ‘Romanzo’,Decode…, ‘Bambini’, Decode… 25,
‘Saggio’, 20, 18)
Case
A partire da Oracle 9, è possibile usare anche la funzione
Case.
Sintassi:
Case attributo
when valore1 then1
when valore2 then2
...
when valoren thenn
else …
end
Esempio
Select nome, titolo,
Case Genere
when ‘Romanzo’ then Datarest-Dataprest-14
when ‘Bambini’ then Datarest-Dataprest-25
when ‘Saggio’ then Datarest-Dataprest-20
else Datarest-Dataprest-18
end as Ritardo,
Case Genere
when ‘Romanzo’ then (Datarest-Dataprest-14)*0.5
when ‘Bambini’ then (Datarest-Dataprest-25)*0.3
when ‘Saggio’, (Datarest-Dataprest-20)*0.5
else (Datarest-dataprest-18)*0.8
end as Mora
From prestiti P, Biblioteca B
Where P.titolo=B.titolo and datarest-dataprest>
Case Genere when ‘Romanzo’ then 14
when ‘Bambini’ then 25,
when ‘Saggio’ then 20 else 18 end
Case annidati
Come per il decode, è possibile annidare diversi costrutti
case.
Esercizio:
Riscrivere la query dell’esempio dei decode annidati
utilizzando il costrutto CASE
Esercizio
Consideriamo il nostro Database e supponiamo che ci
chiedano uno sconto su una fornitura di articoli. Lo sconto
viene effettuato solo sugli articoli che costano più di 500
euro, nella misura seguente:
sui tavoli è possibile applicare uno sconto del 15%,
sui mobili del 10%,
sulle librerie del 18%
sugli armadi del 5%.
Calcolare lo sconto effettuato su ciascun articolo
Esercizio, Soluzione Decode
select art_descrizione, art_prezzo,
decode(cat_descrizione, 'Tavolo', art_prezzo*15/100,
'Mobile', art_prezzo*10/100,
'Libreria', art_prezzo*18/100,
'Armadio', art_prezzo*5/100) Sconto
From Articoli, Categorie
where Articoli.cat_cod=Categorie.cat_cod
and art_prezzo>500
Esercizio, soluzione Case
select art_descrizione, art_prezzo,
Case cat_descrizione
when 'Tavolo' then art_prezzo*15/100
when 'Mobile' then art_prezzo*10/100
when 'Libreria' then art_prezzo*18/100
when 'Armadio' then art_prezzo*5/100
end as Sconto
From Articoli, Categorie
where Articoli.cat_cod=Categorie.cat_cod
and art_prezzo>500
Esercizio (casa)
Fare lo stesso esercizio precedente supponendo che
per i Tavoli, il limite minimo di spesa sia 400 euro, per
i mobili di 550 euro, per le librerie di 350 euro e per
gli armadi di 700 euro