NVL transformacije za različite vrste podataka. Izrazi grananja u naredbi SELECT

NVL funkcija

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

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

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

IZ SCOTT.EMP

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

CEIL(n) funkcija

Funkcija CEIL vraća najmanji cijeli broj veći ili jednak broju n proslijeđenom kao parametru. Na primjer:

ODABIR CEIL(100) X1, CEIL(-100) X2, CEIL(100,2) X3, CEIL(-100,2) X4

IZ DUAL

TRUNC(n[,m])

Funkcija TRUNC vraća broj n skraćen na m decimalnih mjesta. Parametar m može biti izostavljen, u kojem slučaju se n skraćuje na cijeli broj.

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

TRUNC(100.25678, 2) X4

IZ DUAL

ZNAK(n) funkcija

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

ODABIR ZNAK(100,22) X1, ZNAK(-100,22) X2, ZNAK(0) X3

IZ DUAL

Zanimljiva značajka ove funkcije je mogućnost prijenosa m jednaka nuli To ne rezultira pogreškom dijeljenja s 0.

POWER(n, m) funkcija

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

ODABIR NAPAJANJE (10, 2) X1, NAPAJANJE (100, 1/2) X2,

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

IZ DUAL

U nekim slučajevima, kada pozivate ovu funkciju, možete naići iznimka. Na primjer:

ODABIR SNAGE (-100, 1/2) X2

IZ DUAL

V ovaj slučaj pokušava se izračunati kvadratni korijen negativnog broja, što će rezultirati pogreškom ORA-01428 "Argument izvan raspona".

Funkcija SQRT(n)

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

ODABIR SQRT(100) X

IZ DUAL

EXP(n) i LN(n) funkcije

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

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

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 vratit će niz 123, SELECT TO_NUMBER (`12345") FROM DUAL će vratiti broj 12345.

Laboratorijski rad. Promjena formata izlaznih brojeva

Promjene numeričkog formata u Oracle SQL-u, funkcija TO_CHAR za rad s brojčanim vrijednostima.

Vježba:

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

Riža. 3.4 -1

Pritom podatke treba sortirati na način da se prvi prikazuju retki za zaposlenike s najvećom plaćom.

Bilješka:

Neke vrijednosti plaća na sl. 3.4-1 su promijenjeni tako da možda neće odgovarati vašim vrijednostima.

Riješenje:

SELECT first_name AS "Ime", prezime Kao "Prezime", TO_CHAR(PALA, "L999999999.99") Kao "Plaća" OD hr.zaposlenika RED PO OPIS PLAĆA.

TO_NUMBER i TO_DATE funkcije

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

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

IZABERI DO_DATE("01.JAN.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 za pretvaranje niza u brojčana vrijednost TO_NUMBER(niz, format). Najčešće vrijednosti formata navedene su u tablici, pa razmotrimo korištenje ove funkcije na primjerima. 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 vam pohranjivanje datuma iz 20. stoljeća u 21. stoljeće navodeći 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 zadnje dvije znamenke tekuće godine između 50 i 99, tada su prve 2 znamenke vraćene godine za 1 veće od prve 2 znamenke tekuće godine.

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

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

NVL funkcija

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

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

Gornji SQL izraz vratit će n/" ako sadrži polje supplier_city nultu vrijednost. U suprotnom će vratiti vrijednost supplier_city.

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

odaberite dobavljač_id, NVL (supplier_desc, supplier_name) od dobavljača.

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

Posljednji primjer: korištenje funkcije NVL u Oracle/PLSQL je: odaberite NVL(provizija, 0) iz prodaje;

Ovaj SQL izraz vratio je vrijednost 0 if provizija polje sadrži nultu vrijednost. Inače bi se vratilo provizije polje.

NVL transformacije za različite vrste podataka

Za pretvaranje nedefinirane vrijednosti u stvarnu vrijednost, koristite funkciju NVL: NVL ( izraz1, izraz2), gdje:

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

izraz2- Vrijednost koja je zamijenjena nultom vrijednošću.

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

NVL konverzija za različite vrste:

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

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

Laboratorijski rad. Primjena NVL funkcije

NVL funkcija za rad s nultim vrijednostima u Oracle SQL-u.

Vježba:

Napišite upit koji prikazuje podatke o imenima i prezimenima zaposlenika iz tablice hr.employees., kao i stopu provizije (stupac COMMISSION_PCT) za zaposlenika. U tom slučaju, za one zaposlenike za koje provizija nije definirana, trebate prikazati vrijednost 0. Rezultat upita trebao bi biti isti 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 izgledati ovako:

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

Izbori su na putu, što znači da ćemo danas birati guvernera. Točnije, postaviti barijeru kandidatima za prolaz u sljedeći krug. Ali prvo moramo definirati pojmove.

Što je zajamčeni odabir u SQL-u? Recimo da se u uvjetu upita prema tablici vrši usporedba nekog polja s nekom varijablom. Ovisno o vrijednosti ove varijable, upit može ili ne mora vratiti retke iz tablice. Ako takva vrijednost varijable ispadne da se retki iz tablice ne vrate, tada je za ovaj slučaj potrebno posebno generirati unaprijed definirani lijevi rezultat. To jest, u svakom slučaju, opći upit mora biti zajamčen da će nešto vratiti. Sam izraz je preuzet odavde. Međutim, zadatak je kompliciran činjenicom (a možda je i obrnuto, pojednostavljeno) da umjesto jedne jednostavne ćelije s vrijednošću trebamo vratiti punopravni niz s jamstvom.

Donosim podatke Središnjeg izbornog povjerenstva. Prvi krug glasovanja završio je sljedećim rezultatima

kreirajte stolne izbore kao s t (id, ime, profesija, glasovi) kao (odaberite 1, "Nepotkupljivi Amoral Chistorukovich", "tužilac", 9867 iz dvojne unije svi odaberite 2, "Učinkoviti proračun Osvoilovich", "businessman", 8650 iz dvojni sindikat svi odabir 3, "Istiniti Chtodadut Napisailovich", "urednik novina", 745 iz dvojnog sindikata svi odabir 4, "zgodna Lucifera Feoktistovna", "svećenik", 234 iz dvojne unije svi odabir 5, "Hrensgory Tko je ovo Niktonesnaevich", "učitelj seoske škole", 3 od dual) odaberite * od t; alter table izbori add glavni ključ(iskaznica);
Početni upit koji određuje kandidate za sljedeći krug iznimno je jednostavan:
odaberite * iz izbora gdje glasovi > :vezani
Pretpostavimo da je prepreka za ulazak 8000 glasova. Vezanjem ovog broja sa :bound, dobivamo

Ali što ako je prag 10.000, što je više od maksimalnog broja glasova? Tada je očito da nitko od gore navedenih kandidata ne prolazi u sljedeći krug. U tom slučaju uspostavlja se diktatura i mačka pukovnik automatski postaje guverner regije. Evo nekih od načina na koje se dodjeljuje:

Metoda 1. UNION ALL tablice s agregiranim self

Uz t kao (odaberite * iz izbora gdje glasovi > :vezani) odaberite id, ime, profesiju, glasove iz sindikata t, sve odaberite 0, "pukovnik", "mačka", null od t koji ima broj (*) = 0 redoslijed glasova desc

Metoda 2. UNION ALL stolovi s DUAL
s t kao (odaberi * iz izbora gdje glasovi > :vezano) odaberite id, ime, profesiju, glasove iz sindikata t sve odaberite 0, "pukovnik", "mačka", null iz dual gdje ne postoji (odaberite null iz t) red prema 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") profesiju, e.glass from dual d left. pridruži se izborima e na e.glasovi >
za slučajeve kada u tablici ne postoji jedinstveno polje NOT NULL
odaberite nvl2(e.rowid, e.id, 0) id, nvl2(e.rowid, e.name, "pukovnik") ime, nvl2(e.rowid, e.profession, "mačka") zanimanje, e.votes od dual d lijevo pridruži se izborima e na e.votes > :vezan redoslijed prema e.votes desc

Metoda 4. Model s mačkom.
odaberite id, ime, profesiju, glasove s izbora gdje glasovi > :vezana dimenzija modela prema (rownum rn) mjerama (id, naziv, profesija, glasovi) pravilima (ime = nvl2(id, ime, "pukovnik"), profesija = nvl2 (id, profesija, "mačka"), id = nvl(id, 0)) poredak po glasovima desc

Ispod zajamčeni rezultat s vezanjem s prevelikom barijerom 10.000

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

To je sve za sada. Sva podudaranja imena znakova sa pravi ljudi smatrati slučajnim.

Rješenje nekih drugih tipičnih SQL zadataka može se vidjeti

Prilikom sastavljanja sql upita često se javlja situacija kada je prilikom dohvaćanja podataka potrebno usporediti vrijednosti nekoliko stupaca i prikazati onaj koji sadrži podatke (ne prazan). Taj je zadatak savršeno riješen izrazom Spajati se. U ovom članku ćete pronaći kompletan opis Objedinite sql izraze, pojedinosti o upotrebi i primjere.

Prikaz nepraznih vrijednosti s Coalesce. Osobitosti

Razmotrite značajke upotrebe Sql Coalesce:

1. Dopušta bilo koji broj argumenata (za razliku od Isnull/Nvl/Nvl2, koji imaju ograničen broj argumenata).
2. Može prihvatiti potupite kao argumente.
3. Vraća rezultat jednak prvi vrijednost različita od Null ili Null ako nije nulte vrijednosti neće se naći.
4. Sql Coalesce se može koristiti u klauzuli Select za odabir neprazne vrijednosti, a također i u Where za navođenje da skup stupaca sa prazne vrijednosti nije dopušteno (/dopušteno).
5. Ovaj izraz je ekvivalentan primjeni izraza Case, koji provjerava svaki argument u nizu za uvjet Kada argument1 nije null onda argument1. U biti, Coalesce je "prečac" stvoren za jednostavnu upotrebu, au mnogim DBMS optimizatorima upita prepisuje izraz Coalesce s Case.
6. Funkcije Sql Coalesce dostupne su u svim vodećim relacijskim

Sintaksa

Svi koji su ikada koristili Coalesce pri sastavljanju sql upita znaju da je sintaksa dati izraz krajnje jednostavno. Dosta unutra zagrade navedite argumente koje treba provjeriti za Null odvojene zarezima. Ako pretpostavimo da argumenti imaju imena arg1, arg2, ... argN, tada će sintaksa Coalesce biti sljedeća:

Spajati (arg1, arg2, ... argN).

Pripremimo nekoliko tablica za proučavanje mehanizma ovog izraza.

Priprema stola

Za bolje razumijevanje Coalesce sql opis, izradit ćemo dvije tablice u bazi podataka koje sadrže podatke o objektima nekretnina.

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

Neka druga tablica Basic_characteristic sadrži informacije o glavnoj karakteristici svojstva - dužina (Extension), dubina (Depth), površina (Area), volumen (Scope), visina (Height).

Pregledali smo sintaksu Coalesce sql-a, opis, značajke korištenja i išli izravno na primjere.

Primjeri korištenja

Sintaksa izraza Coalesce iznimno je jednostavna, ali je važno zapamtiti da će rezultat izvršenja naredbe biti PRVA neprazna vrijednost pronađena s popisa argumenata. Ova primjedba je vrlo veliku važnost, pa se argumenti u izrazu moraju poredati prema njihovoj važnosti. Najlakši način za razumijevanje principa je iz tablice područja. Napravite upit koji odabire naziv svojstva, kao i vrijednost područja:

I dobiti rezultat:

Za objekte "Zgrada 1", "Zemljište 1" i "Građevina 2" popunjene su obje vrijednosti površine, ali se pokazalo da je navedeno područje prioritet, budući da smo ga prvo naveli na listi argumenata . Izraz Coalesce pronašao je prvu nepraznu vrijednost i ispisao je, prestajući gledati sve daljnje argumente. Ova konstrukcija zahtjev je točan, jer je navedeno područje specifičnije od deklariranog. Da smo kao prvi argument naveli deklarirano područje, tada bi, ako je ovo polje tablice popunjeno, ono imalo prioritet.

Osim što se koristi u Select, vrlo se često koristi izraz Coalesce s gdje stanje. Omogućuje vam da iz rezultata odsiječete one retke u kojima su vrijednosti popisa polja prazne (ili obrnuto, da u rezultat uključite samo one vrijednosti u kojima je popis polja prazan). Ova situacija javlja se posvuda: na primjer, u poduzeću, prilikom registracije novog zaposlenika, u bazu su uneseni samo osnovni podaci o njemu, a popunjavanje detaljne informacije ostavio za kasnije. Postupno se pojavljuju "praznine" - ili prije provjere, ili prilikom slanja zaposlenika na godišnji odmor / poslovni put / bolovanje.

Odaberimo objekte nekretnina iz tablice s glavnim karakteristikama, za koje nije popunjena nijedna od karakterističnih vrijednosti:

Nadamo se da vam je naš detaljni opis Coalesce sql pomogao razumjeti sve značajke korištenja ovog izraza, kao i da se nosite s važnim nijansama.