Funzione NVL
La funzione NVL è solitamente la più utilizzata. La funzione accetta due parametri: NVL (espr1, espr2). Se il primo parametro di expr1 non è NULL, la funzione restituisce il suo valore. Se il primo parametro è NULL, la funzione restituisce invece il valore del secondo parametro expr2.
Tener conto di 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. Nota che se generi un valore utilizzando una funzione, gli viene assegnato un alias. I risultati della query saranno simili a questo:
| EMPNO | ENAME | COMM | NVL_COMM |
| 7369 | FABBRO | 0 | |
| 7499 | ALLEN | 300 | 300 |
| 7521 | REPARTO | 500 | 500 |
| 7566 | JONES | 0 | |
| 7654 | MARTIN | 1400 | 1400 |
| 7698 | BLAKE | 0 | |
| 7782 | CLARK | 0 | |
| 7839 | RE | 0 | |
| 7844 | TURNER | 0 | 0 |
| 7900 | JAMES | 0 | |
| 7902 | GUADO | 0 | |
| 7934 | MUGNAIO | 0 |
CEIL (n) funzione
La funzione CEIL restituisce il numero 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 DUAL
Funzione TRONCO (n [, m])
TRUNC restituisce il numero n, troncato a m posizioni decimali. Il parametro m può essere omesso - in questo caso, n viene troncato a un numero intero.
SELEZIONA TRUNC (100.25678) X1, TRUNC (-100.25678) X2, TRUNC (100.99) X3,
TRONCO (100.25678, 2) X4
DA DUAL
SEGNO (n) funzione
La funzione SEGNO determina il segno di un numero. Se n è positivo, la funzione restituisce 1. Se n è negativo, viene restituito -1. Se uguale a zero, viene restituito 0. Ad esempio:
SELEZIONA SEGNO (100,22) X1, SEGNO (-100,22) X2, SEGNO (0) X3
DA DUAL
Una caratteristica interessante di questa funzione è la capacità di trasferire m uguale a zero- non c'è divisione per 0 errore.
Funzione POTENZA (n, m)
La funzione POWER eleva n alla potenza m. Il grado può essere frazionario o 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 DUAL
| X1 | X2 | X3 | X4 |
| 100 | 10 | 10 | 0,1 |
In alcuni casi, quando si chiama questa funzione, potresti ricevere situazione eccezionale... Per esempio:
SELEZIONA POTENZA (-100, 1/2) X2
DA DUAL
V in questo caso viene effettuato un tentativo di calcolare la radice quadrata di un numero negativo, che genererà l'errore ORA-01428 "Argomento fuori intervallo".
Funzione SQRT (n)
Questa funzione ritorna Radice quadrata dal numero n. Per esempio:
SELEZIONA SQRT (100) X
DA DUAL
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 (dove n deve essere maggiore di zero). Esempio:
SELEZIONA EXP (2) X1, LN (1) X2, LN (EXP (2)) X3
Funzione TO_CHAR con numeri
Funzioni per la conversione dei dati in altri tipi di dati. TO_CHAR (numero) converte un numero in testo. TO_NUMBER (stringa) converte il testo in numero.
SELECT TO_CHAR (123) FROM DUAL restituirà la stringa 123, SELECT TO_NUMBER (`12345") FROM DUAL restituirà 12345.
Lavoro di laboratorio. Modifica del formato dei numeri visualizzati
Modifiche al formato dei valori numerici in Oracle SQL, funzione TO_CHAR per lavorare con valori numerici.
Esercizio:
Scrivi una query che visualizzi il nome, il cognome e le informazioni sullo stipendio per i dipendenti dalla tabella hr.employees, nel formato mostrato nella Figura 1. 3.4-1:
Riso. 3.4 -1
In questo caso, i dati dovrebbero essere ordinati in modo tale che le prime righe vengano visualizzate per i dipendenti con lo stipendio più alto.
Nota:
Alcuni valori salariali in Fig. 3.4-1 sono cambiati, quindi potrebbero non corrispondere ai tuoi valori.
Soluzione:
SELECT nome AS "Nome", cognome As "Cognome", TO_CHAR (STIPENDIO, "L999999999,99") As "Salary" FROM hr.dipendenti ORDINA PER STIPENDIO DESC.
Funzioni TO_NUMBER e TO_DATE
Funzione per convertire una stringa in una data TO_DATE (stringa, formato). Valori possibili formati sono già stati discussi sopra, quindi darò diversi esempi di utilizzo di questa funzione. Esempi:
SELEZIONARE TO_DATE ("01.01.2010", `GG.MM.AAAA") A PARTIRE DAL DUAL restituirà la data "01.01.2010";
SELEZIONARE TO_DATE ("01.GEN.2010", `GG.MON.AAAA") A PARTIRE DAL DUAL restituirà la data "01.01.2009";
SELEZIONARE TO_DATE ("15-01-10", `GG-MM-AA") A PARTIRE DAL DUAL restituirà la data "15/01/2010".
Funzione di conversione da stringa a stringa valore numerico TO_NUMBER (stringa, formato). I valori di formato più comuni sono elencati nella tabella, quindi diamo un'occhiata all'uso di questa funzione tramite esempi. Esempi:
SELEZIONARE TO_NUMBER (`100") A PARTIRE DAL DUAL restituirà il numero 100 SELECT TO_NUMBER (`0010.01", "9999D99") A PARTIRE DAL DUAL restituirà 10.01;
SELEZIONARE TO_NUMBER ("500.000", "999G999") A PARTIRE DAL DUAL restituirà 500000.
Elemento RR in formato data
L'elemento del formato della data e dell'ora RR è simile all'elemento del formato della data e dell'ora YY, ma fornisce ulteriore flessibilità per l'archiviazione dei valori della data in altri secoli. L'elemento di formato datetime RR consente di memorizzare le date dal 20° al 21° secolo specificando solo le ultime due cifre dell'anno.
Se le ultime due cifre dell'anno corrente sono comprese tra 00 e 49, l'anno restituito avrà le stesse prime due cifre dell'anno corrente.
Se le ultime due cifre dell'anno in corso sono comprese tra 50 e 99, le prime 2 cifre dell'anno restituito sono maggiori di 1 rispetto alle prime 2 cifre dell'anno in corso.
Se le ultime due cifre dell'anno in corso vanno da 00 a 49, le prime 2 cifre dell'anno restituito sono 1 in meno delle 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 avrà le stesse prime due cifre dell'anno in corso.
Funzione NVL
La funzione NVL è solitamente la più utilizzata. La funzione accetta due parametri: NVL (espr1, esp2). Se il primo parametro di 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 sopra restituirà n / "se il campo supplier_city è contenuto valore zero... In caso contrario, restituirà il valore supplier_city.
Un altro esempio di utilizzo della funzione NVL in Oracle/PLSQL è:
selezionare id_fornitore, NVL (desc_fornitore, nome_fornitore) dai fornitori.
Questa istruzione SQL ritornerà Nome del fornitore campo se fornitore_desc contiene un valore nullo. Altrimenti tornerà fornitore_desc.
Un ultimo esempio: l'utilizzo della funzione NVL in Oracle/PLSQL è: selezionare NVL (commissione, 0) dalle vendite;
Questa istruzione SQL ha restituito 0 se commissione il campo contiene un valore nullo. Altrimenti, ritornerebbe commissione campo.
Conversioni NVL per diversi tipi di dati
La funzione NVL viene utilizzata per convertire un valore non definito in un valore effettivo: NVL ( espressione1, espressione 2), dove:
espressione1- Il valore originale o calcolato, che potrebbe essere indefinito.
espressione2- Il valore che viene sostituito dal valore indefinito.
Nota: La funzione NVL può essere utilizzata per convertire qualsiasi tipo di dati, ma il risultato sarà sempre lo stesso tipo di espressione1.
Converti NVL per tipi diversi:
NUMERO - NVL (colonna numerica, 9).
CHAR o VARCHAR2 - NVL (simboli | colonna,"Non disponibile").
Lavoro di laboratorio. Applicazione della funzione NVL
Funzione NVL per lavorare con valori null in Oracle SQL.
Esercizio:
Scrivere una query che mostri informazioni sul nome e cognome dei dipendenti dalla tabella hr.employees., nonché il tasso di commissione (colonna COMMISSION_PCT) per il dipendente. In questo caso, per quei dipendenti per i quali la provvigione non è definita, è necessario visualizzare il valore 0. Il risultato della query dovrebbe essere lo stesso mostrato in fig. 3.5-1.
Riso. 3.5 -1 (mostra i valori a partire dalla riga 51)
Soluzione:
Il codice di richiesta corrispondente può essere così:
SELECT first_name AS “Nome”, cognome As “Cognome”, NVL (COMMISSION_PCT, 0) As “Tasso di commissione” FROM hr.employees.
Le elezioni sono in corso, il che significa che oggi eleggeremo il governatore. O meglio, nominare un ostacolo al passaggio dei candidati al turno successivo. Ma prima devi definire i termini.
Qual è la scelta garantita in SQL? Supponiamo che nella condizione di query per la tabella venga effettuato un confronto tra un campo e una variabile. A seconda del valore di questa variabile, la query può o meno restituire righe dalla tabella. Se il valore della variabile esce dal fatto che le righe della tabella non vengono restituite, in questo caso è necessario generare appositamente un risultato sinistro predeterminato. Cioè, in ogni caso, la richiesta generale deve essere garantita per restituire qualcosa. Il termine stesso è preso da qui. Tuttavia, l'attività è complicata dal fatto (o forse viceversa, è semplificata) che invece di una semplice cella con un valore, dobbiamo essere garantiti per restituire una stringa a tutti gli effetti.
Cito i dati della Commissione elettorale centrale. Il primo turno di votazioni si è concluso con i seguenti risultati
crea una tabella di elezione come con t (id, nome, professione, voti) as (seleziona 1, "Incorruttibile Amoral Chistorukovich", "procuratore", 9867 da doppia unione tutti seleziona 2, "Bilancio effettivo Osvoilovich", "uomo d'affari", 8650 da doppia unione tutte selezionate 3, "Pravdivy Chtodadut Wiszajlovich", "editore del giornale", 745 da doppia unione tutte selezionate 4, "Bella Lucifera Feoktistovna", "badessa", 234 da doppia unione tutte selezionate 5, "Khrensgory Ktotakoy Niktoneznaevich", "insegnante di scuola rurale", 3 da dual) selezionare * da t; modificare l'aggiunta della tabella delle elezioni chiave primaria(ID);
La query iniziale che determina l'ingresso dei candidati al turno successivo è estremamente semplice:
seleziona * dall'elezione dove voti>: vincolato
Supponiamo che la soglia di superamento sia di 8000 voti. Combinando questo numero con: legato, otteniamo
Ma cosa succede se la soglia di superamento è 10.000, che è più del numero massimo di voti espressi? Allora è ovvio che nessuno dei suddetti candidati si qualificherà per il turno successivo. In questo caso si instaura una dittatura e il gatto colonnello diventa automaticamente il governatore della regione. Ecco alcuni dei modi per assegnarlo:
Metodo 1. UNION ALL tabelle con autoaggregazione
Con t as (seleziona * da elezione dove voti>: vincolato) seleziona id, nome, professione, voti da t unione tutti seleziona 0, "Colonnello", "gatto", nullo da t con conteggio (*) = 0 ordina per voti desc
Metodo 2... UNION ALL tavoli con DUAL
con t as (seleziona * da elezione dove voti>: vincolato) seleziona id, nome, professione, voti da t unione tutti seleziona 0, "Colonnello", "gatto", null da duale dove non esiste (seleziona nullo da t) ordine per voti disc
Metodo 3. Tavoli LEFT JOIN con DUAL
seleziona nvl (e.id, 0) id, nvl2 (e.id, e.name, "Colonnello") nome, nvl2 (e.id, e.profession, "cat") professione, e.votes from dual d left unisciti alle elezioni e su e.votes>
per i casi in cui la tabella non ha un campo NOT NULL univoco
selezionare nvl2 (e.rowid, e.id, 0) id, nvl2 (e.rowid, e.name, "Colonnello") nome, nvl2 (e.rowid, e.profession, "cat") professione, e.votes da doppia d sinistra unisciti all'elezione e su e.votes>: ordine vincolato da e.votes desc
Metodo 4. Modello con un gatto.
seleziona id, nome, professione, voti dall'elezione dove voti>: dimensione del modello vincolata da (rownum rn) misure (id, nome, professione, voti) regole (nome = nvl2 (id, nome, "Colonnello"), professione = nvl2 (id, profession, "cat"), id = nvl (id, 0)) ordina per voti desc 
Sotto risultato garantito con un legame con una barriera troppo grande di 10.000
È chiaro che se imposti il livello iniziale di 8000, anche queste query funzioneranno correttamente.
È tutto per ora. Il nome di tutti i personaggi corrisponde a persone reali considerato casuale.
È possibile visualizzare la soluzione ad altri problemi tipici di SQL
Quando si compongono 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). L'espressione Coalesce risolve perfettamente questo problema. In questo articolo troverai descrizione completa Unisci espressioni sql, dettagli sull'utilizzo ed esempi.
Emissione di valori non vuoti con Coalesce. Peculiarità
Considera le funzionalità di utilizzo di Sql Coalesce:
- Consente di specificare un numero qualsiasi di argomenti (a differenza di Isnull / Nvl / Nvl2, che hanno un numero limitato di argomenti).
- Può accettare sottoquery come argomento.
- Restituisce un risultato uguale a il primo un valore diverso da Null o Null se diverso da Valori nulli non sarà trovato.
- Sql Coalesce può essere utilizzato in una clausola Select per selezionare un valore non vuoto e anche in Dove chiarire che un insieme di colonne con valori vuoti non consentito (/ consentito).
- Questa espressione equivale all'applicazione dell'espressione Case, che verifica ogni argomento in sequenza rispetto alla condizione Quando argomento1 non è nullo allora argomento1. Fondamentalmente, Coalesce è una scorciatoia per facilità d'uso e, in molti DBMS, gli ottimizzatori di query riscrivono l'espressione Coalesce in Case.
- Le funzioni Sql Coalesce sono disponibili in tutti i principali relazionali
Coalesce sintassi
Chiunque abbia mai usato Coalesce durante la scrittura di query sql sa che la sintassi di questa espressione estremamente semplice. abbastanza in parentesi specificare gli argomenti testati per Null, separati da virgole. Se assumiamo che gli argomenti siano denominati arg1, arg2, ... argN, la sintassi per Coalesce sarà la seguente:
Coalesce (arg1, arg2, ... argN).
Prepariamo diverse tabelle per studiare il meccanismo di questa espressione.
Preparazione tavoli
Per capire meglio Coalesce sql descrizione, creiamo 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).
ID | nome_oggetto | area_yt | area_decl |
Lavori in corso 1 | |||
Camera 1 | |||
Stanza 2 | |||
Terreno 1 | |||
Edificio 1 | |||
Stanza 3 | |||
Edificio 2 | |||
Lascia che la seconda tabella Basic_characteristic contenga informazioni sulle caratteristiche principali della proprietà - Estensione, Profondità, Area, Ambito, Altezza.
ID | nome_oggetto | Estensione | Profondità | La zona | Scopo | Altezza |
Edificio 1 | ||||||
Terreno 1 | ||||||
Camera 1 | ||||||
Stanza 2 | ||||||
Stanza 3 |
Abbiamo esaminato la sintassi di Coalesce sql, la descrizione, le caratteristiche di utilizzo, e passiamo direttamente agli esempi.
Esempi di utilizzo
La sintassi per l'espressione Coalesce è estremamente semplice, ma è importante ricordare che il comando restituirà il PRIMO valore non vuoto trovato dall'elenco degli argomenti. Questa osservazione ha un molto Grande importanza pertanto, gli argomenti nell'espressione devono essere ordinati in ordine di importanza. Il modo più semplice per capire il principio è dalla tabella delle aree. Fai una query che scelga il nome della proprietà, oltre al valore dell'area:
SELEZIONA Area.id, Area.nome_oggetto, unisci (Area.area_yt, Area.area_decl) |
E ottieni il risultato:
Per gli oggetti "Edificio 1", "Terreno 1" e "Costruzione 2", entrambi i valori dell'area sono stati riempiti, ma l'area è stata specificata in priorità, poiché l'abbiamo indicata per prima nell'elenco degli argomenti. L'espressione Coalesce ha trovato il primo valore non vuoto e lo ha scaricato, interrompendo ulteriori scansioni degli argomenti. questa costruzione la richiesta è corretta, perché l'area specificata è più definita di quella dichiarata. Se avessimo specificato l'area dichiarata come primo argomento, quando questo campo della tabella fosse stato riempito, avrebbe avuto la priorità.
Oltre ad essere usata in Select, molto spesso l'espressione Coalesce è usata con dove condizione... Consente di tagliare dal risultato quelle righe in cui i valori dell'elenco dei campi sono vuoti (o viceversa, includere nel risultato solo quei valori in cui l'elenco dei campi è vuoto). Questa situazione si verifica ovunque: ad esempio, in azienda, al momento della registrazione di un nuovo dipendente, nel database sono state inserite solo le informazioni di base su di lui e la compilazione informazioni dettagliate lasciato "per dopo". A poco a poco, emergono "lacune" - prima del controllo o quando si invia un dipendente in vacanza / viaggio di lavoro / congedo per malattia.
Selezioniamo dalla tabella con le caratteristiche principali gli oggetti immobiliari per i quali nessuno dei valori di caratteristica è compilato:
Ci auguriamo che la nostra descrizione dettagliata di Coalesce sql ti abbia aiutato a comprendere tutte le funzionalità dell'utilizzo di questa espressione, oltre a trattare importanti sfumature.
