SQL Function自定義函數(shù)的示例分析

小編給大家分享一下SQL Function自定義函數(shù)的示例分析，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！

目錄

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

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

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

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

發(fā)展歷史

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

構(gòu)成

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

作用

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

使用方法

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

　?。?） 創(chuàng)建標量型用戶自定義函數(shù)（Scalar functions） 其語法如下：

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

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

其語法如下：

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

　　TABLE：指定返回值為一個表。

　　select-stmt：單個SELECT 語句，確定返回的表的數(shù)據(jù)。

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

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

　　其語法如下：

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

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

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

適用范圍

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

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

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

注意事項：

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

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

對于標量函數(shù)：

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

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

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

4. 在begin/end語句塊中，是return。

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

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

2. AS后沒有begin/end，只有一個return語句來返回特定的記錄。

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

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

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

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

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

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

看完了這篇文章，相信你對“SQL Function自定義函數(shù)的示例分析”有了一定的了解，如果想了解更多相關(guān)知識，歡迎關(guān)注億速云行業(yè)資訊頻道，感謝各位的閱讀！