SQL Server用戶自定義函數(shù)

前幾篇博文介紹了不少SQLServer系統(tǒng)自帶函數(shù)方面的使用方法，這次將學(xué)習(xí)SQLSever用戶自定義函數(shù)的使用方法，算是對(duì)SQLServer函數(shù)的補(bǔ)充。希望與園里的朋友共同學(xué)習(xí)并取經(jīng)。關(guān)于SQLServer用戶自定義的函數(shù)，有標(biāo)量函數(shù)、表值函數(shù)（內(nèi)聯(lián)表值函數(shù)、多語(yǔ)句表值函數(shù)）兩種。題外話，可能有部分朋友不知道SQLServe用戶自定義的函數(shù)應(yīng)該是寫在哪里，這里簡(jiǎn)單提示一下，在MicrosoftSQLServerManagermentStudio里面，展開(kāi)具體需要?jiǎng)?chuàng)建SQLServer用戶自定義函數(shù)的數(shù)據(jù)庫(kù)（即每個(gè)用戶自定義函數(shù)只針對(duì)具體的一個(gè)數(shù)據(jù)庫(kù)有用），然后找到可編程性選項(xiàng)，再展開(kāi)找到函數(shù)選項(xiàng)，在具體的函數(shù)選項(xiàng)里面可參照下圖的方式鼠標(biāo)右鍵選擇來(lái)添加。

標(biāo)量函數(shù)

所謂標(biāo)量函數(shù)簡(jiǎn)單點(diǎn)來(lái)講就是返回的結(jié)果只是一個(gè)標(biāo)量，對(duì)于我來(lái)講，返回的結(jié)果就是一種類型的一個(gè)值。寫法如下：--=============================================--Author:<Author,,Name>--Createdate:<CreateDate,,>--Description:<Description,,>--=============================================CREATEFUNCTION<Scalar_Function_Name,sysname,FunctionName>(--Addtheparametersforthefunctionhere<@Param1,sysname,@p1><Data_Type_For_Param1,,int>)RETURNS<Function_Data_Type,,int>ASBEGIN--DeclarethereturnvariablehereDECLARE<@ResultVar,sysname,@Result><Function_Data_Type,,int>--AddtheT-SQLstatementstocomputethereturnvaluehereSELECT<@ResultVar,sysname,@Result>=<@Param1,sysname,@p1>--ReturntheresultofthefunctionRETURN<@ResultVar,sysname,@Result>END例子：--=============================================--Author:<Author,,Name>--Createdate:<CreateDate,,>--Description:<Description,,>--=============================================CREATEFUNCTIONGetSum

(@firstNumint,@secondNumint)RETURNSintASBEGIN--DeclarethereturnvariablehereDECLARE@resultint--AddtheT-SQLstatementstocomputethereturnvaluehereSELECT@result=@firstNum+@secondNum--ReturntheresultofthefunctionRETURN@resultENDGO

題外話：我們來(lái)看看上面的寫法，對(duì)于SQLServer來(lái)講，我們聲明一個(gè)變量的方式是用@變量名，而且相對(duì)于編程來(lái)講，SQLServer聲明的方式跟我們開(kāi)了個(gè)玩笑，是先變量后面才是類型。對(duì)于需要傳參跟不需要傳參的方式，其實(shí)跟我們編程的方式一樣。有參數(shù)則是如下方式：CREATEFUNCTIONGetSum(@firstNumint,@secondNumint)如果沒(méi)有參數(shù)，則只要保留括號(hào)即可。跟我們理解的函數(shù)寫法一致。CREATEFUNCTIONGetSum()對(duì)于返回方式，這跟我們編程的方式又不大一樣。SQLServer函數(shù)的返回類型并不放在函數(shù)名前面，而是函數(shù)名括號(hào)的后面。而且函數(shù)的返回類型需要用到返回關(guān)鍵字RETURNS，而不是RETURN。對(duì)于函數(shù)來(lái)講，當(dāng)然也會(huì)有所謂的函數(shù)體。標(biāo)量函數(shù)也一樣。它的函數(shù)體是包含在：ASBEGIN--函數(shù)體END對(duì)于需要在函數(shù)體里面聲明變量的話，則需要使用到DECLARE關(guān)鍵字進(jìn)行聲明。函數(shù)體內(nèi)的返回才是關(guān)鍵字RETURN。

好了，標(biāo)量函數(shù)的例子也舉完了，要存到數(shù)據(jù)庫(kù)里面，還需要點(diǎn)擊MicrosoftSQLServerManagementStudio工具里的執(zhí)行操作。這樣之后，就可以在查詢窗口里面跟查詢表數(shù)據(jù)一樣來(lái)查詢結(jié)果了。使用方式好懂吧，但是需要注意的是[dbo]這個(gè)對(duì)象名在不能省，[GetSum]函數(shù)后面可也別少了()。說(shuō)來(lái)也奇怪，對(duì)于表值函數(shù)來(lái)說(shuō)，對(duì)象名[dbo]倒是不寫也可以正確執(zhí)行。select[dbo].[GetSum]()

內(nèi)聯(lián)表值函數(shù)

相對(duì)于標(biāo)量函數(shù)只返回一個(gè)標(biāo)量值，內(nèi)聯(lián)表值函數(shù)返回的是表數(shù)據(jù)。當(dāng)然羅，表數(shù)據(jù)就是Table類型。寫法如下：--=============================================--Author:<Author,,Name>--Createdate:<CreateDate,,>--Description:<Description,,>--=============================================CREATEFUNCTION<Inline_Function_Name,sysname,FunctionName>(--Addtheparametersforthefunctionhere<@param1,sysname,@p1><Data_Type_For_Param1,,int>,<@param2,sysname,@p2><Data_Type_For_Param2,,char>)RETURNSTABLEASRETURN(--AddtheSELECTstatementwithparameterreferenceshereSELECT0)GO例子：--=============================================--Author:<Author,,Name>--Createdate:<CreateDate,,>--Description:<Description,,>--=============================================CREATEFUNCTION[GetMoreThanSalary](@salaryint)RETURNSTABLEASRETURN(SELECT[FName],[FCity],[FAge],[FSalary]FROM[Demo].[dbo].[T_Person]Where[FSalary]>@salary)GO題外話：標(biāo)量函數(shù)上面提過(guò)的內(nèi)容，這里就不重復(fù)了。內(nèi)聯(lián)表函數(shù)返回的表結(jié)構(gòu)由函數(shù)體內(nèi)的SELECT語(yǔ)句來(lái)決定。對(duì)于標(biāo)量函數(shù)來(lái)講，函數(shù)體是包含在如下結(jié)構(gòu)中。ASBEGIN--函數(shù)體END但是對(duì)于內(nèi)聯(lián)表值函數(shù)來(lái)講，函數(shù)體的結(jié)構(gòu)則是如下的方式。內(nèi)聯(lián)表值函數(shù)只執(zhí)行一條SQL語(yǔ)句后返回Table結(jié)果。ASRETURN--函數(shù)體END執(zhí)行表值函數(shù)的方式如下：select[FName],[FCity],[FAge],[FSalary]from[dbo].[GetMoreThanSalary](8000)可以看得出，這種執(zhí)行方式就跟普通表的執(zhí)行方式一樣了。表值函數(shù)其實(shí)相當(dāng)于存儲(chǔ)在內(nèi)存空間里面的一張?zhí)摂M表。

多語(yǔ)句表值函數(shù)

多語(yǔ)句表值函數(shù)跟內(nèi)聯(lián)表值函數(shù)都是表值函數(shù)，它們返回的結(jié)果都是Table類型。多語(yǔ)句表值函數(shù)顧名思義，就是可以通過(guò)多條語(yǔ)句來(lái)創(chuàng)建Table類型的數(shù)據(jù)。這里不同于內(nèi)聯(lián)表值函數(shù)，內(nèi)聯(lián)表值函數(shù)的返回結(jié)果是由函數(shù)體內(nèi)的SELECT語(yǔ)句來(lái)決定。而多語(yǔ)句表值函數(shù)，則是需要指定具體的Table類型的結(jié)構(gòu)。也就是說(shuō)返回的Table，已經(jīng)定義好要哪些字段返回。所以它能夠支持多條語(yǔ)句的執(zhí)行來(lái)創(chuàng)建Table數(shù)據(jù)。寫法如下：--=============================================--Author:<Author,,Name>--Createdate:<CreateDate,,>--Description:<Description,,>--=============================================CREATEFUNCTION<Table_Function_Name,sysname,FunctionName>(--Addtheparametersforthefunctionhere<@param1,sysname,@p1><data_type_for_param1,,int>,<@param2,sysname,@p2><data_type_for_param2,,char>)RETURNS<@Table_Variable_Name,sysname,@Table_Var>TABLE(--AddthecolumndefinitionsfortheTABLEvariablehere<Column_1,sysname,c1><Data_Type_For_Column1,,int>,<Column_2,sysname,c2><Data_Type_For_Column2,,int>)ASBEGIN--FillthetablevariablewiththerowsforyourresultsetRETURNENDGO例子：--=============================================--Author:<Author,,Name>--Createdate:<CreateDate,,>--Description:<Description,,>--=============================================ALTERFUNCTIONDemoFun()RETURNS@resultTABLE(namenvarchar(20),citynvarchar(20),ageint,salaryint)ASBEGIN--Fillthetablevariablewiththerowsforyourresultsetinsertinto@result(name,city,age,salary)selectFName,FCity,FAge,FSalaryfromdbo.T_PersonwhereFSalary>8000insertinto@result(name,city,age,salary)values('測(cè)試','China',1,0)RETURNENDGO題外話：可以看得出，多語(yǔ)句表值函數(shù)的返回結(jié)果是定義好表結(jié)構(gòu)的虛擬表。這又跟標(biāo)量函數(shù)一樣了吧，只不過(guò)標(biāo)量函數(shù)是返回一種類型的標(biāo)量值而已。而且在多語(yǔ)句表值函數(shù)里面，你也會(huì)發(fā)現(xiàn)最后一句是RETURN。告訴執(zhí)行程序，多語(yǔ)句表值函數(shù)已經(jīng)執(zhí)行完成。函數(shù)體結(jié)構(gòu)跟標(biāo)量函數(shù)的結(jié)構(gòu)一樣。對(duì)于類型放在變量后面這種方式確實(shí)需要好好轉(zhuǎn)換一下觀念。RETURNS<@Table_Variable_Name,sysname,@Table_Var>TABLE(--AddthecolumndefinitionsfortheTABLEvariablehere<Column_1,sysname,c1><Data_Type_For_Column1,,int>,<Column_2,sysname,c2><Data_Type_For_Column2,,int>)內(nèi)容倒是不多，但是要熟練使用的話，還是需要在項(xiàng)目中多加使用才行。網(wǎng)上有一些網(wǎng)友總結(jié)出來(lái)的常用自定義函數(shù)大家可以收集積累，就像做項(xiàng)目一樣，好的方法要形成所謂的開(kāi)發(fā)庫(kù)，幫助我們?cè)谙乱粋€(gè)項(xiàng)目中復(fù)用。節(jié)省我們的開(kāi)發(fā)時(shí)間，提高我們的工作效率。我眼中的單例模式說(shuō)到單例模式，網(wǎng)上搜索出來(lái)的結(jié)果是多如牛毛，但這不影響我也來(lái)湊熱鬧的心情。任何事情都是要親身去體會(huì)了，才能加深自己的理解。本著不斷學(xué)習(xí)進(jìn)取的精神，我很想可以站在牛人的肩膀上，哪怕是仰視牛人的情況下，我也想發(fā)揮自己的余熱。記錄下自己學(xué)習(xí)的足跡，權(quán)當(dāng)自己未來(lái)細(xì)細(xì)回味也好。（不過(guò)說(shuō)真的，自己試著去組織語(yǔ)言來(lái)介紹你的問(wèn)題也好，你的產(chǎn)品也好，能在很大的程度上提高你的表達(dá)能力。大腦是越鍛煉越活的東西，講話、寫作也一樣，持之以恒，必有收獲?？傊?，貴在堅(jiān)持哦?。┫旅鎭?lái)介紹模式，單例模式就是保證一個(gè)類僅有一個(gè)實(shí)例，并提供一個(gè)訪問(wèn)它的全局訪問(wèn)點(diǎn)。

其實(shí)就是實(shí)現(xiàn)只有一個(gè)門可以進(jìn)入，且每次只給一個(gè)人進(jìn)入。這就像以前的一位博友所舉的例子，很多人排隊(duì)去廁所蹲坑一樣，每一次只能讓一個(gè)人去蹲坑。實(shí)現(xiàn)單例模式的原因，要么是資源共享，要么是控制資源等。所謂資源共享，就是因?yàn)閱卫Ｊ奖ＷC了一個(gè)類僅有一個(gè)實(shí)例，所以大家訪問(wèn)的實(shí)例是一致的。而控制資源的話，主要是減少資源的申請(qǐng)與釋放等。牛人就是牛人，一下給出了五種實(shí)現(xiàn)單例模式的例子?？吹梦颐┤D開(kāi)，大呼過(guò)癮。

第一種：簡(jiǎn)單實(shí)現(xiàn)（惰性實(shí)例化）

namespaceSingleton{publicclassProgram{staticvoidMain(string[]args){Singletons1=Singleton.Instance;Singletons2=Singleton.Instance;if(s1==s2){Console.WriteLine("Objectsarethesameinstance");}Console.Read();}}publicsealedclassSingleton{privateSingleton(){}privatestaticSingletoninstance=null;publicstaticSingletonInstance{get{if(instance==null){instance=newSingleton();}returninstance;}}}}簡(jiǎn)單實(shí)現(xiàn)對(duì)于線程來(lái)說(shuō)是不安全的，因?yàn)樵诙嗑€程的情況下，有可能產(chǎn)生多個(gè)Singleton實(shí)例。多線程的情況下，如果多個(gè)線程都去判斷（instance==null），而它們都還沒(méi)有創(chuàng)建實(shí)例的情況下，就會(huì)產(chǎn)生多個(gè)Singleton實(shí)例。對(duì)于簡(jiǎn)單實(shí)現(xiàn)來(lái)講，Singleton實(shí)例化并不是應(yīng)用程序啟動(dòng)就創(chuàng)建，所以我們把它叫做“惰性實(shí)例化”，這能避免應(yīng)用程序啟動(dòng)時(shí)實(shí)例化不必要的實(shí)例。

第二種：安全的線程

namespaceSingleton{publicclassProgram{staticvoidMain(string[]args){Singletons1=Singleton.Instance;Singletons2=Singleton.Instance;if(s1==s2){Console.WriteLine("Objectsarethesameinstance");}Console.Read();}}publicsealedclassSingleton{privateSingleton(){}privatestaticSingletoninstance=null;privatestaticreadonlyobjectpadLock=newobject();publicstaticSingletonInstance{get{lock(padLock){if(instance==null){instance=newSingleton();}returninstance;}}}}}安全的線程，這是對(duì)簡(jiǎn)單實(shí)例的補(bǔ)充。因?yàn)樘峁┝思渔ilock()的操作，這就能確保只有一個(gè)線程進(jìn)入。但是加鎖需要增加額外的開(kāi)銷，損失性能。

第三種：雙重鎖定檢查

namespaceSingleton{publicclassProgram{staticvoidMain(string[]args){Singletons1=Singleton.Instance;Singletons2=Singleton.Instance;if(s1==s2){Console.WriteLine("Objectsarethesameinstance");}Console.Read();}}publicsealedclassSingleton{publicSingleton(){}privatestaticSingletoninstance=null;

privatestaticreadonlyobjectpadLock=newobject();publicstaticSingletonInstance{get{if(instance==null){lock(padLock){if(instance==null){instance=newSingleton();}}}returninstance;}}}}雙重鎖定檢查在安全的線程上面又進(jìn)行了改進(jìn)，主要是考慮了每次加鎖會(huì)增加額外的開(kāi)銷，影響性能。所以在加鎖前再判斷Singleton有沒(méi)有被實(shí)例化。這樣，它就能減少很多的額外開(kāi)銷且是線程安全的。實(shí)際上，應(yīng)用程序很少需要上面方式的實(shí)現(xiàn)。這種方式仍然有很多缺點(diǎn)：無(wú)法實(shí)現(xiàn)延遲初始化。大多數(shù)情況下我們會(huì)使用靜態(tài)初始化的方式。

第四種：靜態(tài)初始化

namespaceSingleton{publicclassProgram{staticvoidMain(string[]args){Singletons1=Singleton.Instance;Singletons2=Singleton.Instance;if(s1==s2){Console.WriteLine("Objectsarethesameinstance");}Console.Read();}}publicsealedclassSingleton{staticreadonlySingletoninstance=newSingleton();privateSingleton(){}publicstaticSingletonInstance{get{returninstance;}}}}靜態(tài)初始化，是在.NET中實(shí)現(xiàn)Singleton的首選方法。這段代碼有點(diǎn)意思，我也解讀一下。主要是講解下關(guān)鍵字吧。sealed:修改類，意為這個(gè)類不可再被繼承，防止子類被實(shí)例化而不能保證只有一個(gè)實(shí)例的問(wèn)題。privateSingleton():用private修改構(gòu)造函數(shù)，可以防止這個(gè)類在外部被實(shí)例。也就是在Singleton類外面想newSingleton()是會(huì)報(bào)編譯錯(cuò)誤。staticreadonly:表示只能在聲明時(shí)賦值，或是在靜態(tài)構(gòu)造中賦值。

第五種：延遲初始化

namespaceSingleton{classProgram{staticvoidMain(string[]args){Singletons1=Singleton.Instance;Singletons2=Singleton.Instance;if(s1==s2){Console.WriteLine("Objectsarethesameinstance");}Console.Read();}}publicsealedclassSingleton{publicSingleton(){}publicstaticSingletonInstance{get{returnDelay.DelayInstance;