SQL Function自定義函數的示例分析

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

目錄

產生背景（已經有了存儲過程，為什么還要使用自定義函數）發展歷史構成使用方法適用范圍注意事項疑問 內容

產生背景（已經有了存儲過程，為什么還要使用自定義函數）

與存儲過程的區別（存在的意義）：

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

發展歷史

SqlServer 2000之后都支持用戶自定義函數

構成

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

作用

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

使用方法

SQL Server 為三種類型的用戶自定義函數 提供了不同的命令創建格式。

　　（1） 創建標量型用戶自定義函數（Scalar functions） 其語法如下：

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

（2）創建內聯表值型用戶自定義函 數（Inline Table-valued Functions）

其語法如下：

各參數說明如下：

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

　　select-stmt：單個SELECT 語句，確定返回的表的數據。

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

（3） 創建多聲明表值型用戶自定義函數

　　其語法如下：

各參數說明如下：

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

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

適用范圍

1. 只查詢，不修改數據庫的狀態（修改、刪除表中記錄等）

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

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

注意事項：

用戶自定義函數不能用于執行一系列改變數據庫狀態的操作

在編寫自定義函數時需要注意的：

對于標量函數：

1. 所有的入參前都必須加@

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

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

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

內嵌表值函數：

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

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

多語句表值函數：

1. returns后面直接定義返回的表類型，首先是定義表名，表明前面要加@，然后是關鍵字TABLE，最后是表的結構。

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

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

疑問：自定義函數不能修改數據庫，但它可以調用存儲過程，那么在自定義函數中調用一個有修改數據庫的操作的存儲過程，這個自定義函數能不能執行？

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

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