Haarakäskyt SELECT-komennossa. NVL-muunnokset eri Oraclen tietotyypeille nvl-kuvaus

NVL-toiminto

NVL-toimintoa käytetään yleensä useimmin. Funktio vastaanottaa kaksi parametria: NVL(laus1, laus2). Jos ensimmäinen parametri expr1 ei ole NULL, funktio palauttaa arvon. Jos ensimmäinen parametri on NULL, funktio palauttaa sen sijaan toisen parametrin expr2 arvon.

Katsotaanpa käytännön esimerkkiä. EMP-taulukon COMM-kenttä voi sisältää NULL-arvoja. Kun suoritat kyselyn, kuten:

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

LÄHETTÄJÄ SCOTT.EMP

NULL-arvo korvataan nollalla. Huomaa, että kun arvo luodaan funktiolla, sille määritetään alias. Kyselyn tulokset näyttävät tältä:

Funktio CEIL(n)

CEIL-funktio palauttaa pienimmän kokonaisluvun, joka on suurempi tai yhtä suuri kuin parametrina välitetty luku n. Esimerkiksi:

VALITSE KANTA(100) X1, KATTO (-100) X2, KATTO(100.2) X3 , KATTO (-100.2) X4

DUALISTA

Funktio TRUNC(n [,m])

TRUNC-funktio palauttaa luvun n katkaistuna m desimaalin tarkkuudella. Parametria m ei voi määrittää tässä tapauksessa, n on katkaistu kokonaisluvuksi.

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

TRUNC(100. 25678; 2) X4

DUALISTA

Funktio SIGN(n)

SIGN-funktio määrittää luvun etumerkin. Jos n on positiivinen, funktio palauttaa arvon 1. Jos n on negatiivinen, se palauttaa -1. Jos se on nolla, palautetaan 0 Esimerkiksi:

VALITSE TUNNUS(100.22) X1, SIGN(-100.22) X2, SIGN(0) X3

DUALISTA

Tämän funktion mielenkiintoinen ominaisuus on kyky lähettää m yhtä suureksi kuin nolla aiheuttamatta 0:lla jakovirhettä.

Funktio POWER(n, m)

POWER-toiminto nostaa luvun n tehoon m. Aste voi olla murto-osa ja negatiivinen, mikä laajentaa merkittävästi tämän toiminnon ominaisuuksia.

VALITSE TEHO(10, 2) X1, TEHO(100, 1/2) X2,

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

DUALISTA

Joissakin tapauksissa tätä toimintoa kutsuttaessa voi tapahtua poikkeus. Esimerkiksi:

VALITSE TEHO (-100, 1/2) X2

DUALISTA

Tässä tapauksessa negatiivisen luvun neliöjuuri yritetään laskea, mikä johtaa ORA-01428 "Argumentti alueen ulkopuolella" -virheeseen.

Funktio SQRT(n)

Tämä funktio palauttaa luvun n neliöjuuren. Esimerkiksi:

SELECT SQRT(100) X

DUALISTA

EXP(n)- ja LN(n)-funktiot

EXP-funktio nostaa e:n potenssiin n, ja LN-funktio laskee n:n luonnollisen logaritmin (n on oltava suurempi kuin nolla). Esimerkki:

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

NVL-toiminto

NVL-toimintoa käytetään yleensä useimmin. Funktio vastaanottaa kaksi parametria: NVL(laus1, laus2). Jos ensimmäinen parametri expr1 ei ole NULL, funktio palauttaa arvon. Jos ensimmäinen parametri on NULL, funktio palauttaa sen sijaan toisen parametrin expr2 arvon.

Katsotaanpa käytännön esimerkkiä. EMP-taulukon COMM-kenttä voi sisältää NULL-arvoja. Kun suoritat kyselyn, kuten:

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

LÄHETTÄJÄ SCOTT.EMP

NULL-arvo korvataan nollalla. Huomaa, että kun arvo luodaan funktiolla, sille määritetään alias. Kyselyn tulokset näyttävät tältä:

Funktio CEIL(n)

CEIL-funktio palauttaa pienimmän kokonaisluvun, joka on suurempi tai yhtä suuri kuin parametrina välitetty luku n. Esimerkiksi:

VALITSE KANTA(100) X1, KATTO (-100) X2, KATTO(100.2) X3 , KATTO (-100.2) X4

DUALISTA

Funktio TRUNC(n [,m])

TRUNC-funktio palauttaa luvun n katkaistuna m desimaalin tarkkuudella. Parametria m ei voi määrittää tässä tapauksessa, n on katkaistu kokonaisluvuksi.

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

TRUNC(100. 25678; 2) X4

DUALISTA

Funktio SIGN(n)

SIGN-funktio määrittää luvun etumerkin. Jos n on positiivinen, funktio palauttaa arvon 1. Jos n on negatiivinen, se palauttaa -1. Jos se on nolla, palautetaan 0 Esimerkiksi:

VALITSE TUNNUS(100.22) X1, SIGN(-100.22) X2, SIGN(0) X3

DUALISTA

Tämän funktion mielenkiintoinen ominaisuus on kyky lähettää m yhtä suureksi kuin nolla aiheuttamatta 0:lla jakovirhettä.

Funktio POWER(n, m)

POWER-toiminto nostaa luvun n tehoon m. Aste voi olla murto-osa ja negatiivinen, mikä laajentaa merkittävästi tämän toiminnon ominaisuuksia.

VALITSE TEHO(10, 2) X1, TEHO(100, 1/2) X2,

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

DUALISTA

Joissakin tapauksissa tätä toimintoa kutsuttaessa voi tapahtua poikkeus. Esimerkiksi:

VALITSE TEHO (-100, 1/2) X2

DUALISTA

Tässä tapauksessa negatiivisen luvun neliöjuuri yritetään laskea, mikä johtaa ORA-01428 "Argumentti alueen ulkopuolella" -virheeseen.

Funktio SQRT(n)

Tämä funktio palauttaa luvun n neliöjuuren. Esimerkiksi:

SELECT SQRT(100) X

DUALISTA

EXP(n)- ja LN(n)-funktiot

EXP-funktio nostaa e:n potenssiin n, ja LN-funktio laskee n:n luonnollisen logaritmin (n on oltava suurempi kuin nolla). Esimerkki:

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

Käsittelimme sisäkkäisiä toimintoja hieman aiemmin, nyt tarkastelemme niitä hieman yksityiskohtaisemmin. Harkitsemme myös funktioita NULL-arvon kanssa työskentelemiseen ja funktioita, jotka auttavat toteuttamaan haaroitusoperaation kyselyssä.

Sisäkkäiset toiminnot

Sisäkkäiset funktiot käyttävät yhden funktion palautusarvoa toisen funktion syöttöparametrina. Funktiot palauttavat aina vain yhden arvon. Siksi voit käsitellä funktiokutsun tulosta arvoliteraalina, kun käytät sitä parametrina toisessa funktiokutsussa. Merkkijonofunktiot voidaan upottaa mille tahansa sisäkkäistasolle. Yksi funktiokutsu näyttää tältä

Funktio1(parametri1, parametri2, …) = tulos

Funktioparametrin korvaaminen kutsulla toiselle funktiolle voi johtaa lausekkeisiin, kuten

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

Sisäkkäiset funktiot arvioidaan ensin, ennen kuin niiden tuloksia käytetään muiden funktioiden syöttöarvoina. Toimintoja arvioidaan syvimmältä pesimätasolta ylimpään vasemmalta oikealle. Edellinen lauseke suoritetaan seuraavasti

1. Funktio F3(param1) arvioidaan ja palautusarvoa käytetään funktion 2 kolmantena parametrina, kutsutaan sitä param2.3:ksi.
2. Sitten funktio F2(param1, param2.2, param2.3) arvioidaan ja palautusarvoa käytetään funktion F1 - param1.2 toisena parametrina.
3. Lopuksi funktio F1(param1, param2, param1.3) arvioidaan ja tulos palautetaan kutsuvaan ohjelmaan.

Näin ollen F3-funktio on kolmannella sisäkkäisellä tasolla.

Harkitse pyyntöä

valitse seuraava_päivä(viimeinen_päivä(päivämäärä)-7, 'ti') dualista;

1. Tässä kyselyssä on kolme funktiota alemmasta tasosta ylempään tasoon - SYSDATE, LAST_DAY, NEXT_DAY. Pyyntö suoritetaan seuraavasti
2. Sisäkkäinen SYSDATE-toiminto suoritetaan. Se palauttaa nykyisen järjestelmän ajan. Oletetaan, että nykyinen päivämäärä on 28. lokakuuta 2009
3. Seuraavaksi lasketaan toisen tason funktion LAST_DAY tulos. LAST_DATE('28-OCT-2009') palauttaa lokakuun 2009 viimeisen päivän, joka on 31. lokakuuta 2009.
4. Sitten tästä päivämäärästä vähennetään seitsemän päivää - käy ilmi 24. lokakuuta.
5. Lopuksi funktio NEXT_DAY('24-OCT-2009', 'ti') arvioidaan, ja kysely palauttaa lokakuun viimeisen tiistain - joka esimerkissämme on 27-OCT-2009.

On melko vaikeaa ymmärtää ja rakentaa monimutkaisia ​​lausekkeita käyttämällä monia sisäkkäisiä funktiokutsuja, mutta tämä tulee ajan ja käytännön myötä. Voit jakaa tällaiset lausekkeet osiin ja testata niitä erikseen. DUAL-taulukko on erittäin hyödyllinen kyselyiden ja funktiokutsujen tulosten testaamiseen. Voit testata ja korjata pieniä komponentteja, jotka sitten yhdistetään yhdeksi suureksi halutuksi lausekkeeksi.

Haaroitustoiminnot

Haarafunktioita, jotka tunnetaan myös nimellä IF-THEN-ELSE, käytetään määrittämään suorituspolku tietyistä olosuhteista riippuen. Haaroitusfunktiot palauttavat erilaisia ​​tuloksia kunnon arvioinnin tuloksen perusteella. Tällaisten toimintojen ryhmä sisältää NULL-arvon kanssa työskenteleviä toimintoja: NVL, NVL2, NULLIF ja COALESCE. Ja myös yleiset funktiot, joita edustavat DECODE-funktio ja CASE-lauseke. DECODE-funktio on Oracle-funktio, kun taas CASE-lauseke on ANSI SQL -standardissa.

NVL-toiminto

NVL-funktio testaa minkä tahansa tietotyypin sarakkeen tai lausekkeen arvon NULL:ia vastaan. Jos arvo on NULL, se palauttaa vaihtoehtoisen ei-NULL-oletusarvon, muussa tapauksessa se palauttaa alkuperäisen arvon.

NVL-funktiolla on kaksi pakollista parametria ja syntaksi on NVL(alkuperäinen, ifnull), jossa alkuperäinen on alkuperäinen tarkistettava arvo ja ifnull on funktion palauttama tulos, jos alkuperäinen arvo on NULL. Ifnull- ja original-parametrien tietotyypin on oltava yhteensopivia. Eli joko tietotyypin on oltava sama tai arvoja on voitava implisiittisesti muuntaa tyypistä toiseen. NVL-funktio palauttaa arvon, joka on samaa tietotyyppiä kuin alkuperäisen parametrin tietotyyppi. Tarkastellaan kolmea kysymystä

Kysely 1: valitse nvl(1234) dualista;

Kysely 2: valitse nvl(null, 1234) dualista;

Kysely 3: valitse nvl(substr('abc', 4), 'Alimerkkijonoa ei ole olemassa') dualista;

Koska NVL-funktio vaatii kaksi parametria, pyyntö 1 palauttaa virheen ORA-00909: virheellinen argumenttien määrä. Kysely 2 palauttaa 1234, koska NULL-arvo on tarkistettu ja se on NULL. Kysely kolme käyttää sisäkkäistä SUBSTR-funktiota, joka yrittää poimia neljännen merkin kolmen merkin pituisesta merkkijonosta palauttaen NULL-arvon ja NVL-funktion, joka palauttaa merkkijonon "Sbustringiä ei ole olemassa".

NVL-toiminto on erittäin hyödyllinen, kun työskentelet numeroiden kanssa. Sitä käytetään muuttamaan NULL-arvot nollaksi, jotta lukujen aritmeettiset toiminnot eivät palauta NULL-arvoja

NVL-toiminto2

NVL2-toiminto tarjoaa enemmän toimintoja kuin NVL, mutta toimii myös NULL-arvojen käsittelyssä. Se testaa minkä tahansa tyyppisen sarakkeen tai lausekkeen arvon NULL:ia vastaan. Jos arvo ei ole NULL, palautetaan toinen parametri, muuten palautetaan kolmas parametri, toisin kuin NVL-funktio, joka palauttaa tässä tapauksessa alkuperäisen arvon.

NVL2-funktiossa on kolme pakollista parametria ja syntaksi on NVL2 (alkuperäinen, ifnotnull, ifnull), jossa alkuperäinen on testattava arvo, ifnotnull on palautettava arvo, jos alkuperäinen ei ole NULL, ja ifnull on palautettava arvo, jos alkuperäinen on NULL. Ifnotnull- ja ifnull-parametrien tietotyyppien on oltava yhteensopivia, eivätkä ne voi olla tyyppiä LONG. NVL2-funktion palauttama tietotyyppi on sama kuin ifnotnull-parametrin tietotyyppi. Katsotaanpa muutama esimerkki

Kysely 1: valitse nvl2(1234, 1, 'merkkijono') dualista;

Kysely 2: valitse nvl2(null, 1234, 5678) dualista;

Kysely 3: valitse nvl2(substr('abc', 2), 'Not bc', 'No substring') dualista;

Pyynnön 1 ifnotnull-parametri on numero ja ifnull-parametri on merkkijono. Koska tietotyypit eivät ole yhteensopivia, palautetaan virhe "ORA-01722: virheellinen numero". Kysely kaksi palauttaa ifnull-parametrin, koska alkuperäinen on NULL ja tulos on 5678. Kysely 3 käyttää SUBSTR-funktiota, joka palauttaa 'bc' ja kutsuu NVL2('bc','Not bc', 'No alimerkkijono') - joka palauttaa ifnotnull-parametri – 'Not bc'.

NULLIF-toiminto

NULLIF-funktio tarkistaa, ovatko kaksi arvoa identtisiä. Jos ne ovat samat, palautetaan NULL, muuten palautetaan ensimmäinen parametri. NULLIF-funktiolla on kaksi pakollista parametria ja syntaksi on NULLIF(ifunequal, vertailu_kohde). Funktio vertaa kahta parametria ja jos ne ovat identtisiä, palautetaan NULL, muuten parametri on ifunequal. Mietitään pyyntöjä

Kysely 1: valitse nullif(1234, 1234) dualista;

Kysely yksi palauttaa arvon NULL, koska parametrit ovat identtiset. Kyselyn 2 merkkijonoja ei muunneta päivämääräksi, vaan niitä verrataan merkkijonoiksi. Koska merkkijonot ovat eripituisia, ifunequal-parametri palautetaan 24-JUL-2009.

Kuvassa 10-4 NULLIF-funktio on sisäkkäin NVL2-funktion sisällä. NULLIF-funktio puolestaan ​​käyttää SUBSTR- ja UPPER-funktioita osana ifunequal-parametrin lauseketta. EMAIL-saraketta verrataan tähän lausekkeeseen, joka palauttaa etunimen ensimmäisen kirjaimen yhdistettynä sukunimeen työntekijöille, joiden etunimi on 4 merkkiä pitkä. Kun nämä arvot ovat yhtä suuret, NULLIF palauttaa arvon NULL, muuten se palauttaa ifunequal-parametrin arvon. Näitä arvoja käytetään NVL2-toiminnon parametreina. NVL2 puolestaan ​​palauttaa kuvauksen siitä, täsmäävätkö verratut elementit vai eivät.

Kuva 10-4 – NULLIF-funktion käyttö

COALESCE-toiminto

COALESCE-funktio palauttaa parametriluettelon ensimmäisen ei-NULL-arvon. Jos kaikki parametrit ovat NULL, palautetaan NULL. COALESCE-funktiossa on kaksi pakollista parametria ja mikä tahansa määrä valinnaisia ​​parametreja ja syntaksi on COALESCE(laus1, laus2, ..., exprn), jossa tulos on expr1, jos lausekkeen 1 arvo ei ole NULL, muuten tulos on expr2, jos se ei ole NULL, jne. COALESCE on merkitykseltään samanlainen kuin sisäkkäiset NVL-funktiot

COALESCE(laus1, laus2) = NVL(laus1, laus2)

COALESCE(laus1, laus2, laus3) = NVL(laus1,NVL(laus2, laus3))

Palautetun arvon tietotyyppi, jos ei-NULL-arvo löytyy, on sama kuin ensimmäisen ei-NULL-arvon tietotyyppi. Virheen "ORA-00932: epäjohdonmukaiset tietotyypit" välttämiseksi kaikkien muiden kuin NULL-parametrien on oltava yhteensopivia ensimmäisen ei-NULL-parametrin kanssa. Katsotaanpa kolme esimerkkiä

Kysely 1: valitse yhdistä (null, null, null, 'merkkijono') dualista;

Kysely 2: valitse coalesce(null, null, null) dualista;

Kysely 3: valitse coalesce(substr('abc', 4), 'Not bc', 'No substring') dualista;

Kysely 1 palauttaa neljännen parametrin: merkkijonon, koska se on ensimmäinen ei-NULL-parametri. Kysely kaksi palauttaa NULL-arvon, koska kaikki parametrit ovat NULL-arvoja. Kysely 3 arvioi ensimmäisen parametrin, saa NULL-arvon ja palauttaa toisen parametrin, koska se on ensimmäinen ei-NULL-parametri.

NVL2-toiminnon parametrit voivat olla hämmentäviä, jos olet jo perehtynyt NVL-toimintoon. NVL(alkuperäinen, josnull) palauttaa alkuperäisen, jos arvo ei ole NULL, muussa tapauksessa ifnull. NVL2(alkuperäinen, ifnotnull, ifnull) palauttaa ifnotnull, jos alkuperäinen ei ole NULL muuten ifnull. Hämmennys johtuu siitä, että NVL-funktion toinen parametri on ifnull, kun taas NVL2:lla se on ifnotnull. Älä siis luota parametrin sijaintiin funktiossa.

DECODE-toiminto

DECODE-funktio toteuttaa jos-niin-else-logiikkaa testaamalla kahden ensimmäisen parametrin yhtäläisyyttä ja palauttamalla kolmannen arvon, jos ne ovat yhtä suuret, tai eri arvon, jos ne eivät ole samat. DECODE-funktiossa on kolme pakollista parametria ja syntaksi on DECODE(laus1, comp1, iftrue1, , ). Näitä parametreja käytetään seuraavassa pseudokoodiesimerkissä esitetyllä tavalla

IF expr1=comp1 palauta iftrue1

Muussa tapauksessa jos expr1=comp2 palauta iftrue2

Muussa tapauksessa jos exprN=compN palauta iftrueN

Else return NULL|epäfalse;

Ensin expr1 verrataan comp1:een. Jos ne ovat yhtä suuret, palautetaan iftrue1. Jos expr1 ei ole yhtä suuri kuin comp1, seuraava tapahtuma riippuu siitä, onko parametrit comp2 ja iftrue2 määritetty. Jos lauseke on annettu, arvoa expr1 verrataan arvoon comp2. Jos arvot ovat yhtä suuret, palautetaan iftrue2. Jos ei, niin jos on olemassa parametriparia compN, iftrueN, expr1 ja compN verrataan ja jos ne ovat yhtä suuret, palautetaan iftrueN. Jos yhdestäkään parametrijoukosta ei löytynyt vastaavuutta, palautetaan joko iffalse, jos tämä parametri on määritetty, tai NULL.

Kaikki DECODE-funktion parametrit voivat olla lausekkeita. Palautusarvon tyyppi on sama kuin ensimmäisen validoivan elementin - parametrin - tyyppi comp 1. Lauseke expr 1 muunnetaan implisiittisesti comp-parametrin tietotyypiksi1. Kaikki muut käytettävissä olevat comp-parametrit 1...kompN ovat myös implisiittisesti muunnettu tyypiksi comp 1. DECODE käsittelee NULL-arvon yhtä suurena kuin toinen NULL-arvo, ts. jos expr1 on NULL ja comp3 on NULL ja comp2 ei ole NULL, palautetaan iftrue3. Katsotaanpa muutama esimerkki

Kysely 1: valitse dekoodaus (1234, 123, '123 on vastaavuus') dualista;

Kysely 2: valitse dekoodaus (1234, 123, '123 on vastaavuus', 'Ei vastaavuutta') dualista;

Kysely 3: valitse decode('search', 'comp1', 'true1', 'comp2', 'true2', 'haku', 'true3', substr('2search', 2, 6), 'true4', ' false') duaalista;

Kysely yksi vertaa arvoja 1234 ja 123. Koska ne eivät ole yhtä suuret, iftrue1 ohitetaan ja koska arvoa iffalse ei ole määritelty, palautetaan NULL. Pyyntö kaksi on identtinen pyynnön 1 kanssa, paitsi että arvo iffalse on määritetty. Koska 1234 ei ole yhtä suuri kuin 123, se palauttaa jos false - "Ei vastaa". Kysely kolme tarkistaa parametrien arvot vastaamaan haun arvoa. Parametrit comp1 ja comp2 eivät ole yhtä suuria kuin 'haku', joten iftrue1:n ja iftrue2:n tulokset ohitetaan. Vastaavuus löytyy elementin comp3 kolmannesta vertailuoperaatiosta (parametrin paikka 6) ja palautetaan iftrue3:n arvo (parametri 7), joka on yhtä suuri kuin 'true3'. Koska vastaavuus löytyy, laskelmia ei enää suoriteta. Toisin sanoen huolimatta siitä, että comp4:n arvo (parametri 8) vastaa myös expr1:tä, tätä lauseketta ei koskaan lasketa, koska vastaavuus löydettiin edellisessä vertailussa.

CASE-lauseke

Kaikki kolmannen ja neljännen sukupolven ohjelmointikielet toteuttavat case-rakenteen. Kuten DECODE-funktio, CASE-lauseke mahdollistaa if-then-else-logiikan toteuttamisen. CASE-lausekkeen käyttämiseen on kaksi vaihtoehtoa. Yksinkertainen CASE-lauseke määrittää lähdeelementin vertaamaan kerran ja luettelee sitten kaikki tarvittavat testiolosuhteet. Monimutkainen (haettu) CASE arvioi molemmat lauseet kullekin ehdolle.

CASE-lausekkeella on kolme pakollista parametria. Lausekkeen syntaksi riippuu tyypistä. Yksinkertaisessa CASE-lausekkeessa se näyttää tältä

CASE-haku_lause

KUN vertailu_lause1 NIIN iftrue1

}