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

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 svoju vrijednost. Ako je prvi parametar NULL, funkcija umjesto toga vraća vrijednost drugog parametra expr2.

Smatrati praktični primjer... Polje COMM u EMP tablici može sadržavati nulte 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 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 proslijeđenom kao parametru. Na primjer:

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

IZ DUAL

TRUNC funkcija (n [, m])

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

Funkcija POWER (n, m).

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

IZ DUAL

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

ODABIR SNAGE (-100, 1/2) X2

IZ DUAL

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

SQRT (n) funkcija

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 (gdje n mora biti veće od nule). Primjer:

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

Dotaknuli smo se ugniježđenih funkcija malo ranije, sada ćemo ih detaljnije razmotriti. Također ćemo pogledati funkcije za rad s NULL vrijednostima i funkcije koje pomažu implementirati operaciju grananja u upitu.

Ugniježđene funkcije

Ugniježđene funkcije koriste povratnu vrijednost jedne funkcije kao ulazni parametar drugoj funkciji. Funkcije uvijek vraćaju samo jednu vrijednost. Stoga rezultat poziva funkcije možete tretirati kao doslovno vrijednost kada ga koristite kao parametar za poziv drugoj funkciji. Inline funkcije mogu biti ugniježđene na bilo kojoj razini 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 evaluiraju prije nego što se njihovi rezultati koriste kao ulazni podaci u druge funkcije. Funkcije se vrednuju od najdublje razine ugniježđenja do najviše, s lijeva na desno. Prethodni izraz se izvodi na sljedeći način

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

Dakle, funkcija F3 je na trećoj razini ugniježđenja.

Razmotrite zahtjev

odaberite sljedeći_dan (zadnji_dan (sysdate) -7, 'uto') iz dual;

1. U ovom zahtjevu postoje tri funkcije, od niži nivo na vrh - SYSDATE, LAST_DAY, NEXT_DAY. Zahtjev se izvršava na sljedeći način
2. Izvršava se najgniježđenija funkcija SYSDATE. Vraća struju sistemsko vrijeme... Pretpostavimo da je trenutni datum 28. listopada 2009
3. Zatim se izračunava rezultat funkcije druge razine LAST_DAY. LAST_DATE ('28 -OKT-2009') vraća zadnji dan listopada 2009., a to je 31. listopada 2009.
4. Tada se od ovog datuma oduzima sedam dana - ispada 24. listopada.
5. Konačno se evaluira funkcija NEXT_DAY ('24. -OKT-2009 ',' uto '), a upit vraća zadnji utorak u listopadu - što je 27. listopada 2009. u našem primjeru.

Dovoljno 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 podijeliti na dijelove i testirati ih zasebno. DUAL tablica je vrlo korisna za testiranje upita i poziva funkcija. Možete testirati i otkloniti greške u malim komponentama, koje se zatim mogu kombinirati u jedan veliki, željeni izraz.

Funkcije grananja

Funkcije grananja, također poznate kao IF-THEN-ELSE, koriste se za određivanje puta izvršenja na temelju neke okolnosti. Funkcije grananja vraćaju različite rezultate na temelju rezultata evaluacije stanja. U skupini takvih funkcija razlikuju se funkcije za rad s NULL vrijednošću: NVL, NVL2, NULLIF i COALESCE. I također opće funkcije predstavljeno funkcijom DECODE i izrazom CASE. Funkcija DECODE je Oracle funkcija dok je izraz CASE prisutan u ANSI SQL standardu.

NVL funkcija

Funkcija NVL testira vrijednost stupca ili izraza bilo kojeg tipa podataka na NULL. Ako je vrijednost NULL, vraća alternativnu zadanu vrijednost koja nije NULL; inače se vraća izvorna vrijednost.

NVL funkcija ima dvije potrebnih parametara a sintaksa je NVL (original, ifnull) gdje je original izvorna vrijednost za testiranje, a ifnull je rezultat koji vraća funkcija ako je original NULL. Tip podataka ifnull i izvornih parametara mora biti kompatibilan. Odnosno, ili tip podataka mora biti isti ili mora biti moguće implicitno pretvoriti vrijednosti iz jedne vrste u drugu. Funkcija NVL vraća vrijednost istog tipa podataka kao i tip podataka izvornog parametra. Razmotrite tri zahtjeva

Upit 1: odaberite nvl (1234) iz dual;

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

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

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

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

NVL funkcija2

NVL2 pruža više funkcionalnosti od NVL-a, ali također obrađuje NULL vrijednosti. Testira vrijednost stupca ili izraza bilo koje vrste za NULL. 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 izvornu vrijednost.

Funkcija NVL2 ima tri potrebna parametra i sintaksu NVL2 (original, ifnotnull, ifnull), gdje je original vrijednost koju treba provjeriti, ifnotnull je vrijednost vraćena ako original nije NULL, a ifnull je vrijednost vraćena ako je original NULL. Tipovi podataka parametara ifnotnull i ifnull moraju biti kompatibilni i ne mogu biti DUGA tip... Tip podataka koji vraća funkcija NVL2 jednak je tipu podataka parametra ifnotnull. Pogledajmo neke primjere

Upit 1: odaberite nvl2 (1234, 1, 'niz') 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. Budući da tipovi podataka nisu kompatibilni, vraća se pogreška “ORA-01722: nevažeći broj”. Zahtjev dva vraća parametar ifnull, budući da je original NULL, a rezultat je 5678. Treći zahtjev koristi funkciju SUBSTR koja vraća 'bc' i poziva NVL2 ('bc', 'Not bc', 'No substring') - koja vraća parametar ifnotnull - 'Ne bc'.

NULLIF funkcija

Funkcija NULLIF testira dvije vrijednosti za identitet. Ako su isti, vraća se NULL, inače se vraća prvi parametar. Funkcija NULLIF ima dva potrebna parametra i NULLIF sintaksu (ifunequal, usporedba_stavka). Funkcija uspoređuje dva parametra i ako su identični - vraća se NULL, u suprotnom parametar ifunequal. Razmotrite zahtjeve

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

Upit jedan vraća NULL jer su parametri identični. Nizovi u upitu 2 ne pretvaraju se u datum, već se uspoređuju kao nizovi. Budući da su žice različite duljine, vraća se 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. Stupac EMAIL uspoređuje se s 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, a vrijednost će vratiti vrijednost parametra ifunequal. Ove vrijednosti se koriste kao parametar za funkciju NVL2. NVL2 zauzvrat vraća opis podudaraju li se uspoređene stavke ili ne.

Slika 10-4 - Korištenje funkcije NULLIF

Funkcija COALESCE

Funkcija COALESCE vraća prvu vrijednost koja nije NULL s popisa parametara. Ako su svi parametri NULL, tada se vraća NULL. Funkcija COALESCE ima dva potrebna parametra i isto toliko izborni parametri i sintaksu COALESCE (expr1, expr2, ..., exprn) gdje je rezultat izraz expr1 ako izraz nije NULL, u protivnom se expr2 vraća ako nije NULL, i tako dalje. COALESCE je jednak ugniježđenim NVL funkcijama

SPAJANJE (izraz1, ekspr2) = NVL (izraz1, ekspr2)

SPAJANJE (izraz1, izraz2, ekspr3) = NVL (izraz1, NVL (izraz2, izraz3))

Tip podataka povratne vrijednosti ako je pronađena vrijednost koja nije NULL jednaka je tipu podataka prve vrijednosti koja nije NULL. Kako biste izbjegli pogrešku 'ORA-00932: nedosljedni tipovi podataka', svi parametri koji nisu NULL moraju biti kompatibilni s prvim parametrom koji nije NULL. Pogledajmo 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), 'Not bc', 'No substring') iz dual;

Upit 1 vraća četvrti parametar: niz, budući da je ovo prvi parametar koji nije NULL. Upit dva vraća NULL jer su svi parametri NULL. Upit 3 procjenjuje prvi parametar, dobiva NULL vrijednost i vraća drugi parametar, budući da 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 je zbog činjenice da je drugi parametar funkcije NVL ifnull, dok je u NVL2 ifnotnull. Stoga se nemojte oslanjati na položaj parametra u funkciji.

DECODE funkcija

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

IF expr1 = comp1 onda vrati iftrue1

Inače ako je izraz1 = comp2 onda vrati iftrue2

Inače ako je exprN = compN onda vrati iftrueN

Inače vrati NULL | iffalse;

Prvo, expr1 se uspoređuje s comp1. Ako su jednaki, vraća se iftrue1. Ako izraz expr1 nije jednak comp1, ono što se dalje događa ovisi o tome jesu li navedeni parametri comp2 i iftrue2. Ako je zadana, vrijednost izraza1 uspoređuje se s komp2. Ako su vrijednosti jednake, onda se vraća iftrue2. Ako ne, onda ako postoje parovi parametara compN, iftrueN, expr1 i compN se uspoređuju i iftrueN se vraća ako su jednaki. Ako nije pronađeno podudaranje ni u jednom od skupova parametara, tada se vraća iffalse ako je ovaj parametar naveden ili NULL.

Svi parametri u funkciji DECODE mogu biti izrazi. Tip povratka je isti kao tip prvog elementa za provjeru valjanosti - parametra komp 1. Izraz ekspr 1 se implicitno pretvara u tip podataka parametra comp1. Svi ostali dostupne opcije komp 1 ... kompN također implicitno pretvoriti u komp 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 neke primjere

Upit 1: odaberite dekodiranje (1234, 123, '123 je podudaranje') iz dual;

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

Upit 3: odaberite dekodiranje ('search', 'comp1', 'true1', 'comp2', 'true2', 'search', 'true3', substr ('2search', 2, 6), 'true4', ' lažno ') od dual;

Upit jedan uspoređuje vrijednost 1234 i 123. Budući da nisu jednake, iftrue1 se zanemaruje, a budući da nije definirana vrijednost iffalse, vraća se NULL. Zahtjev dva je identičan zahtjevu 1 osim što je navedeno iffalse. Budući da 1234 nije jednako 123, onda se vraća iffalse - ‘Nema podudaranja’. Zahtjev tri provjerava vrijednosti parametara da vidi odgovaraju li vrijednosti pretraživanja. Parametri comp1 i comp2 nisu jednaki 'pretraži' pa se rezultati iftrue1 i iftrue2 preskaču. Podudaranje se nalazi u trećoj usporedbi comp3 (položaj parametra 6), a povratna vrijednost iftrue3 (parametar 7) je "true3". Budući da je pronađeno podudaranje, daljnji izračuni se ne izvode. To jest, unatoč činjenici da se vrijednost comp4 (parametar 8) također podudara s izrazom expr1, ovaj izraz se nikada ne izračunava jer je pronađeno podudaranje u prethodnoj usporedbi.

CASE izraz

Svi programski jezici treće i četvrte generacije implementiraju case konstrukciju. Poput funkcije DECODE, izraz CASE vam omogućuje implementaciju if-then-else logike. Postoje dvije opcije za korištenje izraza CASE. Jednostavni skupovi izraza CASE izvorni element uspoređuje jednom, a zatim sve navodi potrebne uvjete provjere. Traženi CASE procjenjuje obje izjave za svaki uvjet.

Izraz CASE ima tri potrebna parametra. Sintaksa izraza ovisi o tipu. Za jednostavan CASE izraz, to izgleda ovako

CASE search_expr

KADA usporedba_izraz1 ONDA iftrue1

)

TRUNC vraća broj n, skraćen na m decimalnih mjesta. Parametar m može se izostaviti - u ovom slučaju, n se 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, funkcija vraća 1. Ako je n negativan, vraća se -1. Ako je jednako nuli, tada se vraća 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 prolaska m jednako nuli - u ovom slučaju nema pogreške dijeljenja s 0.

Funkcija POWER (n, m).

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

IZ DUAL

U nekim slučajevima, prilikom pozivanja ove funkcije, može se pojaviti iznimka. Na primjer:

ODABIR SNAGE (-100, 1/2) X2

IZ DUAL

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

SQRT (n) funkcija

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

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