Konvertime NVL për lloje të ndryshme të dhënash. Deklaratat e degëzimit në deklaratën SELECT

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ë vlera NULL. 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:

Funksioni CEIL (n).

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ë 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

Funksioni TO_CHAR me numra

Funksionet për konvertimin e të dhënave në lloje të tjera të dhënash. TO_CHAR (numri) konverton një numër në tekst. TO_NUMBER (varg) konverton tekstin në numër.

SELECT TO_CHAR (123) FROM DUAL do të kthejë vargun 123, SELECT TO_NUMBER (`12345") FROM DUAL do të kthejë 12345.

Puna laboratorike. Ndryshimi i formatit të numrave të shfaqur

Ndryshime në formatin e vlerave numerike në Oracle SQL, funksioni TO_CHAR për të punuar me vlerat numerike.

Ushtrimi:

Shkruani një pyetje që shfaq emrin, mbiemrin dhe informacionin e pagave për punonjësit nga tabela hr.employees, në formatin e paraqitur në Figurën 1. 3.4-1:

Oriz. 3.4 -1

Në këtë rast, të dhënat duhet të renditen në atë mënyrë që të shfaqen rreshtat e parë për punonjësit me pagën më të lartë.

Shënim:

Disa vlera të pagave në Fig. 3.4-1 kanë ndryshuar, kështu që ato mund të mos përputhen me vlerat tuaja.

Zgjidhja:

ZGJIDHni emrin AS "Emri", mbiemri Si "Mbiemri", TO_CHAR (PAGË, "L999999999.99") Si "Paga" NGA hr.punonjësit RENDOSJE SIPAS PAGËS DESC.

Funksionet TO_NUMBER dhe TO_DATE

Funksioni për konvertimin e një vargu në një datë TO_DATE (varg, format). Vlerat e mundshme formatet janë diskutuar tashmë më lart, kështu që unë do të jap disa shembuj të përdorimit të këtij funksioni. Shembuj:

ZGJIDH TO_DATE ("01.01.2010", `DD.MM.VVVV“) NGA DUAL do të kthejë datën `01.01.2010”;

ZGJIDH TO_DATE ("01.JAN.2010", `DD.HËN.VVVV“) NGA DUAL do të kthejë datën `01.01.2009”;

ZGJIDH TO_DATA ("15-01-10", `DD-MM-YY") NGA DUAL do të kthejë datën `15/01/2010”.

Funksioni i konvertimit të vargut në varg vlerë numerike TO_NUMBER (varg, format). Vlerat më të zakonshme të formatit janë renditur në tabelë, kështu që le të shohim përdorimin e këtij funksioni me shembuj. Shembuj:

ZGJIDH TO_NUMBER ('100") NGA DUAL do të kthejë numrin 100 SELECT TO_NUMBER (`0010.01", "9999D99") NGA DUAL do të kthehet 10.01;

ZGJIDH TO_NUMBER ("500,000", "999G999") NGA DUAL do të kthejë 500000.

Elementi RR në formatin e datës

Elementi i formatit të datës dhe kohës RR është i ngjashëm me elementin e formatit të datës dhe orës YY, por ofron fleksibilitet shtesë për ruajtjen e vlerave të datës në shekujt e tjerë. Elementi i formatit datatime RR ju lejon të ruani datat nga shekulli i 20-të deri në shekullin e 21-të duke specifikuar vetëm dy shifrat e fundit të vitit.

Nëse dy shifrat e fundit të vitit aktual janë nga 00 deri në 49, atëherë viti i kthyer ka të njëjtat dy shifrat e para si viti aktual.

Nëse dy shifrat e fundit të vitit aktual janë midis 50 dhe 99, atëherë 2 shifrat e para të vitit të kthyer janë 1 më të mëdha se 2 shifrat e para të vitit aktual.

Nëse dy shifrat e fundit të vitit aktual janë nga 00 deri në 49, atëherë 2 shifrat e para të vitit të kthyer janë 1 më pak se 2 shifrat e para të vitit aktual.

Nëse dy shifrat e fundit të vitit aktual janë midis 50 dhe 99, atëherë viti i kthyer ka të njëjtat dy shifrat e para si viti aktual.

Funksioni NVL

Funksioni NVL është zakonisht më i përdoruri. Funksioni merr dy parametra: NVL (expr1, exp2). 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ë exp2.

Shembull: Zgjidhni NVL (furnizuesi_qyteti, n / a ") nga furnitorët:

Deklarata SQL e mësipërme do të kthehet n / "nëse përmbahet fusha furnizuesi_qyteti vlerë zero... Përndryshe, ai do të kthejë vlerën furnizuesi_qyteti.

Një shembull tjetër i përdorimit të funksionit NVL në Oracle / PLSQL është:

zgjidhni furnizuesin_id, NVL (supplier_desc, provider_name) nga furnitorët.

Kjo deklaratë SQL do të kthehet furnizuesi_emri fushë nëse furnizuesi_përshk përmban një vlerë zero. Përndryshe do të kthehet furnizuesi_përshk.

Një shembull i fundit: përdorimi i funksionit NVL në Oracle / PLSQL është: zgjidhni NVL (komision, 0) nga shitjet;

Kjo deklaratë SQL ktheu 0 nëse komisioni fusha përmban një vlerë null. Përndryshe, do të kthehej komisioni fushë.

Konvertime NVL për lloje të ndryshme të dhënash

Funksioni NVL përdoret për të kthyer një vlerë të pacaktuar në një vlerë aktuale: NVL ( shprehja 1, shprehja 2), ku:

shprehje 1- Vlera origjinale ose e llogaritur, e cila mund të jetë e papërcaktuar.

shprehje2- Vlera që zëvendësohet me vlerën e papërcaktuar.

Shënim: Funksioni NVL mund të përdoret për të kthyer çdo lloj të dhënash, por rezultati do të jetë gjithmonë i njëjti lloj si shprehje 1.

Konvertoni NVL për tipe te ndryshme:

NUMRI - NVL (kolona numerike, 9).

CHAR ose VARCHAR2 - NVL (simbolet | kolona,"I padisponueshem").

Puna laboratorike. Aplikimi i funksionit NVL

Funksioni NVL për të punuar me vlera null në Oracle SQL.

Ushtrimi:

Shkruani një pyetje që shfaq emrin dhe mbiemrin e punonjësve nga hr.employees Tabela dhe norma e komisionit (kolona COMMISSION_PCT) për punonjësin. Në këtë rast, për ata punonjës për të cilët komisioni nuk është i përcaktuar, ju duhet të shfaqni vlerën 0. Rezultati i pyetjes duhet të jetë i njëjtë siç tregohet në fig. 3,5-1.

Oriz. 3.5 -1 (duke treguar vlerat duke filluar nga rreshti 51)

Zgjidhja:

Kodi përkatës i kërkesës mund të jetë si ky:

ZGJIDHni emrin e parë AS "Emri", mbiemri si "Mbiemri", NVL (COMMISSION_PCT, 0) Si "Norma e komisionit" FROM hr.employees.

Zgjedhjet janë në rrugë e sipër, që do të thotë se sot do të zgjedhim guvernatorin. Ose më mirë, të vendoset një pengesë për kalimin e kandidatëve në raundin tjetër. Por së pari ju duhet të përcaktoni termat.

Çfarë është zgjedhja e garantuar në SQL? Supozoni se në kushtin e pyetjes për tabelën, bëhet një krahasim midis një fushe dhe një ndryshoreje. Në varësi të vlerës së kësaj ndryshoreje, pyetja mund ose nuk mund të kthejë rreshta nga tabela. Nëse vlera e ndryshores bie që rreshtat nga tabela nuk kthehen, atëherë për këtë rast është e nevojshme të gjenerohet posaçërisht një rezultat i paracaktuar majtas. Kjo është, në çdo rast, kërkesa e përgjithshme duhet të garantohet për të kthyer diçka. Vetë termi është marrë nga këtu. Sidoqoftë, detyra është e ndërlikuar nga fakti (dhe mbase anasjelltas, thjeshtohet) që në vend të një qelize të thjeshtë me një vlerë, duhet të garantohemi të kthejmë një varg të plotë.

Po citoj të dhënat e Komisionit Qendror të Zgjedhjeve. Raundi i parë i votimit përfundoi me këto rezultate

krijoni zgjedhjen e tabelës si me t (id, emri, profesioni, votat) si (zgjidh 1, "I pa korruptueshëm Amoral Chistorukovich", "prokuror", 9867 nga bashkimi i dyfishtë të gjithë zgjidhni 2, "Buxheti efektiv Osvoilovich", "biznesmen", 8650 nga bashkim i dyfishtë të gjithë të zgjedhur 3, "Pravdivy Chtodadut Wiszajlovich", "redaktor i gazetës", 745 nga bashkimi i dyfishtë, të gjithë të zgjedhur 4, "Lucifera Feoktistovna me pamje të bukur", "abbase", 234 nga bashkimi i dyfishtë, të gjithë 5 të zgjedhur, "Khrensgory Ktotakoy Niktoneznaevich", " mësues i shkollës rurale ", 3 nga dyfishtë) zgjidhni * nga t; alter tabela e zgjedhjes shtoj çelesi primar(id);
Pyetja fillestare që përcakton hyrjen e kandidatëve në raundin tjetër është jashtëzakonisht e thjeshtë:
zgjidhni * nga zgjedhja ku votat>: i lidhur
Le të supozojmë se pragu i kalueshmërisë është 8000 vota. Duke e kombinuar këtë numër me: të lidhur, marrim

Por, çka nëse pragu i kalimit është 10,000, që është më shumë se numri maksimal i votave të hedhura? Atëherë është e qartë se asnjë nga kandidatët e lartpërmendur nuk do të kualifikohet në raundin tjetër. Në këtë rast vendoset një diktaturë dhe koloneli mace bëhet automatikisht guvernator i rajonit. Këtu janë disa nga mënyrat për ta caktuar atë:

Metoda 1. UNION TË GJITHA tavolina me vetë-grumbullim

Me t si (zgjidh * nga zgjedhjet ku votat>: të lidhura) zgjidhni ID, emrin, profesionin, votat nga t union zgjidhni 0, "Kolonel", "cat", null nga t që ka numërim (*) = 0 renditje sipas votave përshkruaj

Metoda 2... UNION ALL tavolina me DUAL
me t si (zgjidh * nga zgjedhjet ku votat>: i lidhur) zgjidh id, emrin, profesionin, votat nga t union zgjidhni 0, "Kolonel", "cat", null nga dyfishi ku nuk ekziston (zgjidh null nga t) renditja me vota desk

Metoda 3. LEFT JOIN tavolina me DUAL
zgjidhni nvl (e.id, 0) id, nvl2 (e.id, e.emri, "Kolonel") emri, nvl2 (e.id, e.profession, "cat") profesioni, e.vota nga dyfishi d majtas bashkohu në zgjedhje e në e.vota>
për rastet kur tabela nuk ka një fushë unike NOT NULL
zgjidhni nvl2 (e.rowid, e.id, 0) id, nvl2 (e.rowid, e.name, "Colonel") emrin, nvl2 (e.rowid, e.profession, "cat") profesion, e.votes nga dyfishi d majtas bashkohu me zgjedhjet e mbi e.votat>: renditja e lidhur me e.votat desc

Metoda 4. Model me një mace.
zgjidhni ID, emrin, profesionin, votat nga zgjedhjet ku votat>: dimensioni i modelit të lidhur sipas (rownum rn) masat (id, emri, profesioni, votat) rregullat (emri = nvl2 (id, emri, "Kolonel"), profesioni = nvl2 (id, profesion, "mace"), id = nvl (id, 0)) rendit sipas votave desc

Më poshtë rezultat i garantuar me një lidhje me një pengesë shumë të madhe prej 10,000

Është e qartë se nëse vendosni nivelin fillestar prej 8000, atëherë edhe këto pyetje do të funksionojnë si duhet.

Kjo është e gjitha për tani. Emri i të gjithë personazheve përputhet me njerëz të vërtetë konsiderohet e rastësishme.

Zgjidhja për disa probleme të tjera tipike SQL mund të shihet

Kur hartoni pyetje sql, shpesh lind një situatë kur, kur merrni të dhëna, është e nevojshme të krahasoni vlerat e disa kolonave dhe të shfaqni atë që përmban të dhënat (jo bosh). Shprehja Coalesce e zgjidh këtë problem në mënyrë të përsosur. Në këtë artikull do të gjeni përshkrim i plotë Bashkoni shprehjet sql, detajet e përdorimit dhe shembujt.

Dalja e vlerave jo boshe me Coalesce. Veçoritë

Merrni parasysh veçoritë e përdorimit të Sql Coalesce:

1. Lejon specifikimin e çdo numri argumentesh (ndryshe nga Isnull / Nvl / Nvl2, të cilat kanë një numër të kufizuar argumentesh).
2. Mund të marrë nënpyetje si argument.
3. Rikthen një rezultat të barabartë me i pari një vlerë të ndryshme nga Null, ose Null nëse është e ndryshme nga Vlerat zero nuk do të gjendet.
4. Sql Coalesce mund të përdoret në një klauzolë Select për të zgjedhur një vlerë jo bosh, dhe gjithashtu në Ku të sqarohet se një grup kolonash me vlerat boshe nuk lejohet (/ lejohet).
5. Kjo shprehje është e njëjtë me aplikimin e shprehjes Case, e cila teston çdo argument në mënyrë sekuenciale kundrejt kushtit Kur argumenti1 nuk është null atëherë argumenti1. Në thelb, Coalesce është një shkurtore për lehtësinë e përdorimit, dhe në shumë DBMS, optimizuesit e pyetjeve rishkruajnë shprehjen Coalesce në Case.
6. Funksionet Sql Coalesce janë të disponueshme në të gjitha relacionalet kryesore

Sintaksa e bashkimit

Kushdo që ka përdorur ndonjëherë Coalesce kur shkruan pyetje sql e di se sintaksa të kësaj shprehjeje jashtëzakonisht e thjeshtë. Mjaft në kllapa specifikoni argumentet e testuara për Null, të ndara me presje. Nëse supozojmë se argumentet emërtohen arg1, arg2, ... argN, atëherë sintaksa për Coalesce do të jetë si më poshtë:

Bashkohen (arg1, arg2, ... argN).

Le të përgatisim disa tabela për të studiuar mekanizmin e kësaj shprehjeje.

Përgatitja e tabelave

Për të kuptuar më mirë Coalesce përshkrimi sql, le të krijojmë dy tabela në bazën e të dhënave që përmbajnë informacion mbi objektet e pasurive të paluajtshme.

Tabela e parë, Zona, le të përmbajë emrat e objekteve të pasurive të paluajtshme dhe sipërfaqen e tyre. Zona mund të specifikohet (area_yt) ose të deklarohet (area_decl).

Tabela e dytë Bazë_karakteristike le të përmbajë informacion mbi karakteristikat kryesore të pronës - Zgjerimi, Thellësia, Sipërfaqja, Shtrirja, Lartësia.

Ne shqyrtuam sintaksën e Coalesce sql, përshkrimin, veçoritë e përdorimit dhe le të kalojmë drejtpërdrejt te shembujt.

Shembuj të përdorimit

Sintaksa për shprehjen Coalesce është jashtëzakonisht e thjeshtë, por është e rëndësishme të mbani mend se komanda do të kthejë vlerën e PARË jo bosh të gjetur nga lista e argumenteve. Kjo vërejtje ka një shumë rëndësi të madhe prandaj edhe argumentet në shprehje duhet të renditen sipas rëndësisë. Mënyra më e lehtë për të kuptuar parimin është nga tabela e zonës. Bëni një pyetje që zgjedh emrin e pronës, si dhe vlerën e zonës:

Dhe merrni rezultatin:

Për objektet "Ndërtesa 1", "Trua 1" dhe "Ndërtimi 2" janë plotësuar të dyja vlerat e sipërfaqes, por sipërfaqja është përcaktuar me përparësi, pasi e kemi shënuar të parën në listën e argumenteve. Shprehja Coalesce gjeti vlerën e parë jo boshe dhe e hodhi atë, duke ndaluar skanimet e mëtejshme të argumenteve. Ky konstruksion kërkesa është e saktë, sepse zona e specifikuar është më e caktuar se ajo e deklaruar. Nëse do të kishim specifikuar zonën e deklaruar si argument të parë, atëherë kur të plotësohej kjo fushë e tabelës, do të kishte përparësi.

Përveç përdorimit në Select, shumë shpesh shprehja Coalesce përdoret me ku kusht... Kjo ju lejon të shkëputni nga rezultati ato rreshta në të cilat vlerat e listës së fushave janë bosh (ose anasjelltas, përfshini në rezultat vetëm ato vlera ku lista e fushave është bosh). Kjo situatë ndodh kudo: për shembull, në ndërmarrje, kur regjistroni një punonjës të ri, vetëm informacioni bazë rreth tij u fut në bazën e të dhënave dhe plotësimi informacion i detajuar lënë "për më vonë". Gradualisht, shfaqen "boshllëqe" - ose para kontrollit, ose kur dërgoni një punonjës me pushime / udhëtim pune / pushim mjekësor.

Le të zgjedhim nga tabela me karakteristikat kryesore objektet e pasurive të paluajtshme për të cilat nuk plotësohet asnjë nga vlerat karakteristike:

Shpresojmë që përshkrimi ynë i detajuar i Coalesce sql ju ka ndihmuar të kuptoni të gjitha tiparet e përdorimit të kësaj shprehjeje, si dhe të merreni me nuanca të rëndësishme.