Trasformazioni NVL per vari tipi di dati.

Quando si compilano query sql, si verifica spesso una situazione in cui, durante il recupero dei dati, è necessario confrontare i valori di più colonne e visualizzare quella che contiene i dati (non vuota). Questo compito è perfettamente risolto dall'espressione Coalesce. In questo articolo troverai descrizione completa Unisci espressioni sql, dettagli sull'utilizzo ed esempi.

Visualizzazione di valori non vuoti con Coalesce. Peculiarità

Considera le funzionalità di utilizzo di Sql Coalesce:

1. Consente un numero qualsiasi di argomenti (a differenza di Isnull/Nvl/Nvl2, che hanno un numero limitato di argomenti).
2. Può accettare sottoquery come argomenti.
3. Restituisce un risultato uguale a primo valore non Null o Null se non viene trovato alcun valore non Null.
4. Sql Coalesce può essere utilizzato in una clausola Select per selezionare un valore non vuoto e anche in un Where per specificare che un insieme di colonne con valori vuoti non consentito (/consentito).
5. Questa espressione equivale all'applicazione dell'espressione Case, che controlla ogni argomento in sequenza per la condizione Quando argomento1 non è nullo quindi argomento1. In sostanza, Coalesce è una "scorciatoia" creata per facilità d'uso e in molti ottimizzatori di query DBMS riscrivono l'espressione Coalesce con Case.
6. Le funzioni Sql Coalesce sono disponibili in tutti i principali relazionali

Sintassi

Chiunque abbia mai usato Coalesce durante la compilazione di query sql sa che la sintassi data espressione estremamente semplice. Abbastanza dentro parentesi specificare gli argomenti da controllare per Null separati da virgole. Se assumiamo che gli argomenti abbiano i nomi arg1, arg2, ... argN, la sintassi di Coalesce sarà la seguente:

Coalesce(arg1, arg2, ... argN).

Prepariamo diverse tabelle per studiare il meccanismo di questa espressione.

Preparazione della tavola

Per capire meglio Coalesce descrizione sql, creeremo due tabelle nel database contenenti informazioni sugli oggetti immobiliari.

Lascia che la prima tabella Area contenga i nomi degli oggetti immobiliari e la loro area. L'area può essere specificata (area_yt) o dichiarata (area_decl).

Lascia che la seconda tabella Basic_characteristic contenga informazioni sulla caratteristica principale della proprietà: lunghezza (Estensione), profondità (Profondità), area (Area), volume (Ambito), altezza (Altezza).

Abbiamo esaminato la sintassi di Coalesce sql, la descrizione, le funzionalità di utilizzo e andiamo direttamente agli esempi.

Esempi di utilizzo

La sintassi dell'espressione Coalesce è estremamente semplice, ma è importante ricordare che il risultato dell'esecuzione del comando sarà il PRIMO valore non vuoto trovato dall'elenco degli argomenti. Questa osservazione è molto Grande importanza, quindi gli argomenti nell'espressione devono essere disposti in ordine di importanza. Il modo più semplice per comprendere il principio è dalla tabella dell'area. Effettuare una query che selezioni il nome della proprietà, nonché il valore dell'area:

E ottieni il risultato:

Per gli oggetti "Edificio 1", "Terra 1" e "Costruzione 2", sono stati compilati entrambi i valori dell'area, ma l'area specificata si è rivelata prioritaria, poiché l'abbiamo indicata per prima nell'elenco degli argomenti . L'espressione Coalesce ha trovato il primo valore non nullo e l'ha emesso, interrompendo la ricerca di ulteriori argomenti. Questa costruzione richiesta è corretta, perché l'area specificata è più specifica di quella dichiarata. Se avessimo specificato l'area dichiarata come primo argomento, se questo campo della tabella fosse stato riempito, sarebbe prioritario.

Oltre ad essere usata in Seleziona, molto spesso viene usata l'espressione Coalesce con dove condizione. Consente di tagliare dal risultato quelle righe in cui i valori dell'elenco dei campi sono vuoti (o viceversa, di includere nel risultato solo quei valori in cui l'elenco dei campi è vuoto). Questa situazione si verifica ovunque: ad esempio, in un'impresa, al momento della registrazione di un nuovo dipendente, nel database sono state inserite solo le informazioni di base su di lui e il riempimento informazioni dettagliate lasciato per dopo. A poco a poco, emergono "lacune", prima dell'assegno o quando si invia un dipendente in vacanza / viaggio di lavoro / congedo per malattia.

Selezioniamo dalla tabella gli oggetti immobiliari con le caratteristiche principali, per i quali nessuno dei valori caratteristici è compilato:

Ci auguriamo che la nostra descrizione dettagliata di Coalesce sql ti abbia aiutato a comprendere tutte le caratteristiche dell'utilizzo di questa espressione, oltre a gestire importanti sfumature.

funzione NVL

La funzione NVL è generalmente la più utilizzata. La funzione riceve due parametri: NVL(espr1, expr2). Se il primo parametro expr1 non è NULL, la funzione restituisce il suo valore. Se il primo parametro è NULL, la funzione restituisce invece il valore del secondo parametro expr2.

Ritenere esempio pratico. Il campo COMM nella tabella EMP può contenere valori NULL. Quando si esegue una query come:

SELEZIONA EMPNO, ENAME, COMM, NVL(COMM, 0) NVL_COMM

DA SCOTT.EMP

il valore NULL verrà sostituito con zero. Si noti che se un valore viene generato utilizzando una funzione, gli viene assegnato un alias. I risultati della query saranno simili a:

funzione CEIL(n).

La funzione CEIL restituisce l'intero più piccolo maggiore o uguale al numero n passato come parametro. Per esempio:

SELEZIONA CEIL(100) X1, CEIL(-100) X2, CEIL(100.2) X3 , CEIL(-100.2) X4

DA DOPPIO

TRONCO(n[,m])

La funzione TRUNC restituisce il numero n troncato a m decimali. Il parametro m può essere omesso, nel qual caso n viene troncato a un numero intero.

SELEZIONA TRUNC(100.25678) X1, TRUNC(-100.25678) X2, TRUNC(100.99) X3,

TRUNC(100.25678, 2) X4

DA DOPPIO

funzione SEGNO(n).

La funzione SEGNO determina il segno di un numero. Se n è positivo, la funzione restituisce 1. Se negativo, viene restituito -1. Se è zero, viene restituito 0. Ad esempio:

SELEZIONA SEGNO(100.22) X1, SEGNO(-100.22) X2, SEGNO(0) X3

DA DOPPIO

Una caratteristica interessante di questa funzione è la capacità di trasferire m uguale a zero Ciò non comporta una divisione per 0 errore.

Funzione POTENZA(n, m).

La funzione POWER eleva il numero n alla potenza m. Il grado può essere frazionario e negativo, il che espande significativamente le capacità di questa funzione.

SELEZIONA POTENZA(10, 2) X1, POTENZA(100, 1/2) X2,

POTENZA(1000, 1/3) X3, POTENZA(1000, -1/3) X4

DA DOPPIO

In alcuni casi, quando si chiama questa funzione, è possibile che si verifichi eccezione. Per esempio:

SELEZIONA POTENZA(-100, 1/2) X2

DA DOPPIO

IN questo caso viene effettuato un tentativo di calcolare la radice quadrata di un numero negativo, che risulterà in un errore ORA-01428 "Argomento fuori intervallo".

Funzione SQRT(n)

Questa funzione ritorna Radice quadrata dal numero n. Per esempio:

SELEZIONA SQRT(100) X

DA DOPPIO

Funzioni EXP(n) e LN(n).

La funzione EXP eleva e alla potenza di n e la funzione LN calcola il logaritmo naturale di n (con n deve essere maggiore di zero). Esempio:

SELEZIONA EXP(2) X1, LN(1) X2, LN(EXP(2)) X3

Le elezioni sono in arrivo, il che significa che oggi sceglieremo il governatore. Per essere più precisi, per porre una barriera affinché i candidati passino al turno successivo. Ma prima dobbiamo definire i termini.

Che cos'è la selezione garantita in SQL? Diciamo che nella condizione di query alla tabella viene eseguito un confronto di qualche campo con qualche variabile. A seconda del valore di questa variabile, la query può restituire o meno righe dalla tabella. Se un tale valore della variabile cade e le righe della tabella non vengono restituite, in questo caso è necessario generare appositamente un risultato sinistro predefinito. Cioè, in ogni caso, deve essere garantito che la query generale restituisca qualcosa. Il termine stesso è tratto da qui. Tuttavia, il compito è complicato dal fatto (e forse viceversa, è semplificato) che invece di una semplice cella con un valore, dobbiamo essere garantiti per restituire una stringa a tutti gli effetti.

Porto i dati della Commissione elettorale centrale. Il primo turno di votazioni si è concluso con i seguenti risultati

creare elezione tabella come con t (id, nome, professione, voti) come (selezionare 1, "Chistorukovich amorale incorruttibile", "procuratore", 9867 da doppia unione tutti selezionare 2, "Bilancio effettivo Osvoilovich", "uomo d'affari", 8650 da doppia unione tutti seleziona 3, "Verissimo Chtodadut Napisailovich", "editore di giornali", 745 da doppia unione tutti seleziona 4, "Bella Lucifera Feoktistovna", "sacerdote", 234 da doppia unione tutti seleziona 5, "Hrensgory Chi è questo Niktonesnaevich", "insegnante di scuola rurale", 3 da doppio) selezionare * da t; modificare l'elezione del tavolo aggiungere chiave primaria(ID);
La query iniziale che determina i candidati per il prossimo round è estremamente semplice:
seleziona * da elezione dove voti > :bound
Supponiamo che la barriera all'ingresso sia di 8.000 voti. Associando questo numero con :bound, otteniamo

Ma cosa succede se la soglia è 10.000, che è più del numero massimo di voti? Quindi è ovvio che nessuno dei candidati sopra menzionati arriva al turno successivo. In questo caso si instaura una dittatura e il colonnello gatto diventa automaticamente il governatore della regione. Ecco alcuni dei modi in cui viene assegnato:

Metodo 1. UNION ALL tabelle con self aggregati

Con t come (seleziona * dall'elezione dove voti > :bound) seleziona id, nome, professione, voti da t union tutti seleziona 0, "Colonnello", "cat", null da t avendo conteggio(*) = 0 ordina per voti disc

Metodo 2. UNION ALL tabelle con DUAL
con t as (seleziona * da elezione dove voti > :bound) seleziona id, nome, professione, voti da t unione tutti seleziona 0, "colonnello", "gatto", nullo da duale dove non esiste (seleziona nullo da t) ordine per voti disc

Metodo 3. LEFT JOIN tavoli con DUAL
select nvl(e.id, 0) id, nvl2(e.id, e.name, "Colonnello") nome, nvl2(e.id, e.profession, "cat") professione, e.votes dalla doppia d sinistra unisciti alle elezioni e su e.votes >
per i casi in cui non esiste un campo NOT NULL univoco nella tabella
select nvl2(e.rowid, e.id, 0) id, nvl2(e.rowid, e.name, "Colonnello") nome, nvl2(e.rowid, e.profession, "cat") professione, e.votes dalla doppia d sinistra unisciti alle elezioni e su e.votes > :bound order by e.votes desc

Metodo 4. Modello con gatto.
seleziona id, nome, professione, voti dall'elezione dove voti > :bound modello dimensione per (rownum rn) misure (id, nome, professione, voti) regole (nome = nvl2(id, nome, "colonnello"), professione = nvl2 (id, professione, "cat"), id = nvl(id, 0)) ordine per voti disc

Qui di seguito risultato garantito con un legame con troppa barriera 10.000

È chiaro che se si imposta la barra iniziale su 8000, anche queste query funzioneranno correttamente.

È tutto per ora. Tutte le corrispondenze dei nomi dei personaggi con persone reali considera casuale.

È possibile visualizzare la soluzione di alcune altre attività SQL tipiche

TO_CHAR funzione con numeri

Funzioni per convertire i dati in altri tipi di dati. TO_CHAR(numero) converte un numero in testo. TO_NUMBER(stringa) converte il testo in un numero.

SELECT TO_CHAR (123) FROM DUAL restituirà la stringa 123, SELECT TO_NUMBER (`12345") FROM DUAL restituirà il numero 12345.

Lavoro di laboratorio. Modifica del formato dei numeri di output

Modifiche al formato numerico in Oracle SQL, funzione TO_CHAR per lavorare con valori numerici.

L'obiettivo:

Scrivere una query che visualizzi informazioni su nome, cognome e stipendio dei dipendenti dalla tabella hr.employees nel formato mostrato nella Figura 1. 3.4-1:

Riso. 3.4 -1

Allo stesso tempo, i dati dovrebbero essere ordinati in modo tale che le righe per i dipendenti con lo stipendio più alto vengano visualizzate per prime.

Nota:

Alcuni valori salariali in Fig. 3.4-1 sono stati modificati in modo che potrebbero non corrispondere ai tuoi valori.

Soluzione:

SELEZIONA nome COME "Nome", cognome Come "Cognome", TO_CHAR(SALARY, "L999999999.99") Come "Stipendio" DA hr.employees ORDINA PER SALARY DESC.

Funzioni TO_NUMBER e TO_DATE

Funzione per convertire la stringa in data TO_DATE(string, format). Possibili valori i formati sono già stati discussi sopra, quindi fornirò alcuni esempi di utilizzo di questa funzione. Esempi:

SELEZIONARE TO_DATE("01/01/2010", `GG.MM.AAAA") DA DUAL restituirà la data `01.01.2010";

SELEZIONARE TO_DATE("01.GEN.2010",`DD.MON.YYYY") DA DUAL restituirà la data `01.01.2009";

SELEZIONARE TO_DATE("15-01-10",`GG-MM-AA") DA DUAL restituirà la data `01/15/2010'.

Funzione in cui convertire la stringa valore numerico TO_NUMBER(stringa, formato). I valori di formato più comuni sono elencati nella tabella, quindi consideriamo l'uso di questa funzione usando degli esempi. Esempi:

SELEZIONARE TO_NUMBER(`100") DA DUAL restituirà il numero 100 SELECT TO_NUMBER(`0010.01", "9999D99") DA DUAL restituirà il numero 10.01;

SELEZIONARE TO_NUMBER("500.000", "999G999") DA DUAL restituirà il numero 500000.

Elemento RR in formato data

L'elemento formato data e ora RR è simile all'elemento formato data e ora YY, ma offre ulteriore flessibilità per la memorizzazione dei valori di data in altri secoli. L'elemento di formato RR datetime consente di memorizzare le date del 20° secolo nel 21° secolo specificando solo le ultime due cifre dell'anno.

Se le ultime due cifre dell'anno in corso sono comprese tra 00 e 49, l'anno restituito ha le stesse prime due cifre dell'anno in corso.

Se le ultime due cifre dell'anno in corso sono comprese tra 50 e 99, le prime 2 cifre dell'anno restituito sono 1 maggiore delle prime 2 cifre dell'anno in corso.

Se le ultime due cifre dell'anno in corso sono comprese tra 00 e 49, le prime 2 cifre dell'anno restituito sono 1 in meno rispetto alle prime 2 cifre dell'anno in corso.

Se le ultime due cifre dell'anno in corso sono comprese tra 50 e 99, l'anno restituito ha le stesse prime due cifre dell'anno in corso.

funzione NVL

La funzione NVL è generalmente la più utilizzata. La funzione riceve due parametri: NVL(espr1, expr2). Se il primo parametro expr1 non è NULL, la funzione restituisce il suo valore. Se il primo parametro è NULL, la funzione restituisce invece il valore del secondo parametro exp2.

Esempio: selezionare NVL (supplier_city, n/a") dai fornitori:

L'istruzione SQL precedente restituirà n/" se il campo supplier_city contiene valore zero. In caso contrario, restituirà il valore supplier_city.

Un altro esempio di utilizzo della funzione NVL in Oracle/PLSQL è:

selezionare supplier_id, NVL (supplier_desc, supplier_name) dai fornitori.

Questa istruzione SQL verrà restituita Nome del fornitore campo se fornitore_desc contiene un valore nullo. Altrimenti tornerà fornitore_desc.

Ultimo esempio: l'utilizzo della funzione NVL in Oracle/PLSQL è: select NVL(commission, 0) from sales;

Questa istruzione SQL ha restituito il valore 0 se commissione il campo contiene un valore nullo. Altrimenti, ritornerebbe commissioni campo.

Trasformazioni NVL per vari tipi di dati

Per convertire un valore non definito in un valore effettivo, utilizzare la funzione NVL: NVL ( espressione1, espressione2), dove:

espressione1- Il valore originale o calcolato, che può essere nullo.

espressione2- Il valore che viene sostituito dal valore null.

Nota: La funzione NVL può essere utilizzata per convertire qualsiasi tipo di dati, ma il risultato sarà sempre dello stesso tipo di espressione1.

Conversione NVL per vari tipi:

NUMERO-NVL (colonna numerica, 9).

CHAR o VARCHAR2 - NVL (caratteri|colonna,"Non disponibile").

Lavoro di laboratorio. Applicazione della funzione NVL

Funzione NVL per gestire valori nulli in Oracle SQL.

L'obiettivo:

Scrivere una query che visualizzi le informazioni sul nome e sul cognome dei dipendenti dalla tabella hr.employees., nonché sul tasso di commissione (colonna COMMISSION_PCT) per il dipendente. In questo caso, per quei dipendenti per i quali non è definita la provvigione, è necessario visualizzare il valore 0. Il risultato della query dovrebbe essere lo stesso di Fig. 3.5-1.

Riso. 3.5 -1 (valori riportati a partire dalla riga 51)

Soluzione:

Il codice di richiesta corrispondente potrebbe essere simile al seguente:

SELECT first_name AS "First Name", last_name As "Last Name", NVL (COMMISSION_PCT, 0) Come "Commission Rate" FROM hr.employees.