計算機類英文資料翻譯

英文資料翻譯系部名稱軟件與服務(wù)外包學(xué)院專業(yè)軟件外包班級軟外0902學(xué)生姓名董彥孝學(xué)號100090823.指導(dǎo)教師孫振亞.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用戶定義的函數(shù)用戶定義的函數(shù)(UDF)是準(zhǔn)備好的代碼片段，它可以接受參數(shù)，處理邏輯，然后返回某些數(shù)據(jù)。根據(jù)SQLServerBooksOnline，SQLServer2000中的UDF可以接受從0到1024的任意個數(shù)的參數(shù)，不過我必須承認(rèn)，我還未嘗試將1024個參數(shù)傳遞到UDF中。UDF的另一個關(guān)鍵特征是返回一個值。取決于UDF的類型，調(diào)用例程可以使用這個值來繼續(xù)處理它的數(shù)據(jù)。因此，如果UDF返回單一值（標(biāo)量值），調(diào)用例程就可以在任何能夠使用標(biāo)準(zhǔn)變量或文字值的地方使用這個值。如果UDF返回一個行集，則調(diào)用例程可以循環(huán)訪問該行集，聯(lián)接到該行集，或簡單地從該行集中選擇列。雖然現(xiàn)在大多數(shù)編程語言已經(jīng)暫時支持函數(shù)，但只有SQLServer2000引入了UDF。存儲過程和視圖在SQLServer中可用的時間遠(yuǎn)早于UDF，但這些對象中的每一個在SQLServer開發(fā)中都有自己適當(dāng)?shù)奈恢?。存儲過程可以很好地用于處理復(fù)雜的SQL邏輯、保證和控制對數(shù)據(jù)的訪問，以及將行集返回到調(diào)用例程，無論此例程是基于VisualBasic?的程序，還是另一個Transact-SQL(T-SQL)批處理文件。與視圖不同，存儲過程是已編譯的，這使得它們成為用來表示和處理頻繁運行的SQL語句的理想候選者。視圖可以很好地用于控制對數(shù)據(jù)的訪問，但它們的控制方式與存儲過程不同。視圖僅限于生成該視圖的基礎(chǔ)SELECT語句中的某些列和行。因而視圖常用于表示常用的SELECT語句，該語句可以聯(lián)接多個表、使用WHERE子句，以及公開特定的列。在聯(lián)接到其他表和視圖的SQL語句的FROM子句中經(jīng)常會發(fā)現(xiàn)視圖。在其核心部分，UDF既類似于視圖，也類似于存儲過程。像視圖一樣，UDF可以返回一個行集，該行集可用于JOIN中。因此，當(dāng)UDF返回一個行集并接受參數(shù)時，它像一個您可以聯(lián)接到的存儲過程、或者一個參數(shù)化的視圖。但是，正如我將演示的，UDF可以做到這一點，甚至更多。有兩種主要的UDF類型：返回標(biāo)量值的UDF和返回表值的UDF。在表值UDF中，您將找到返回內(nèi)聯(lián)表和多語句表的UDF。在以下部分中，我將對每種類型都加以關(guān)注。標(biāo)量UDF返回標(biāo)量值的UDF最類似于許多編程語言所引用的作為函數(shù)的內(nèi)容。它們返回由標(biāo)量數(shù)據(jù)類型（例如，integer、varchar(n)、char(n)、money、datetime、bit，等等）組成的單一值。如果用戶定義的數(shù)據(jù)類型(UDDT)基于標(biāo)量數(shù)據(jù)類型，UDF也可以返回這些數(shù)據(jù)類型。使用返回內(nèi)聯(lián)或多語句表的UDF，可以通過表數(shù)據(jù)類型返回行集。然而，并非所有的數(shù)據(jù)類型都可以從UDF中返回。例如，UDF無法返回下列數(shù)據(jù)類型中任何一個的值：text、ntext、image、cursor、或timestamp。返回標(biāo)量數(shù)據(jù)類型的UDF可以用于多種情況，以使代碼具有更好的可維護(hù)性、可重用性和更少的復(fù)雜性。當(dāng)T-SQL代碼的相同段在幾個地方（可能由幾個存儲過程和批SQL語句）使用時，這會非常有用。例如，假定一個應(yīng)用程序中的幾個部分都需要查找產(chǎn)品是否必須重新訂購。在每個需要此操作的地方，代碼可以檢查重新訂購等級，并將它與庫存量加訂購量的和相比較。然而，因為這個代碼在幾個地方用到，所以可以改為使用UDF以減少代碼塊，并使得萬一需要更改時維護(hù)函數(shù)更加容易。這樣的UDF可能看起來像圖中的代碼，并可以使用以下SQL語句進(jìn)行調(diào)用：SELECTProductID,ReorderLevel,UnitsInStock,UnitsOnOrder,dbo.fnNeedToReorder(ReorderLevel,UnitsInStock,UnitsOnOrder)ASsNeedToReorderFROMProductsfnNeedToReorderUDF執(zhí)行計算并返回適當(dāng)?shù)闹?。這本來可以通過CASE語句在SELECT子句內(nèi)完成，但如果改為使用UDF，代碼就會簡潔得多。而且更容易傳播到其他可能需要相同邏輯的地方。假定一個應(yīng)用程序中有幾個部分需要確定是否要重新訂購產(chǎn)品，UDF確實變得有價值，因為它使得當(dāng)邏輯改變時應(yīng)用程序更容易維護(hù)。例如，重新訂購已經(jīng)終止的產(chǎn)品并不是很有意義。因此，通過更改UDF以說明這個業(yè)務(wù)規(guī)則，可以在一個地方更改此邏輯并使用下列代碼運行：SELECTProductID,ReorderLevel,UnitsInStock,UnitsOnOrder,dbo.fnNeedToReorder(ReorderLevel,UnitsInStock,UnitsOnOrder,Discontinued)ASsNeedToReorderFROMProducts請注意，UDF是使用由兩個部分（對象所有者和對象名）組成的名稱調(diào)用的。當(dāng)使用返回標(biāo)量數(shù)據(jù)類型值的UDF時需要該對象的所有者?？梢允跈?quán)所有調(diào)用UDF的地方也必須加以更改，方法是將第四個參數(shù)(Discontinued)添加到UDF中。為了更容易維護(hù)，我可以重新編寫UDF，以便使用每一行的ProductID來檢索數(shù)據(jù)本身，這種技術(shù)更容易維護(hù)，因為它不需要任何調(diào)用例程來更改邏輯改變時更改UDF的方式，只要可以從當(dāng)前Products表行中提取數(shù)據(jù)即可。然而，要獲得這種可維護(hù)性，會有性能方面的損失。中的UDF必須為每個從調(diào)用例程中返回的行從Products表中檢索行。因為調(diào)用例程已經(jīng)從Products表中檢索每個行，所以如果該表有77行，則代碼將執(zhí)行77次SELECT語句（從主SELECT語句中返回每行一次）。雖然每個SELECT都是基于主鍵字段(ProductID)進(jìn)行選擇的，因而會很快，但是當(dāng)行集非常大或者SELECT語句效率較低時，性能就會受到負(fù)面影響。中的代碼可以通過以下SQL片段來調(diào)用：SELECTProductID,ReorderLevel,UnitsInStock,UnitsOnOrder,dbo.fnNeedToReorder(ProductId)ASsNeedToReorderFROMProducts在SELECT語句中使用這個函數(shù)的可選方法是，在名為NeedToReorder的Products表中創(chuàng)建一個計算所得的列。該列并不定義為一種數(shù)據(jù)類型，而是定義為如所示的fnNeedToReorderUDF的返回值。要添加此列，我可以按以下方式更改Products表，以指示應(yīng)計算這個列：ALTERTABLEProductsADDNeedToReorderASdbo.fnNeedToReorder(ReorderLevel,UnitsInStock,UnitsOnOrder,Discontinued)通用UDF和嵌套至此，我已經(jīng)展示了使用返回標(biāo)量值的UDF解決同一問題的幾種方式。還有其他有用的UDF應(yīng)用程序，其中包括T-SQL中還未準(zhǔn)備好可用的函數(shù)。一個例子是專用格式化函數(shù)。例如，電話號碼通常存儲（不帶格式化字符）在char(10)列中，這些列表示區(qū)號和電話號碼（假定這是一個美國的號碼）。UDF可以用于在格式化結(jié)構(gòu)中檢索電話號碼。因此，檢索和格式化電話號碼像下面一樣簡單：SELECTdbo.fnCOM_FormatTelephoneNumber('3335558888')可以使用這種技術(shù)創(chuàng)建任何常用函數(shù)，以增加SQLServer中可用函數(shù)的數(shù)量。另一個示例是將日期格式化為帶有前導(dǎo)零的MM/DD/YYYY格式的函數(shù)：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)值。當(dāng)然，這很簡單，如果您的應(yīng)用程序常常需要特定格式，那么這種技術(shù)就可以使它更容易維護(hù)。在前面的代碼中需要注意的一個關(guān)鍵部分是嵌套UDF的使用。fnCOM_StandardDateUDF兩次調(diào)用fnCOM_2DigitsUDF（在下一個示例中顯示），每次都在小于10的日或月前放置一個前導(dǎo)零。CREATEFUNCTIONfnCOM_2Digits(@sValueVARCHAR(2))RETURNSVARCHAR(2)ASBEGINIF(LEN(@sValue)<2)SET@sValue='0'+@sValueRETURN@sValueENDUDF可以互相嵌套，只要其中的UDF是先創(chuàng)建的即可。使用嵌套函數(shù)的一個catch是非確定性內(nèi)置函數(shù)（例如getdate函數(shù)），不能在另一個UDF內(nèi)嵌套（否則會引發(fā)SQLServer錯誤）。非確定性函數(shù)是用完全相同的參數(shù)調(diào)用多次時可能返回不同結(jié)果的函數(shù)。getdate函數(shù)屬于這一類，因為每次調(diào)用時，它會返回新的當(dāng)前日期和時間。另一個常用的非確定性內(nèi)置函數(shù)是NewID函數(shù)。它也是非確定性的，因為它總是返回唯