Deklaratat e degëve në një deklaratë SELECT. Oracle, detyra tipike SQL

Funksioni NVL

Funksioni NVL është zakonisht më i përdoruri. Funksioni merr dy parametra: NVL (expr1, expr2). Nëse parametri i parë i expr1 nuk është NULL, atëherë funksioni kthen vlerën e tij. Nëse parametri i parë është NULL, atëherë funksioni kthen vlerën e parametrit të dytë expr2.

Merrni parasysh shembull praktik... Fusha COMM në tabelën EMP mund të përmbajë vlerat zero... Kur ekzekutoni një pyetje si:

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

NGA SCOTT.EMP

vlera NULL do të zëvendësohet me zero. Vini re se nëse gjeneroni një vlerë duke përdorur një funksion, atij i caktohet një pseudonim. Rezultatet e pyetjes do të duken kështu:

CEIL (n) Funksioni

Funksioni CEIL kthen numrin më të vogël të plotë më të madh ose të barabartë me numrin n të kaluar si parametër. Për shembull:

SELECT TAVANI (100) X1, CEIL (-100) X2, CEIL (100.2) X3, CEIL (-100.2) X4

NGA DUAL

Funksioni TRUNC (n [, m])

TRUNC kthen numrin n, të cunguar në m shifra dhjetore. Parametri m mund të hiqet - në këtë rast, n shkurtohet në një numër të plotë.

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

TRUNC (100.25678, 2) X4

NGA DUAL

Funksioni SIGN (n).

Funksioni SIGN përcakton shenjën e një numri. Nëse n është pozitiv, funksioni kthen 1. Nëse n është negativ, kthehet -1. Nëse është e barabartë me zero, atëherë kthehet 0. Për shembull:

ZGJIDH SHENJEN (100.22) X1, SHENJA (-100.22) X2, SHENJA (0) X3

NGA DUAL

Një tipar interesant i këtij funksioni është aftësia për të transferuar m e barabartë me zero- nuk ka gabim ndarje me 0.

Funksioni POWER (n, m).

Funksioni POWER rrit n në fuqinë m. Shkalla mund të jetë e pjesshme ose negative, gjë që zgjeron ndjeshëm aftësitë e këtij funksioni.

SELECT POWER (10, 2) X1, POWER (100, 1/2) X2,

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

NGA DUAL

Në disa raste, kur telefononi këtë funksion, mund të merrni situatë e jashtëzakonshme... Për shembull:

ZGJIDH FUQINË (-100, 1/2) X2

NGA DUAL

V në këtë rast Bëhet një përpjekje për të llogaritur rrënjën katrore të një numri negativ, i cili do të rezultojë në gabimin ORA-01428 "Argumenti jashtë rrezes".

Funksioni SQRT (n).

Ky funksion kthehet Rrenja katrore nga numri n. Për shembull:

SELECT SQRT (100) X

NGA DUAL

Funksionet EXP (n) dhe LN (n).

Funksioni EXP ngre e në fuqinë e n dhe funksioni LN llogarit logaritmin natyror të n (ku n duhet të jetë më i madh se zero). Shembull:

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

Ne prekëm funksionet e mbivendosur pak më herët, tani do t'i shqyrtojmë ato në më shumë detaje. Ne gjithashtu do të shikojmë funksionet për të punuar me vlerat dhe funksionet NULL që ndihmojnë në zbatimin e operacionit të degëzimit në një pyetje.

Funksionet e mbivendosura

Funksionet e ndërlidhura përdorin vlerën e kthimit të një funksioni si parametër hyrës në një funksion tjetër. Funksionet gjithmonë kthejnë vetëm një vlerë. Prandaj, mund ta trajtoni rezultatin e një thirrjeje funksioni si një vlerë të mirëfilltë kur e përdorni atë si parametër për një thirrje në një funksion tjetër. Funksionet inline mund të futen në çdo nivel foleje. Një thirrje funksioni duket kështu

Funksioni1 (parametri1, parametri2,…) = rezultat

Zëvendësimi i një parametri funksioni me një thirrje në një funksion tjetër mund të rezultojë në shprehje si

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

Funksionet e ndërlidhura vlerësohen së pari përpara se rezultatet e tyre të përdoren si hyrje në funksione të tjera. Funksionet vlerësohen nga niveli më i thellë i foleve deri tek ai më i larti, nga e majta në të djathtë. Shprehja e mëparshme ekzekutohet si më poshtë

1. Funksioni F3 (param1) llogaritet dhe vlera e kthyer përdoret si parametër i tretë për funksionin 2, le ta quajmë param2.
2. Pastaj llogaritet funksioni F2 (param1, param2.2, param2.3) dhe vlera e kthyer përdoret si parametër i dytë i funksionit F1 - param1.2.
3. Më në fund, funksioni F1 (param1, param2, param1.3) vlerësohet dhe kthehet në programin thirrës.

Kështu, funksioni F3 është në nivelin e tretë të foleve.

Merrni parasysh kërkesën

zgjidhni next_day (dita e fundit (sysdate) -7, 'tue') nga dual;

1. Ka tre funksione në këtë kërkesë, nga nivel më të ulët në krye - SYSDATE, LAST_DAY, NEXT_DAY. Kërkesa ekzekutohet si më poshtë
2. Funksioni më i mbivendosur SYSDATE është ekzekutuar. E kthen rrymën Koha e sistemit... Supozoni se data aktuale është 28 tetor 2009
3. Më pas, llogaritet rezultati i funksionit të nivelit të dytë LAST_DAY. LAST_DATE ('28 -TETOR-2009 ') kthehet në ditën e fundit të tetorit 2009, që është 31 tetor 2009.
4. Pastaj nga kjo datë zbriten shtatë ditë - rezulton 24 tetor.
5. Më në fund, funksioni NEXT_DAY ('24 -TETOR-2009 ',' e martë ') vlerësohet dhe pyetja kthehet të martën e fundit të tetorit - që është 27-TETOR-2009 në shembullin tonë.

Mjaft e vështirë për të kuptuar dhe ndërtuar shprehje komplekse duke përdorur shumë thirrje funksionesh të ndërlidhura, por kjo vjen me kohë dhe praktikë. Ju mund t'i ndani shprehjet e tilla në pjesë dhe t'i provoni ato veç e veç. Tabela DUAL është shumë e dobishme për testimin e pyetjeve dhe thirrjeve të funksioneve. Mund të testoni dhe korrigjoni komponentët e vegjël, të cilët më pas mund të kombinohen në një shprehje të madhe dhe të dëshiruar.

Funksionet e degëzimit

Funksionet e degëzimit, të njohura edhe si IF-THEN-ELSE, përdoren për të përcaktuar rrugën e ekzekutimit bazuar në disa rrethana. Funksionet e degëzimit japin rezultate të ndryshme bazuar në rezultatin e vlerësimit të një gjendjeje. Në grupin e funksioneve të tilla dallohen funksionet për të punuar me vlerën NULL: NVL, NVL2, NULLIF dhe COALESCE. Dhe gjithashtu funksionet e përgjithshme përfaqësohet nga funksioni DECODE dhe shprehja CASE. Funksioni DEKODI është Funksioni i Oracle kurse shprehja CASE është e pranishme në standardin ANSI SQL.

Funksioni NVL

Funksioni NVL teston vlerën e një kolone ose shprehjeje të çdo lloji të dhënash për NULL. Nëse vlera është NULL, ajo kthen një vlerë alternative të paracaktuar jo-NULL; përndryshe, vlera origjinale kthehet.

Funksioni NVL ka dy parametrat e kërkuar dhe sintaksa është NVL (origjinale, ifnull) ku origjinali është vlera origjinale për t'u testuar dhe ifnull është rezultati i kthyer nga funksioni nëse origjinali është NULL. Lloji i të dhënave të parametrave ifnull dhe origjinal duhet të jetë i pajtueshëm. Kjo do të thotë, ose lloji i të dhënave duhet të jetë i njëjtë ose duhet të jetë e mundur që në mënyrë implicite të konvertohen vlerat nga një lloj në tjetrin. Funksioni NVL kthen një vlerë të të njëjtit lloj të dhënash si lloji i të dhënave të parametrit origjinal. Merrni parasysh tre kërkesa

Pyetja 1: zgjidhni nvl (1234) nga dual;

Pyetja 2: zgjidhni nvl (null, 1234) nga dual;

Pyetja 3: zgjidhni nvl (substr ('abc', 4), 'Nuk ekziston nënvarg') nga dual;

Meqenëse funksioni NVL kërkon dy parametra, pyetja 1 do të kthejë një gabim ORA-00909: numër i pavlefshëm i argumenteve. Pyetja 2 do të kthejë 1234 pasi NULL është kontrolluar dhe është NULL. Kërkesa e tretë përdor një funksion të ndërthurur SUBSTR që përpiqet të nxjerrë karakterin e katërt nga një varg me tre karaktere, kthen NULL dhe NVL kthen vargun "Nuk ekziston sbusstring".

Funksioni NVL është shumë i dobishëm kur punoni me numra. Përdoret për të kthyer vlerat NULL në 0 në mënyrë që veprimet aritmetike mbi numrat nuk u kthyen NULL

Funksioni NVL2

NVL2 ofron më shumë funksionalitet se NVL, por gjithashtu trajton vlerat NULL. Ai teston vlerën e një kolone ose shprehjeje të çdo lloji për NULL. Nëse vlera nuk është NULL, atëherë kthehet parametri i dytë, përndryshe kthehet parametri i tretë, ndryshe nga funksioni NVL, i cili në këtë rast kthen vlerën origjinale.

Funksioni NVL2 ka tre parametra të kërkuar dhe sintaksën NVL2 (origjinale, ifnotnull, ifnull), ku origjinali është vlera që duhet kontrolluar, ifnotnull është vlera e kthyer nëse origjinali nuk është NULL dhe ifnull është vlera e kthyer nëse origjinali është NULL. Llojet e të dhënave të parametrave ifnotnull dhe ifnull duhet të jenë të pajtueshme, dhe ato nuk mund të jenë Lloji i GJATË... Lloji i të dhënave të kthyer nga funksioni NVL2 është i barabartë me llojin e të dhënave të parametrit ifnotnull. Le të shohim disa shembuj

Pyetja 1: zgjidhni nvl2 (1234, 1, 'një varg') nga dual;

Pyetja 2: zgjidhni nvl2 (null, 1234, 5678) nga dual;

Pyetja 3: zgjidhni nvl2 (substr ('abc', 2), 'Jo bc', 'Pa substring') nga dual;

Parametri ifnotnull në pyetjen 1 është një numër, dhe parametri ifnull është një varg. Meqenëse llojet e të dhënave nuk janë të përputhshme, është kthyer gabimi "ORA-01722: numër i pavlefshëm". Kërkesa dy kthen parametrin ifnull, pasi origjinali është NULL dhe rezultati është 5678. Kërkesa tre përdor funksionin SUBSTR i cili kthen 'bc' dhe thërret NVL2 ('bc', 'Jo bc', 'Pa nënvarg') - që kthen Parametri ifnotnull - 'Jo bc'.

Funksioni NULLIF

Funksioni NULLIF teston dy vlera për identitetin. Nëse janë të njëjta, kthehet NULL përndryshe kthehet parametri i parë. Funksioni NULLIF ka dy parametra të kërkuar dhe sintaksën NULLIF (i pabarabartë, krahasimi_artikull). Funksioni krahason dy parametra dhe nëse janë identikë - kthehet NULL, përndryshe parametri jo i barabartë. Merrni parasysh kërkesat

Pyetja 1: zgjidhni nullif (1234, 1234) nga dual;

Pyetja e parë kthen NULL sepse parametrat janë identikë. Vargjet në pyetjen 2 nuk konvertohen në datë, por krahasohen si vargje. Meqenëse vargjet janë me gjatësi të ndryshme, kthehen nëse janë të pabarabarta 24-KORRIK-2009.

Në figurën 10-4, funksioni NULLIF është i vendosur brenda funksionit NVL2. Funksioni NULLIF nga ana e tij përdor funksionet SUBSTR dhe UPPER si pjesë e shprehjes në parametrin e pabarabartë. Kolona EMAIL krahasohet me këtë shprehje, e cila kthen shkronjën e parë të emrit të bashkuar me mbiemrin për punonjësit, emri i të cilëve është 4 karaktere. Kur këto vlera janë të barabarta, NULLIF do të kthejë NULL, dhe vlera do të kthejë vlerën e parametrit të pabarabartë. Këto vlera përdoren si parametër për funksionin NVL2. NVL2, nga ana tjetër, kthen një përshkrim nëse artikujt e krahasuar përputhen apo jo.

Figura 10-4 - Përdorimi i funksionit NULLIF

Funksioni COALESCE

Funksioni COALESCE kthen vlerën e parë jo-NULL nga lista e parametrave. Nëse të gjithë parametrat janë NULL, atëherë NULL kthehet. Funksioni COALESCE ka dy parametra të kërkuar dhe po aq parametrat opsionalë dhe sintaksa COALESCE (expr1, expr2, ..., exprn) ku rezultati është expr1 nëse expr nuk është NULL, përndryshe expr2 kthehet nëse nuk është NULL, e kështu me radhë. COALESCE është e barabartë me funksionet e mbivendosura NVL

BASHKIMI (expr1, expr2) = NVL (expr1, expr2)

BASHKIMI (expr1, expr2, expr3) = NVL (expr1, NVL (expr2, expr3))

Lloji i të dhënave të vlerës së kthyer nëse gjendet një vlerë jo-NULL është e barabartë me llojin e të dhënave të vlerës së parë jo-NULL. Për të shmangur gabimin "ORA-00932: lloje të dhënash jokonsistente", të gjithë parametrat jo-NULL duhet të jenë të pajtueshëm me parametrin e parë jo-NULL. Le të shohim tre shembuj

Pyetja 1: zgjidhni bashkimin (null, null, null, 'një varg') nga dual;

Pyetja 2: zgjidhni bashkimin (null, null, null) nga dual;

Pyetja 3: zgjidhni bashkimin (substr ('abc', 4), 'Jo bc', 'Pa substring') nga dual;

Pyetja 1 kthen parametrin e katërt: një varg, pasi ky është parametri i parë jo NULL. Query two kthen NULL sepse të gjithë parametrat janë NULL. Pyetja 3 vlerëson parametrin e parë, merr një vlerë NULL dhe kthen parametrin e dytë, pasi është parametri i parë jo-NULL.

Parametrat e funksionit NVL2 mund të jenë konfuz nëse tashmë jeni njohur me funksionin NVL. NVL (origjinal, ifnull) kthehet origjinal nëse vlera nuk është NULL, përndryshe ifnull. NVL2 (origjinale, ifnotnull, ifnull) kthen ifnotnull nëse origjinali nuk është NULL ndryshe ifnull. Konfuzioni është për faktin se parametri i dytë i funksionit NVL është ifnull, ndërsa në NVL2 është ifnotnull. Pra, mos u mbështetni në pozicionin e parametrit në funksion.

Funksioni DEKODI

Funksioni DECODE zbaton logjikën nëse-atëherë-tjetër duke kontrolluar dy parametrat e parë për barazi dhe duke kthyer një vlerë të tretë nëse janë të barabartë, ose një vlerë tjetër nëse nuk janë të barabartë. Funksioni DECODE ka tre parametra të kërkuar dhe sintaksën DECODE (expr1, comp1, iftrue1,,). Këto parametra përdoren siç tregohet në shembullin e mëposhtëm të pseudokodit

IF expr1 = comp1, atëherë kthejeni iftrue1

Përndryshe, nëse expr1 = comp2, atëherë kthejeni iftrue2

Përndryshe, nëse exprN = compN, atëherë kthejeni iftrueN

Përndryshe kthen NULL | nëse është e gabuar;

Së pari, expr1 krahasohet me comp1. Nëse ato janë të barabarta, iftrue1 kthehet. Nëse expr1 nuk është e barabartë me comp1, ajo që ndodh më pas varet nëse janë specifikuar parametrat comp2 dhe iftrue2. Nëse jepet, vlera e expr1 krahasohet me comp2. Nëse vlerat janë të barabarta, atëherë iftrue2 kthehet. Nëse jo, atëherë nëse ka çifte parametrash compN, krahasohen iftrueN, expr1 dhe compN dhe iftrueN kthehet nëse është i barabartë. Nëse nuk u gjet asnjë përputhje në asnjë nga grupet e parametrave, atëherë ose nëse është specifikuar ky parametër, kthehet nëse është false, ose NULL.

Të gjithë parametrat në funksionin DECODE mund të jenë shprehje. Lloji i kthimit është i njëjtë me llojin e elementit të parë vërtetues - parametrit komp 1. Shprehje expr 1 është në mënyrë implicite e konvertueshme në llojin e të dhënave të parametrit komp1. Të gjithë të tjerët opsionet e disponueshme komp 1 ... kompN gjithashtu në mënyrë implicite të konvertueshme në komp 1. DECODE trajton një vlerë NULL si të barabartë me një vlerë tjetër NULL, d.m.th. nëse expr1 është NULL dhe comp3 është NULL dhe comp2 nuk është NULL, atëherë iftrue3 kthehet. Le të shohim disa shembuj

Pyetja 1: zgjidhni dekodimin (1234, 123, '123 është një përputhje') nga dual;

Pyetja 2: zgjidhni dekodimin (1234, 123, '123 është një përputhje', 'Nuk ka përputhje') nga dual;

Pyetja 3: zgjidhni dekodimin ('kërko', 'comp1', 'true1', 'comp2', 'true2', 'kërko', 'true3', substr ('2search', 2, 6), 'true4', ' false ') nga dual;

Pyetja e parë krahason vlerën 1234 dhe 123. Meqenëse ato nuk janë të barabarta, iftrue1 shpërfillet dhe meqenëse nuk është përcaktuar asnjë vlerë e gabuar, kthehet NULL. Kërkesa dy është identike me kërkesën 1 me përjashtim të faktit se është specifikuar nëse është false. Meqenëse viti 1234 nuk është i barabartë me 123, atëherë nëse është e gabuar - "Nuk ka ndeshje" kthehet. Kërkoni tre kontrolle të vlerave të parametrave për të parë nëse ato përputhen me vlerën e kërkimit. Parametrat comp1 dhe comp2 nuk janë të barabartë 'kërkim' kështu që rezultatet e iftrue1 dhe iftrue2 janë anashkaluar. Përputhja gjendet në krahasimin e tretë të comp3 (pozicioni i parametrit 6) dhe vlera e kthimit iftrue3 (parametri 7) është 'true3'. Meqenëse u gjet një përputhje, nuk kryhen llogaritje të mëtejshme. Kjo do të thotë, përkundër faktit se vlera e comp4 (parametri 8) gjithashtu përputhet me expr1, kjo shprehje nuk llogaritet kurrë pasi u gjet një përputhje në krahasimin e mëparshëm.

Shprehje RASTE

Të gjitha gjuhët e programimit të gjeneratës së tretë dhe të katërt zbatojnë konstruktin e rastit. Ashtu si funksioni DECODE, shprehja CASE ju lejon të zbatoni logjikën nëse-atëherë-tjetër. Ekzistojnë dy mundësi për përdorimin e shprehjes CASE. Një grup i thjeshtë deklarate CASE element origjinal krahason një herë dhe më pas rendit të gjitha kushtet e nevojshmeçeqe. RASTI i kërkuar vlerëson të dy deklaratat për secilën gjendje.

Shprehja CASE ka tre parametra të kërkuar. Sintaksa e shprehjes varet nga lloji. Për një shprehje të thjeshtë CASE, duket kështu

RAST search_expr

KUR krahasimi_expr1 PASTAJ iftrue1

)

TRUNC kthen numrin n, të cunguar në m shifra dhjetore. Parametri m mund të hiqet - në këtë rast, n shkurtohet në një numër të plotë.

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

TRUNC (100.25678, 2) X4

NGA DUAL

Funksioni SIGN (n).

Funksioni SIGN përcakton shenjën e një numri. Nëse n është pozitiv, funksioni kthen 1. Nëse n është negativ, kthehet -1. Nëse është e barabartë me zero, atëherë kthehet 0. Për shembull:

ZGJIDH SHENJEN (100.22) X1, SHENJA (-100.22) X2, SHENJA (0) X3

NGA DUAL

Një tipar interesant i këtij funksioni është aftësia për të kaluar m të barabartë me zero - në këtë rast, nuk ka ndarje me 0 gabim.

Funksioni POWER (n, m).

Funksioni POWER rrit n në fuqinë m. Shkalla mund të jetë e pjesshme ose negative, gjë që zgjeron ndjeshëm aftësitë e këtij funksioni.

SELECT POWER (10, 2) X1, POWER (100, 1/2) X2,

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

NGA DUAL

Në disa raste, kur thirret ky funksion, mund të bëhet një përjashtim. Për shembull:

ZGJIDH FUQINË (-100, 1/2) X2

NGA DUAL

Në këtë rast, tentohet të llogaritet rrënja katrore e një numri negativ, i cili do të rezultojë në gabimin ORA-01428 "Argumenti jashtë rrezes".

Funksioni SQRT (n).

Ky funksion kthen rrënjën katrore të n. Për shembull:

SELECT SQRT (100) X

NGA DUAL

Funksionet EXP (n) dhe LN (n).

Funksioni EXP ngre e në fuqinë e n dhe funksioni LN llogarit logaritmin natyror të n (ku n duhet të jetë më i madh se zero). Shembull:

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