辽宁石油化工大学计算机与通信工程学院(3).ppt

1,数据库管理系统DatabaseManagementSystems,Chapter7DatabaseIntegrityandTransactions第七章数据库完整性和事务,2,DBMS,ProgrammingEnvironment,Createcode1Withinthequerysystem2Insandreports3Hostedinexternalprograms,,,,,Tables,sRETURNAmtEND,5,LookingUpData,CREATEFUNCTIONIncreaseSalaryEmpIDINTEGER,AmtCURRENCYRETURNSCURRENCYDECLARECURRENCYMaxAmount;BEGINSELECTMaxRaiseINTOMaxAmountFROMCompanyLimitsWHERELimitName‘Raise’;IFAmt50000THENRETURN-1--errorflagENDUPDATEEmployeeSETSalarySalaryAmtWHEREEmployeeIDEmpID;RETURNAmt;END,6,DataTriggerEvents,OracleadditionsTablesALTER,CREATE,DROPUserLOGOFF,LOGONDatabaseSERVERERROR,SHUTDOWN,STARTUP,INSERTDELETEUPDATE,BEFORE,AFTER,7,Statementv.RowTriggers,UPDATEEmployeeSETSalarySalary10000WHEREEmployeeID442OREmployeeID558,SQL,,time,BeforeUpdateOntable,,AfterUpdateOntable,,BeforeUpdateRow442,AfterUpdateRow442,UpdateRow442,,,,otherrows,Triggersforoveralltable,Triggersforeachrow,8,DataTriggerExample,CREATETRIGGERLogSalaryChangesAFTERUPDATEOFSalaryONEmployeeREFERENCINGOLDROWasoldrowNEWROWASnewrowFOREACHROWINSERTINTOSalaryChangesEmpID,ChangeDate,User,OldValue,NewValueVALUESnewrow.EmployeeID,CURRENT_TIMESTAMP,CURRENT_USER,oldrow.Salary,newrow.Salary;,9,CancelingDataChangesinTriggers,CREATETRIGGERTestDeletePresidentBEFOREDELETEONEmployeeREFERENCINGOLDROWASoldrowFOREACHROWWHENoldrow.Title‘President’SIGNAL_CANNOT_DELETE_PRES;,10,CascadingTriggers,SaleSaleID,SaleDate,,OrderItemOrderID,ItemID,Quantity,,OrderOrderID,OrderDate,,InventoryItemID,QOH,,SaleItemSaleID,ItemID,Quantity,,AFTERINSERTUPDATEInventorySETQOHQOH–newrow.Quantity,AFTERUPDATEWHENnewrow.QOH100000THENAddBonusEND,AFTERUPDATEOrINSERTIFnewrow.Bonus50000THENReduceBonusAddOptionsEND,AFTERUPDATEOrINSERTIFnewrow.Amount100000THENReduceSalaryEND,,,,,,,12,Transactions,Sometransactionsresultinmultiplechanges.Thesechangesmustallbecompletedsuccessfully,orthegroupmustfail.Protectionforhardwareandcommunicationfailures.examplebankcustomertransfersmoneyfromsavingsaccounttocheckingaccount.DecreasesavingsbalanceIncreasecheckingbalanceProblemifonetransactionandmachinecrashes.Possiblygiveusersachancetoreverse/undoatransaction.Perancegainbycutingtransactionsasablock.,SavingsAccountsInez5340.924340.92,CheckingAccountsInez1424.27,Transaction1.Subtract1000fromsavings.machinecrashes2.Add1000toChecking.moneydisappears,,1000,,13,DefiningTransactions,Thecomputerneedstobetoldwhichchangesmustbegroupedintoatransaction.Turnontransactionprocessing.Signifyatransactionstart.Signifytheend.SuccesssaveallchangesFailurecancelallchangesMustbesetinmodulecodeCommitRollback,14,SQLTransactionCode,CREATEFUNCTIONTransferMoneyAmountCurrency,AccountFromNumber,AccountToNumberRETURNSNUMBERcurBalanceCurrency;BEGINDECLAREHANDLERFORSQLEXCEPTIONBEGINROLLBACK;Return-2;--flagforcompletionerrorEND;STARTTRANSACTION;--optionalSELECTCurrentBalanceINTOcurBalanceFROMAccountsWHEREAccountIDAccountFrom;IFcurBalance0THENtestEndTRUE;RETURN0;ENDIF--keepacountertoavoidinfiniteloopsENDEND,24,ACIDTransactions,Atomicityallchangessucceedorfailtogether.Consistencyalldataremaininternallyconsistentwhencommittedandcanbidatedbyapplicationchecks.IsolationThesystemgiveseachtransactiontheperceptionthatitisrunninginisolation.Therearenoconcurrentaccessissues.DurabilityWhenatransactioniscommitted,allchangesarepermanentlysavedevenifthereisahardwareorsystemfailure.,25,SQL99/200 xIsolationLevels,READUNCOMMITTEDProblemmightreaddirtydatathatisrolledbackRestrictionnotallowedtosaveanydataREADCOMMITTEDProblemSecondtransactionmightchangeordeletedataRestrictionNeedoptimisticconcurrencyhandlingREPEATABLEREADProblemPhantomrowsSERIALIZABLEProvidessamelevelofcontrolasifalltransactionswererunsequentially.But,stillmightencounterlocksanddeadlocks,26,PhantomRows,SELECTSUMQOHFROMInventoryWHEREPriceBETWEEN10and20,UPDATEInventorySETPricePrice/2WHERE,,,,Includedinfirstquery,SELECTSUMQOHFROMInventoryWHEREPriceBETWEEN10and20,,,Additionalrowswillbeincludedinthesecondquery,27,GeneratedKeys,Createanorderforanewcustomer1CreatenewkeyforCustomerID2INSERTrowintoCustomer3CreatekeyfornewOrderID4INSERTrowintoOrder,CustomerTableCustomerID,Name,,OrderTableOrderID,CustomerID,,,,,28,stoGenerateKeys,TheDBMSgenerateskeyvaluesautomaticallywheneverarowisinsertedintoatable.Drawbackitistrickytogetthegeneratedvaluetouseitinasecondtable.Aseparatekeygeneratoriscalledbyaprogrammertocreateanewkeyforaspecifiedtable.Drawbackprogrammershavetowritecodetogenerateakeyforeverytableandeachrowinsertion.Overalldrawbacksneitherislikelytobetransportable.IfyouchangetheDBMS,youwillhavetorewritetheprocedurestogeneratekeys.,29,Auto-GeneratedKeys,CreateanorderforanewcustomerINSERTrowintoCustomerGetthekeyvaluethatwasgeneratedVerifythekeyvalueiscorrect.HowINSERTrowintoOrder,MajorproblemStep2requiresthattheDBMSreturnthekeyvaluethatwasmostrecentlygenerated.HowdoyouknowitistherightvalueWhathappensiftwotransactionsgeneratekeysatalmostthesametimeonthesametable,30,Key-GenerationRoutine,CreateanorderforanewcustomerGenerateakeyforCustomerIDINSERTrowintoCustomerGenerateakeyforOrderIDINSERTrowintoOrder,Thisensuresthatuniquekeysaregenerated,andthatyoucanusethekeysinmultipletablesbecauseyouknowthue.But,noneofitisautomatic.Italwaysrequiresproceduresandsometimesdatatriggers.,31,DatabaseCursors,PurposeTrackthroughtableorqueryonerowatatime.Datacursorisapointertoactiverow.WhyPerance.SQLcannotdoeverything.Complexcalculations.Comparemultiplerows.,YearSales1998104,3211999145,9982000276,0042001362,736,,,,,1998104,321,1999145,998,2000276,004,2001362,736,32,DatabaseCursorProgramStructure,DECLAREcursor1CURSORFORSELECTAccountBalanceFROMCustomer;sumAccount,balanceCurrency;SQLSTATEChar5;BEGINsumAccount0;OPENcursor1;WHILESQLSTATE‘00000’BEGINFETCHcursor1INTObalance;IFSQLSTATE‘00000’THENsumAccountsumAccountbalance;ENDIFENDCLOSEcursor1;--displaythesumAccountordoacalculationEND,33,CursorPositioningwithFETCH,DECLAREcursor2SCROLLCURSORFORSELECTOPENcursor2;FETCHLASTFROMcursor2INTOLoopFETCHPRIORFROMcursor2INTOEndloopCLOSEcursor2;,FETCHpositioningoptionsFETCHNEXTnextrowFETCHPRIORpriorrowFETCHFIRSTfirstrowFETCHLASTlastrowFETCHABSOLUTE5fifthrowFETCHRELATIVE-3back3rows,34,ProblemswithMultipleUsers,NameSalesAlice444,321Carl254,998Donna652,004Ed411,736,OriginalData,NameSalesAlice444,321Bob333,229Carl254,998Donna652,004Ed411,736,ModifiedData,Newrowisadded--whilecodeisrunning.,,TheSQLstandardcanpreventthisproblemwiththeINSENSITIVEoptionDECLAREcursor3INSENSITIVECURSORFOR,But,thisisanexpensiveapproach,becausetheDBMSusuallymakesacopyofthedata.Instead,avoidmovingbackwards.,35,ChangingDatawithCursors,DECLAREcursor1CURSORFORSELECTYear,Sales,GainFROMSalesTotalORDERBYYearFORUPDATEOFGain;priorSales,curYear,curSales,curGainBEGINpriorSales0;OPENcursor1;LoopFETCHcursor1INTOcurYear,curSales,curGainUPDATESalesTotalSETGainSales–priorSalesWHERECURRENTOFcursor1;priorSalescurSales;UntilendofrowsCLOSEcursor1;COMMIT;END,36,DynamicParameterizedCursorQueries,DECLAREcursor2CURSORFORSELECTItemID,Description,PriceFROMInventoryWHEREPricemaxPrice;maxPriceCurrency;BEGINmaxPrice--fromuserorotherqueryOPENcursor2;--runsquerywithcurrentvalueLoop--DosomethingwiththerowsretrievedUntilendofrowsCLOSEcursor2;END,Parametersenableyoutocontroltherowsretrieveddynamicallyfromwithintheprocedurecode.Thueisappliedwhenthecursorisopened.,37,Sally’sPetStoreInventory,Inventory1calculatethecurrentquantityonhandbytotalingallpurchasesandsaleseverytimethetotalisneeded.DrawbackperanceInventory2keeparunningbalanceintheinventorytableandupdateitwhenanitemispurchasedorsold.DrawbacktrickycodeAlso,youneedanadjustmentprocessfor“inventoryshrink”,38,InventoryQuantityOnHand,ItemIDDescriptionQuantityOnHandListPriceCategory,Merchandise,AdditemspurchasedSubtractitemssoldAdjustforshrink,,,SaleIDItemIDQuantitySalePrice,SaleItem,,39,InventoryEvents,Foranewsale,arowisaddedtotheSaleItemtable.Asaleoranitemcouldberemovedbecauseofaclericalerrororthecustomerchangeshisorhermind.ASaleItemrowwillbedeleted.Anitemcouldbereturned,orthequantitycouldbeadjustedbecauseofacountingerror.TheQuantityisupdatedintheSaleItemtable.Anitemisenteredincorrectly.ItemIDisupdatedintheSaleItemtable.,SaleIDItemIDQuantitySalePrice,SaleItem,Addarow.Deletearow.UpdateQuantity.UpdateItemID.,40,NewSaleInsertSaleItemRow,CREATETRIGGERNewSaleItemAFTERINSERTONSaleItemREFERENCINGNEWROWASnewrowFOREACHROWUPDATEMerchandiseSETQuantityOnHandQuantityOnHand–newrow.QuantityWHEREItemIDnewrow.ItemID;,41,DeleteSaleItemRow,CREATETRIGGERDeleteSaleItemAFTERDELETEONSaleItemREFERENCINGOLDROWASoldrowFOREACHROWUPDATEMerchandiseSETQuantityOnHandQuantityOnHandoldrow.QuantityWHEREItemIDoldrow.ItemID;,42,QuantityChangedEvent,CREATETRIGGERUpdateSaleItemAFTERUPDATEONSaleItemREFERENCINGOLDROWASoldrowNEWROWASnewrowFOREACHROWUPDATEMerchandiseSETQuantityOnHandQuantityOnHandoldrow.Quantity–newrow.QuantityWHEREItemIDoldrow.ItemID;,43,ItemIDorQuantityChangedEvent,CREATETRIGGERUpdateSaleItemAFTERUPDATEONSaleItemREFERENCINGOLDROWASoldrowNEWROWASnewrowFOREACHROWBEGINUPDATEMerchandiseSETQuantityOnHandQuantityOnHandoldRow.QuantityWHEREItemIDoldrow.ItemID;UPDATEMerchandiseSETQuantityOnHandQuantityOnHand–newRow.QuantityWHEREItemIDnewrow.ItemID;COMMIT;END,