目錄

產(chǎn)生背景（已經(jīng)有了存儲(chǔ)過(guò)程，為什么還要使用自定義函數(shù)）
發(fā)展歷史
構(gòu)成
使用方法
適用范圍
注意事項(xiàng)
疑問(wèn)
內(nèi)容

產(chǎn)生背景（已經(jīng)有了存儲(chǔ)過(guò)程，為什么還要使用自定義函數(shù)）

與存儲(chǔ)過(guò)程的區(qū)別（存在的意義）：

1.     能夠在select等SQL語(yǔ)句中直接使用自定義函數(shù)，存儲(chǔ)過(guò)程不行。
2.     自定義函數(shù)可以調(diào)用其他函數(shù)，也可以調(diào)用自己（遞歸）
3.     可以在表列和 CHECK 約束中使用自定義函數(shù)來(lái)實(shí)現(xiàn)特殊列或約束
4.       自定義函數(shù)不能有任何副作用。函數(shù)副作用是指對(duì)具有函數(shù)外作用域（例如數(shù)據(jù)庫(kù)表的修改）的資源狀態(tài)的任何永久性更改。函數(shù)中的語(yǔ)句唯一能做的更改是對(duì)函數(shù)上的局部對(duì)象（如局部游標(biāo)或局部變量）的更改。不能在函數(shù)中執(zhí)行的操作包括：對(duì)數(shù)據(jù)庫(kù)表的修改，對(duì)不在函數(shù)上的局部游標(biāo)進(jìn)行操作，發(fā)送電子郵件，嘗試修改 目錄，以及生成返回至用戶的結(jié)果集。存儲(chǔ)過(guò)程沒(méi)有此限制
5.       函數(shù)只能返回一個(gè)變量。而存儲(chǔ)過(guò)程可以返回多個(gè)

發(fā)展歷史

SqlServer 2000之后都支持用戶自定義函數(shù)

構(gòu)成

在SQL Server 2000 中根據(jù)函數(shù)返回值形式的不同將用戶自定義函數(shù)分為三種類型：標(biāo)量函數(shù)（Scalar Function）、內(nèi)嵌表值函數(shù)（Inline Function）、多聲明表值函數(shù)（Multi-Statement Function）
標(biāo)量函數(shù)：標(biāo)量函數(shù)是對(duì)單一值操作，返回單一值。能夠使用表達(dá)式的地方，就可以使用標(biāo)量函數(shù)。像我們經(jīng)常使用的left、getdate等，都屬于標(biāo)量函數(shù)。系統(tǒng)函數(shù)中的標(biāo)量函數(shù)包括：數(shù)學(xué)函數(shù)、日期和時(shí)間函數(shù)、字符串函數(shù)、數(shù)據(jù)類型轉(zhuǎn)換函數(shù)等
內(nèi)嵌表值函數(shù)：內(nèi)嵌表值函數(shù)的功能相當(dāng)于一個(gè)參數(shù)化的視圖。它返回的是一個(gè)表，內(nèi)聯(lián)表值型函數(shù)沒(méi)有由BEGIN-END 語(yǔ)句括起來(lái)的函數(shù)體。其返回的表由一個(gè)位于RETURN 子句中的SELECT 命令段從數(shù)據(jù)庫(kù)中篩選出來(lái)。

作用

多聲明表值函數(shù)：可以看作標(biāo)量型和內(nèi)嵌表值型函數(shù)的結(jié)合體。它的返回值是一個(gè)表，但它和標(biāo)量型函數(shù)一樣有一個(gè)用BEGIN-END 語(yǔ)句括起來(lái)的函數(shù)體，返回值的表中的數(shù)據(jù)是由函數(shù)體中的語(yǔ)句插入的。由此可見(jiàn)，它可以進(jìn)行多次查詢，對(duì)數(shù)據(jù)進(jìn)行多次篩選與合并，彌補(bǔ)了內(nèi)聯(lián)表值型函數(shù)的不足。

使用方法

SQL Server 為三種類型的用戶自定義函數(shù) 提供了不同的命令創(chuàng)建格式。

　　（1） 創(chuàng)建標(biāo)量型用戶自定義函數(shù)（Scalar functions） 其語(yǔ)法如下：

各參數(shù)說(shuō)明如下：
　　owner_name ：指定用戶自定義函數(shù)的所有者。
　　function_name：指定用戶自定義函數(shù)的名稱。database_name.owner_name.function_name 應(yīng)是惟一的。
　　@parameter_name：定義一個(gè)或多個(gè)參數(shù)的名稱。一個(gè)函數(shù)最多可以定義1024 個(gè)參數(shù)每個(gè)參數(shù)前用“@”符號(hào)標(biāo)明。參數(shù)的作用范圍是整個(gè)函數(shù)。參數(shù)只能替代常量，不能替代表 名、列名或其它數(shù)據(jù)庫(kù)對(duì)象的名稱。用戶自定義函數(shù)不支持輸出參數(shù)。 
　　scalar_parameter_data_type：指定標(biāo)量型參數(shù)的數(shù)據(jù)類型，可以為除TEXT、 NTEXT、 IMAGE、 CURSOR、TIMESTAMP 和TABLE 類型外的其它數(shù)據(jù)類型。 
　　scalar_return_data_type：指定標(biāo)量型返回值的數(shù)據(jù)類型，可以為除TEXT、 NTEXT、 IMAGE、 CURSOR、TIMESTAMP 和TABLE 類型外的其它數(shù)據(jù)類型。 
　　scalar_expression：指定標(biāo)量型用戶自定義函數(shù)返回的標(biāo)量值表達(dá)式。
　　function_body：指定一系列的Transact-SQL 語(yǔ)句，它們決定了函數(shù)的返回值。 
　　ENCRYPTION：加密選項(xiàng)。讓SQL Server 對(duì)系統(tǒng)表中有關(guān)CREATE FUNCTION 的聲明加密，以防止用戶自定義函數(shù)作為SQL Server 復(fù)制的一部分被發(fā)布（Publish） 。
 　　SCHEMABINDING：計(jì)劃綁定選項(xiàng)將用戶自定義函數(shù)綁定到它所引用的數(shù)據(jù)庫(kù)對(duì)象如果指定 了此選項(xiàng)，則函數(shù)所涉及的數(shù)據(jù)庫(kù)對(duì)象從此將不能被刪除或修改，除非函數(shù)被刪除或去掉此選項(xiàng)。應(yīng)注意的是，要綁定的數(shù)據(jù)庫(kù)對(duì)象必須與函數(shù)在同一數(shù)據(jù)庫(kù)中。

（2）創(chuàng)建內(nèi)聯(lián)表值型用戶自定義函 數(shù)（Inline Table-valued Functions）

其語(yǔ)法如下：

各參數(shù)說(shuō)明如下：

　　TABLE：指定返回值為一個(gè)表。

　　select-stmt：?jiǎn)蝹€(gè)SELECT 語(yǔ)句，確定返回的表的數(shù)據(jù)。

　　其余參數(shù)與標(biāo)量型用戶自定義函數(shù)相同。

（3） 創(chuàng)建多聲明表值型用戶自定義函數(shù)

　　其語(yǔ)法如下：

各參數(shù)說(shuō)明如下：

　　@return_variable ：一個(gè)TABLE 類型的變量，用于存儲(chǔ)和累積返回的表中的數(shù)據(jù)行。 其余參數(shù)與標(biāo)量型用戶自定義函數(shù)相同。

　　在多聲明表值型用戶自定義函數(shù)的函數(shù)體中允許使用下列Transact-SQL 語(yǔ)句。 賦值語(yǔ)句（Assignment statements）； 流程控制語(yǔ)句（Control-of-Flow statements）； 定義作用范圍在函數(shù)內(nèi)的變量和 游標(biāo)的DECLARE 語(yǔ)句； SELECT 語(yǔ)句； 編輯函數(shù)中定義的表變量的INSERT、 UPDATE 和DELETE 語(yǔ)句； 在函數(shù)中允許涉及諸如聲明游 標(biāo)、打開(kāi)游標(biāo)、關(guān)閉游標(biāo)、釋放游標(biāo)這樣的游標(biāo)操作，對(duì)于讀取游標(biāo)而言，除非在FETCH 語(yǔ)句中使用INTO 從句來(lái)對(duì)某一變量賦值，否則不允許在函數(shù)中使用FETCH 語(yǔ)句來(lái)向客戶端返回?cái)?shù)據(jù)。此 外不確定性函數(shù)（Non-deterministic functions） 不能在用戶自定義函數(shù)中使 用。所謂不確定性函數(shù)是指那些使用相同的調(diào)用參數(shù)在不同時(shí)刻調(diào)用得到的返回值不同的函數(shù)。這些函數(shù)如表13-3 所示（全局變量也可以視為一種函數(shù)）。

適用范圍

1. 只查詢，不修改數(shù)據(jù)庫(kù)的狀態(tài)（修改、刪除表中記錄等）

2. 結(jié)果集需要通過(guò)遞歸等方法得到時(shí)，可以使用函數(shù)，函數(shù)比較靈活

3. 結(jié)果集需要直接被引用時(shí)，可以使用函數(shù)。需要對(duì)結(jié)果集進(jìn)行再加工（指放在select語(yǔ)句中等），可以使用函數(shù)，函數(shù)可以嵌在select等sql語(yǔ)句中。

注意事項(xiàng)：

用戶自定義函數(shù)不能用于執(zhí)行一系列改變數(shù)據(jù)庫(kù)狀態(tài)的操作

在編寫自定義函數(shù)時(shí)需要注意的：

對(duì)于標(biāo)量函數(shù)：

1. 所有的入?yún)⑶岸急仨毤覢

2. create后的返回，單詞是returns，而不是return

3. returns后面的跟的不是變量，而是返回值的類型，如：int，char等。

4. 在begin/end語(yǔ)句塊中，是return。

內(nèi)嵌表值函數(shù)：

1. 只能返回table，所以returns后面一定是TABLE

2. AS后沒(méi)有begin/end，只有一個(gè)return語(yǔ)句來(lái)返回特定的記錄。

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

1. returns后面直接定義返回的表類型，首先是定義表名，表明前面要加@，然后是關(guān)鍵字TABLE，最后是表的結(jié)構(gòu)。

2. 在begin/end語(yǔ)句塊中，直接將需要返回的結(jié)果insert到returns定義的表中就可以了，在最后return時(shí)，會(huì)將結(jié)果返回。

3. 最后只需要return，return后面不跟任何變量。

疑問(wèn)：自定義函數(shù)不能修改數(shù)據(jù)庫(kù)，但它可以調(diào)用存儲(chǔ)過(guò)程，那么在自定義函數(shù)中調(diào)用一個(gè)有修改數(shù)據(jù)庫(kù)的操作的存儲(chǔ)過(guò)程，這個(gè)自定義函數(shù)能不能執(zhí)行？

答：自定義函數(shù)只能調(diào)用擴(kuò)展存儲(chǔ)過(guò)程，但是SQL Server 2008的后續(xù)版本將刪除該功能，不再支持?jǐn)U展存儲(chǔ)過(guò)程，所以應(yīng)避免在開(kāi)發(fā)中使用擴(kuò)展存儲(chǔ)過(guò)程。因此，可以得出結(jié)論是：實(shí)際開(kāi)發(fā)中，函數(shù)不會(huì)去調(diào)用存儲(chǔ)過(guò)程，也就無(wú)法對(duì)數(shù)據(jù)庫(kù)進(jìn)行修改操作了。

參考：
http://technet.microsoft.com/zh-tw/library/ms186755.aspx
http://msdn.microsoft.com/zh-cn/library/ms175200.aspx
http://www.cnblogs.com/Athrun/archive/2007/07/27/833416.html