计算机类英文资料翻译

英文资料翻译系部名称软件与服务外包学院专业软件外包班级软外0902学生姓名董彦孝学号100090823.指导教师孙振亚.2012年4月1SQLserverUser-definedFunctionsAuser-definedfunction(UDF)isapreparedcodesegmentthatcanacceptparameters,processsomelogic,andthenreturnsomedata.AccordingtoSQLServerBooksOnline,UDFsinSQLServer™2000canacceptanywherefrom0to1024parameters,althoughImustconfessIhavenevertriedtopass1024parametersintoaUDF.AnotherkeycharacteristicofUDFsisthattheyreturnavalue.DependingonthetypeofUDF,thevaluecanbeusedbythecallingroutinetocontinueprocessingitsdata.Thus,ifaUDFreturnsasinglevalue(ascalarvalue),thecallingroutinecanusethatvalueanywhereastandardvariableoraliteralvaluecanbeused.IfaUDFreturnsarowset,thecallingroutinecanloopthroughtherowset,jointoit,orsimplyselectcolumnsfromit.Whilemostprogramminglanguageshavesupportedfunctionsforawhilenow,UDFswereonlyintroducedwithSQLServer2000.StoredproceduresandviewshavebeenavailableinSQLServermuchlongerthanUDFs,buteachoftheseobjectshastheirnicheinSQLServerdevelopment.StoredproceduresaregreatforprocessingcomplexSQLlogic,securingandcontrollingaccesstodata,andreturningarowsettoacallingroutinewhetherthatroutineisaVisualBasic®-basedprogramoranotherTransact-SQL(T-SQL)batch.Unlikeviews,storedproceduresarecompiled,makingthemidealcandidatestorepresentandprocessfrequentlyrunSQLstatements.Viewsaregreatforcontrollingaccesstodata,buttheydoitdifferentlythanstoredprocedures.ViewsarelimitedtoonlycertaincolumnsandrowsfromtheunderlyingSELECTstatementthatgeneratedtheview.ThusaviewisoftenusedtorepresentacommonlyusedSELECTstatementthatmayjoinseveraltables,employaWHEREclause,andexposespecificcolumns.ViewsareoftenfoundintheFROMclauseofaSQLstatementjoinedtoothertablesandviews.Attheircore,UDFsresemblebothviewsandstoredprocedures.Likeviews,UDFscanreturnarowsetthatcanbeusedinaJOIN.Therefore,whenaUDFreturnsarowsetandacceptsparameters,it'slikeastoredprocedurethatyoucanjointo,oraparameterizedview.But,asIwilldemonstrate,UDFscanbethisandmuchmore.2TherearetwomaintypesofUDFs:scalarvalue-returningUDFsandtablevalue-returningUDFs.WithintablevalueUDFsyou'llfindUDFsthatreturninlinetablesandmultistatementtables.InthefollowingsectionsI'lltakealookateach.Scalarvalue-returningUDFsaremostsimilartowhatmanyprogramminglanguagesrefertoasfunctions.Theyreturnasinglevalueconsistingofascalardatatypesuchasinteger,varchar(n),char(n),money,datetime,bit,andsoon.UDFscanalsoreturnuser-defineddatatypes(UDDTs)iftheyarebasedonascalardatatype.WithUDFsthatreturneitherinlineormultistatementtables,arowsetcanbereturnedviathetabledatatype.However,notalldatatypescanbereturnedfromUDFs.Forexample,aUDFcannotreturnavalueofanyofthesedatatypes:text,ntext,image,cursor,ortimestamp.Scalardatatype-returningUDFscanbeusedinvarioussituationstomakethecodemoremaintainable,reusable,andlesscomplex.ThiscanbeveryusefulwhenthesamesegmentofT-SQLcodeisusedinseveralplaces,perhapsbyseveralstoredproceduresandbatchSQLstatements.Forexample,let'ssayseveralpartsofanapplicationneedtofindwhetheraproductmustbereordered.Ineachoftheplacesthisisrequired,thecodecouldcheckthereorderlevelandcompareittotheunitsinstockplusthenumberofunitsonorder.However,sincethiscodeisusedinseveralplaces,aUDFcouldbeusedinsteadtoreducethecodeblocksandmakeiteasiertomaintainthisfunctionincaseiteverneedstochange.SuchaUDFmightlooksomethinglikethecodeinandcouldbecalledwiththefollowingSQLstatement:SELECTProductID,ReorderLevel,UnitsInStock,UnitsOnOrder,dbo.fnNeedToReorder(ReorderLevel,UnitsInStock,UnitsOnOrder)ASsNeedToReorderFROMProductsthefnNeedToReorderUDFperformsthecalculationandreturnstheappropriatevalue.ThiscouldhavebeenaccomplishedviaaCASEstatementinsidetheSELECT3clause,butthecodeismuchmorecompactwhenaUDFisusedinstead.Plusit'seasiertopropagatetootherplacesthatmayrequirethesamelogic.Assumingthatthereareseveralsectionsofanapplicationthatneedtodeterminewhethertoreorderproducts,theUDFinreallybecomesvaluableasitmakestheapplicationeasiertomaintainwhenthelogicchanges.Forexample,itdoesn'tmakealotofsensetoreorderaproductthathasbeendiscontinued.Thus,bychangingtheUDFinordertoaccountforthisbusinessrule,thelogicischangedinoneplace,andcanberunwiththefollowingcode:SELECTProductID,ReorderLevel,UnitsInStock,UnitsOnOrder,dbo.fnNeedToReorder(ReorderLevel,UnitsInStock,UnitsOnOrder,Discontinued)ASsNeedToReorderFROMProductsNoticethattheUDFiscalledusingthetwo-partnameofobjectownerandobjectname.Theobject'sownerisrequiredwhenusingaUDFthatreturnsascalardatatypevalue.Granted,byaddingthefourthparameter(Discontinued)totheUDF,alloftheplacesthatcalltheUDFmustalsobechanged.Foreasiermaintenance,IcouldrewritetheUDFtoretrievethedataitselfusingtheProductIDforeachrow,ThistechniqueiseasiertomaintainbecauseitdoesnotrequireanyofthecallingroutinestochangehowtheUDFiscalledwhenthelogicchanges—aslongasthedatacanbepulledinfromthecurrentProductstablerow.However,togainthismaintainabilitythereisaperformancetrade-off.TheUDFhastoretrievearowfromtheProductstableforeveryrowthatisreturnedfromthecallingroutine.SincethecallingroutineisretrievingeveryrowfromtheProductstablealready,ifthetabl