NVL pretvorbe za različite vrste podataka.

Prilikom sastavljanja SQL upita često se javlja situacija kada prilikom odabira podataka trebate usporediti vrijednosti nekoliko stupaca i ispisati onaj koji sadrži podatke (nije prazan). Izraz Coalesce savršeno rješava ovaj problem. U ovom članku ćete pronaći puni opis Spajanje sql izraza, opis značajki korištenja, kao i primjeri.

Ispis vrijednosti koje nisu nulte koristeći Coalesce. Osobitosti

Pogledajmo značajke korištenja Sql Coalesce:

1. Omogućuje navođenje bilo kojeg broja argumenata (za razliku od Isnull/Nvl/Nvl2, koji imaju ograničen broj argumenata).
2. Može prihvatiti podupiti kao argumente.
3. Vraća rezultat jednak prvi vrijednost koja nije Null, ili Null ako nije pronađena nijedna vrijednost osim Null.
4. Sql Coalesce može se koristiti u klauzuli Select za odabir vrijednosti koja nije prazna, a također i u Gdje za navođenje da skup stupaca s prazne vrijednosti nedopušteno (/dopušteno).
5. Ovaj izraz je ekvivalentan korištenju izraza Case, koji provjerava svaki argument sekvencijalno za uvjet. Kada argument1 nije null, onda argument1. U biti, Coalesce je "prečac" stvoren za jednostavnu upotrebu, au mnogim DBMS-ovima, optimizatori upita prepisuju Coalesce izraz u Case.
6. Sql Coalesce funkcije dostupne su u svim vodećim relacijskim

Spoji sintaksu

Svatko tko je ikada koristio Coalesce pri pisanju sql upita zna da sintaksa dati izraz krajnje jednostavno. Dosta unutra zagrade navedite argumente testirane za Null, odvojene zarezima. Ako pretpostavimo da su argumenti nazvani arg1, arg2, ... argN, tada će sintaksa Coalesce izgledati ovako:

Spajanje (arg1, arg2, … argN).

Pripremimo nekoliko tablica za proučavanje mehanizma rada ovog izraza.

Priprema stolova

Da bismo bolje razumjeli Coalesce sql opis, napravimo dvije tablice u bazi podataka s podacima o objektima nekretnina.

Neka prva tablica Površina sadrži nazive nekretnina i njihovu površinu. Područje se može specificirati (area_yt) ili deklarirati (area_decl).

Neka druga tablica Osnovna_karakteristika sadrži podatke o glavnim karakteristikama objekta nekretnine - dužina (Extension), dubina (Depth), površina (Area), volumen (Scope), visina (Height).

Pogledali smo Coalesce sql sintaksu, opis, značajke korištenja i prijeđimo izravno na primjere.

Primjeri korištenja

Sintaksa izraza Coalesce iznimno je jednostavna, no važno je upamtiti da će rezultat izvršavanja naredbe biti PRVA neprazna vrijednost pronađena s popisa argumenata. Ova primjedba je vrlo veliki značaj, pa argumenti u izrazu moraju biti poredani po važnosti. Najlakši način da shvatite princip je iz tablice područja. Sastavite upit odabirom naziva svojstva, kao i vrijednosti područja:

I dobiti rezultat:

Za objekte "Zgrada 1", "Zemljište 1" i "Objekt 2" ispunjene su obje vrijednosti područja, ali je navedeno područje dobilo prioritet, jer smo ga naveli prvi na popisu argumenata. Izraz Coalesce pronašao je prvu nepraznu vrijednost i ispisao je, zaustavljajući daljnje argumente. Ova konstrukcija zahtjev - točan, jer je navedeno područje specifičnije od deklariranog. Da smo naveli deklarirano područje kao prvi argument, tada bi ovo polje tablice imalo prioritet kad bi se ispunilo.

Osim upotrebe u Selectu, Coalesce izraz se vrlo često koristi sa Gdje stanje. Omogućuje vam da iz rezultata izrežete one retke u kojima su vrijednosti popisa polja prazne (ili obrnuto, u rezultat uključite samo one vrijednosti u kojima popis polja nije popunjen). Ova situacija nalazi se posvuda: na primjer, u poduzeću, prilikom registracije novog zaposlenika, u bazu podataka unesene su samo osnovne informacije o njemu, a popunjavanje detaljne informacije ostavio za kasnije. Postupno nastaju “praznine” - bilo prije inspekcije, bilo kad se zaposlenik pošalje na godišnji odmor/službeni put/bolovanje.

Odaberimo iz tablice s glavnim karakteristikama objekte nekretnina za koje nije popunjena niti jedna karakteristična vrijednost:

Nadamo se da vam je naš detaljan opis Coalesce sql pomogao razumjeti sve značajke korištenja ovog izraza, kao i razumjeti važne nijanse.

NVL funkcija

Općenito se najčešće koristi funkcija NVL. Funkcija prima dva parametra: NVL(expr1, expr2). Ako prvi parametar expr1 nije NULL, tada funkcija vraća njegovu vrijednost. Ako je prvi parametar NULL, tada funkcija umjesto toga vraća vrijednost drugog parametra expr2.

Razmotrimo praktični primjer. COMM polje u EMP tablici može sadržavati NULL vrijednosti. Prilikom izvršavanja upita poput:

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

OD SCOTT.EMP

vrijednost NULL bit će zamijenjena nulom. Imajte na umu da kada se vrijednost generira pomoću funkcije, dodjeljuje joj se alias. Rezultati upita će izgledati ovako:

Funkcija CEIL(n)

Funkcija CEIL vraća najmanji cijeli broj veći ili jednak broju n koji je proslijeđen kao parametar. Na primjer:

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

OD DUALNOG

Funkcija TRUNC(n [,m])

Funkcija TRUNC vraća broj n, skraćen na m decimalnih mjesta. Parametar m ne može biti naveden; u ovom slučaju, n je skraćen na cijeli broj.

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

TRUNC(100.25678, 2) X4

OD DUALNOG

Funkcija SIGN(n)

Funkcija SIGN određuje predznak broja. Ako je n pozitivan, tada funkcija vraća 1. Ako je n negativan, vraća -1. Ako je jednako nuli, vraća se 0. Na primjer:

SELECT SIGN(100.22) X1, SIGN(-100.22) X2, SIGN(0) X3

OD DUALNOG

Zanimljiva značajka ove funkcije je mogućnost prijenosa m jednaka nuli ovo ne uzrokuje pogrešku dijeljenja s 0.

Funkcija POWER(n, m)

Funkcija POWER podiže broj n na potenciju m. Stupanj može biti frakcijski i negativan, što značajno proširuje mogućnosti ove funkcije.

ODABERITE SNAGU (10, 2) X1, SNAGU (100, 1/2) X2,

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

OD DUALNOG

U nekim slučajevima, pozivanje ove funkcije može uzrokovati iznimna situacija. Na primjer:

ODABERITE SNAGU (-100, 1/2) X2

OD DUALNOG

U u ovom slučaju pokušava se izračunati kvadratni korijen negativnog broja, što će generirati pogrešku ORA-01428 "Argument izvan raspona".

Funkcija SQRT(n)

Ova funkcija vraća Korijen od broja n. Na primjer:

SELECT SQRT(100) X

OD DUALNOG

EXP(n) i LN(n) funkcije

Funkcija EXP podiže e na potenciju n, a funkcija LN izračunava prirodni logaritam od n (n mora biti veći od nule). Primjer:

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

Bliže se izbori, što znači da ćemo danas birati guvernera. Ili bolje rečeno, postaviti barijeru kandidatima za prolaz u sljedeći krug. Ali prvo moramo definirati pojmove.

Što je zajamčeni odabir u SQL-u? Pretpostavimo da uvjet upita za tablicu uspoređuje polje s varijablom. Ovisno o vrijednosti ove varijable, upit može ili ne mora vratiti retke iz tablice. Ako vrijednost varijable ispadne tako da se retci iz tablice ne vraćaju, tada se za ovaj slučaj mora posebno generirati unaprijed određeni lijevi rezultat. Odnosno, u svakom slučaju treba zajamčiti opći zahtjev da se nešto vrati. Sam termin je preuzet odavde. Međutim, zadatak je kompliciran činjenicom da umjesto jedne jednostavne ćelije s vrijednošću, trebamo jamčiti vraćanje punopravnog niza.

Iznosim podatke Središnjeg izbornog povjerenstva. Ovim rezultatima završio je prvi krug glasovanja

stvoriti tablicu izbor kao s t (id, ime, profesija, glasovi) kao (odaberite 1, "Nepotkupljivi amoral Chistorukovich", "tužitelj", 9867 iz dvojne unije sve odaberite 2, "Efektivni proračun Osvoilovich", "biznismen", 8650 od dvojna unija sve odaberite 3, "Istiniti Chtodat Napisailovich", "urednik novina", 745 iz dvojne unije sve odaberite 4, "Prekrasna Lucifera Feoktistovna", "Opatica", 234 iz dvojne unije sve odaberite 5, "Khrensgory Ktotakoy Niktogneznaevich", " učitelj seoske škole", 3 iz duala) odabir * iz t; alter stol izbor add Osnovni ključ(iskaznica);
Inicijalni upit koji određuje hoće li kandidati proći u sljedeći krug iznimno je jednostavan:
odaberite * iz izbora gdje glasovi > :vezani
Pretpostavimo da je ulazna barijera 8000 glasova. Kombinirajući ovaj broj s:bound, dobivamo

Ali što ako je prag za ulazak 10.000, dakle više od maksimalnog broja glasova? Tada je očito da niti jedan od navedenih kandidata ne prolazi u sljedeći krug. U tom slučaju se uspostavlja diktatura i mačak pukovnik automatski postaje guverner regije. Evo nekih od načina na koje se može koristiti:

Metoda 1. UNION SVE tablice sa samoagregiranim

S t kao (odaberite * iz izbora gdje su glasovi > :vezani) odaberite id, ime, profesiju, glasove iz t sindikata sve odaberite 0, "Pukovnik", "mačka", null od t imajući broj (*) = 0 poredak po glasovima desc

Metoda 2. UNION SVE tablice s DUAL
s t kao (odaberite * iz izbora gdje glasovi > :vezani) odaberite ID, ime, profesiju, glasove iz t sindikata sve odaberite 0, "Pukovnik", "mačka", null iz dvojnog gdje ne postoji (odaberite null iz t) poredak po glasovima desc

Metoda 3. LEFT JOIN tablice s DUAL
odaberite nvl(e.id, 0) id, nvl2(e.id, e.name, "Pukovnik") ime, nvl2(e.id, e.profession, "cat") zanimanje, e.votes iz dvojnog d lijevo pridružite se izborima e na e.glasovi >
za slučajeve kada tablica nema jedinstveno polje NOT NULL
odaberite nvl2(e.rowid, e.id, 0) id, nvl2(e.rowid, e.name, "Pukovnik") ime, nvl2(e.rowid, e.profession, "cat") zanimanje, e.votes od dvojnog d lijevo spoji izbore e na e.votes > :bound poredak po e.votes desc

Metoda 4. Model s mačkom.
odaberite ID, ime, profesiju, glasove s izbora gdje su glasovi > :vezana dimenzija modela po (rownum rn) mjere (id, ime, profesija, glasovi) pravila (ime = nvl2(id, ime, "Pukovnik"), profesija = nvl2 (id, zanimanje, "mačka"), id = nvl(id, 0)) poredak po glasovima desc

Ispod zajamčeni rezultat kod vezivanja s previsokom barijerom od 10.000

Jasno je da ako početnu traku postavite na 8000, tada će i ovi upiti raditi ispravno.

To je sve za sada. Sva imena likova odgovaraju pravi ljudi smatrati slučajnim.

Za rješenja nekih drugih uobičajenih SQL problema, pogledajte

TO_CHAR funkcija s brojevima

Funkcije za pretvaranje podataka u druge vrste podataka. TO_CHAR(broj) pretvara broj u tekst. TO_NUMBER(niz) pretvara tekst u broj.

SELECT TO_CHAR (123) FROM DUAL će vratiti niz 123, SELECT TO_NUMBER (`12345") FROM DUAL će vratiti broj 12345.

Laboratorijski rad. Promjena formata izlaznog broja

Promjene u formatu numeričkih vrijednosti u Oracle SQL, funkcija TO_CHAR za rad s numeričkim vrijednostima.

Vježba:

Napišite upit koji bi prikazao podatke o imenu, prezimenu i plaći zaposlenika iz tablice hr.employees u formatu prikazanom na sl. 3.4-1:

Riža. 3.4 -1

Podatke u tom slučaju treba poredati na način da se prvo ispisuju redovi za zaposlenike s najvećom plaćom.

Bilješka:

Neke vrijednosti plaća na sl. 3.4-1 su modificirani, tako da možda nisu isti kao vaše vrijednosti.

Riješenje:

SELECT first_name AS "First Name", last_name As "Last Name", TO_CHAR (SALARY, "L999999999.99") Kao "Plaća" FROM hr.employees ORDER BY SALARY DESC.

Funkcije TO_NUMBER i TO_DATE

Funkcija za pretvaranje niza u datum TO_DATE (string, format). Moguće vrijednosti formati su već spomenuti gore, pa ću dati nekoliko primjera korištenja ove funkcije. Primjeri:

IZABERI TO_DATE("1.1.2010.", `DD.MM.GGGG") IZ DUAL će vratiti datum `01/01/2010";

IZABERI TO_DATE("01.SIJEČ.2010", `DD.MON.GGGG") IZ DUAL će vratiti datum `01/01/2009";

IZABERI TO_DATE("15-01-10", `DD-MM-GG") IZ DUAL će vratiti datum `01/15/2010".

Funkcija u koju se pretvara niz numerička vrijednost TO_NUMBER (niz, format). Najčešće vrijednosti formata navedene su u tablici, pa pogledajmo korištenje ove funkcije pomoću primjera. Primjeri:

IZABERI TO_NUMBER('100") IZ DUAL će vratiti broj 100 SELECT TO_NUMBER (`0010.01", "9999D99") IZ DUAL će vratiti broj 10.01;

IZABERI TO_NUMBER("500.000", "999G999") IZ DUAL će vratiti broj 500000.

RR element u formatu datuma

Element formata datuma i vremena RR sličan je elementu formata datuma i vremena YY, ali pruža dodatnu fleksibilnost za pohranjivanje vrijednosti datuma u drugim stoljećima. Element formata datuma i vremena RR omogućuje pohranjivanje datuma iz 20. stoljeća u 21. stoljeće navođenjem samo posljednje dvije znamenke godine.

Ako su zadnje dvije znamenke tekuće godine od 00 do 49, tada vraćena godina ima iste prve dvije znamenke kao i tekuća godina.

Ako su posljednje dvije znamenke tekuće godine od 50 do 99, tada su prve 2 znamenke vraćene godine za 1 veće od prve 2 znamenke tekuće godine.

Ako su posljednje dvije znamenke tekuće godine od 00 do 49, tada su prve 2 znamenke vraćene godine 1 manje od prve 2 znamenke tekuće godine.

Ako su zadnje dvije znamenke tekuće godine od 50 do 99, tada vraćena godina ima iste prve dvije znamenke kao i tekuća godina.

NVL funkcija

Općenito se najčešće koristi funkcija NVL. Funkcija prima dva parametra: NVL (expr1, expr2). Ako prvi parametar expr1 nije NULL, tada funkcija vraća njegovu vrijednost. Ako je prvi parametar NULL, tada funkcija umjesto toga vraća vrijednost drugog parametra expr2.

Primjer: Odaberite NVL (grad_dobavljača, n/a") od dobavljača:

Gornja SQL izjava vratit će n/" ako je sadržano polje dobavljač_grad nulta vrijednost. Inače će vratiti vrijednost dobavljač_grad.

Drugi primjer korištenja NVL funkcije u Oracle/PLSQL je:

odaberite id_dobavljača, NVL (desc_dobavljača, naziv_dobavljača) od dobavljača.

Ova SQL izjava će se vratiti ime dobavljača polje ako dobavljač_desc sadrži nultu vrijednost. Inače će se vratiti dobavljač_desc.

Zadnji primjer: Korištenje NVL funkcije u Oracle/PLSQL je: odaberite NVL(provizija, 0) od prodaje;

Ova SQL izjava vratila je vrijednost 0 if provizija Polje sadrži nultu vrijednost. Inače bi bilo vraćeno provizije polje.

NVL pretvorbe za različite vrste podataka

Za pretvaranje nulte vrijednosti u stvarnu vrijednost upotrijebite funkciju NVL: NVL ( izraz1, izraz2), Gdje:

izraz1- Izvorna ili izračunata vrijednost, koja može biti nedefinirana.

izraz2- Vrijednost koja je zamijenjena za nedefiniranu vrijednost.

Bilješka: Funkcija NVL može se koristiti za pretvaranje bilo kojeg tipa podataka, ali rezultat će uvijek biti isti tip kao izraz1.

NVL konverzija za različite vrste:

BROJ - NVL (brojčani stupac, 9).

CHAR ili VARCHAR2 - NVL (znakovi|stupac,"Nije dostupno").

Laboratorijski rad. Korištenje funkcije NVL

NVL funkcija za rad s null vrijednostima u Oracle SQL.

Vježba:

Napišite upit koji prikazuje podatke o imenu i prezimenu zaposlenika iz tablice hr.employees., kao i stopu provizije (stupac COMMISSION_PCT) za zaposlenika. U tom slučaju za one djelatnike za koje provizija nije definirana mora biti prikazana vrijednost 0. Rezultat zahtjeva trebao bi biti kao što je prikazano na sl. 3.5-1.

Riža. 3.5 -1 (vrijednosti prikazane počevši od retka 51)

Riješenje:

Odgovarajući kod zahtjeva mogao bi biti ovakav:

SELECT first_name KAO "Ime", last_name Kao "Prezime", NVL (COMMISSION_PCT, 0) Kao "Stopa provizije" FROM hr.employees.