NVL konverzije za različite tipove podataka.

Prilikom sastavljanja sql upita često se javlja situacija kada je prilikom dohvaćanja podataka potrebno uporediti vrijednosti nekoliko stupaca i prikazati onu koja sadrži podatke (nije prazna). Izraz Coalesce savršeno rješava ovaj problem. U ovom članku ćete pronaći puni opis Objedinite sql izraze, detalje upotrebe i primjere.

Izlaz nepraznih vrijednosti pomoću Coalesce. Posebnosti

Razmotrite karakteristike upotrebe Sql Coalesce:

1. Omogućava navođenje bilo kojeg broja argumenata (za razliku od Isnull / Nvl / Nvl2, koji imaju ograničen broj argumenata).
2. Može uzeti podupite kao argument.
3. Vraća rezultat jednak prvi vrijednost koja nije nula, ili Null ako nije pronađena vrijednost koja nije nula.
4. Sql Coalesce se može koristiti u klauzuli Select za odabir neprazne vrijednosti, kao i u Gdje da se razjasni da skup stupaca sa prazne vrijednosti nije dozvoljeno (/ dozvoljeno).
5. Ovaj izraz je isti kao i primjena izraza Case, koji testira svaki argument uzastopno u odnosu na uvjet Kada argument1 nije null onda argument1. U osnovi, Coalesce je prečica za jednostavnu upotrebu, au mnogim DBMS-ovima optimizatori upita prepisuju izraz Coalesce u Case.
6. Sql Coalesce funkcije su dostupne u svim vodećim relacijskim

Sintaksa spajanja

Svako ko je ikada koristio Coalesce prilikom pisanja sql upita zna da je sintaksa ovog izraza krajnje jednostavno. Dosta unutra zagrade navedite argumente testirane za Null, odvojene zarezima. Ako pretpostavimo da su argumenti nazvani arg1, arg2, ... argN, tada će sintaksa za Coalesce biti sljedeća:

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

Hajde da pripremimo nekoliko tabela za proučavanje mehanizma ovog izraza.

Priprema stolova

Za bolje razumijevanje Coalesce sql opis, napravimo dvije tabele u bazi podataka koje sadrže informacije o objektima nekretnina.

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

Neka druga tabela Basic_characteristic sadrži informacije o glavnim karakteristikama svojstva - Proširenje, Dubina, Površina, Obim, Visina.

Ispitali smo sintaksu Coalesce sql-a, opis, karakteristike upotrebe i idemo direktno na primjere.

Primjeri korištenja

Sintaksa za izraz Coalesce je izuzetno jednostavna, ali je važno zapamtiti da će naredba vratiti PRVU nepraznu vrijednost pronađenu na listi argumenata. Ova primjedba ima vrlo veliki značaj stoga, argumenti u izrazu moraju biti poredani po važnosti. Najlakši način da shvatite princip je iz tabele područja. Napravite upit koji bira naziv svojstva, kao i vrijednost područja:

I dobijte rezultat:

Za objekte „Objekat 1“, „Zemljišna parcela 1“ i „Izgradnja 2“ popunjene su obe vrednosti površine, ali je površina određena kao prioritet, pošto smo je prvo naznačili u listi argumenata. Izraz Coalesce pronašao je prvu nepraznu vrijednost i izbacio je, zaustavljajući daljnje skeniranje argumenata. Ova konstrukcija zahtjev je ispravan, jer je navedena oblast određenija od deklarirane. Da smo naveli deklarirano područje kao prvi argument, onda kada se ovo polje tabele popuni, ono bi imalo prioritet.

Osim što se koristi u Select, vrlo često se koristi izraz Coalesce sa gde uslov... Omogućava vam da odsiječete iz rezultata one redove u kojima su vrijednosti liste polja prazne (ili obrnuto, u rezultat uključite samo one vrijednosti u kojima je lista polja prazna). Ova situacija javlja se svuda: na primjer, u preduzeću, prilikom registracije novog zaposlenog, u bazu podataka unose se samo osnovni podaci o njemu, a popunjavanje detaljne informacije ostavio "za kasnije". Postepeno se pojavljuju "praznine" - ili prije provjere, ili prilikom slanja zaposlenika na godišnji odmor / poslovni put / bolovanje.

Odaberimo iz tabele sa glavnim karakteristikama objekte nekretnina za koje nije popunjena nijedna od karakterističnih vrednosti:

Nadamo se da vam je naš detaljan Coalesce sql opis pomogao da razumete sve karakteristike korišćenja ovog izraza, kao i da se nosite sa važnim nijansama.

NVL funkcija

NVL funkcija je obično najčešće korištena. Funkcija uzima 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.

Razmislite praktični primjer... COMM polje u EMP tabeli može sadržavati null vrijednosti... Prilikom izvršavanja upita kao što je:

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

OD SCOTT.EMP

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

CEIL (n) funkcija

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

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

OD DUAL

TRUNC funkcija (n [, m])

TRUNC vraća broj n, skraćen na m decimalnih mjesta. Parametar m se može izostaviti - u ovom slučaju, n se skraćuje na cijeli broj.

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

TRUNC (100.25678, 2) X4

OD DUAL

SIGN (n) funkcija

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

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

OD DUAL

Zanimljiva karakteristika ove funkcije je mogućnost prijenosa m jednak nuli- nema greške dijeljenja sa 0.

POWER (n, m) funkcija

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

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

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

OD DUAL

U nekim slučajevima, kada pozivate ovu funkciju, možete primiti izuzetna situacija... Na primjer:

ODABIR SNAGE (-100, 1/2) X2

OD DUAL

V u ovom slučaju pokušava se izračunati kvadratni korijen negativnog broja, što će rezultirati greškom ORA-01428 "Argument izvan opsega".

SQRT (n) funkcija

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

ODABIR SQRT (100) X

OD DUAL

EXP (n) i LN (n) funkcije

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

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

Izbori su na putu, što znači da ćemo danas birati guvernera. Ili bolje rečeno, postaviti barijeru za prolaz kandidata u naredni krug. Ali prvo morate definirati pojmove.

Šta je zagarantovani izbor u SQL-u? Pretpostavimo da se u uslovu upita za tabelu pravi poređenje između polja i varijable. Ovisno o vrijednosti ove varijable, upit može ili ne mora vratiti redove iz tabele. Ako vrijednost varijable ispadne da se redovi iz tablice ne vrate, tada je za ovaj slučaj potrebno posebno generirati unaprijed određen lijevi rezultat. To jest, u svakom slučaju, generalni zahtjev mora biti zagarantovan da se nešto vrati. Sam termin je preuzet odavde. Međutim, zadatak je kompliciran činjenicom (a možda je i obrnuto, pojednostavljen) da umjesto jedne jednostavne ćelije s vrijednošću, moramo biti zajamčeni da vratimo punopravni string.

Navodim podatke Centralne izborne komisije. Prvi krug glasanja završen je sljedećim rezultatima

kreirajte tabelarni izbor kao sa t (id, ime, profesija, glasovi) kao (odaberite 1, "Nepotkupljivi amoral Čistorukovič", "tužilac", 9867 iz dvojne unije svi izaberite 2, "Efektivni budžet Osvoilović", "biznismen", 8650 iz dvojna zajednica svi odabrani 3, "Pravi Chtodadut Wiszajlovich", "urednik novina", 745 iz dvojne unije svi odabrani 4, "Milostiva Lucifera Feoktistovna", "igumanija", 234 iz dvojne unije svi odabrani 5, "Khrensgory Ktotakoyich" Niktoneznana , "učitelj seoske škole", 3 iz dualnog) odaberite * iz t; alter table izbori dodaj primarni ključ(id);
Početni upit koji određuje ulazak kandidata u sljedeći krug je izuzetno jednostavan:
odaberite * iz izbora gdje su glasovi>: vezani
Pretpostavimo da je prag za prolaz 8000 glasova. Kombinacijom ovog broja sa: vezano, dobijamo

Ali šta ako je prag za prolaz 10.000, što je više od maksimalnog broja glasova? Tada je očigledno da se nijedan od navedenih kandidata neće plasirati u naredni krug. U ovom slučaju se uspostavlja diktatura i mačka pukovnik automatski postaje guverner regije. Evo nekih od načina da ga dodijelite:

Metoda 1. UNION SVE tabele sa samoagregiranim

Sa t as (odaberite * iz izbora gdje su glasovi>: vezani) odaberite ID, ime, profesiju, glasove iz sindikata t, sve odaberite 0, "pukovnik", "mačka", null od t koji ima brojanje (*) = 0 redoslijed glasova desc

Metoda 2... UNION ALL stolovi sa DUAL
sa t kao (odaberi * iz izbora gdje glasovi>: vezani) 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 tabele sa DUAL
odaberite nvl (e.id, 0) id, nvl2 (e.id, e.name, "pukovnik") ime, nvl2 (e.id, e.profession, "cat") profesiju, e.votes iz dual d lijevog. pridruži se izborima e na e.glasovima>
za slučajeve kada tabela nema jedinstveno NOT NULL polje
odaberite nvl2 (e.rowid, e.id, 0) id, nvl2 (e.rowid, e.name, "pukovnik") ime, nvl2 (e.rowid, e.profession, "mačka") profesiju, e.votes od dual d lijevo pridruži se izborima e na e.votes>: vezani redoslijed prema e.votes desc

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

Ispod zagarantovan rezultat sa vezanjem sa prevelikom barijerom od 10.000

Jasno je da ako postavite početni nivo od 8000, onda će i ovi upiti raditi ispravno.

To je sve za sada. Ime svih znakova se podudara sa pravi ljudi smatra se slučajnim.

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

TO_CHAR funkcija s brojevima

Funkcije za pretvaranje podataka u druge tipove 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 12345.

Laboratorijski rad. Promjena formata prikazanih brojeva

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

vježba:

Napišite upit koji prikazuje ime, prezime i podatke o plaći za zaposlenike iz tabele hr.employees, u formatu prikazanom na slici 1. 3.4-1:

Rice. 3.4 -1

U tom slučaju podatke treba sortirati na način da se prvi redovi prikazuju za zaposlene sa najvećom platom.

Bilješka:

Neke vrijednosti plata na sl. 3.4-1 su se promijenili, tako da možda neće odgovarati vašim vrijednostima.

Rješenje:

SELECT ime_ime KAO "Ime", prezime Kao "Prezime", TO_CHAR (PLATA, "L999999999.99") Kao "Plata" OD hr.zaposlenika RED PO OPIS PLATE.

TO_NUMBER i TO_DATE funkcije

Funkcija za pretvaranje stringa u datum TO_DATE (string, format). Moguće vrijednosti o formatima je već bilo riječi gore, pa ću dati nekoliko primjera korištenja ove funkcije. primjeri:

SELECT DO_DATE ("01.01.2010", `DD.MM.GGGG") OD DUAL će vratiti datum `01.01.2010";

SELECT DO_DATE ("01.JAN.2010", `DD.MON.YYYY") OD DUAL će vratiti datum `01.01.2009";

SELECT DO_DATE ("15-01-10", `DD-MM-GG") OD DUAL će vratiti datum `01/15/2010".

Funkcija konverzije niza u niz numerička vrijednost TO_NUMBER (niz, format). Najčešće vrijednosti formata navedene su u tabeli, pa pogledajmo korištenje ove funkcije na primjerima. primjeri:

SELECT TO_NUMBER (`100") OD DUAL će vratiti broj 100 SELECT TO_NUMBER (`0010.01", "9999D99") OD DUAL će se vratiti 10.01;

SELECT TO_NUMBER ("500,000", "999G999") OD DUAL će vratiti 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ćava vam da pohranite datume od 20. vijeka do 21. stoljeća tako što ćete specificirati samo posljednje dvije cifre godine.

Ako su posljednje 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 cifre tekuće godine između 50 i 99, tada su prve 2 cifre vraćene godine za 1 veće od prve 2 znamenke tekuće godine.

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

Ako su posljednje 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 obično najčešće korištena. Funkcija uzima dva parametra: NVL (expr1, exp2). 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 exp2.

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

Gornji SQL izraz će vratiti n / "ako je sadržano polje supplier_city nula vrijednost... U suprotnom će vratiti vrijednost supplier_city.

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

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

Ovaj SQL izraz će se vratiti Dobavljač Ime polje if dobavljač_desc sadrži null vrijednost. U suprotnom će se vratiti dobavljač_desc.

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

Ovaj SQL izraz vratio je 0 if provizija polje sadrži nultu vrijednost. U suprotnom bi se vratilo provizija polje.

NVL konverzije za različite tipove podataka

NVL funkcija se koristi za pretvaranje nedefinirane vrijednosti u stvarnu vrijednost: NVL ( izraz1, izraz 2), gdje:

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

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

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

Pretvori NVL za različite vrste:

BROJ - NVL (numerička kolona, 9).

CHAR ili VARCHAR2 - NVL (simboli | kolona,"Nije dostupno").

Laboratorijski rad. Primjena NVL funkcije

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

vježba:

Napišite upit koji prikazuje podatke o imenima i prezimenima zaposlenih iz hr.zaposlenih Tabela, kao i stopu provizije (stupac COMMISSION_PCT) za zaposlenog. U ovom slučaju, za one zaposlene za koje nije definisana provizija, potrebno je prikazati vrijednost 0. Rezultat upita bi trebao biti isti kao što je prikazano na sl. 3.5-1.

Rice. 3.5 -1 (prikazuje vrijednosti počevši od 51. reda)

Rješenje:

Odgovarajući kod zahtjeva može biti ovakav:

SELECT first_name KAO “Ime”, prezime Kao “Prezime”, NVL (COMMISSION_PCT, 0) Kao “Stopa provizije” OD hr.zaposlenih.