CREATE FUNCTION (SQL 資料倉儲)CREATE FUNCTION (SQL Data Warehouse)

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

SQL 資料倉儲SQL Data Warehouse 中建立使用者定義函數。Creates a user-defined function in SQL 資料倉儲SQL Data Warehouse. 使用者定義函式是一種 Transact-SQLTransact-SQL 常式,它會接受參數、執行動作 (例如複雜計算) 並且將該動作的結果傳回成值。A user-defined function is a Transact-SQLTransact-SQL routine that accepts parameters, performs an action, such as a complex calculation, and returns the result of that action as a value. 傳回值必須是純量 (單一) 值。The return value must be a scalar (single) value. 您可以使用這個陳述式來建立可用下列方式使用的可重複使用常式:Use this statement to create a reusable routine that can be used in these ways:

  • Transact-SQLTransact-SQL 陳述式中,例如 SELECTIn Transact-SQLTransact-SQL statements such as SELECT

  • 在呼叫函數的應用程式中In applications calling the function

  • 在另一個使用者自訂函數的定義中In the definition of another user-defined function

  • 若要在資料行上定義 CHECK 條件約束To define a CHECK constraint on a column

  • 取代預存程序To replace a stored procedure

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

--Transact-SQL Scalar Function Syntax  
CREATE FUNCTION [ schema_name. ] function_name   
( [ { @parameter_name [ AS ] parameter_data_type   
    [ = default ] }   
    [ ,...n ]  
  ]  
)  
RETURNS return_data_type  
    [ WITH <function_option> [ ,...n ] ]  
    [ AS ]  
    BEGIN   
        function_body   
        RETURN scalar_expression  
    END  
[ ; ]  
  
<function_option>::=   
{  
    [ SCHEMABINDING ]  
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]  
}  
  

引數Arguments

schema_nameschema_name
這是使用者定義函數所屬的結構描述名稱。Is the name of the schema to which the user-defined function belongs.

function_namefunction_name
這是使用者定義函數的名稱。Is the name of the user-defined function. 函式名稱必須符合識別碼的規則。另外,函式名稱在資料庫內必須是唯一的,且對於它的結構描述也必須是唯一的。Function names must comply with the rules for identifiers and must be unique within the database and to its schema.

注意

即使沒有指定參數,函數名稱後面仍需要括號。Parentheses are required after the function name even if a parameter is not specified.

@parameter_name@parameter_name
這是使用者定義函數中的參數。Is a parameter in the user-defined function. 您可以宣告一個或多個參數。One or more parameters can be declared.

函數最多可以有 2,100 個參數。A function can have a maximum of 2,100 parameters. 除非定義了參數的預設值,否則在執行函數時,使用者必須提供每個已宣告之參數的值。The value of each declared parameter must be supplied by the user when the function is executed, unless a default for the parameter is defined.

使用 @ 記號當做第一個字元來指定參數名稱。Specify a parameter name by using an at sign (@) as the first character. 參數名稱必須符合識別碼的規則。The parameter name must comply with the rules for identifiers. 對函數而言,參數必須是本機參數;相同的參數名稱可以用在其他函數中。Parameters are local to the function; the same parameter names can be used in other functions. 參數只能取代常數,不能用來取代資料表名稱、資料行名稱或其他資料庫物件的名稱。Parameters can take the place only of constants; they cannot be used instead of table names, column names, or the names of other database objects.

注意

在預存程序或使用者定義函數中傳遞參數,或在批次陳述式中宣告和設定變數時,不接受 ANSI_WARNINGS。ANSI_WARNINGS is not honored when you pass parameters in a stored procedure, user-defined function, or when you declare and set variables in a batch statement. 例如,如果將變數定義為 char(3) ,然後設定為大於三個字元的值,資料就會被截斷成定義的大小,而 INSERT 或 UPDATE 陳述式會執行成功。For example, if a variable is defined as char(3), and then set to a value larger than three characters, the data is truncated to the defined size and the INSERT or UPDATE statement succeeds.

parameter_data_typeparameter_data_type
參數資料類型。Is the parameter data type. 針對 Transact-SQLTransact-SQL 函式,SQL 資料倉儲SQL Data Warehouse 中支援的所有純量資料類型皆允許。For Transact-SQLTransact-SQL functions, all scalar data types supported in SQL 資料倉儲SQL Data Warehouse are allowed. 時間戳記 (rowversion) 資料類型是不支援的類型。The timestamp (rowversion) data type is not a supported type.

[ =default ][ =default ]
這是參數的預設值。Is a default value for the parameter. 如果已定義 default 值,則不需為該參數指定值,即可執行函式。If a default value is defined, the function can be executed without specifying a value for that parameter.

如果函數的參數有預設值,則必須在呼叫函數來擷取該預設值時指定關鍵字 DEFAULT。When a parameter of the function has a default value, the keyword DEFAULT must be specified when the function is called to retrieve the default value. 這個行為與使用預存程序中具有預設值的參數不一樣,因為在預存程序中,省略參數也意味著使用預設值。This behavior is different from using parameters with default values in stored procedures in which omitting the parameter also implies the default value.

return_data_typereturn_data_type
這是純量使用者定義函數的傳回值。Is the return value of a scalar user-defined function. 針對 Transact-SQLTransact-SQL 函式,SQL 資料倉儲SQL Data Warehouse 中支援的所有純量資料類型皆允許。For Transact-SQLTransact-SQL functions, all scalar data types supported in SQL 資料倉儲SQL Data Warehouse are allowed. 時間戳記 (rowversion) 資料類型是不支援的類型。The timestamp (rowversion) data type is not a supported type. 不允許資料指標和資料表的非純量類型。The cursor and table nonscalar types are not allowed.

function_bodyfunction_body
一連串的 Transact-SQLTransact-SQL 陳述式。Series of Transact-SQLTransact-SQL statements. function_body 無法包含 SELECT 陳述式,且無法參考資料庫資料。The function_body cannot contain a SELECT statement and cannot reference database data. function_body 無法參考資料表或檢視。The function_body cannot reference tables or views. 函式主體可以呼叫其他具決定性的函式,但是無法呼叫非決定性函式。The function body can call other deterministic functions but cannot call nondeterministic functions.

在純量函式中,function_body 是一系列的 Transact-SQLTransact-SQL 陳述式,這些陳述式會一起評估為純量值。In scalar functions, function_body is a series of Transact-SQLTransact-SQL statements that together evaluate to a scalar value.

scalar_expressionscalar_expression
指定純量函數傳回的純量值。Specifies the scalar value that the scalar function returns.

<function_option>::=<function_option>::=

指定此函數將會有下列其中一個或多個選項。Specifies that the function will have one or more of the following options.

SCHEMABINDINGSCHEMABINDING
指定函數必須繫結到它所參考的資料庫物件。Specifies that the function is bound to the database objects that it references. 當指定 SCHEMABINDING 時,無法依照會影響函數定義的方式來修改基底物件。When SCHEMABINDING is specified, the base objects cannot be modified in a way that would affect the function definition. 您必須先修改或卸除函數定義才能移除對於要修改之物件的相依性。The function definition itself must first be modified or dropped to remove dependencies on the object that is to be modified.

只有在下發生下列其中一個動作時,才會移除函數與其參考的物件之間的繫結:The binding of the function to the objects it references is removed only when one of the following actions occurs:

  • 已卸除這個函數。The function is dropped.

  • 您可以利用未指定 SCHEMABINDING 選項的 ALTER 陳述式來修改函數。The function is modified by using the ALTER statement with the SCHEMABINDING option not specified.

只有當下列條件成立時,函數才可繫結結構描述:A function can be schema bound only if the following conditions are true:

  • 函式參考的任何使用者定義函式也繫結結構描述。Any user-defined functions referenced by the function are also schema-bound.

  • 函式和函式所參考的其他 UDF 會使單一部分或兩部分名稱進行參考。The functions and other UDFs referenced by the function are referenced using a one-part or two-part name.

  • 僅有內建函式以及相同資料庫中的其他 UDF 可以在 UDF 的主體內參考。Only built-in functions and other UDFs in the same database can be referenced within the body of UDFs.

  • 執行 CREATE FUNCTION 陳述式的使用者在函數參考的資料庫物件上有 REFERENCES 權限。The user who executed the CREATE FUNCTION statement has REFERENCES permission on the database objects that the function references.

若要移除 SCHEMABINDING,請使用 ALTERTo remove SCHEMABINDING use ALTER

RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUTRETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT
指定純量值函式的 OnNULLCall 屬性。Specifies the OnNULLCall attribute of a scalar-valued function. 若未指定,預設情況下意味著 CALLED ON NULL INPUT。If not specified, CALLED ON NULL INPUT is implied by default. 這表示,即使傳遞 NULL 做為引數,函數主體仍會執行。This means that the function body executes even if NULL is passed as an argument.

最佳作法Best Practices

如果未以 SCHEMABINDING 子句建立使用者定義函數,叫用該函數時,對基礎物件所進行的變更可能會影響函數的定義並產生非預期的結果。If a user-defined function is not created with the SCHEMABINDING clause, changes that are made to underlying objects can affect the definition of the function and produce unexpected results when it is invoked. 建議您實作下列其中一個方法,以確保函數不會因為其基礎物件的變更而變成過期:We recommend that you implement one of the following methods to ensure that the function does not become outdated because of changes to its underlying objects:

  • 當您要建立函數時,指定 WITH SCHEMABINDING 子句。Specify the WITH SCHEMABINDING clause when you are creating the function. 這可以確保系統無法修改函數定義中參考的物件 (除非同時修改函數)。This ensures that the objects referenced in the function definition cannot be modified unless the function is also modified.

互通性Interoperability

以下是函數中的有效陳述式:The following statements are valid in a function:

  • 指派陳述式。Assignment statements.

  • 流程控制陳述式 (但不包括 TRY...CATCH 陳述式)。Control-of-Flow statements except TRY...CATCH statements.

  • DECLARE 陳述式 - 定義區域資料變數。DECLARE statements defining local data variables.

限制事項Limitations and Restrictions

使用者定義函數不能用來執行修改資料庫狀態的動作。User-defined functions cannot be used to perform actions that modify the database state.

使用者定義函數可以具有巢狀結構;也就是說,某個使用者定義函數可以呼叫另一個使用者定義函數。User-defined functions can be nested; that is, one user-defined function can call another. 被呼叫的函數開始執行時,巢狀層級會遞增;被呼叫的函數完成執行時,巢狀層級會遞減。The nesting level is incremented when the called function starts execution, and decremented when the called function finishes execution. 使用者定義函數所建立的巢狀結構最多可以有 32 個層級。User-defined functions can be nested up to 32 levels. 超過巢狀層級上限會導致整個呼叫函數鏈結失敗。Exceeding the maximum levels of nesting causes the whole calling function chain to fail.

中繼資料Metadata

此小節列出您可以用來傳回使用者定義函式之中繼資料的系統目錄檢視表。This section lists the system catalog views that you can use to return metadata about user-defined functions.

sys.sql_modules:顯示 Transact-SQLTransact-SQL 使用者定義函式的定義。sys.sql_modules : Displays the definition of Transact-SQLTransact-SQL user-defined functions. 例如:For example:

SELECT definition, type   
FROM sys.sql_modules AS m  
JOIN sys.objects AS o   
    ON m.object_id = o.object_id   
    AND type = ('FN');  
GO  
  

sys.parameters:顯示使用者定義函數中定義之參數的相關資訊。sys.parameters : Displays information about the parameters defined in user-defined functions.

sys.sql_expression_dependencies:顯示函數所參考的基礎物件。sys.sql_expression_dependencies : Displays the underlying objects referenced by a function.

權限Permissions

需要資料庫中的 CREATE FUNCTION 權限,以及此函數建立所在之結構描述上的 ALTER 權限。Requires CREATE FUNCTION permission in the database and ALTER permission on the schema in which the function is being created.

範例:Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW)平行處理資料倉儲Parallel Data WarehouseExamples: Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW) and 平行處理資料倉儲Parallel Data Warehouse

A.A. 使用純量值使用者定義函式來變更資料類型Using a scalar-valued user-defined function to change a data type

這個簡單的函式會採用 int 資料類型作為輸入,並傳回 decimal(10,2) 資料類型作為輸出。This simple function takes a int data type as an input, and returns a decimal(10,2) data type as an output.

CREATE FUNCTION dbo.ConvertInput (@MyValueIn int)  
RETURNS decimal(10,2)  
AS  
BEGIN  
    DECLARE @MyValueOut int;  
    SET @MyValueOut= CAST( @MyValueIn AS decimal(10,2));  
    RETURN(@MyValueOut);  
END;  
GO  
  
SELECT dbo.ConvertInput(15) AS 'ConvertedValue';  

另請參閱See Also

ALTER FUNCTION (SQL Server PDW) ALTER FUNCTION (SQL Server PDW)
DROP FUNCTION (SQL Server PDW)DROP FUNCTION (SQL Server PDW)