Oracle查询层次结构数据.docx

Oracle查询层次结构数据.docx

《Oracle查询层次结构数据.docx》由会员分享，可在线阅读，更多相关《Oracle查询层次结构数据.docx（14页珍藏版）》请在冰豆网上搜索。

Oracle查询层次结构数据

在OracleDatabase11g中查询层次结构数据

ThepreviousarticlesinthisintroductoryPL/SQLseriesfocusedonworkingwithstringsandnumbersinPL/SQL-basedapplications.Withoutadoubt,stringsandnumbersareimportant,butitiscertainlyaveryrareapplicationthatdoesnotalsorelyondates.Youneedtokeeptrackofwheneventsoccurred,whenpeoplewereborn,andmuchmore.

Asaresult,youwillquiteoftenneedto 

∙Declarevariablesandconstantsfordates

∙Usebuilt-infunctionstodisplayandmodifydatevalues

∙Performcomputationsondates 

Adateisalsoaconsiderablymorecomplexdatatypethanastringoranumber.Ithasmultipleparts（year,month,day,hour,andsoon）,andtherearemanyrulesaboutwhatconstitutesavaliddate.ThisarticlegivesyoualltheinformationyouneedinordertobeginworkingwithdatesinyourPL/SQLprograms.

Dates,TimeStamps,andIntervalsinPL/SQL

Mostapplicationsrequirethestorageandmanipulationofdatesandtimes.Unlikestringsandnumbers,datesarequitecomplicated:

notonlyaretheyhighlyformatteddata,buttherearealsomanyrulesfordeterminingvalidvaluesandvalidcalculations（leapdaysandyears,daylightsavingtimechanges,nationalandcompanyholidays,dateranges,andsoon）.

Fortunately,OracleDatabaseandPL/SQLprovideasetoftruedateandtimedatatypesthatstorebothdateandtimeinformationinastandardinternalformat,andtheyalsohaveanextensivesetofbuilt-infunctionsformanipulatingthedateandtime.

Therearethreedatatypesyoucanusetoworkwithdatesandtimes:

∙DATE—Thisdatatypestoresadateandatime,resolvedtothesecond.Itdoesnotincludethetimezone.DATEistheoldestandmostcommonlyuseddatatypeforworkingwithdatesinOracleapplications.

∙TIMESTAMP—Timestampsaresimilartodates,butwiththesetwokeydistinctions:

（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:

=SYSTIMESTAMP;

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

Session

TIMESTAMPWITHTIMEZONE

LOCALTIMESTAMP

Session

TIMESTAMP

SYSDATE

Databaseserver

DATE

SYSTIMESTAMP

Databaseserver

TIMESTAMPWITHTIMEZONE

Table1:

 SYSDATEandotheroptionsforworkingwiththecurrentdateandtime

Listing2displaysthevaluesreturnedbycallstoSYSDATEandSYSTIMESTAMP.

CodeListing2:

 CallstoSYSDATEandSYSTIMESTAMPandthereturnedvalues

BEGIN

DBMS_OUTPUT.put_line（SYSDATE）;

DBMS_OUTPUT.put_line（SYSTIMESTAMP）;

DBMS_OUTPUT.put_line（SYSDATE-SYSTIMESTAMP）;

END;

/

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:

BEGIN

DBMS_OUTPUT.put_line（

TO_CHAR（SYSDATE））;

DBMS_OUTPUT.put_line（

TO_CHAR（SYSTIMESTAMP））;

END;

/

07-AUG-11

07-AUG-1108.55.00.470000000AM-05:

00

2.UseTO_CHARtodisplaythefullnamesofboththedayandthemonthinthedate:

BEGIN

DBMS_OUTPUT.put_line（

TO_CHAR（SYSDATE,

'Day,DDthMonthYYYY'））;

END;

/

Sunday,07THAugust2011

Note:

ThelanguageusedtodisplaythesenamesisdeterminedbytheNLS_DATE_LANGUAGEsetting,whichcanalsobespecifiedasthethirdargumentinthecalltoTO_CHAR,asin

BEGIN

DBMS_OUTPUT.put_line（

TO_CHAR（SYSDATE,

'Day,DDthMonthYYYY',

'NLS_DATE_LANGUAGE=Spanish'））;

END;

/

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.） 

BEGIN

DBMS_OUTPUT.put_line（

TO_CHAR（SYSDATE,

'YYYY-MM-DDHH24:

MI:

SS'））;

END;

/

YoucanalsouseEXTRACTtoextractandreturnthevalueofaspecifiedelementofadate.Forexample

1.Whatyearisit?

EXTRACT（YEARFROMSYSDATE）

2.Whatisthedayfortoday’sdate?

EXTRACT（DAYFROMSYSDATE）

Toconvertastringtoadate,usetheTO_DATEortheTO_TIMESTAMPbuilt-infunction.ProvidethestringandOracleDatabasereturnsadateoratimestamp,usingthedefaultformatmaskforthesession:

DECLARE

l_dateDATE;

BEGIN

l_date:

=TO_DATE（'12-JAN-2011'）;

END;

Ifthestringyouprovidedoesnotmatchthedefaultformat,OracleDatabasewillraiseanexception:

DECLARE

l_dateDATE;

BEGIN

l_date:

=TO_DATE（'January122011'）;

END;

/

ORA-01858:

anon-numericcharacterwas

foundwhereanumericwasexpected

YoushouldnotassumethattheliteralvalueyouprovideinyourcalltoTO_DATEmatchesthedefaultformat.Whatiftheformatchangesovertime?

Instead,alwaysprovide