CREATE FUNCTION （Azure Synapse Analytics 和 Microsoft Fabric）

發行項
04/26/2024

適用於：Azure Synapse Analytics Analytics Platform System (PDW) Microsoft Fabric 中的 SQL 分析端點 Microsoft Fabric 中的倉儲

在 Azure Synapse Analytics、Analytics Platform System （PDW）或 Microsoft Fabric 中建立使用者定義函式。使用者定義函數是一種 Transact-SQL 常式，它會接受參數、執行動作 (例如複雜計算) 並且將該動作的結果傳回成值。

在 Analytics Platform System (PDW)，傳回值必須為純量 (單一) 值。
在 Azure Synapse Analytics 中，CREATE FUNCTION 可以使用內嵌資料表值函數 (預覽) 的語法來傳回資料表，或是使用純量函數的語法傳回單一值。
在 Azure Synapse Analytics 中的Microsoft網狀架構和無伺服器 SQL 集區中，CREATE FUNCTION 可以建立內嵌數據表值函式，但不能建立純量函式。 .使用者定義的資料表值函數 (TVF) 會傳回 table 資料類型。

您可以使用這個陳述式來建立可用下列方式使用的可重複使用常式：
在 Transact-SQL 語句中，例如 SELECT
在呼叫函數的應用程式中
在另一個使用者自訂函數的定義中
若要在資料行上定義 CHECK 條件約束
取代預存程序
使用內嵌函式作為安全性原則的篩選述詞

Transact-SQL 語法慣例

Syntax

純量函式語法

-- Transact-SQL Scalar Function Syntax  (in dedicated pools in Azure Synapse Analytics and Parallel Data Warehouse)
-- Not available in the serverless SQL pools in Azure Synapse Analytics or Microsoft Fabric

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 ]  
}

內嵌數據表值函式語法

-- Transact-SQL Inline Table-Valued Function Syntax
-- Preview in dedicated SQL pools in Azure Synapse Analytics
-- Available in the serverless SQL pools in Azure Synapse Analytics and Microsoft Fabric
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] parameter_data_type
    [ = default ] }
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH SCHEMABINDING ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

引數

schema_name

使用者定義函式所屬的架構名稱。

function_name

用戶定義函數的名稱。函式名稱必須符合識別碼的規則，且在資料庫內及對其結構描述而言，必須是唯一的。

注意

即使沒有指定參數，函數名稱後面仍需要括號。

@parameter_name

這是使用者定義函數中的參數。您可以宣告一個或多個參數。

函數最多可以有 2,100 個參數。除非定義了參數的預設值，否則在執行函數時，使用者必須提供每個已宣告之參數的值。

使用 "at" 記號 ( @ ) 當作第一個字元來指定參數名稱。參數名稱必須符合識別碼的規則。對函數而言，參數必須是本機參數；相同的參數名稱可以用在其他函數中。參數只能取代常數，不能用來取代資料表名稱、資料行名稱或其他資料庫物件的名稱。

注意

ANSI_WARNINGS 當您在預存程式、使用者定義函式中傳遞參數，或在批次語句中宣告和設定變數時，則不會接受。例如，如果將變數定義為 char(3) ，然後設定為大於三個字元的值，資料就會被截斷成定義的大小，而 INSERT 或 UPDATE 陳述式會執行成功。

parameter_data_type

參數資料類型。針對 Transact-SQL 函數，會允許 Azure Synapse Analytics 中支援的所有純量資料類型。時間戳記 (rowversion) 資料類型是不支援的類型。

[ =default ]

這是參數的預設值。如果已定義 default 值，則不需為該參數指定值，即可執行函式。

如果函數的參數有預設值，則必須在呼叫函數來擷取該預設值時指定關鍵字 DEFAULT。這個行為與使用預存程序中具有預設值的參數不一樣，因為在預存程序中，省略參數也意味著使用預設值。

return_data_type

這是純量使用者定義函數的傳回值。針對 Transact-SQL 函數，會允許 Azure Synapse Analytics 中支援的所有純量資料類型。 rowversion/timestamp 數據類型不是支援的型別。不允許資料指標和資料表的非純量類型。

function_body

Transact-SQL 陳述式系列。 function_body不能包含 SELECT 語句，也無法參考資料庫數據。 function_body無法參考數據表或檢視表。函式主體可以呼叫其他具決定性的函式，但是無法呼叫非決定性函式。

在純量函式中，function_body 是一系列的 Transact-SQL 陳述式，這些陳述式會一起評估為純量值。

scalar_expression

指定純量函數傳回的純量值。

select_stmt

這是定義內嵌數據表值函式傳回值的單 SELECT 一語句。對於內嵌數據表值函式，沒有函式主體;數據表是單 SELECT 一語句的結果集。

TABLE

指定資料表值函式 (TVF) 的傳回值是資料表。只有常數和 @local_variables 才能傳遞給 TVF。

在內嵌TVF中，TABLE 傳回值是透過單 SELECT 一語句來定義。內嵌函數沒有相關聯的傳回變數。

<function_option>

指定函式具有下列一或多個選項。

SCHEMABINDING

指定函數必須繫結到它所參考的資料庫物件。當指定 SCHEMABINDING 時，無法依照會影響函數定義的方式來修改基底物件。您必須先修改或卸除函數定義才能移除對於要修改之物件的相依性。

只有在下發生下列其中一個動作時，才會移除函數與其參考的物件之間的繫結：

已卸除這個函數。
您可以利用未指定 SCHEMABINDING 選項的 ALTER 陳述式來修改函數。

只有當下列條件成立時，函數才可繫結結構描述：

函式參考的任何使用者定義函式也繫結結構描述。
函式和函式所參考的其他 UDF 會使單一部分或兩部分名稱進行參考。
僅有內建函式以及相同資料庫中的其他 UDF 可以在 UDF 的主體內參考。
執行 CREATE FUNCTION 語句的使用者具有函式所參考之資料庫物件的 REFERENCES 許可權。

若要移除 SCHEMABINDING，請使用 ALTER。

RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT

指定純量值函式的 OnNULLCall 屬性。如果未指定， CALLED ON NULL INPUT 則預設為隱含，即使 NULL 傳遞為自變數，函式主體也會執行。

最佳做法

如果未以 SCHEMABINDING 子句建立使用者定義函數，叫用該函數時，對基礎物件所進行的變更可能會影響函數的定義並產生非預期的結果。建議您實作下列其中一個方法，以確保函數不會因為其基礎物件的變更而變成過期：

當您要建立函式時，請指定 WITH SCHEMABINDING 子句。這可以確保系統無法修改函數定義中參考的物件 (除非同時修改函數)。

互通性

以下是純量值函式中的有效陳述式：

指派陳述式。
流程控制陳述式 (但不包括 TRY...CATCH 陳述式)。
DECLARE 陳述式 - 定義區域資料變數。

在內嵌資料表值函式 (預覽) 中，僅允許單一 SELECT 陳述式。

限制

使用者定義函數不能用來執行修改資料庫狀態的動作。

使用者定義函數可以具有巢狀結構；也就是說，某個使用者定義函數可以呼叫另一個使用者定義函數。被呼叫的函數開始執行時，巢狀層級會遞增；被呼叫的函數完成執行時，巢狀層級會遞減。使用者定義函數所建立的巢狀結構最多可以有 32 個層級。超過巢狀層級上限會導致整個呼叫函數鏈結失敗。

物件，包括函式，無法在 Azure Synapse Analytics 中無伺服器 SQL 集區的資料庫中建立 master 。

中繼資料

此小節列出您可以用來傳回使用者定義函式之中繼資料的系統目錄檢視表。

sys.sql_modules：顯示 Transact-SQL 使用者定義函數的定義。例如：

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

sys.parameters：顯示使用者定義函數中定義之參數的相關資訊。
sys.sql_expression_dependencies：顯示函數所參考的基礎物件。

權限

需要資料庫中的 CREATE FUNCTION 權限，以及此函數建立所在之結構描述上的 ALTER 權限。

範例：Azure Synapse Analytics 和 Analytics Platform System (PDW)

A. 使用純量值使用者定義函數來變更數據類型

這個簡單的函式會採用 int 資料類型作為輸入，並傳回 decimal(10,2) 資料類型作為輸出。

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';

注意

無伺服器 SQL 集區或 Microsoft Fabric 中無法使用純量函式。

範例：Azure Synapse Analytics

A. 建立內嵌數據表值函式

下列範例會建立內嵌資料表值函式以傳回模組上的某些重要資訊，並依 objectType 參數篩選。其中包含使用參數呼叫函式時傳回所有模組的 DEFAULT 預設值。此範例會利用在中繼資料中提到的一些系統目錄檢視。

CREATE FUNCTION dbo.ModulesByType(@objectType CHAR(2) = '%%')
RETURNS TABLE
AS
RETURN
(
    SELECT 
        sm.object_id AS 'Object Id',
        o.create_date AS 'Date Created',
        OBJECT_NAME(sm.object_id) AS 'Name',
        o.type AS 'Type',
        o.type_desc AS 'Type Description', 
        sm.definition AS 'Module Description'
    FROM sys.sql_modules AS sm  
    JOIN sys.objects AS o ON sm.object_id = o.object_id
    WHERE o.type like '%' + @objectType + '%'
);
GO

接著可以使用下列內容呼叫函式，以傳回所有檢視 (V) 物件：

select * from dbo.ModulesByType('V');

注意

內嵌資料表值函數可在無伺服器 SQL 集區中使用，但在專用 SQL 集區中處於預覽。

B. 合併內嵌數據表值函式的結果

這個簡單的範例會使用先前所建立的內嵌 TVF，示範如何使用 CROSS APPLY 將其結果與其他資料表合併。在這裡，我們會從中sys.objects選取所有數據行，以及類型數據行上符合所有數據列的結果ModulesByType。如需使用 APPLY 的詳細資料，請參閱 FROM 子句與 JOIN、APPLY、PIVOT。

SELECT * 
FROM sys.objects o
CROSS APPLY dbo.ModulesByType(o.type);
GO

注意