Utilizzo di espressioni condizionali

Funzioni generali

Le funzioni generali vengono utilizzate per gestire i valori NULL nel database. L'obiettivo delle funzioni generali di gestione NULL è 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, ad esempio 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 di seguito 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