计算机类英文资料翻译

英文资料翻译系部名称软件与服务外包学院专业软件外包班级软外0902学生姓名董彦孝学号100090823.指导教师孙振亚.2012年4月SQLserverUser-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.TherearetwomaintypesofUDFs: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)ASsNeedToReorderFROMProductsthefnNeedToReorderUDFperformsthecalculationandreturnstheappropriatevalue.ThiscouldhavebeenaccomplishedviaaCASEstatementinsidetheSELECTclause,butthecodeismuchmorecompactwhenaUDFisusedinstead.Plusit'seasiertopropagatetootherplacesthatmayrequirethesamelogic.Assumingthatthereareseveralsectionsofanapplicationthatneedtodeterminewhethertoreorderproducts,theUDFinreallybecomesvaluableasitmakestheapplicationeasiertomaintainwhenthelogicchanges.Forexample,itdoesn'tmakealotofsensetoreorderaproductthathasbeendiscontinued.Thus,bychangingtheUDFinordertoaccountforthisbusinessrule,thelogicischangedinoneplace,andcanberunwiththefollowingcode:SELECTProductID,ReorderLevel,UnitsInStock,UnitsOnOrder,dbo.fnNeedToReorder(ReorderLevel,UnitsInStock,UnitsOnOrder,Discontinued)ASsNeedToReorderFROMProductsNoticethattheUDFiscalledusingthetwo-partnameofobjectownerandobjectname.Theobject'sownerisrequiredwhenusingaUDFthatreturnsascalardatatypevalue.Granted,byaddingthefourthparameter(Discontinued)totheUDF,alloftheplacesthatcalltheUDFmustalsobechanged.Foreasiermaintenance,IcouldrewritetheUDFtoretrievethedataitselfusingtheProductIDforeachrow,ThistechniqueiseasiertomaintainbecauseitdoesnotrequireanyofthecallingroutinestochangehowtheUDFiscalledwhenthelogicchanges—aslongasthedatacanbepulledinfromthecurrentProductstablerow.However,togainthismaintainabilitythereisaperformancetrade-off.TheUDFhastoretrievearowfromtheProductstableforeveryrowthatisreturnedfromthecallingroutine.SincethecallingroutineisretrievingeveryrowfromtheProductstablealready,ifthetablehas77rows,thecodewillexecute77SELECTstatements,oneforeachrowreturnedfromthemainSELECTstatement.WhileeachSELECTisselectingbasedontheprimarykeyfield(ProductID)andhenceisveryfast,performancecanbeadverselyaffectedwhentherowsetisverylargeortheSELECTstatementislessefficient.ThecodeincanbecalledbyusingthefollowingSQLsnippet:SELECTProductID,ReorderLevel,UnitsInStock,UnitsOnOrder,dbo.fnNeedToReorder(ProductId)ASsNeedToReorderFROMProductsAnalternativetousingthisfunctioninaSELECTstatementistocreateacomputedcolumnintheProductstablecalledNeedToReorder.ThiscolumnwouldbedefinednotasadatatypebutasthereturnvalueofthefnNeedToReorderUDFfrom.Toaddthiscolumn,IcanaltertheProductstableasfollowstoindicatethatthecolumnshouldbecomputed:ALTERTABLEProductsADDNeedToReorderASdbo.fnNeedToReorder(ReorderLevel,UnitsInStock,UnitsOnOrder,Discontinued)Thiswaythecolumnisdefinedinthetableitself,butisautomaticallycalculatedusingtheUDF.CommonUDFsandNestingThusfarIhaveshownseveralwaystotacklethesameissueusingaUDFthatreturnsascalarvalue.ThereareotherusefulapplicationsofUDFsincludingfunctionsthatarenotreadilyavailableinT-SQL.Oneexampleisaspecializedformattingfunction.Forinstance,phonenumbersarecommonlystoredwithouttheirformattingcharactersinchar(10)columnsthatrepresenttheareacodeandphonenumber(assumingit'saUnitedStatesnumber).AUDFcouldbeusedtoretrievethephonenumberinaformattedstructure.Thus,retrievingandformattingaphonenumberisassimpleasthis:SELECTdbo.fnCOM_FormatTelephoneNumber('3335558888')AnycommonfunctioncanbecreatedusingthistechniquetoaugmenttheregimentoffunctionsavailableinSQLServer.AnotherexampleisafunctionthatformatsadatetotheMM/DD/YYYYformatwithleadingzeros:CREATEFUNCTIONfnCOM_StandardDate(@dtDateDATETIME)RETURNSVARCHAR(10)ASBEGINRETURNdbo.fnCOM_2Digits(CAST(MONTH(@dtDate)ASVARCHAR(2)))+'/'+dbo.fnCOM_2Digits(CAST(DAY(@dtDate)ASVARCHAR(2)))+'/'+CAST(YEAR(@dtDate)ASVARCHAR(4))ENDThefnCOM_StandardDateUDFacceptsadatetimevalueandreturnsavarchar(10)inaMM/DD/YYYYformat.Prettysimple,ofcourse,andifyourapplicationrequiresaparticularformatoften,thistechniquecouldmakeiteasiertomaintain.OnekeycomponenttonoticeinthepreviouscodeistheuseofanestedUDF.ThefnCOM_StandardDateUDFcallsthefnCOM_2DigitsUDF(showninthenextsample)twice,bothtimestoputaleadingzeroinfrontofdaysormonthslessthan10.CREATEFUNCTIONfnCOM_2Digits(@sValueVARCHAR(2))RETURNSVARCHAR(2)ASBEGINIF(LEN(@sValue)<2)SET@sValue='0'+@sValueRETURN@sValueENDUDFscanbenestedwithineachotheraslongastheinsideUDFiscreatedfirst.Onecatchwithnestingfunctionsisthatbuilt-infunctionsthatarenondeterministic,suchasthegetdatefunction,cannotbenestedinsideofanotherUDF(otherwise,aSQLServererrorisraised).Anondeterministicfunctionisonewhichmaynotreturnthesameresultwhencalledmultipletimeswithexactlythesameparameters.Thegetdatefunctionfallsintothiscategorysinceeverytimeitiscalled,itreturnsthenewcurrentdateandtime.Anothercommonlyusednondeterministicbuilt-infunctionistheNewIDfunction.ItisalsonondeterministicasitalwaysreturnsauniqueGUIDand,assuch,theNewIDfunctionisnotallowedtobenestedwithinaUDF.Table-valuedUDFsWithinthecategoryoftable-valuedUDFstherearetwosub-types:inlinetablevalue-returningUDFsandmultistatementtablevalue-returningUDFs.UDFsthatreturninlinetablesreturnarowsetviatheSQLServertabledatatype.TheyaredefinedwithasingleSELECTstatementmakingupthebodyofthefunction.Inlinetablevalue-returningUDFscannotcontainadditionalT-SQLlogicoutsideoftheSQLSELECTstatementthatdefinesthetableitwillreturn.However,theyaresimplertocreatethanUDFsthatreturnmultistatementtablessincetheydonothavetodefinetheexacttablestructuretobereturned.UDFsthatreturninlinetablesextrapolatethestructureoftherowsetfromtheSELECTstatementitself.Thus,thecolumnsthattheUDFwillreturnaredeterminedbythecolumnsintheSELECTlist.ThefollowingcodeshowsthefnGetEmployeesByCityUDF,whichacceptsacityandreturnsatablecontainingallemployees'firstname,lastname,andaddress:CREATEFUNCTIONfnGetEmployeesByCity(@sCityVARCHAR(30))RETURNSTABLEASRETURN(SELECTFirstName,LastName,AddressFROMEmployeesWHERECity=@sCity)GOThisinlinetablevalue-returningUDFcanbeselectedfromorevenjoinedtobecauseitreturnsarowsetviathetabledatatype,asshownhere:SELECT*FROMdbo.fnGetEmployeesByCity('seattle')NoticethattheUDFiscalledusingthetwo-partnameofobjectownerandobjectname.However,theobject'sownerisnotrequired(butisacceptable)whenusingaUDFthatreturnsatabledatatypevalue.Table-valuedUDFsarequiteflexibleinthattheycanbeusedlikeapreparedandparameterizedview(ifoneexisted).Intable-valuedUDFsyoucanuseparameters,achievetheperformanceofapreparedquery,andjoinorselectfromtheresultingrowset(ortableinthiscase).AlthoughthistypeofUDFiscompact,itisimportanttorememberthatifadditionallogicneedstobeaddedtotheUDF,itwillhavetobeconvertedtoamultistatementtablevalue-returningUDF.Also,inlinetablevalue-returningUDFscannothaveanORDERBYclauseontheSELECTstatementeither(unlessitisusedinconjunctionwiththeTOPclause).AUDFthatreturnsmultistatementtablesexplicitlydefinesthestructureofthetabletoreturn.ItdoessobydefiningthecolumnnamesanddatatypesrightintheRETURNSclause.Thusittakesabitmorecodetogetitsetupthananinlinetablevalue-returningUDF.However,ithasseveraladvantagesoverinlinetablevalue-returningUDFsincludingtheabilitytohousemorecomplicated,numerousT-SQLlogicblocks.Astheirnamesuggests,multistatementtablevalue-returningUDFsallowmultiplestatementstodefinetheUDF.Thusstatementssuchascontrolofflow,assignments,cursors,SELECTS,INSERTS,UPDATES,andDELETESareallowedandcanallexistinasingleUDF.So,asopposedtoUDFsthatreturninlinetables,theirmultistatementbrethrenarenotlimitedtoasingleSELECTstatementnoraretheyprohibitedfromorderingthereturningrowset.showshowtorewritetheinlinetablevalue-returningUDFfromthecodesnippetIjustshowedasamultistatementtablevalue-returningUDF.Thusthemultistatementtypecandoanythingtheinlinetypecando.AmorecomplicateduseofaUDFthatreturnsmultistatementtablescouldinvolveretrievingallemployeesbycity,butifnocustomersmatchthegivencitythenadummyrowisreturnedwheretheAddressfieldisfilledwith"Nomatchingemployeesfoundinthespecifiedcity,"asshownin.It'saWrapTherearesomeotherkeyfactorsthatcanhelpcreateapowerfulUDFofanytype,oneofwhichisrecursion.RecursionissupportedwithUDFssuchthataUDFcancallitselffromwithinitself.Basically,recursionisjustnestingaUDFexceptthattheUDFyouarenestingisthesameoneyouarein.Thiscanbeveryusefulincertainsituationsincludingwhenyou'recreatingaUDFthatmustcomputeafactorialorevaluateeachcharacterinastring.Thereisalimiteddepthof32levelsofrecursioninSQLServer2000,afterwhichanerrorisraised.ItisalsoimportanttopointoutthataUDFcanbeboundtotheschemaoftheunderlyingobjectstowhichitrefers.Todothis,theUDFmustbecreatedusingtheWITHSCHEMABINDINGclause.IftheUDFiscreatedthiswayandsomeoneattemptstoalteroneoftheunderlyingobjects'schemawithoutfirstremovingtheschemabinding,anerrorwillbegeneratedandraised.UsingthisoptionwillhelpyouensurethatnoUDFsbreakinadvertentlyduetochangesinanunderlyingobject'sschema.WhenevaluatingUDFsitisvitaltoconsiderthebalancebetweenperformanceandmaintainability.WhileUDFscanreducetheamountofcommoncode,beusedaspartofacommonfunctionlibrary,canpromoteshortercodeblocks,andaregenerallyeasiertomaintainthanadditionalversionsofthesameSQLlogic,itwouldberecklesstouseaUDFwithoutfirsttakingintoconsiderationanyofthedrawbacks.ItwouldbeabadideatouseaUDFifperformancesufferstremendously.Forexample,assumethatthereisaUDFthatperformsaSQLSELECTstatementthattakesonesecondtoexecute.IfthisUDFisusedinaSELECToraWHEREclauseitwillbeexecutedforeveryrow.Thusthetimethemainquerytakestoexecutecouldincreasedrasticallydependingonsuchfactorsasthenumberofrowsevaluatedandreturnedandthetypesofindicesinplace.BeforeusingaUDFinthistypeofsituation,carefullyweightheoptionsanddosomeperformancetesting.However,usingaUDFthatperformsacalculationsuchastheoneshowninbarelyaffectstheperformanceofquery.Aswithanytool,whenusedproperlyandevaluatedaccordinglypriortogoinglive,UDFsoffergreatconvenienceandmaintainability.SQLServer用户定义的函数用户定义的函数(UDF)是准备好的代码片段，它可以接受参数，处理逻辑，然后返回某些数据。根据SQLServerBooksOnline，SQLServer2000中的UDF可以接受从0到1024的任意个数的参数，不过我必须承认，我还未尝试将1024个参数传递到UDF中。UDF的另一个关键特征是返回一个值。取决于UDF的类型，调用例程可以使用这个值来继续处理它的数据。因此，如果UDF返回单一值（标量值），调用例程就可以在任何能够使用标准变量或文字值的地方使用这个值。如果UDF返回一个行集，则调用例程可以循环访问该行集，联接到该行集，或简单地从该行集中选择列。虽然现在大多数编程语言已经暂时支持函数，但只有SQLServer2000引入了UDF。存储过程和视图在SQLServer中可用的时间远早于UDF，但这些对象中的每一个在SQLServer开发中都有自己适当的位置。存储过程可以很好地用于处理复杂的SQL逻辑、保证和控制对数据的访问，以及将行集返回到调用例程，无论此例程是基于VisualBasic®的程序，还是另一个Transact-SQL(T-SQL)批处理文件。与视图不同，存储过程是已编译的，这使得它们成为用来表示和处理频繁运行的SQL语句的理想候选者。视图可以很好地用于控制对数据的访问，但它们的控制方式与存储过程不同。视图仅限于生成该视图的基础SELECT语句中的某些列和行。因而视图常用于表示常用的SELECT语句，该语句可以联接多个表、使用WHERE子句，以及公开特定的列。在联接到其他表和视图的SQL语句的FROM子句中经常会发现视图。在其核心部分，UDF既类似于视图，也类似于存储过程。像视图一样，UDF可以返回一个行集，该行集可用于JOIN中。因此，当UDF返回一个行集并接受参数时，它像一个您可以联接到的存储过程、或者一个参数化的视图。但是，正如我将演示的，UDF可以做到这一点，甚至更多。有两种主要的UDF类型：返回标量值的UDF和返回表值的UDF。在表值UDF中，您将找到返回内联表和多语句表的UDF。在以下部分中，我将对每种类型都加以关注。标量UDF返回标量值的UDF最类似于许多编程语言所引用的作为函数的内容。它们返回由标量数据类型（例如，integer、varchar(n)、char(n)、money、datetime、bit，等等）组成的单一值。如果用户定义的数据类型(UDDT)基于标量数据类型，UDF也可以返回这些数据类型。使用返回内联或多语句表的UDF，可以通过表数据类型返回行集。然而，并非所有的数据类型都可以从UDF中返回。例如，UDF无法返回下列数据类型中任何一个的值：text、ntext、image、cursor、或timestamp。返回标量数据类型的UDF可以用于多种情况，以使代码具有更好的可维护性、可重用性和更少的复杂性。当T-SQL代码的相同段在几个地方（可能由几个存储过程和批SQL语句）使用时，这会非常有用。例如，假定一个应用程序中的几个部分都需要查找产品是否必须重新订购。在每个需要此操作的地方，代码可以检查重新订购等级，并将它与库存量加订购量的和相比较。然而，因为这个代码在几个地方用到，所以可以改为使用UDF以减少代码块，并使得万一需要更改时维护函数更加容易。这样的UDF可能看起来像图中的代码，并可以使用以下SQL语句进行调用：SELECTProductID,ReorderLevel,UnitsInStock,UnitsOnOrder,dbo.fnNeedToReorder(ReorderLevel,UnitsInStock,UnitsOnOrder)ASsNeedToReorderFROMProductsfnNeedToReorderUDF执行计算并返回适当的值。这本来可以通过CASE语句在SELECT子句内完成，但如果改为使用UDF，代码就会简洁得多。而且更容易传播到其他可能需要相同逻辑的地方。假定一个应用程序中有几个部分需要确定是否要重新订购产品，UDF确实变得有价值，因为它使得当逻辑改变时应用程序更容易维护。例如，重新订购已经终止的产品并不是很有意义。因此，通过更改UDF以说明这个业务规则，可以在一个地方更改此逻辑并使用下列代码运行：SELECTProductID,ReorderLevel,UnitsInStock,UnitsOnOrder,dbo.fnNeedToReorder(ReorderLevel,UnitsInStock,UnitsOnOrder,Discontinued)ASsNeedToReorderFROMProducts请注意，UDF是使用由两个部分（对象所有者和对象名）组成的名称调用的。当使用返回标量数据类型值的UDF时需要该对象的所有者。可以授权所有调用UDF的地方也必须加以更改，方法是将第四个参数(Discontinued)添加到UDF中。为了更容易维护，我可以重新编写UDF，以便使用每一行的ProductID来检索数据本身，这种技术更容易维护，因为它不需要任何调用例程来更改逻辑改变时更改UDF的方式，只要可以从当前Products表行中提取数据即可。然而，要获得这种可维护性，会有性能方面的损失。中的UDF必须为每个从调用例程中返回的行从Products表中检索行。因为调用例程已经从Products表中检索每个行，所以如果该表有77行，则代码将执行77次SELECT语句（从主SELECT语句中返回每行一次）。虽然每个SELECT都是基于主键字段(ProductID)进行选择的，因而会很快，但是当行集非常大或者SELECT语句效率较低时，性能就会受到负面影响。中的代码可以通过以下SQL片段来调用：SELECTProductID,ReorderLevel,UnitsInStock,UnitsOnOrder,dbo.fnNeedToReorder(ProductId)ASsNeedToReorderFROMProducts在SELECT语句中使用这个函数的可选方法是，在名为NeedToReorder的Products表中创建一个计算所得的列。该列并不定义为一种数据类型，而是定义为如所示的fnNeedToReorderUDF的返回值。要添加此列，我可以按以下方式更改Products表，以指示应计算这个列：ALTERTABLEProductsADDNeedToReorderASdbo.fnNeedToReorder(ReorderLevel,UnitsInStock,UnitsOnOrder,Discontinued)通用UDF和嵌套至此，我已经展示了使用返回标量值的UDF解决同一问题的几种方式。还有其他有用的UDF应用程序，其中包括T-SQL中还未准备好可用的函数。一个例子是专用格式化函数。例如，电话号码通常存储（不带格式化字符）在char(10)列中，这些列表示区号和电话号码（假定这是一个美国的号码）。UDF可以用于在格式化结构中检索电话号码。因此，检索和格式化电话号码像下面一样简单：SELECTdbo.fnCOM_FormatTelephoneNumber('3335558888')可以使用这种技术创建任何常用函数，以增加SQLServer中可用函数的数量。另一个示例是将日期格式化为带有前导零的MM/DD/YYYY格式的函数：CREATEFUNCTIONfnCOM_StandardDate(@dtDateDATETIME)RETURNSVARCHAR(10)ASBEGINRETURNdbo.fnCOM_2Digits(CAST(MONTH(@dtDate)ASVARCHAR(2)))+'/'+dbo.fnCOM_2Digits(CAST(DAY(@dtDate)ASVARCHAR(2)))+'/'+CAST(YEAR(@dtDate)ASVARCHAR(4))ENDfnCOM_StandardDateUDF接受日期时间值，并返回MM/DD/YYYY格式的varchar(10)值。当然，这很简单，如果您的应用程序常常需要特定格式，那么这种技术就可以使它更容易维护。在前面的代码中需要注意的一个关键部分是嵌套UDF的使用。fnCOM_StandardDateUDF两次调用fnCOM_2DigitsUDF（在下一个示例中显示），每次都在小于10的日或月前放置一个前导零。CREATEFUNCTIONfnCOM_2Digits(@sValueVARCHAR(2))RETURNSVARCHAR(2)ASBEGINIF(LEN(@sValue)<2)SET@sValue='0'+@sValueRETURN@sValueENDUDF可以互相嵌套，只要其中的UDF是先创建的即可。使用嵌套函数的一个catch是非确定性内置函数（例如getdate函数），不能在另一个UDF内嵌套（否则会引发SQLServer错误）。非确定性函数是用完全相同的参数调用多次时可能返回不同结果的函数。getdate函数属于这一类，因为每次调用时，它会返回新的当前日期和时间。另一个常用的非确定性内置函数是NewID函数。它也是非确定性的，因为它总是返回唯