Conversii NVL pentru diferite tipuri de date. Instrucțiuni de ramificare în instrucțiunea SELECT

Funcția NVL

Funcția NVL este de obicei cea mai frecvent utilizată. Funcția ia doi parametri: NVL (expr1, expr2). Dacă primul parametru al expr1 nu este NULL, atunci funcția își returnează valoarea. Dacă primul parametru este NULL, atunci funcția returnează în schimb valoarea celui de-al doilea parametru expr2.

Considera exemplu practic... Câmpul COMM din tabelul EMP poate conține valori NULL. Când executați o interogare precum:

SELECTAȚI EMPNO, ENAME, COMM, NVL (COMM, 0) NVL_COMM

DE LA SCOTT.EMP

valoarea NULL va fi înlocuită cu zero. Rețineți că, dacă generați o valoare folosind o funcție, i se atribuie un alias. Rezultatele interogării vor arăta astfel:

CEIL (n) Funcție

Funcția CEIL returnează cel mai mic număr întreg mai mare sau egal cu numărul n transmis ca parametru. De exemplu:

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

DE LA DUAL

Funcția TRUNC (n [, m])

TRUNC returnează numărul n, trunchiat la m zecimale. Parametrul m poate fi omis - în acest caz, n este trunchiat la un număr întreg.

SELECTARE TRUNC (100,25678) X1, TRUNC (-100,25678) X2, TRUNC (100,99) X3,

TRUNC (100.25678, 2) X4

DE LA DUAL

Funcția SIGN (n).

Funcția SIGN determină semnul unui număr. Dacă n este pozitiv, funcția returnează 1. Dacă n este negativ, este returnat -1. Dacă este egal cu zero, atunci este returnat 0. De exemplu:

SELECTARE SEMNUL (100.22) X1, SEMNUL (-100.22) X2, SEMNUL (0) X3

DE LA DUAL

O caracteristică interesantă a acestei funcții este capacitatea de a transfera m egal cu zero- nu există o eroare de împărțire cu 0.

Funcția POWER (n, m).

Funcția POWER ridică n la puterea m. Gradul poate fi fracționat sau negativ, ceea ce extinde semnificativ capacitățile acestei funcții.

SELECTARE PUTERE (10, 2) X1, PUTERE (100, 1/2) X2,

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

DE LA DUAL

În unele cazuri, atunci când apelați această funcție, este posibil să primiți situatie exceptionala... De exemplu:

SELECTARE PUTEREA (-100, 1/2) X2

DE LA DUAL

V în acest caz se încearcă calcularea rădăcinii pătrate a unui număr negativ, ceea ce va avea ca rezultat eroarea ORA-01428 „Argument în afara intervalului”.

Funcția SQRT (n).

Această funcție se intoarce Rădăcină pătrată din numărul n. De exemplu:

SELECTARE SQRT (100) X

DE LA DUAL

Funcțiile EXP (n) și LN (n).

Funcția EXP ridică e la puterea lui n, iar funcția LN calculează logaritmul natural al lui n (unde n trebuie să fie mai mare decât zero). Exemplu:

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

Funcția TO_CHAR cu numere

Funcții pentru conversia datelor în alte tipuri de date. TO_CHAR (număr) convertește un număr în text. TO_NUMBER (șir) convertește textul în număr.

SELECT TO_CHAR (123) FROM DUAL va returna șirul 123, SELECT TO_NUMBER (`12345") FROM DUAL va returna 12345.

Lucrări de laborator. Modificarea formatului numerelor afișate

Modificări ale formatului valorilor numerice în Oracle SQL, funcție TO_CHAR pentru lucrul cu valori numerice.

Exercițiu:

Scrieți o interogare care să afișeze informațiile despre prenume, prenume și salariu pentru angajați din tabelul hr.employees, în formatul prezentat în Figura 1. 3.4-1:

Orez. 3.4 -1

În acest caz, datele trebuie sortate în așa fel încât primele rânduri să fie afișate pentru angajații cu cel mai mare salariu.

Notă:

Unele valori salariale din Fig. 3.4-1 s-au schimbat, așa că este posibil să nu se potrivească cu valorile dvs.

Soluţie:

SELECT prenumele AS „Prenume”, prenume Ca „Nume”, TO_CHAR (SALARY, „L999999999.99”) Ca „Salariu” FROM hr.employees ORDER BY SALARY DESC.

Funcțiile TO_NUMBER și TO_DATE

Funcție pentru conversia unui șir într-o dată TO_DATE (șir, format). Valori posibile formatele au fost deja discutate mai sus, așa că voi da câteva exemple de utilizare a acestei funcții. Exemple:

SELECTAȚI TO_DATE ("01.01.2010", `ZZ.LL.AAAA") DIN DUAL va returna data `01.01.2010";

SELECTAȚI TO_DATE ("01.JAN.2010", `ZZ.LUN.AAAA") DIN DUAL va returna data `01.01.2009";

SELECTAȚI TO_DATE ("15-01-10", `ZZ-LL-AA") DIN DUAL va returna data `15/01/2010".

Funcția de conversie șir în șir valoare numerică TO_NUMBER (șir, format). Cele mai obișnuite valori de format sunt enumerate în tabel, așa că să ne uităm la utilizarea acestei funcții prin exemple. Exemple:

SELECTAȚI TO_NUMBER (`100") DIN DUAL va returna numărul 100 SELECT TO_NUMBER (`0010.01", "9999D99") DIN DUAL va reveni 10.01;

SELECTAȚI TO_NUMBER ("500.000", "999G999") DIN DUAL va returna 500000.

Element RR în format de dată

Elementul de format de dată și oră RR este similar cu elementul de format de dată și oră YY, dar oferă o flexibilitate suplimentară pentru stocarea valorilor datei în alte secole. Elementul de format RR datetime vă permite să stocați date din secolul 20 până în secolul 21, specificând doar ultimele două cifre ale anului.

Dacă ultimele două cifre ale anului curent sunt de la 00 la 49, atunci anul returnat are aceleași primele două cifre ca și anul curent.

Dacă ultimele două cifre ale anului curent sunt între 50 și 99, atunci primele 2 cifre ale anului returnat sunt cu 1 mai mari decât primele 2 cifre ale anului curent.

Dacă ultimele două cifre ale anului curent sunt de la 00 la 49, atunci primele 2 cifre ale anului returnat sunt cu 1 mai mici decât primele 2 cifre ale anului curent.

Dacă ultimele două cifre ale anului curent sunt între 50 și 99, atunci anul returnat are aceleași primele două cifre ca anul curent.

Funcția NVL

Funcția NVL este de obicei cea mai frecvent utilizată. Funcția ia doi parametri: NVL (expr1, exp2). Dacă primul parametru al expr1 nu este NULL, atunci funcția își returnează valoarea. Dacă primul parametru este NULL, atunci funcția returnează în schimb valoarea celui de-al doilea parametru exp2.

Exemplu: Selectați NVL (furnizor_oraș, n / a ") de la furnizori:

Declarația SQL de mai sus va returna n / "dacă este conținut câmpul furnizor_city valoare zero... În caz contrar, va returna valoarea furnizor_city.

Un alt exemplu de utilizare a funcției NVL în Oracle / PLSQL este:

selectați supplier_id, NVL (furnizor_desc, furnizor_name) de la furnizori.

Această instrucțiune SQL va reveni numele furnizorului câmp dacă furnizor_desc conține o valoare nulă. Altfel se va întoarce furnizor_desc.

Un ultim exemplu: utilizarea funcției NVL în Oracle / PLSQL este: selectați NVL (comision, 0) din vânzări;

Această instrucțiune SQL a returnat 0 dacă comision câmpul conține o valoare nulă. Altfel, s-ar întoarce comision camp.

Conversii NVL pentru diferite tipuri de date

Funcția NVL este utilizată pentru a converti o valoare nedefinită într-o valoare reală: NVL ( expresia 1, expresia 2), Unde:

expresie 1- Valoarea originală sau calculată, care poate fi nedefinită.

expresie2- Valoarea care este înlocuită cu valoarea nedefinită.

Notă: Funcția NVL poate fi folosită pentru a converti orice tip de date, dar rezultatul va fi întotdeauna același tip ca expresie1.

Convertiți NVL pentru tipuri diferite:

NUMĂR - NVL (coloană numerică, 9).

CHAR sau VARCHAR2 - NVL (simboluri | coloană,"Nu e disponibil").

Lucrări de laborator. Aplicarea funcției NVL

Funcția NVL pentru lucrul cu valori nule în Oracle SQL.

Exercițiu:

Scrieți o interogare care să afișeze informații despre numele și prenumele angajaților din tabelul hr.employees, precum și rata comisionului (coloana COMMISSION_PCT) pentru angajat. În acest caz, pentru acei angajați pentru care comisionul nu este definit, trebuie să afișați valoarea 0. Rezultatul interogării ar trebui să fie același cu cel prezentat în fig. 3,5-1.

Orez. 3.5 -1 (afișează valorile începând cu rândul 51)

Soluţie:

Codul de solicitare corespunzător poate fi astfel:

SELECTAȚI prenume AS „Nume”, prenume Ca „Nume”, NVL (COMMISSION_PCT, 0) Ca „Rata comisionului” FROM hr.employees.

Alegerile sunt pe drum, ceea ce înseamnă că astăzi vom alege guvernatorul. Sau, mai degrabă, să desemneze o barieră în calea trecerii candidaților în turul următor. Dar mai întâi trebuie să definiți termenii.

Ce este alegerea garantată în SQL? Să presupunem că în condiția de interogare pentru tabel, se face o comparație între un câmp și o variabilă. În funcție de valoarea acestei variabile, interogarea poate returna sau nu rânduri din tabel. Dacă valoarea variabilei nu este returnată, atunci pentru acest caz este necesar să se genereze în mod special un rezultat din stânga predeterminat. Adică, în orice caz, cererea generală trebuie să fie garantată pentru a returna ceva. Termenul în sine este preluat de aici. Cu toate acestea, sarcina este complicată de faptul (și poate invers, este simplificată) că, în loc de o celulă simplă cu o valoare, trebuie să fim garantați că returnăm un șir cu drepturi depline.

Citez datele Comisiei Electorale Centrale. Primul tur de vot s-a încheiat cu următoarele rezultate

creați alegeri de masă ca cu t (id, nume, profesie, voturi) ca (selectați 1, "Incoruptibil Amoral Chistorukovich", "procuror", 9867 din uniunea dublă, toate selectați 2, "Buget efectiv Osvoilovici", "om de afaceri", 8650 din dublă uniune toți selectează 3, „True Chtodadut Wiszajlovich”, „redactor al ziarului”, 745 din dual union toți selectează 4, „Gracious Lucifera Feoktistovna”, „stăteță”, 234 din dual union toți selectează 5, „Khrensgory Ktotakoy Niktoneznaevich” , „profesor de școală rurală”, 3 din dual) selectați * din t; alter table election add cheia principala(id);
Interogarea inițială care determină intrarea candidaților în runda următoare este extrem de simplă:
selectați * din alegeri unde voturi>: legat
Să presupunem că pragul de trecere este de 8000 de voturi. Combinând acest număr cu: legat, obținem

Dar dacă pragul de trecere este de 10.000, care este mai mult decât numărul maxim de voturi exprimate? Atunci este evident că niciunul dintre candidații menționați mai sus nu se va califica în turul următor. În acest caz, se instaurează o dictatură și pisica colonel devine automat guvernatorul regiunii. Iată câteva dintre modalitățile de a-l atribui:

Metoda 1. UNION ALL mese cu autoagregate

Cu t ca (selectați * din alegeri unde voturi>: legat) selectați id, nume, profesie, voturi din t sindicat, toate selectați 0, „Colonel”, „pisica”, nul din t având număr (*) = 0 ordine după voturi desc

Metoda 2... UNIONARE TOATE mesele cu DUAL
cu t ca (selectați * din alegeri unde voturi>: legat) selectați id, nume, profesie, voturi din t sindicat toate selectați 0, „Colonel”, „pisica”, nul din dual unde nu există (selectați nul din t) ordine prin voturi desc

Metoda 3. LEFT JOIN mesele cu DUAL
selectați nvl (e.id, 0) id, nvl2 (e.id, e.name, „colonel”) nume, nvl2 (e.id, e.profession, „cat”) profesie, e.votes din dual d stânga înscrieți-vă la alegeri e pe e.voturi>
pentru cazurile în care tabelul nu are un câmp unic NOT NULL
selectați nvl2 (e.rowid, e.id, 0) id, nvl2 (e.rowid, e.name, „Colonel”) nume, nvl2 (e.rowid, e.profession, „cat”) profesie, e.votes din dual d stânga alăturați-vă alegerilor e pe e.voturi>: ordine legată prin e.voturi desc

Metoda 4. Model cu pisica.
selectați id, nume, profesie, voturi din alegeri unde voturi>: dimensiunea modelului legat de (rownum rn) măsuri (id, nume, profesie, voturi) reguli (nume = nvl2 (id, nume, "colonel"), profesie = nvl2 (id, profesie, "pisica"), id = nvl (id, 0)) ordonata dupa voturi desc

De mai jos rezultat garantat cu o legătură cu o barieră prea mare de 10.000

Este clar că dacă setați nivelul inițial de 8000, atunci și aceste interogări vor funcționa corect.

Asta este tot pentru acum. Numele tuturor personajelor se potrivește cu oameni adevărați considerat aleatoriu.

Soluția pentru alte probleme tipice SQL poate fi vizualizată

Când se compun interogări sql, apare adesea o situație când, la preluarea datelor, este necesar să se compare valorile mai multor coloane și să se afișeze pe cea care conține datele (nu goală). Expresia Coalesce rezolvă perfect această problemă. În acest articol veți găsi descriere completa Combinați expresii sql, detalii de utilizare și exemple.

Emiterea valorilor nevide cu Coalesce. Particularități

Luați în considerare caracteristicile de utilizare Sql Coalesce:

1. Permite specificarea oricărui număr de argumente (spre deosebire de Isnull / Nvl / Nvl2, care au un număr limitat de argumente).
2. Poate lua subinterogări ca argument.
3. Returnează un rezultat egal cu primul o altă valoare decât Null sau Null dacă este diferită de Valori nule nu va fi găsit.
4. Sql Coalesce poate fi folosit într-o clauză Select pentru a selecta o valoare nevidă și, de asemenea, în Unde pentru a clarifica faptul că un set de coloane cu valori goale nepermis (/ permis).
5. Această expresie este aceeași cu aplicarea expresiei Case, care testează secvențial fiecare argument față de condiția Când argument1 nu este nul, atunci argument1. Practic, Coalesce este o scurtătură pentru ușurință în utilizare, iar în multe SGBD-uri, optimizatorii de interogări rescriu expresia Coalesce în Case.
6. Funcțiile Sql Coalesce sunt disponibile în toate relațiile principale

Sintaxa de adunare

Oricine a folosit vreodată Coalesce când scrie interogări SQL știe că sintaxa a acestei expresii extrem de simplu. Destul în parantezele specificați argumentele testate pentru Null, separate prin virgule. Dacă presupunem că argumentele sunt denumite arg1, arg2, ... argN, atunci sintaxa pentru Coalesce va fi următoarea:

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

Să pregătim mai multe tabele pentru a studia mecanismul acestei expresii.

Pregatirea meselor

Pentru a înțelege mai bine Coalesce descriere sql, să creăm două tabele în baza de date care să conțină informații despre obiectele imobiliare.

Fie ca primul tabel, Zona, să conțină numele obiectelor imobiliare și zona acestora. Zona poate fi specificată (area_yt) sau declarată (area_decl).

Fie ca al doilea tabel Basic_characteristic să conțină informații despre principalele caracteristici ale proprietății - Extensie, Adâncime, Arie, Domeniu, Înălțime.

Am examinat sintaxa Coalesce sql, descrierea, caracteristicile de utilizare și să trecem direct la exemple.

Exemple de utilizare

Sintaxa expresiei Coalesce este extrem de simplă, dar este important să ne amintim că comanda va returna PRIMA valoare nevidă găsită din lista de argumente. Această remarcă are un foarte mare importanță prin urmare, argumentele din expresie trebuie ordonate în ordinea importanței. Cel mai simplu mod de a înțelege principiul este din tabelul cu zone. Faceți o interogare care alege numele proprietății, precum și valoarea zonei:

Și obțineți rezultatul:

Pentru obiectele „Clădire 1”, „Teren 1” și „Construcție 2”, ambele valori ale zonei au fost completate, dar zona a fost specificată în prioritate, deoarece am indicat-o primul în lista de argumente. Expresia Coalesce a găsit prima valoare nevide și a aruncat-o, oprind scanările ulterioare ale argumentelor. Această construcție cererea este corecta, deoarece zona specificata este mai definita decat cea declarata. Dacă am fi specificat zona declarată ca prim argument, atunci când acest câmp al tabelului a fost completat, ar avea prioritate.

Pe lângă faptul că este folosit în Select, foarte des este folosită expresia Coalesce cu unde condiție... Vă permite să tăiați din rezultat acele linii în care valorile listei de câmpuri sunt goale (sau invers, includeți în rezultat doar acele valori în care lista de câmpuri este goală). Această situație apare peste tot: de exemplu, la întreprindere, la înregistrarea unui nou angajat, numai informațiile de bază despre el au fost introduse în baza de date și completarea informatii detaliate lăsat „pentru mai târziu”. Treptat, apar „lacune” - fie înainte de cec, fie la trimiterea unui angajat în vacanță / călătorie de afaceri / concediu medical.

Să selectăm din tabelul cu principalele caracteristici obiecte imobiliare pentru care niciuna dintre valorile caracteristice nu este completată:

Sperăm că descrierea noastră detaliată Coalesce sql v-a ajutat să înțelegeți toate caracteristicile utilizării acestei expresii, precum și să vă ocupați de nuanțe importante.