I tillegg til SQL nyttefunksjoner, inneholder Oracle innebygd funksjon bibliotek typen konvertering funksjoner. Det kan være situasjoner der spørsmålet forventer innspill i en bestemt datatype, men det får det på en annen datatype. I slike tilfeller, Oracle implisitt forsøker å omdanne uventet verdi til en kompatibel datatype som kan være substituert på plass og anvendelse kontinuitet ikke blir kompromittert. Type konvertering kan enten implisitt gjort av Oracle eller eksplisitt gjort av programmereren.
Implisitt datatypekonvertering verk basert på en matrise som viser Oracle støtte for intern type casting. I tillegg til disse reglene, tilbyr Oracle skriver konverteringsfunksjoner som kan brukes i spørringene for eksplisitt konvertering og formatering. Som et spørsmål om faktum, er det anbefalt å utføre eksplisitt konvertering i stedet for å stole på programvare intelligens. Selv implisitt konvertering fungerer godt, men for å eliminere skjevheter sjansene hvor dårlige innganger kan være vanskelig å typecast internt.
Implisitt Datatype Conversion
En VARCHAR2 eller røye verdi kan implisitt konverteres til NUMMER eller DATE typen verdi av Oracle. På samme måte kan et tall eller datatype verdien automatisk konvertert til tegndata av Oracle server. Merk at impicit interconversion skjer bare når tegnet representerer et gyldig nummer eller dato type verdi hhv.
For eksempel undersøke nedenfor SELECT spørringer. Begge søkene vil gi samme resultat fordi Oracle internt behandler 15 000 og "15000" som samme
Query-en
Velg ansatt_nr, FIRST_NAME, salaryFROM employeesWHERE lønn> 15000;.
Query-2
SELECT ansatt_nr, FIRST_NAME, salaryFROM employeesWHERE lønn> '15000';
Explicit Datatype Conversion
SQL konverteringsfunksjoner er på én rad, funksjoner som er i stand typecasting kolonneverdi, bokstavelig eller et uttrykk for. TO_CHAR, TO_NUMBER og TO_DATE er de tre funksjonene som utfører tverr modifisering av datatyper.
TO_CHAR funksjon
TO_CHAR funksjonen brukes til å typecast en numerisk eller dato innspill til karaktertype med et format modell ( valgfritt).
Syntax
TO_CHAR (tall1, [format], [nls_parameter])
For nummeret til tegnkonvertering, NLS parametere kan brukes til å angi desimaltall tegn, gruppearbeid separator, lokal valuta modell eller internasjonal valuta modell. Det er en valgfri spesifikasjonen - hvis ikke er tilgjengelig, vil økt nivå NLS innstillinger brukes. For dato til tegnkonvertering, kan NLS parameter brukes til å angi dag og månedsnavn, som er aktuelt.
Datoer kan formateres i flere formater etter konvertering til tegntyper som bruker TO_CHAR funksjon. Den TO_CHAR funksjonen brukes til å ha Oracle 11g visningsdatoer i et bestemt format. Format modeller er case sensitive og må vedlegges innen apostrof.
Tenk nedenfor SELECT spørringen. . Spørringen format de HIRE_DATE og lønn kolonner av ANSATTE tabellen ved hjelp TO_CHAR funksjon
SELECT FIRST_NAME, TO_CHAR (hire_date, 'MÅNED DD, ÅÅÅÅ') HIRE_DATE, TO_CHAR (lønn, '$ 99999,99') SalaryFROM employeesWHERE rownum <5; FIRST_NAME HIRE_DATE LØNN -------------------- ------------------ ---------- Steven 17 juni 2003 $ 24000.00Neena 21 september 2005 $ 17000.00Lex 13 januar 2001 $ 17000.00Alexander 3 januar 2006 $ 9000,00
Den første TO_CHAR brukes til å konvertere leie dato til dato format MÅNED DD, ÅÅÅÅ dvs. måned stavet ut og polstret med mellomrom, etterfulgt av to-sifret dagen i måneden, og deretter de fire-sifret år. Hvis du foretrekker å vise måned navnet i mixed saken (det vil si "Skuffet"), kan du bruke denne saken i formatet argument:. ('Month DD, ÅÅÅÅ')
Den andre TO_CHAR funksjon i Figur 10 -39 brukes til å formatere LØNN å vise pengesymbolet og to desimaler stillinger.
Oracle tilbyr omfattende sett av format modeller. Tabellen nedenfor viser listen over format modeller som kan brukes til å typecast dato og tallverdier som tegn med TO_CHAR.
Format Modell
, (komma) Den returnerer et komma i den angitte stilling. Du kan angi flere kommaer i en rekke format modell. Begrensninger: En komma element kan ikke begynne et tallformat modell. Et komma kan ikke vises til høyre for en desimal karakter eller periode i en rekke format modell. . (periode) Returnerer et desimaltegn, som er et punktum (.) i angitt posisjon. Begrensning: Du kan spesifisere bare én periode i en rekke format modell $ Returns verdi med en ledende dollar sign0Returns ledende nuller. Avkastning følgende zeros.9Returns verdi med det angitte antall sifre med en ledende plass hvis positiv eller med en ledende minus hvis negative. Ledende nuller er tomme, bortsett fra en nullverdi, som returnerer en null for heltall del av de faste punkt number.BReturns blanks for heltall del av et fast punkt nummer når heltallet del er null (uavhengig av "0" s i formatet modell) .CReturns i det angitte ISO valutasymbol (den nåværende verdien av NLS_ISO_CURRENCY parameter) .DReturns i den angitte posisjonen desimaltegnet karakter, som er nåverdien av NLS_NUMERIC_CHARACTER parameter. Standard er et punktum (.). Begrensning: Du kan angi bare én desimal karakter i et tallformat model.EEEReturns en verdi med i vitenskapelige notation.FMReturns en verdi uten innledende eller etterfølgende blanks.GReturns i angitt posisjon gruppen separator (nåverdien av den NLS_NUMERIC_CHARACTER parameter) . Du kan angi flere gruppe separatorer i en rekke format modell. Begrensning: En gruppe separator kan ikke vises til høyre for en desimal karakter eller perioden i et tallformat modelLReturns i angitt posisjon lokal valuta symbol (nåverdien av den NLS_CURRENCY parameter) .MIReturns negativ verdi med en etterfølgende minustegn (-) . Returnerer positiv verdi med en etterfølgende blank. Begrensning: Den MI format element kan bare vises i siste posisjon i en rekke format model.PRReturns negativ verdi i. Det kan bare vises i slutten av et tallformat model.RN, rmReturns en verdi som romertall i store bokstaver. Returnerer en verdi som romertall i små bokstaver. Verdien kan være et heltall mellom 1 og 3999.SReturns negativ verdi med en ledende eller etterfølgende minustegn (-). Avkastning positiv verdi med en ledende eller etterfølgende plusstegn (+). Begrensning: S format element kan bare vises i første eller siste posisjon i en rekke format model.TM "Tekst minimum". Avkastning (i desimal utgang) den minste antallet tegn mulige. Dette elementet er store eller små bokstaver. UReturns i angitt posisjon "Euro" (eller andre) dual valutasymbol (den nåværende verdien av NLS_DUAL_CURRENCY parameter) .VReturns en verdi multiplisert med 10n (og om nødvendig, runde det opp), der n er antall 9 gav etter "V" .XReturns den heksadesimale verdien av det angitte antall sifre.
TO_NUMBER funksjon
TO_NUMBER funksjonen konverterer et tegn verdi til en numerisk datatype. Hvis strengen blir konvertert inneholder ikke-numeriske tegn, returnerer en feil funksjonen.
Syntax
TO_NUMBER (streng1, [format], [nls_parameter])
Tabellen nedenfor viser listen over format modeller som kan brukes til å typecast tegn verdier som nummer hjelp TO_NUMBER
Format Modell
CCCenturySCCCentury BC prefikset -. YYYYYear med 4 tall SYYYYear BC prefiks - IYYYISO året med 4 tall YYYear med 2 tall RRYear med 2 tall med Y2k kompatibilitet YEARYear i tegn SYEARYear i karakterer, BC prefiks - BCBC /AD Indicator QQuarter i tall (1,2,3,4) MMMonth av året 01, 02 ... 12 MONTHMonth i tegn (dvs. januar ) MONJAN, Feb WWWeek nummer (dvs 1) wweek antall måneden (dvs. 5) IWWeek antall år i ISO-standarden. DDDDay av året i tall (dvs. 365) DDDay i måneden i tall (dvs. 28) DDay av uken i tall (dvs. 7) DAYDay i uken i tegn (dvs. mandag) FMDAYDay i uken i tegn (dvs. mandag) DYDay av uken i korte karakter beskrivelse (dvs. SUN) JJulian Day (antall dager siden 1. januar 4713 f.Kr., der 1 januar 4713 BC er en i Oracle) HH, H12Hour antall av dagen (1-12) HH24Hour antall dagen med 24hours notasjon (0-23) AM, PMAM eller PM MI, SSNumber minutter og sekunder (dvs. 59), SSSSSNumber sekunder denne dagen. DSShort datoformat. Avhenger av NLS-innstillinger. Bruk kun med tidsstempel. DLLong datoformat. Avhenger av NLS-innstillinger. Bruk kun med tidsstempel. EAbbreviated æra navn. Gjelder kun for kalendere: Japansk Imperial, ROC Offisielle, Thai Buddha.EEThe hel æra navn FFThe brøk sekunder. Brukes med tidsstempel. FF1..FF9The brøk sekunder. Brukes med tidsstempel. Tallet styrer antall desimaler som brukes for fractional seconds.FMFill Mode: undertrykker blanks i produksjonen fra konvertering FXFormat Exact: krever nøyaktige mønstertilpasnings mellom data og format modell. IYY ELLER IY ELLER IFant siste 3,2,1 sifrene i ISO standard året. Utgang onlyRMThe romertall representasjon av måneden (jeg .. XII) RRThe 2 siste sifrene i året. RRRRThe 2 siste sifrene i året når det brukes for utdata. Godtar fut-sifrede år når det brukes til input.SPSpelled format. Kan vises på slutten av en rekke element. Resultatet er alltid på engelsk. For eksempel måned 10 i format MMSP returnerer "ten" SPTHSpelled og ordens format; 1 resultater i første. THConverts et nummer til det er ordens format. For eksempel en becoms første. TSShort tidsformat. Avhenger av NLS-innstillinger. Bruk kun med tidsstempel. TZDAbbreviated tidssone navn. dvs. PST. TZH, TZMTime sone time /minutt fortrengning. TZRTime sone region XLocal Radix karakter. I Amerika er dette et punktum (.)
SELECT spørringer under godta tallene som tegn innganger og skriver dem etter format Specifier.
SELECT TO_NUMBER ('121,23', '9G999D99') Fra DUALTO_NUMBER ('121,23', '9G999D99') ------------------------------ 121.23SELECT TO_NUMBER ('1210,73', '9999,99') FROM DUAL; TO_NUMBER ('1210,73', '9999,99') ------------------------------ 1210,73
TO_DATE funksjon
Funksjonen tar tegn verdier som input og returnerer formatert dato tilsvarer det samme. Den TO_DATE funksjonen lar brukere å skrive inn en dato i alle formater, og da er det konverterer trer i standardformatet som brukes av Oracle 11g
Syntax.
TO_DATE (streng1, [format_mask], [nls_language] )
En format_mask argument består av en rekke elementer som representerer nøyaktig hva dataene skal se ut, og må legges inn i enkle anførselstegn.
Format Modell
YEARYear, spelt outYYYY4- sifret yearYYY, YY, YLast 3, 2 eller 1 siffer (r) av year.IYY, IY, ILast 3, 2 eller 1 siffer (e) av ISO year.IYYY4-sifret år basert på ISO standardRRRRAccepts en 2- sifret år og returnerer en fire-sifret year.QQuarter av året (1, 2, 3, 4; JAN-MAR = 1) .MMMonth (01-12; JAN = 01) .MONAbbreviated navnet month.MONTHName av måneden, polstret med blanks til lengde på 9 characters.RMRoman tallet måned (I-XII; JAN = I) .WWWeek av året (1-53) der uke 1 starter på den første dagen i året og fortsetter til den syvende dagen i året. wweek av måneden (1-5) der uke 1 starter på den første dagen i måneden og ender på seventh.IWWeek av året (1-52 eller 1-53) basert på ISO standard.DDay av uken (1-7 ) .DAYName av day.DDDay av måneden (1-31) .DDDDay av året (1-366) .DYAbbreviated navn day.JJulian dag; antall dager siden 1. januar 4712 BC.HH12Hour av dagen (1-12) .HH24Hour på dagen (0-23) .MI, SSMinute (0-59) .SSSSSSeconds forbi midnatt (0-86399) .FFFractional sekunder. Bruk en verdi fra 1 til 9 etter FF for å indikere antall sifre i brøk sekunder. For eksempel "FF4'.AM, PMMeridian indicatorAD, BCAD, BC indicatorTZDDaylight besparelser informasjon. For eksempel ". PST'TZH, TZM, TZRTime sone time /minutt /region
Følgende eksempel konverterer en tegnstreng til en date:
SELECT TO_DATE ('15 januar 1989, 11:00', 'Month dd, ÅÅÅÅ, TT: MI AM', 'NLS_DATE_LANGUAGE = American') FROM DUAL; TO_DATE ('--------- 15-JAN-89
Generelle funksjoner
Generelt funksjonene brukes til å håndtere NULL-verdier i databasen. Målet med de generelle NULL håndtering funksjoner er å erstatte NULL verdier med en alternativ verdi. Vi skal kort se gjennom disse funksjonene nedenfor.
NVL
Den NVL funksjonen erstatter en alternativ verdi for en NULL verdi
Syntax.
NVL (Arg1, replace_with)
I syntaks, begge parametrene er obligatoriske Merk at NVL funksjonen virker med alle. typer datatyper. Og også at datatypen opprinnelige strengen og utskifting må være kompatibel tilstand dvs. enten samme eller implisitt konvertible av Oracle.
Hvis arg1 er en tegnverdi, deretter oracle konverterer erstatning streng til datatype kompatibel med arg1 før sammenligne dem og returnerer VARCHAR2 i karakter sett expr1. Hvis arg1 er numerisk, da Oracle bestemmer argumentet med høyest numerisk forrang, implisitt konverterer den andre argument til at datatype, og returnerer den datatypen.
SELECT-setningen nedenfor vil vise 'n /a' hvis en arbeidstaker har ikke blitt tildelt noen jobb ennå dvs. JOB_ID er NULL. Ellers ville det vise den faktiske JOB_ID verdi
SELECT FIRST_NAME, NVL (JOB_ID, 'n /a') Fra ansatte;.
NVL2
Som en forbedring i løpet av NVL, Oracle innført en funksjon for å erstatning verdi ikke bare for NULL kolonner verdier, men også for NOT NULL kolonner. NVL2 funksjonen kan brukes til å erstatte en alternativ valuta for NULL så vel som ikke NULL verdi
Syntax.
NVL2 (streng1, value_if_NOT_null, value_if_null)
SELECT-setningen nedenfor vil vise "Bench ' hvis JOB_CODE for en ansatt er NULL. For et klart ikke nullverdi av jobbkode, ville det vise seg konstant verdi 'Job Assigned'
SQL> SELECT NVL2 (JOB_CODE, 'Job Assigned', 'Benk') Fra ansatte;.
NULLIF
< p> NULLIF funksjonen sammenligner to argumenter expr1 og expr2. Hvis expr1 og expr2 er like, returnerer den NULL; annet, returnerer den expr1. I motsetning til andre null håndtering funksjon, kan første argumentet ikke være NULL
Syntax.
NULLIF (expr1, expr2)
Legg merke til at første argument kan være et uttrykk som evalueres til NULL, men det kan ikke være den bokstave NULL. Begge parametrene er obligatoriske for funksjonen til å utføre
under søket returnerer NULL siden begge inngangsverdiene, 12 er like
VELG NULLIF (12, 12) Fra DUAL;..
På samme måte Nedenfor spør return 'sø' siden begge strengene er ikke lik
SELECT NULLIF ('sø', 'MOON') FROM DUAL;.
COALESCE
COALESCE funksjon, en mer generisk form av NVL returnerer den første ikke-null uttrykk i argumentlisten. Det tar minimum to obligatoriske parametre, men maksimalt argumenter har ingen grense.
Syntax:
COALESCE (expr1, expr2, ... expr_n)
Tenk nedenfor SELECT spørringen. Det velger det første ikke nullverdi matet inn adressefelt for en ansatt
VELG koaleserer (Adresse 1, Address2, address3) AddressFROM ansatte;.
Interessant, er arbeider av COALESCE funksjon som ligner IF..ELSIF..ENDIF konstruere. Spørringen ovenfor kan omskrevet som -
IF Adresse 1 er ikke null da resultere: = Adresse 1; ELSIF Address2 er ikke null da resultere: = Address2; ELSIF address3 er ikke null da resultere: = address3; ELSE resultat: = null; END IF;
Betinget Funksjoner
Oracle gir betinget funksjoner dekode og CASE å stille vilkår selv i SQL-setning
Den AVKODE funksjon
Funksjonen er. SQL likeverdighet av IF..THEN..ELSE betinget prosessuelle uttalelse. DEKODING fungerer med verdier /søyler /uttrykk av alle typer data
Syntax.
AVKODE (uttrykk, søk, resultere [, søk, resultatet] ... [, standard])
AVKODE funksjon sammen uttrykk mot hverandre søkeverdi i orden. Hvis likestilling eksisterer mellom uttrykk og søk argument, da den returnerer den tilsvarende resultat. Ved manglende kamp, er standardverdien tilbake, hvis definert, ellers NULL. Ved alle typer kompatibilitet mismatch, internt gjør oracle mulig implisitt konvertering å returnere resultatene.
Som et spørsmål om faktum, mener Oracle to nuller til å være tilsvarende mens du arbeider med AVKODE funksjon.
VELG AVKODE ( NULL, NULL, 'EQUAL', 'ikke lik') FROM DUAL; DECOD ----- EQUAL
Hvis uttrykket er null, da Oracle returnerer resultatet av det første søket som også er null. Det maksimale antall komponenter i AVKODE funksjonen er 255.
Velg FIRST_NAME, lønn, dekode (hire_date, SYSDATE, 'NEW JOINEE', 'ANSATTE') Fra ansatte;
CASE uttrykk
CASE uttrykk fungerer på samme konsept som AVKODE men skiller i syntaks og bruk
Syntax.
CASE [uttrykk] NÅR condition_1 SÅ result_1 NÅR condition_2 SÅ result_2 ... NÅR condition_n THEN result_n ELSE resultEND
Oracle Søket starter fra venstre og flytter mot høyre til den finner en ekte tilstand, og deretter fortsetter resultere uttrykk knyttet til den. Hvis ingen betingelse er funnet å være sant, og en ELSE klausulen eksisterer, da Oracle avkastning resultat definert med andre. Ellers Oracle returnerer null.
Det maksimale antall argumenter i et CASE-uttrykk er 255. Alle uttrykk telle mot denne grensen, inkludert den første uttrykk for en enkel CASE-uttrykk og den valgfrie ELSE uttrykk. Hver NÅR ... SÅ pare teller som to argumenter. For å unngå å overskride denne grensen, kan du reir CASE-uttrykk slik at return_expr seg selv er et CASE-uttrykk.
SELECT FIRST_NAME, CASE NÅR lønn <200 THEN 'GRADE en' NÅR lønn> 200 og lønn <5000 THEN 'GRADE 2' ELSE 'GRADE 3' END CASEFROM ansatte; ENAM CASE ---- ------- JOHN GRADE 2EDWIN GRADE 3KING GRADE en