搜索
Oracle9i:ProgramwithPL/SQLAdditionalPractices40054GC11Production1.1October2001D34006Copyright©OracleCorporation,1999,2000,2001.Allrightsreserved.ThisdocumentationcontainsproprietaryinformationofOracleCorporation.Itisprovidedunderalicenseagreementcontainingrestrictionsonuseanddisclosureandisalsoprotectedbycopyrightlaw.Reverseengineeringofthesoftwareisprohibited.IfthisdocumentationisdeliveredtoaU.S.GovernmentAgencyoftheDepartmentofDefense,thenitisdeliveredwithRestrictedRightsandthefollowinglegendisapplicable:RestrictedRightsLegendUse,duplicationordisclosurebytheGovernmentissubjecttorestrictionsforcommercialcomputersoftwareandshallbedeemedtobeRestrictedRightssoftwareunderFederallaw,assetforthinsubparagraph(c)(1)(ii)ofDFARS252.227-7013,RightsinTechnicalDataandComputerSoftware(October1988).ThismaterialoranyportionofitmaynotbecopiedinanyformorbyanymeanswithouttheexpresspriorwrittenpermissionofOracleCorporation.Anyothercopyingisaviolationofcopyrightlawandmayresultinciviland/orcriminalpenalties.IfthisdocumentationisdeliveredtoaU.S.GovernmentAgencynotwithintheDepartmentofDefense,thenitisdeliveredwith“RestrictedRights,”asdefinedinFAR52.227-14,RightsinData-General,includingAlternateIII(June1987).Theinformationinthisdocumentissubjecttochangewithoutnotice.Ifyoufindanyproblemsinthedocumentation,pleasereporttheminwritingtoEducationProducts,OracleCorporation,500OracleParkway,BoxSB-6,RedwoodShores,CA94065.OracleCorporationdoesnotwarrantthatthisdocumentiserror-free.AllreferencestoOracleandOracleproductsaretrademarksorregisteredtrademarksofOracleCorporation.Allotherproductsorcompanynamesareusedforidentificationpurposesonly,andmaybetrademarksoftheirrespectiveowners.AuthorsNagavalliPataballaPriyaNathanTechnicalContributorsandReviewersAnnaAtkinsonBryanRobertsCarolinePeredaCesljasZarcoColeyWilliamDanielGabelDr.ChristophBurandtHakanLindforsHelenRobertsonJohnHoffLachlanWilliamsLaszloCzinkoczkiLauraPezziniLindaBoldtMarcoVerbeekNatarajanSenthilPriyaVennapusaRogerAbuzalafRuedigerSteffanSarahJonesStefanLindbladSusanDeePublisherSherylDomingueAdditionalPracticesOracle9i:ProgramwithPL/SQL-AdditionalPractices-3AdditionalPracticesOverviewTheseadditionalpracticesareprovidedasasupplementtothecourseOracle9i:ProgramwithPL/SQL.Inthesepractices,youapplytheconceptsthatyoulearnedinOracle9i:ProgramwithPL/SQL.Theadditionalpracticescompriseoftwoparts:PartAprovidessupplementalpracticedeclaringvariables,writingexecutablestatements,interactingwiththeOracleserver,writingcontrolstructures,andworkingwithcompositedatatypes,cursorsandhandleexceptions.InpartA,youalsocreatestoredprocedures,functions,packages,andtriggers,andtousetheOracle-suppliedpackageswithiSQL*Plusasthedevelopmentenvironment.ThetablesusedinthisportionoftheadditionalpracticesincludeEMPLOYEES,JOBS,JOB_HISTORY,andDEPARTMENTS.PartBisacasestudywhichcanbecompletedattheendofthecourse.Thispartsupplementsthepracticesforcreatingandmanagingprogramunits.ThetablesusedinthecasestudyarebasedonavideodatabaseandcontaintheTITLE,TITLE_COPY,RENTAL,RESERVATION,andMEMBERtables.AnentityrelationshipdiagramisprovidedatthestartofpartAandpartB.Eachentityrelationshipdiagramdisplaysthetableentitiesandtheirrelationships.MoredetaileddefinitionsofthetablesandthedatacontainedineachofthetablesisprovidedintheappendixAdditionalPractices:TableDescriptionsandData.Oracle9i:ProgramwithPL/SQL-AdditionalPractices-4PartA:EntityRelationshipDiagramHumanResourcesOracle9i:ProgramwithPL/SQL-AdditionalPractices-5PartANote:Theseexercisescanbeusedforextrapracticewhendiscussinghowtodeclarevariablesandwriteexecutablestatements.1.Evaluateeachofthefollowingdeclarations.Determinewhichofthemarenotlegalandexplainwhy.a.DECLAREv_name,v_deptVARCHAR2(14);b.DECLAREv_testNUMBER(5);c.DECLAREV_MAXSALARYNUMBER(7,2)=5000;d.DECLAREV_JOINDATEBOOLEAN:=SYSDATE;2.Ineachofthefollowingassignments,determinethedatatypeoftheresultingexpression.a.v_email:=v_firstname||to_char(v_empno);b.v_confirm:=to_date('20-JAN-1999','DD-MON-YYYY');c.v_sal:=(1000*12)+500d.v_test:=FALSE;e.v_temp:=v_temp1(v_temp2/3);f.v_var:=sysdate;Oracle9i:ProgramwithPL/SQL-AdditionalPractices-6PartA3.DECLAREv_custidNUMBER(4):=1600;v_custnameVARCHAR2(300):='WomenSportsClub';v_new_custidNUMBER(3):=500;BEGINDECLAREv_custidNUMBER(4):=0;v_custnameVARCHAR2(300):='ShapeupSportsClub';v_new_custidNUMBER(3):=300;v_new_custnameVARCHAR2(300):='JansportsClub';BEGINv_custid:=v_new_custid;v_custname:=v_custname||''||v_new_custname;END;v_custid:=(v_custid*12)/10;END;/EvaluatethePL/SQLblockaboveanddeterminethedatatypeandvalueofeachofthefollowingvariablesaccordingtotherulesofscoping:a.ThevalueofV_CUSTIDatposition1is:b.ThevalueofV_CUSTNAMEatposition1is:c.ThevalueofV_NEW_CUSTIDatposition2is:d.ThevalueofV_NEW_CUSTNAMEatposition1is:e.ThevalueofV_CUSTIDatposition2is:f.ThevalueofV_CUSTNAMEatposition2is:Note:TheseexercisescanbeusedforextrapracticewhendiscussinghowtointeractwiththeOracleserverandwritecontrolstructures.4.WriteaPL/SQLblocktoacceptayearandcheckwhetheritisaleapyear.Forexample,iftheyearenteredis1990,theoutputshou