使用者定義的函式User-Defined Functions

適用於: 是SQL Server 是Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

SQL ServerSQL Server 使用者定義函數與程式語言函數類似,是可接受參數、執行動作 (如複雜計算) 以及傳回該動作所得值的常式。Like functions in programming languages, SQL ServerSQL Server user-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. 傳回值可以是單一純量值或結果集。The return value can either be a single scalar value or a result set.

使用者定義的函式User-defined functions

為何要使用使用者定義函式 (UDF)?Why use user-defined functions (UDFs)?

  • 可進行模組化的程式撰寫。They allow modular programming.

    只需建立函數一次,將它儲存在資料庫中,就可以在程式中無限次地隨意呼叫。You can create the function once, store it in the database, and call it any number of times in your program. 使用者自訂函數不需透過原始程式碼即可修改 。User-defined functions can be modified independently of the program source code.

  • 可加快執行速度。They allow faster execution.

    如同預存程序,Transact-SQLTransact-SQL 使用者自訂函數可藉由針對重複執行來快取以及重複使用計畫,來降低 Transact-SQLTransact-SQL 程式碼的編譯成本。Similar to stored procedures, Transact-SQLTransact-SQL user-defined functions reduce the compilation cost of Transact-SQLTransact-SQL code by caching the plans and reusing them for repeated executions. 這表示,每次使用時,使用者自訂函數不需要重新剖析和最佳化,所以執行時間可以更快。This means the user-defined function does not need to be reparsed and reoptimized with each use resulting in much faster execution times.

    Transact-SQLTransact-SQL 函數相比,CLR 函數在計算工作、字串處理與商務邏輯等方面提供更顯著的效能優勢。CLR functions offer significant performance advantage over Transact-SQLTransact-SQL functions for computational tasks, string manipulation, and business logic. Transact-SQLTransact-SQL 函數更適用於經常需要存取資料的作業。functions are better suited for data-access intensive logic.

  • 可降低網路傳輸量。They can reduce network traffic.

    對於無法以單一純量運算式表示的作業 (例如,根據某些複雜條件約束來篩選資料),可以利用函數來表示。An operation that filters data based on some complex constraint that cannot be expressed in a single scalar expression can be expressed as a function. 接著,您可以在 WHERE 子句中叫用函數,減少傳送到用戶端的資料列數。The function can then invoked in the WHERE clause to reduce the number or rows sent to the client.

重要

查詢中的 Transact-SQLTransact-SQL UDF 只能在單一執行緒上執行 (序列執行計劃)。Transact-SQLTransact-SQL UDFs in queries can only be executed on a single thread (serial execution plan). 因此使用 UDF 會禁止平行查詢處理。Therefore using UDFs inhibits parallel query processing. 如需平行查詢處理的詳細資訊,請參閱查詢處理架構指南For more information about parallel query processing, see the Query Processing Architecture Guide.

函式類型Types of functions

純量函式Scalar Function
使用者定義純量函數會傳回在 RETURNS 子句中所定義之類型的單一資料值。User-defined scalar functions return a single data value of the type defined in the RETURNS clause. 針對內嵌純量函式,傳回的純量值是單一陳述式的結果。For an inline scalar function, the returned scalar value is the result of a single statement. 針對多重陳述式純量函式,函式主體可包含一系列傳回單一值的 Transact-SQLTransact-SQL 陳述式。For a multistatement scalar function, the function body can contain a series of Transact-SQLTransact-SQL statements that return the single value. 傳回類型可以是 textntextimagecursortimestamp以外的任何資料類型。The return type can be any data type except text, ntext, image, cursor, and timestamp. 範例。Examples.

資料表值函式Table-Valued Functions
使用者定義的資料表值函數會傳回 table 資料類型。User-defined table-valued functions return a table data type. 若是內嵌資料表值函式,則不會有函式主體;資料表會是單一 SELECT 陳述式的結果集。For an inline table-valued function, there is no function body; the table is the result set of a single SELECT statement. 範例。Examples.

系統函式System Functions
SQL ServerSQL Server 提供許多可用以執行各種作業的系統函數。provides many system functions that you can use to perform a variety of operations. 這些函數不能修改。They cannot be modified. 如需詳細資訊,請參閱內建函數 (Transact-SQL)系統預存函式 (Transact-SQL),和動態管理檢視與函數 (Transact-SQL)For more information, see Built-in Functions (Transact-SQL), System Stored Functions (Transact-SQL), and Dynamic Management Views and Functions (Transact-SQL).

指導方針Guidelines

Transact-SQLTransact-SQL 造成陳述式取消並且以模組中下一個陳述式繼續 (例如觸發程序或預存程序) 的錯誤會在函式內部以不同方式處理。errors that cause a statement to be canceled and continue with the next statement in the module (such as triggers or stored procedures) are treated differently inside a function. 在函數中,這樣的錯誤會造成函數停止執行。In functions, such errors cause the execution of the function to stop. 進而導致叫用該函數的陳述式取消。This in turn causes the statement that invoked the function to be canceled.

BEGIN...END 區塊中的陳述式不能有任何副作用。The statements in a BEGIN...END block cannot have any side effects. 函數副作用是在函數的範圍外對資源狀態所做的任何永久變更,例如修改資料庫資料表。Function side effects are any permanent changes to the state of a resource that has a scope outside the function such as a modification to a database table. 在函數中陳述式只能變更函數的區域性物件,例如本機資料指標或變數。The only changes that can be made by the statements in the function are changes to objects local to the function, such as local cursors or variables. 在函數中不得執行的動作包括修改資料庫資料表、對函數的非本機資料指標進行運算、傳送電子郵件、試圖修改目錄,以及產生傳回給使用者的結果集。Modifications to database tables, operations on cursors that are not local to the function, sending e-mail, attempting a catalog modification, and generating a result set that is returned to the user are examples of actions that cannot be performed in a function.

注意

CREATE FUNCTION 陳述式會對資源產生在發出 CREATE FUNCTION 陳述式時不存在的副作用,則 SQL ServerSQL Server 會執行該陳述式。If a CREATE FUNCTION statement produces side effects against resources that do not exist when the CREATE FUNCTION statement is issued, SQL ServerSQL Server executes the statement. 不過, SQL ServerSQL Server 不會執行叫用的函數。However, SQL ServerSQL Server does not execute the function when it is invoked.

查詢中指定的函數真正執行的次數,會因最佳化工具建立的執行計畫而有不同。The number of times that a function specified in a query is actually executed can vary between execution plans built by the optimizer. WHERE 子句中的子查詢所叫用的函式就是一個例子。An example is a function invoked by a subquery in a WHERE clause. 子查詢及其函數的執行次數,會因最佳化工具選擇的存取路徑而有不同。The number of times the subquery and its function is executed can vary with different access paths chosen by the optimizer.

重要

如需使用者定義函式的詳細資訊和效能考量事項,請參閱建立使用者定義函式 (資料庫引擎)For more information and performance considerations on user-defined functions, see Create User-defined Functions (Database Engine).

函式中有效的陳述式Valid statements in a function

函數中有效的陳述式類型包括:The types of statements that are valid in a function include:

  • DECLARE 陳述式,可用來定義對函式而言為區域的資料變數與資料指標。DECLARE statements can be used to define data variables and cursors that are local to the function.

  • 指派值給對函式而言為區域的物件,例如使用 SET 將值指派給純量及資料表區域變數。Assignments of values to objects local to the function, such as using SET to assign values to scalar and table local variables.

  • 參考在函數中宣告、開啟、關閉及取消配置的本機資料指標之資料指標運算。Cursor operations that reference local cursors that are declared, opened, closed, and deallocated in the function. 不允許將資料傳回用戶端的 FETCH 陳述式。FETCH statements that return data to the client are not allowed. 只允許使用 INTO 子句將值指派給區域變數的 FETCH 陳述式。Only FETCH statements that assign values to local variables using the INTO clause are allowed.

  • TRY...CATCH 陳述式之外的流程控制陳述式。Control-of-flow statements except TRY...CATCH statements.

  • SELECT 陳述式,包含選取清單,其中含有將值指派給區域變數的運算式。SELECT statements containing select lists with expressions that assign values to variables that are local to the function.

  • UPDATEINSERTDELETE 陳述式,修改對函式而言為區域變數的資料表變數。UPDATE, INSERT, and DELETE statements modifying table variables that are local to the function.

  • 呼叫擴充預存程序的 EXECUTE 陳述式。EXECUTE statements calling an extended stored procedure.

內建系統函數Built-in system functions

下列非決定性內建函數可用於 Transact-SQL 使用者定義函數中。The following nondeterministic built-in functions can be used in Transact-SQL user-defined functions.

CURRENT_TIMESTAMPCURRENT_TIMESTAMP @@MAX_CONNECTIONS@@MAX_CONNECTIONS
GET_TRANSMISSION_STATUSGET_TRANSMISSION_STATUS @@PACK_RECEIVED@@PACK_RECEIVED
GETDATEGETDATE @@PACK_SENT@@PACK_SENT
GETUTCDATEGETUTCDATE @@PACKET_ERRORS@@PACKET_ERRORS
@@CONNECTIONS@@CONNECTIONS @@TIMETICKS@@TIMETICKS
@@CPU_BUSY@@CPU_BUSY @@TOTAL_ERRORS@@TOTAL_ERRORS
@@DBTS@@DBTS @@TOTAL_READ@@TOTAL_READ
@@IDLE@@IDLE @@TOTAL_WRITE@@TOTAL_WRITE
@@IO_BUSY@@IO_BUSY

下列非決定性內建函式不得用於 Transact-SQLTransact-SQL 使用者定義函式中。The following nondeterministic built-in functions cannot be used in Transact-SQLTransact-SQL user-defined functions.

NEWIDNEWID RANDRAND
NEWSEQUENTIALIDNEWSEQUENTIALID TEXTPTRTEXTPTR

如需決定性與非決定性內建系統函數的清單,請參閱決定性與非決定性函數For a list of deterministic and nondeterministic built-in system functions, see Deterministic and Nondeterministic Functions.

結構描述繫結的函式Schema-bound functions

CREATE FUNCTION 支援 SCHEMABINDING 子句,它可將函式與其參考的任何物件結構描述繫結在一起,例如資料表、檢視及其他使用者定義函式。CREATE FUNCTION supports a SCHEMABINDING clause that binds the function to the schema of any objects it references, such as tables, views, and other user-defined functions. 嘗試更改或卸除任何被結構描述繫結函數所參考的物件將會失敗。An attempt to alter or drop any object referenced by a schema-bound function fails.

必須滿足以下條件,您才可以在 CREATE FUNCTION 中指定 SCHEMABINDINGThese conditions must be met before you can specify SCHEMABINDING in CREATE FUNCTION:

  • 函數所參考的所有檢視及使用者自訂函數,都必須是結構描述繫結的。All views and user-defined functions referenced by the function must be schema-bound.

  • 函數所參考的所有物件,都必須與函數位於相同的資料庫。All objects referenced by the function must be in the same database as the function. 這些物件必須利用單一部份或兩部份名稱來加以參考。The objects must be referenced using either one-part or two-part names.

  • 您對於函式中參考的所有物件 (資料表、檢視及使用者定義函式) 必須擁有 REFERENCES 權限。You must have REFERENCES permission on all objects (tables, views, and user-defined functions) referenced in the function.

您可以使用 ALTER FUNCTION 來移除結構描述繫結。You can use ALTER FUNCTION to remove the schema binding. ALTER FUNCTION 陳述式應在不指定 WITH SCHEMABINDING 的情況下重新定義函式。The ALTER FUNCTION statement should redefine the function without specifying WITH SCHEMABINDING.

指定參數Specifying parameters

使用者自訂函數會使用零或多個輸入參數,並會傳回純量值或資料表。A user-defined function takes zero or more input parameters and returns either a scalar value or a table. 每一函數最多可以有 1024 個輸入參數。A function can have a maximum of 1024 input parameters. 若函數的參數有預設值,在呼叫函數以取得預設值時必須指定 DEFAULT 關鍵字。When a parameter of the function has a default value, the keyword DEFAULT must be specified when calling the function to get the default value. 此一行為不同於使用者自訂預存程序中有預設值的參數,在這些預存程序中省略參數亦意謂著省略預設值。This behavior is different from parameters with default values in user-defined stored procedures in which omitting the parameter also implies the default value. 使用者自訂函數不支援輸出參數。User-defined functions do not support output parameters.

更多範例!More examples!

工作描述Task Description 主題Topic
描述如何建立 Transact-SQL 使用者定義函數。Describes how to create a Transact-SQL user-defined function. 建立使用者定義函式 (資料庫引擎)Create User-defined Functions (Database Engine)
描述如何建立 CLR 函數。Describes how create a CLR function. 建立 CLR 函式Create CLR Functions
描述如何建立使用者定義的彙總函式。Describes how to create a user-defined aggregate function 建立使用者定義彙總Create User-defined Aggregates
描述如何修改 Transact-SQL 使用者定義函數。Describes how to modify a Transact-SQL user-defined function. 修改使用者定義函式Modify User-defined Functions
描述如何刪除使用者定義函數。Describes how to delete a user-defined function. 刪除使用者定義函式Delete User-defined Functions
描述如何執行使用者定義函數。Describes how to execute a user-defined function. 執行使用者定義函式Execute User-defined Functions
描述如何重新命名使用者定義函數。Describes how to rename a user-defined function 重新命名使用者定義函式Rename User-defined Functions
描述如何檢視使用者定義函數的定義。Describes how to view the definition of a user-defined function. 檢視使用者定義函式View User-defined Functions