Utilizzo delle funzioni di conversione

Oltre alle funzioni di utilità SQL, la libreria di funzioni incorporata di Oracle contiene funzioni di conversione del tipo. Potrebbero verificarsi scenari in cui la query prevede l'input in un tipo di dati specifico, ma lo riceve in un tipo di dati diverso. In questi casi, Oracle tenta implicitamente di convertire il valore imprevisto in un tipo di dati compatibile che può essere sostituito sul posto e la continuità dell'applicazione non viene compromessa. La conversione dei tipi può essere eseguita implicitamente da Oracle o esplicitamente dal programmatore.

La conversione implicita del tipo di dati funziona in base a una matrice che mostra il supporto di Oracle per il casting interno del tipo. Oltre a queste regole, Oracle offre funzioni di conversione del tipo che possono essere utilizzate nelle query per la conversione e la formattazione esplicite. È un dato di fatto, si consiglia di eseguire una conversione esplicita invece di affidarsi all'intelligenza del software. Sebbene la conversione implicita funzioni bene, ma per eliminare le possibilità di disallineamento in cui gli input errati potrebbero essere difficili da tipizzare internamente.

Conversione implicita del tipo di dati

Un valore VARCHAR2 o CHAR può essere convertito implicitamente in un valore di tipo NUMBER o DATE da Oracle. Allo stesso modo, un valore di tipo NUMBER o DATA può essere convertito automaticamente in dati di caratteri dal server Oracle. Si noti che l'interconversione impicit si verifica solo quando il carattere rappresenta rispettivamente il valore di un numero o di un tipo di data valido.

Ad esempio, esamina le seguenti query SELECT. Entrambe le query daranno lo stesso risultato perché Oracle tratta internamente 15000 e "15000" come stessi.

Query-1

SELECT employee_id,first_name,salary
FROM employees
WHERE salary > 15000;

Query-2

SELECT employee_id,first_name,salary
FROM employees
WHERE salary > '15000';

Conversione esplicita del tipo di dati

Le funzioni di conversione SQL sono funzioni a riga singola che sono in grado di digitare il valore di una colonna, un valore letterale o un'espressione. TO_CHAR, TO_NUMBER e TO_DATE sono le tre funzioni che eseguono la modifica incrociata dei tipi di dati.

Funzione TO_CHAR

La funzione TO_CHAR viene utilizzata per trasformare un input numerico o di data in un tipo di carattere con un modello di formato (opzionale).

Sintassi 

TO_CHAR(number1, [format], [nls_parameter])

Per la conversione da numero a carattere, i parametri nls possono essere utilizzati per specificare caratteri decimali, separatore di gruppo, modello di valuta locale o modello di valuta internazionale. È una specifica facoltativa: se non disponibile, verranno utilizzate le impostazioni nls a livello di sessione. Per la conversione da data a carattere, il parametro nls può essere utilizzato per specificare i nomi del giorno e del mese, a seconda dei casi.

Le date possono essere formattate in più formati dopo la conversione in tipi di carattere utilizzando la funzione TO_CHAR. La funzione TO_CHAR viene utilizzata per visualizzare le date di Oracle 11g in un formato particolare. I modelli di formato fanno distinzione tra maiuscole e minuscole e devono essere racchiusi tra virgolette singole.

Considera la seguente query SELECT. La query formatta le colonne HIRE_DATE e SALARY della tabella EMPLOYEES utilizzando la funzione TO_CHAR.

SELECT first_name,
       TO_CHAR (hire_date, 'MONTH DD, YYYY') HIRE_DATE,
	   TO_CHAR (salary, '$99999.99') Salary
FROM employees
WHERE rownum < 5;

FIRST_NAME           HIRE_DATE          SALARY
-------------------- ------------------ ----------
Steven               JUNE      17, 2003  $24000.00
Neena                SEPTEMBER 21, 2005  $17000.00
Lex                  JANUARY   13, 2001  $17000.00
Alexander            JANUARY   03, 2006   $9000.00

Il primo TO_CHAR viene utilizzato per convertire la data di assunzione nel formato della data MESE GG, AAAA, ovvero mese indicato e riempito con spazi, seguito dal giorno a due cifre del mese e quindi dall'anno a quattro cifre. Se preferisci visualizzare il nome del mese in lettere maiuscole (cioè "Dicembre"), utilizza semplicemente questo caso nell'argomento del formato: ('Mese GG, AAAA').

La seconda funzione TO_CHAR nella Figura 10-39 viene utilizzata per formattare lo STIPENDIO per visualizzare il segno di valuta e due posizioni decimali.

Oracle offre un set completo di modelli di formato. La tabella seguente mostra l'elenco dei modelli di formato che possono essere utilizzati per digitare la data e i valori numerici come caratteri utilizzando TO_CHAR.

Formato modello Descrizione
,(virgola) Restituisce una virgola nella posizione specificata. È possibile specificare più virgole in un modello di formato numerico. Restrizioni: un elemento virgola non può iniziare un modello di formato numerico. Una virgola non può apparire a destra di un carattere decimale o di un punto in un modello di formato numerico.
.(periodo) Restituisce un punto decimale, che è un punto (.) Nella posizione specificata. Limitazione: è possibile specificare solo un periodo in un modello di formato numerico
$ Restituisce un valore con un segno di dollaro iniziale
0 Restituisce gli zeri iniziali. Restituisce gli zeri finali.
9 Restituisce il valore con il numero di cifre specificato con uno spazio iniziale se positivo o con un segno meno iniziale se negativo. Gli zeri iniziali sono vuoti, ad eccezione di un valore zero, che restituisce uno zero per la parte intera del numero a virgola fissa.
B Restituisce spazi vuoti per la parte intera di un numero a virgola fissa quando la parte intera è zero (indipendentemente dagli "0" nel modello di formato).
C Restituisce nella posizione specificata il simbolo di valuta ISO (il valore corrente del parametro NLS_ISO_CURRENCY).
D Restituisce nella posizione specificata il carattere decimale, che è il valore corrente del parametro NLS_NUMERIC_CHARACTER. L'impostazione predefinita è un punto (.). Limitazione: è possibile specificare solo un carattere decimale in un modello di formato numerico.
EEE Restituisce un valore utilizzando nella notazione scientifica.
FM Restituisce un valore senza spazi iniziali o finali.
G Restituisce nella posizione specificata il separatore di gruppo (il valore corrente del parametro NLS_NUMERIC_CHARACTER). È possibile specificare più separatori di gruppo in un modello di formato numerico. Limitazione: un separatore di gruppo non può essere visualizzato a destra di un carattere decimale o di un punto in un modello di formato numerico
L Restituisce nella posizione specificata il simbolo della valuta locale (il valore corrente del parametro NLS_CURRENCY).
MI Restituisce un valore negativo con un segno meno (-). Restituisce un valore positivo con uno spazio vuoto finale. Limitazione: l'elemento di formato MI può apparire solo nell'ultima posizione di un modello di formato numerico.
PR Restituisce un valore negativo in. Può apparire solo alla fine di un modello in formato numerico.
RN, rm Restituisce un valore come numeri romani in maiuscolo. Restituisce un valore come numeri romani in minuscolo. Il valore può essere un numero intero compreso tra 1 e 3999.
S Restituisce un valore negativo con un segno meno iniziale o finale (-). Restituisce un valore positivo con un segno più iniziale o finale (+). Limitazione: l'elemento di formato S può apparire solo nella prima o nell'ultima posizione di un modello di formato numerico.
TM "Testo minimo". Restituisce (in output decimale) il minor numero di caratteri possibile. Questo elemento non fa distinzione tra maiuscole e minuscole.
U Restituisce nella posizione specificata il simbolo della doppia valuta "Euro" (o altro) (il valore corrente del parametro NLS_DUAL_CURRENCY).
V Restituisce un valore moltiplicato per 10n (e, se necessario, arrotondato per eccesso), dove n è il numero di 9 dopo la "V".
X Restituisce il valore esadecimale del numero di cifre specificato.

Funzione TO_NUMBER

La funzione TO_NUMBER converte un valore di carattere in un tipo di dati numerico. Se la stringa da convertire contiene caratteri non numerici, la funzione restituisce un errore.

Sintassi

TO_NUMBER (string1, [format], [nls_parameter])

La tabella seguente mostra l'elenco dei modelli di formato che possono essere utilizzati per digitare i valori dei caratteri come numeri utilizzando TO_NUMBER.

Formato modello Descrizione
CC Secolo
SCC Secolo a.C. con prefisso -
AAAA Anno con 4 numeri
SYYY Anno aC con prefisso -
IYYY Anno ISO con 4 numeri
YY Anno con 2 numeri
RR Anno con 2 numeri con compatibilità Y2k
ANNO Anno in caratteri
SYEAR Anno in caratteri, BC preceduto da -
AVANTI CRISTO Indicatore BC / AD
Q Un quarto in cifre (1,2,3,4)
MM Mese dell'anno 01, 02 ... 12
MESE Mese in caratteri (es. Gennaio)
MON GEN, FEB
WW Numero della settimana (es. 1)
W Numero della settimana del mese (es. 5)
IW Numero della settimana dell'anno nello standard ISO.
DDD Giorno dell'anno in cifre (es. 365)
DD Giorno del mese in cifre (es. 28)
D Giorno della settimana in cifre (es. 7)
GIORNO Giorno della settimana in caratteri (es. Lunedì)
FMDAY Giorno della settimana in caratteri (es. Lunedì)
DY Giorno della settimana in una breve descrizione del carattere (es. SUN)
J Julian Day (numero di giorni dal 1 gennaio 4713 a.C., dove 1 gennaio 4713 a.C. è 1 in Oracle)
HH, H12 Numero dell'ora del giorno (1-12)
HH24 Numero dell'ora del giorno con notazione 24Hours (0-23)
AM PM Antimeridiane o pomeridiane
PERDERE Numero di minuti e secondi (ovvero 59),
SSSSS Numero di secondi in questo giorno.
DS Formato data breve. Dipende dalle impostazioni NLS. Utilizzare solo con timestamp.
DL Formato data lungo. Dipende dalle impostazioni NLS. Utilizzare solo con timestamp.
E Nome dell'era abbreviato. Valido solo per i calendari: imperiale giapponese, ufficiale ROC, Buddha thailandese.
EE Il nome completo dell'era
FF I secondi frazionari. Usa con timestamp.
FF1..FF9 I secondi frazionari. Usa con timestamp. La cifra controlla il numero di cifre decimali utilizzate per le frazioni di secondo.
FM Modalità riempimento: sopprime gli spazi vuoti nell'output dalla conversione
FX Formato esatto: richiede la corrispondenza esatta del modello tra i dati e il modello di formato.
IYY O IY O I Le ultime 3,2,1 cifre dell'anno standard ISO. Solo output
RM La rappresentazione in numeri romani del mese (I .. XII)
RR Le ultime 2 cifre dell'anno.
RRRR Le ultime 2 cifre dell'anno se utilizzate per l'output. Accetta anni di quattro cifre quando viene utilizzato per l'input.
SP Formato ortografato. Può apparire alla fine di un elemento numerico. Il risultato è sempre in inglese. Ad esempio, il mese 10 in formato MMSP restituisce "dieci"
SPTH Formato ortografico e ordinale; 1 risultati in primo.
TH Converte un numero nel suo formato ordinale. Ad esempio 1 diventa il primo.
TS Formato breve. Dipende dalle impostazioni NLS. Utilizzare solo con timestamp.
TZD Nome del fuso orario abbreviato. cioè PST.
TZH, TZM Spostamento ora / minuto del fuso orario.
TZR Regione del fuso orario
X Carattere radice locale. In America questo è un periodo (.)

Le query SELECT di seguito accettano numeri come input di caratteri e li stampa seguendo l'identificatore di formato.

SELECT  TO_NUMBER('121.23', '9G999D99') 
FROM DUAL

TO_NUMBER('121.23','9G999D99')
------------------------------
                        121.23

SELECT  TO_NUMBER('1210.73', '9999.99') 
FROM DUAL;

TO_NUMBER('1210.73','9999.99')
------------------------------
                       1210.73

Funzione TO_DATE

La funzione accetta i valori dei caratteri come input e restituisce la data formattata equivalente alla stessa. La funzione TO_DATE consente agli utenti di inserire una data in qualsiasi formato, quindi converte la voce nel formato predefinito utilizzato da Oracle 11g.

Sintassi:

TO_DATE( string1, [ format_mask ], [ nls_language ] )

Un argomento format_mask è costituito da una serie di elementi che rappresentano esattamente come dovrebbero apparire i dati e devono essere inseriti tra virgolette singole.

Formato modello Descrizione
ANNO Anno, precisato
AAAA Anno a 4 cifre
YYY, YY, Y Ultimi 3, 2 o 1 cifra / e dell'anno.
IYY, IY, I Ultime 3, 2 o 1 cifra / e dell'anno ISO.
IYYY Anno a 4 cifre basato sullo standard ISO
RRRR Accetta un anno a 2 cifre e restituisce un anno a 4 cifre.
Q Trimestre dell'anno (1, 2, 3, 4; GEN-MAR = 1).
MM Mese (01-12; JAN = 01).
MON Nome abbreviato del mese.
MESE Nome del mese, riempito con spazi fino a una lunghezza di 9 caratteri.
RM Mese in numeri romani (I-XII; JAN = I).
WW Settimana dell'anno (1-53) in cui la settimana 1 inizia il primo giorno dell'anno e continua fino al settimo giorno dell'anno.
W Settimana del mese (1-5) in cui la settimana 1 inizia il primo giorno del mese e termina il settimo.
IW Settimana dell'anno (1-52 o 1-53) in base allo standard ISO.
D Giorno della settimana (1-7).
GIORNO Nome del giorno.
DD Giorno del mese (1-31).
DDD Giorno dell'anno (1-366).
DY Nome del giorno abbreviato.
J Giorno giuliano; il numero di giorni dal 1 ° gennaio 4712 aC.
HH12 Ora del giorno (1-12).
HH24 Ora del giorno (0-23).
PERDERE Minuto (0-59).
SSSSS Secondi dopo la mezzanotte (0-86399).
FF Secondi frazionari. Utilizzare un valore compreso tra 1 e 9 dopo FF per indicare il numero di cifre nelle frazioni di secondo. Ad esempio, "FF4".
AM PM Indicatore meridiano
D.C., a.C. Indicatore AD, BC
TZD Informazioni sull'ora legale. Ad esempio, "PST"
TZH, TZM, TZR Ora / minuto / regione del fuso orario.

L'esempio seguente converte una stringa di caratteri in una data:

SELECT TO_DATE('January 15, 1989, 11:00 A.M.',  'Month dd, YYYY, HH:MI A.M.',  'NLS_DATE_LANGUAGE = American')
FROM DUAL;

TO_DATE('
---------
15-JAN-89

Funzioni generali

Le funzioni generali vengono utilizzate per gestire i valori NULL nel database. L'obiettivo delle funzioni di gestione NULL generali è sostituire i valori NULL con un valore alternativo. Vedremo brevemente queste funzioni di seguito.

NVL

La funzione NVL sostituisce un valore alternativo con un valore NULL.

Sintassi:

NVL( Arg1, replace_with )

Nella sintassi, entrambi i parametri sono obbligatori. Notare che la funzione NVL funziona con tutti i tipi di dati. Inoltre, il tipo di dati della stringa originale e la sostituzione devono essere in uno stato compatibile, ovvero uguale o convertibile implicitamente da Oracle.

Se arg1 è un valore di carattere, oracle converte la stringa di sostituzione nel tipo di dati compatibile con arg1 prima di confrontarli e restituisce VARCHAR2 nel set di caratteri di expr1. Se arg1 è numerico, Oracle determina l'argomento con la precedenza numerica più alta, converte implicitamente l'altro argomento in quel tipo di dati e restituisce quel tipo di dati.

L'istruzione SELECT di seguito visualizzerà "n / a" se un dipendente non è stato ancora assegnato a nessun lavoro, ovvero JOB_ID è NULL. In caso contrario, visualizzerà il valore JOB_ID effettivo.

SELECT  first_name, NVL(JOB_ID, 'n/a')
FROM employees;

NVL2

Come miglioramento rispetto a NVL, Oracle ha introdotto una funzione per sostituire il valore non solo per i valori delle colonne NULL ma anche per le colonne NOT NULL. La funzione NVL2 può essere utilizzata per sostituire un valore alternativo per NULL e un valore non NULL.

Sintassi:

NVL2( string1, value_if_NOT_null, value_if_null )

L'istruzione SELECT di seguito visualizzerà "Bench" se JOB_CODE per un dipendente è NULL. Per un valore definito non nullo di CODICE LAVORO, mostrerebbe il valore costante "Lavoro assegnato".

SQL> SELECT NVL2(JOB_CODE, 'Job Assigned', 'Bench')
FROM employees;

NULLIF

La funzione NULLIF confronta due argomenti expr1 ed expr2. Se expr1 ed expr2 sono uguali, restituisce NULL; altrimenti, restituisce expr1. A differenza dell'altra funzione di gestione null, il primo argomento non può essere NULL.

Sintassi:

NULLIF (expr1, expr2)

Si noti che il primo argomento può essere un'espressione che restituisce NULL, ma non può essere il valore letterale NULL. Entrambi i parametri sono obbligatori per l'esecuzione della funzione.

La query seguente restituisce NULL poiché entrambi i valori di input, 12 sono uguali.

SELECT	NULLIF (12, 12)
FROM DUAL;

Allo stesso modo, la query sottostante restituisce "SUN" poiché entrambe le stringhe non sono uguali.

SELECT NULLIF ('SUN', 'MOON')
FROM DUAL;

COALESCE

La funzione COALESCE, una forma più generica di NVL, restituisce la prima espressione non nulla nell'elenco degli argomenti. Richiede almeno due parametri obbligatori ma il massimo di argomenti non ha limiti.

Sintassi:

COALESCE (expr1, expr2, ... expr_n )

Considera la seguente query SELECT. Seleziona il primo valore non nullo inserito nei campi indirizzo per un dipendente.

SELECT COALESCE (address1, address2, address3) Address
FROM  employees;

È interessante notare che il funzionamento della funzione COALESCE è simile al costrutto IF..ELSIF..ENDIF. La query sopra può essere riscritta come -

IF address1 is not null THEN
   result := address1;
ELSIF address2 is not null THEN
   result := address2;
ELSIF address3 is not null THEN
   result := address3;
ELSE
   result := null;
END IF;

Funzioni condizionali

Oracle fornisce le funzioni condizionali DECODE e CASE per imporre condizioni anche nell'istruzione SQL.

La funzione DECODE

La funzione è l'equivalenza SQL dell'istruzione procedurale condizionale IF..THEN..ELSE. DECODE funziona con valori / colonne / espressioni di tutti i tipi di dati.

Sintassi:

DECODE (expression, search, result [, search, result]... [, default])

La funzione DECODE confronta l'espressione con ogni valore di ricerca in ordine. Se esiste l'uguaglianza tra l'espressione e l'argomento di ricerca, restituisce il risultato corrispondente. In caso di mancata corrispondenza, viene restituito il valore predefinito, se definito, altrimenti NULL. In caso di mancata corrispondenza di compatibilità di tipo, Oracle esegue internamente una possibile conversione implicita per restituire i risultati.

È un dato di fatto, Oracle considera due valori nulli equivalenti mentre lavora con la funzione DECODE.

SELECT DECODE(NULL,NULL,'EQUAL','NOT EQUAL') 
FROM DUAL;

DECOD
-----
EQUAL

Se expression è null, Oracle restituisce il risultato della prima ricerca anch'esso null. Il numero massimo di componenti nella funzione DECODE è 255.

SELECT	first_name, salary, DECODE (hire_date, sysdate,'NEW JOINEE','EMPLOYEE')
	FROM employees;

Espressione CASE

Le espressioni CASE funzionano sullo stesso concetto di DECODE ma differiscono per sintassi e utilizzo.

Sintassi:

CASE  [ expression ]
   WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   ...
   WHEN condition_n THEN result_n
   ELSE result
END

La ricerca Oracle inizia da sinistra e si sposta verso destra finché non trova una condizione vera, quindi restituisce l'espressione del risultato ad essa associata. Se non viene rilevata alcuna condizione ed esiste una clausola ELSE, Oracle restituisce il risultato definito con else. In caso contrario, Oracle restituisce null.

Il numero massimo di argomenti in un'espressione CASE è 255. Tutte le espressioni contano per questo limite, inclusa l'espressione iniziale di un'espressione CASE semplice e l'espressione ELSE opzionale. Ogni coppia WHEN ... THEN conta come due argomenti. Per evitare di superare questo limite, è possibile nidificare espressioni CASE in modo che return_expr stessa sia un'espressione CASE.

SELECT first_name, CASE	WHEN salary < 200 THEN 'GRADE 1'
			WHEN salary > 200 AND salary < 5000 THEN 'GRADE 2'
			ELSE 'GRADE 3'
		   END CASE
FROM employees;	

ENAM  	CASE
----    -------
JOHN    GRADE 2
EDWIN   GRADE 3
KING    GRADE 1