Oracle查询层次结构数据Word格式文档下载.docx
《Oracle查询层次结构数据Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《Oracle查询层次结构数据Word格式文档下载.docx（14页珍藏版）》请在冰豆网上搜索。
（1）youcanstoreandmanipulatetimesresolvedtothenearest
billionth
ofasecond（9decimalplacesofprecision）,and
（2）youcanassociateatimezonewithatimestamp,andOracleDatabasewilltakethattimezoneintoaccountwhenmanipulatingthetimestamp.
∙INTERVAL—WhereasDATEandTIMESTAMPrecordaspecificpointintime,INTERVALrecordsandcomputesatime
duration.Youcanspecifyanintervalintermsofyearsandmonths,ordaysandseconds.
Listing1includesexamplevariableswhosedeclarationisbasedonthesedatatypes.
CodeListing1:
DeclaringDATE,TIMESTAMP,andINTERVALvariables
DECLARE
l_today_dateDATE:
=SYSDATE;
l_today_timestampTIMESTAMP:
=SYSTIMESTAMP;
l_today_timetzoneTIMESTAMPWITHTIMEZONE:
l_interval1INTERVALYEAR（4）TOMONTH:
='
2011-11'
;
l_interval2INTERVALDAY
（2）TOSECOND:
1500:
30:
44'
BEGIN
null;
END;
Workingwithintervalsandtimestampswithtimezonescanbeverycomplicated;
relativelyfewdeveloperswillneedthesemoreadvancedfeatures.ThisarticlefocusesonthecoreDATEandTIMESTAMPtypes,alongwiththemostcommonlyusedbuilt-infunctions.
Choosingadatatype.
Withsuchanabundanceofriches,howdoyoudecidewhichofthesedate-and-timedatatypestouse?
Herearesomeguidelines:
∙UseoneoftheTIMESTAMPtypesifyouneedtotracktimedowntoafractionofasecond.
∙Youcan,ingeneral,useTIMESTAMPinplaceofDATE.Atimestampthatdoesnotcontainsubsecondprecisiontakesup7bytesofstorage,justasaDATEdatatypedoes.Whenyourtimestampdoescontainsubseconddata,ittakesup11bytesofstorage.
∙UseTIMESTAMPWITHTIMEZONEifyouneedtokeeptrackofthesessiontimezoneinwhichthedatawasentered.
∙UseTIMESTAMPWITHLOCALTIMEZONEifyouwantthedatabasetoautomaticallyconvertatimebetweenthedatabaseandsessiontimezones.
∙UseDATEwhenit’snecessarytomaintaincompatibilitywithanexistingapplicationwrittenbeforeanyoftheTIMESTAMPdatatypeswereintroduced.
∙UsedatatypesinyourPL/SQLcodethatcorrespondto,orareatleastcompatiblewith,theunderlyingdatabasetables.Thinktwice,forexample,beforereadingaTIMESTAMPvaluefromatableintoaDATEvariable,becauseyoumightloseinformation（inthiscase,thefractionalsecondsandperhapsthetimezone）.
Gettingthecurrentdateandtime.
PL/SQLdevelopersoftenneedtoretrieveandworkwiththecurrentdateandtime.MostdevelopersusetheclassicSYSDATEfunction,butOracleDatabasenowoffersseveralfunctionstoprovidevariationsofthisinformation,asshowninTable1.
Function
TimeZone
DatatypeReturned
CURRENT_DATE
Session
DATE
CURRENT_TIMESTAMP
TIMESTAMPWITHTIMEZONE
LOCALTIMESTAMP
TIMESTAMP
SYSDATE
Databaseserver
SYSTIMESTAMP
Table1:
SYSDATEandotheroptionsforworkingwiththecurrentdateandtime
Listing2displaysthevaluesreturnedbycallstoSYSDATEandSYSTIMESTAMP.
CodeListing2:
CallstoSYSDATEandSYSTIMESTAMPandthereturnedvalues
DBMS_OUTPUT.put_line（SYSDATE）;
DBMS_OUTPUT.put_line（SYSTIMESTAMP）;
DBMS_OUTPUT.put_line（SYSDATE-SYSTIMESTAMP）;
/
Hereistheoutput:
07-AUG-11
07-AUG-1108.46.16.379000000AM-05:
00
-00000000000:
00:
00.379000000
BecauseIhavepasseddatesandtimestampstoDBMS_OUTPUT.PUT_LINE,OracleDatabaseimplicitlyconvertsthemtostrings,usingthedefaultformatmasksforthedatabaseorthesession（asspecifiedbytheNationalLanguageSettingsNLS_DATE_FORMATparameter）.AdefaultinstallationofOracleDatabasesetsthedefaultDATEformattoDD-MON-YYYY.ThedefaultTIMESTAMPformatincludesboththedateoffsetandthetimezoneoffset.
Notethatitispossibletoperformdatearithmetic:
IsubtractthevaluereturnedbySYSTIMESTAMPfromthevaluereturnedbySYSDATE.Theresultisan
interval
thatis
veryclose（butnotquiteequal）tozero.
Convertingdatestostringsandstringstodates.
AswithTO_CHARfornumbers,youuseanotherversionoftheTO_CHARfunctiontoconvertadateoratimestamptoastring.And,againaswithnumbers,OracleDatabaseoffersalargesetofformatelementstohelpyoutweakthatstringsoitappearsexactlyasyouneedit.Herearesomeexamples:
1.UseTO_CHARwithoutaformatmask.Ifyoudonotincludeaformatmask,thestringreturnedbyTO_CHARwillbethesameasthatreturnedwhenOracleDatabaseperformsanimplicitconversion:
DBMS_OUTPUT.put_line（
TO_CHAR（SYSDATE））;
TO_CHAR（SYSTIMESTAMP））;
/
07-AUG-1108.55.00.470000000AM-05:
2.UseTO_CHARtodisplaythefullnamesofboththedayandthemonthinthedate:
TO_CHAR（SYSDATE,
'
Day,DDthMonthYYYY'
））;
Sunday,07THAugust2011
Note:
ThelanguageusedtodisplaythesenamesisdeterminedbytheNLS_DATE_LANGUAGEsetting,whichcanalsobespecifiedasthethirdargumentinthecalltoTO_CHAR,asin
TO_CHAR（SYSDATE,
NLS_DATE_LANGUAGE=Spanish'
Domingo,07THAgosto2011
3.
AnswerstotheChallenge
HerearetheanswerstothePL/SQLChallengequestionsinlastissue’s“WorkingwithNumbersinPL/SQL”article:
Answer1:
Theplch_ceil_and_floorfunctionalwaysreturnseither1or0:
0ifthenumberpassedtothefunctionisaninteger,1otherwise.
Answer2:
（a）and（b）arecorrect;
（c）isincorrect.
Forfullexplanationsofbothoftheseanswers,,registerorlogin,andclicktheClosed/Taken
tabinPlayaQuiz,orgotobit.ly/r1SwvP.
4.UseTO_CHARtodisplaythefullnamesofboththedayandthemonthinthedate—butwithoutallthoseextraspacesinthedate-as-string.OracleDatabase,bydefault,padsthestringwithspacestomatchthemaximumlengthofthedayorthemonth.Inmostsituations,youdon’twanttoincludethatextratext,andOracleDatabaseoffersaformatelementmodifier,FM,tocontrolblankandzeropadding.Inthefollowingblock,IprefixtheformatmaskwithFMandremovethe0（before7）andextraspacesafterAugust:
5.
6.BEGIN
7.DBMS_OUTPUT.put_line（
8.TO_CHAR（SYSDATE,
9.'
FMDay,DDthMonthYYYY'
10.END;
11./
12.Sunday,7THAugust2011
Youcanalsousetheformatmasktoextractjustaportionof,orinformationabout,thedate,asshowninthefollowingexamples:
1.Whatquarterisit?
2.TO_CHAR（SYSDATE,'
Q'
）
3.Whatisthedayoftheyear（1-366）fortoday’sdate?
4.TO_CHAR（SYSDATE,'
DDD'
5.Whatarethedate
andtime
ofaDATEvariable?
（Thisisaverycommonrequirement,becausethedefaultformatmaskforadatedoes
not
includethetimecomponent,whichmeansthataskingDBMS_OUTPUT.PUT_LINEtodisplayadateleavesoutthetime.）
YYYY-MM-DDHH24:
MI:
SS'
YoucanalsouseEXTRACTtoextractandreturnthevalueofaspecifiedelementofadate.Forexample
1.Whatyearisit?
EXTRACT（YEARFROMSYSDATE）
2.Whatisthedayfortoday’sdate?
EXTRACT（DAYFROMSYSDATE）
Toconvertastringtoadate,usetheTO_DATEortheTO_TIMESTAMPbuilt-infunction.ProvidethestringandOracleDatabasereturnsadateoratimestamp,usingthedefaultformatmaskforthesession:
l_dateDATE;
l_date:
=TO_DATE（'
12-JAN-2011'
）;
END;
Ifthestringyouprovidedoesnotmatchthedefaultformat,OracleDatabasewillraiseanexception:
January122011'
ORA-01858:
anon-numericcharacterwas
foundwhereanumericwasexpected
YoushouldnotassumethattheliteralvalueyouprovideinyourcalltoTO_DATEmatchesthedefaultformat.Whatiftheformatchangesovertime?
Instead,alwaysprovide
升级会员 