Izrazi grananja u naredbi SELECT. Oracle, tipični SQL zadaci

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, 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 je vrijednost generirana 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 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(n[,m])

Funkcija TRUNC vraća broj n skraćen na m decimalnih mjesta. Parametar m može biti izostavljen, u kom slučaju se n 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 pozitivan, tada funkcija vraća 1. Ako je negativan, vraća se -1. Ako je nula, 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 Ovo ne rezultira greškom dijeljenja s 0.

POWER(n, m) funkcija

Funkcija POWER podiže broj n na stepen m. Stepen 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

OD DUAL

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

ODABIR SNAGE(-100, 1/2) X2

OD DUAL

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

Funkcija SQRT(n)

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 (pri čemu n mora biti veće od nule). primjer:

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

Nešto ranije smo se dotakli ugniježđenih funkcija, a sada ih pogledajmo malo detaljnije. Također ćemo pogledati funkcije za rad sa NULL vrijednošću i funkcije koje pomažu u implementaciji operacije grananja u upitu.

ugniježđene funkcije

Ugniježđene funkcije koriste povratnu vrijednost jedne funkcije kao ulazni parametar za drugu funkciju. Funkcije uvijek vraćaju samo jednu vrijednost. Stoga možete tretirati rezultat poziva funkcije kao literalnu vrijednost kada ga koristite kao parametar za drugi poziv funkcije. Funkcije reda mogu biti ugniježđene do bilo kojeg nivoa ugniježđenja. Jedan poziv funkcije izgleda ovako

Funkcija1(parametar1, parametar2, …) = rezultat

Zamjena parametra funkcije pozivom druge funkcije može rezultirati izrazima poput

F1(param1.1, F2(param2.1, param2.2, F3(param3.1)), param1.3)

Ugniježđene funkcije se prvo procjenjuju prije nego što se njihovi rezultati koriste kao ulazne vrijednosti za druge funkcije. Funkcije se procjenjuju od najdubljeg nivoa gniježđenja do najvišeg s lijeva na desno. Prethodni izraz se izvršava na sljedeći način

1. Funkcija F3(param1) se evaluira i povratna vrijednost se koristi kao treći parametar za funkciju 2, nazovimo je param2.3
2. Zatim se evaluira funkcija F2(param1, param2.2, param2.3) i vraćena vrijednost se koristi kao drugi parametar funkcije F1 - param1.2
3. Konačno, funkcija F1(param1, param2, param1.3) se evaluira i rezultat se vraća pozivnom programu.

Dakle, funkcija F3 je na trećem nivou gniježđenja.

Razmotrite upit

odaberite next_day(last_day(sysdate)-7, 'uto') iz dual;

1. U ovom upitu postoje tri funkcije, from niži nivo na vrh - SYSDATE, LAST_DAY, NEXT_DAY. Zahtjev se postavlja ovako
2. Izvršava se ugniježđena funkcija SYSDATE. Vraća struju sistemsko vrijeme. Recimo da je trenutni datum 28. oktobar 2009
3. Zatim se izračunava rezultat funkcije drugog nivoa LAST_DAY. LAST_DATE('28-OCT-2009') vraća posljednji dan oktobra 2009., tj. 31. oktobar 2009.
4. Zatim se od ovog datuma oduzima sedam dana - dobije se 24. oktobar.
5. Konačno, funkcija NEXT_DAY('24-OCT-2009', 'uto') se evaluira i upit vraća zadnji utorak u oktobru - što je u našem primjeru 27-OCT-2009.

Prilično teško razumjeti i izgraditi složeni izrazi koristeći puno ugniježđenih poziva funkcija, ali to dolazi s vremenom i vježbom. Takve izraze možete razbiti na dijelove i testirati zasebno. DUAL tablica je vrlo korisna za testiranje upita i rezultata poziva funkcija. Možete testirati i otklanjati greške u malim komponentama, koje se zatim kombinuju u jedan veliki željeni izraz.

Funkcije podružnice

Funkcije grananja, poznate i kao IF-THEN-ELSE, koriste se za određivanje putanje izvršenja ovisno o nekim okolnostima. Funkcije grananja vraćaju različite rezultate na osnovu rezultata evaluacije stanja. U grupi takvih funkcija nalaze se funkcije za rad sa NULL vrijednošću: NVL, NVL2, NULLIF i COALESCE. I takođe opšte funkcije, predstavljen funkcijom DECODE i izrazom CASE. Funkcija DECODE je Oracle funkcija, dok je izraz CASE u ANSI SQL standardu.

NVL funkcija

NVL funkcija provjerava vrijednost stupca ili izraza bilo kojeg tipa podataka za nultu vrijednost. Ako je vrijednost NULL, vraća alternativnu zadanu vrijednost koja nije NULL, inače se vraća originalna vrijednost.

NVL funkcija ima dvije potrebne parametre a sintaksa je NVL(original, ifnull) gdje je original originalna vrijednost za testiranje, a ifnull je rezultat koji vraća funkcija ako je original NULL. Tip podataka ifnull i originalnih parametara mora biti kompatibilan. Odnosno, ili tip podataka mora biti isti ili mora biti moguće implicitno pretvoriti vrijednosti iz jednog tipa u drugi. Funkcija NVL vraća vrijednost istog tipa podataka kao tip podataka originalnog parametra. Razmotrite tri upita

Upit 1: odaberite nvl(1234) iz dual;

Upit 2: odaberite nvl(null, 1234) iz dual;

Upit 3: izaberite nvl(substr('abc', 4), 'Ne postoji podniz') iz dual;

Budući da NVL funkcija zahtijeva dva parametra, upit 1 će vratiti grešku ORA-00909: nevažeći broj argumenata. Upit 2 će vratiti 1234 jer se provjerava NULL i ono je NULL. Upit tri koristi ugniježđenu funkciju SUBSTR koja pokušava izdvojiti četvrti znak iz niza od tri znaka, vraća NULL, a NVL funkcija vraća string 'Ne postoji sbustring'.

NVL funkcija je vrlo korisna kada radite s brojevima. Koristi se za pretvaranje NULL vrijednosti u 0 tako da aritmetičke operacije preko brojeva nije vratio NULL

NVL funkcija2

Funkcija NVL2 pruža više funkcionalnosti od NVL-a, ali također rukuje NULL-om. Provjerava vrijednost stupca ili izraza bilo kojeg tipa za nultu vrijednost. Ako vrijednost nije NULL, onda se vraća drugi parametar, u suprotnom se vraća treći parametar, za razliku od funkcije NVL, koja u ovom slučaju vraća originalnu vrijednost.

Funkcija NVL2 ima tri potrebna parametra, a sintaksa je NVL2(original, ifnotnull, ifnull), gdje je original vrijednost za testiranje, ifnotnull je vrijednost koju treba vratiti ako original nije NULL, a ifnull je vrijednost koju treba vratiti ako je original NULL. Tipovi podataka parametara ifnotnull i ifnull moraju biti kompatibilni, a ne mogu biti tip DUGO. Tip podataka koji vraća funkcija NVL2 jednak je tipu podataka parametra ifnotnull. Pogledajmo nekoliko primjera

Upit 1: odaberite nvl2(1234, 1, 'a string') iz dual;

Upit 2: odaberite nvl2(null, 1234, 5678) iz dual;

Upit 3: odaberite nvl2(substr('abc', 2), 'Not bc', 'No substring') iz dual;

Parametar ifnotnull u upitu 1 je broj, a parametar ifnull je niz. Pošto su tipovi podataka nekompatibilni, vraća se greška “ORA-01722: nevažeći broj”. Upit dva vraća ifnull parametar jer je original NULL, a rezultat je 5678. Upit tri koristi funkciju SUBSTR koja vraća 'bc' i poziva NVL2('bc','Not bc','No substring') - koja vraća ifnotnull parametar – 'Ne bc'.

NULLIF funkcija

Funkcija NULLIF testira dvije vrijednosti za iste. Ako su isti, vraća se NULL, u suprotnom se vraća prvi parametar. Funkcija NULLIF ima dva potrebna parametra, a sintaksa je NULLIF(ifunequal, Compare_item). Funkcija uspoređuje dva parametra, i ako su identični, vraća NULL, u suprotnom parametar ifunequal. Razmotrite upite

Upit 1: odaberite nullif(1234, 1234) iz dual;

Upit jedan vraća NULL jer su parametri identični. Nizovi u upitu 2 se ne konvertuju u datum, već se porede kao nizovi. Pošto su nizovi različite dužine, vraća se parametar ifunequal 24-JUL-2009.

Na slici 10-4, funkcija NULLIF je ugniježđena unutar funkcije NVL2. Funkcija NULLIF zauzvrat koristi funkcije SUBSTR i UPPER kao dio izraza u parametru ifunequal. Kolona EMAIL se upoređuje sa ovim izrazom, koji vraća prvo slovo imena spojeno s prezimenom za zaposlenike čije ime ima 4 znaka. Kada su ove vrijednosti jednake, NULLIF će vratiti NULL, u suprotnom će vratiti vrijednost parametra ifunequal. Ove vrijednosti se koriste kao parametar za funkciju NVL2. NVL2, zauzvrat, vraća opis da li se upoređeni elementi podudaraju ili ne.

Slika 10-4 - Upotreba funkcije NULLIF

Funkcija COALESCE

Funkcija COALESCE vraća prvu vrijednost koja nije NULL s liste parametara. Ako su svi parametri NULL, onda se vraća NULL. Funkcija COALESCE ima dva potrebna parametra i onoliko koliko želite opcioni parametri i sintaksu COALESCE(expr1, expr2, ..., exprn) gdje je rezultat izraz 1 ako vrijednost izraza 1 nije NULL, u suprotnom rezultat je izraz 2 ako nije NULL, i tako dalje. COALESCE je po značenju jednak ugniježđenim NVL funkcijama

COALESCE(izraz1,izraz2) = NVL(izraz1,izraz2)

COALESCE(izraz1,izraz2,izraz3) = NVL(izraz1,NVL(izraz2,izraz3))

Tip podataka vraćene vrijednosti ako je pronađena vrijednost koja nije NULL jednaka je tipu podataka prve vrijednosti koja nije NULL. Da biste izbjegli grešku 'ORA-00932: nedosljedni tipovi podataka', svi parametri koji nisu NULL moraju biti kompatibilni s prvim parametrom koji nije NULL. Razmotrite tri primjera

Upit 1: odaberite spojiti (null, null, null, 'a string') iz dual;

Upit 2: odaberite spojiti (null, null, null) iz dual;

Upit 3: odaberite spojiti (substr('abc', 4), 'Ne bc', 'Nema podniza') iz dual;

Upit 1 vraća četvrti parametar: string, jer je to prvi parametar koji nije NULL. Upit dva vraća NULL jer su svi parametri NULL. Upit 3 procjenjuje prvi parametar, dobiva NULL i vraća drugi parametar jer je to prvi parametar koji nije NULL.

Parametri funkcije NVL2 mogu biti zbunjujući ako ste već upoznati s funkcijom NVL. NVL(original, ifnull) vraća original ako vrijednost nije NULL, inače ifnull. NVL2(original, ifnotnull, ifnull) vraća ifnotnull ako original nije null inače ifnull. Zabuna dolazi od činjenice da je drugi parametar NVL funkcije ifnull, dok NVL2 ima ifnotnull. Dakle, nemojte se oslanjati na poziciju parametra u funkciji.

DECO funkcija

Funkcija DECODE implementira if-then-else logiku provjeravanjem prva dva parametra na jednakost i vraćanjem treće vrijednosti ako su jednaki, ili različite vrijednosti ako nisu. Funkcija DECODE ima tri potrebna parametra, a sintaksa je DECODE(expr1, comp1, iftrue1, , ). Ovi parametri se koriste kao što je prikazano u sljedećem primjeru pseudokoda.

IF expr1=comp1 onda vrati iftrue1

Inače ako je izraz1=komp2 onda vrati iftrue2

Inače ako je exprN=compN onda vrati iftrueN

Inače vrati NULL|ako je netačno;

Prvo, expr1 se upoređuje sa comp1. Ako su jednaki, vraća se iftrue1. Ako izraz1 nije jednak comp1, ono što se dalje događa ovisi o tome jesu li specificirani parametri comp2 i iftrue2. Ako je data, tada se vrijednost expr1 upoređuje sa comp2. Ako su vrijednosti jednake, onda se vraća iftrue2. Ako ne, onda ako postoje parovi parametara compN, iftrueN, expr1 i compN se uspoređuju, a ako je istinito, iftrueN se vraća ako je jednak. Ako nije pronađeno podudaranje ni u jednom skupu parametara, tada se vraća iffalse ako je ovaj parametar naveden ili NULL.

Svi parametri u funkciji DECODE mogu biti izrazi. Tip povratne vrijednosti jednak je tipu prvog validatora - parametra comp 1. Izraz ekspr 1 implicitno pretvara u tip podataka parametra comp1. Svi ostali dostupne opcije komp 1 … kompN se također implicitno pretvaraju u tip comp 1. DECODE tretira NULL vrijednost kao jednaku drugoj NULL vrijednosti, tj. ako je expr1 NULL, a comp3 je NULL, a comp2 nije NULL, tada se vraća iftrue3. Pogledajmo nekoliko primjera

Upit 1: odaberite decode(1234, 123, '123 se podudara') iz dual;

Upit 2: odaberite decode(1234, 123, '123 se podudara', 'Nema podudaranja') iz dual;

Upit 3: odaberite decode('search', 'comp1', 'true1', 'comp2', 'true2', 'search', 'true3', substr('2search', 2, 6), 'true4', ' false') od dual;

Upit jedan upoređuje vrijednost 1234 i 123. Pošto nisu jednake, iftrue1 se zanemaruje, a pošto iffalse nije definirano, vraća se NULL. Upit dva je identičan upitu 1 osim što je definirana vrijednost iffalse. Pošto 1234 nije jednako 123, iffalse - 'Nema podudaranja' se vraća. Upit tri provjerava vrijednosti parametara u odnosu na vrijednost pretraživanja. Parametri comp1 i comp2 nisu jednaki 'search' pa se rezultati iftrue1 i iftrue2 preskaču. Podudaranje je pronađeno u trećoj operaciji poređenja elementa comp3 (pozicija parametra 6), a povratna vrijednost iftrue3 (parametar 7) je 'true3'. Pošto je pronađeno podudaranje, više se ne vrše kalkulacije. To jest, uprkos činjenici da se vrijednost comp4 (parametar 8) također podudara s expr1, ovaj izraz se nikada ne evaluira jer je podudaranje pronađeno u prethodnom poređenju.

CASE izraz

Svi programski jezici treće i četvrte generacije implementiraju case konstrukciju. Kao i funkcija DECODE, izraz CASE vam omogućava da implementirate if-then-else logiku. Postoje dvije opcije za korištenje izraza CASE. Jednostavni CASE skupovi izraza originalni element da uporedite jednom, a zatim sve navedete neophodne uslove provjere. Složeni (pretraženi) CASE procjenjuje oba izraza za svaki uslov.

Izraz CASE ima tri potrebna parametra. Sintaksa izraza zavisi od tipa. Za jednostavan CASE izraz to izgleda ovako

CASE search_expr

KADA usporedba_izraz1 ONDA iftrue1

)

Funkcija TRUNC vraća broj n skraćen na m decimalnih mjesta. Parametar m može biti izostavljen, u kom slučaju se n 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 pozitivan, tada funkcija vraća 1. Ako je negativan, vraća se -1. Ako je nula, 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 da se prenese m jednako nuli - u ovom slučaju nema greške dijeljenja sa 0.

POWER(n, m) funkcija

Funkcija POWER podiže broj n na stepen m. Stepen 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

OD DUAL

U nekim slučajevima može doći do izuzetka prilikom pozivanja ove funkcije. Na primjer:

ODABIR SNAGE(-100, 1/2) X2

OD DUAL

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

Funkcija SQRT(n)

Ova funkcija vraća kvadratni korijen od 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 (pri čemu n mora biti veće od nule). primjer:

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