CREATE FUNCTION

適用於:Microsoft Fabric 中的 SQL 分析端點及 Microsoft Fabric 中的 Warehouse

CREATE FUNCTION 建立內嵌表格值函數與純量函數。

注意

外部 UDF 是 Fabric Data Warehouse 的預覽功能。

本文適用於Microsoft Fabric Data Warehouse及Fabric項目的SQL分析端點。 其他平台請參見 CREATE FUNCTION (Transact-SQL)。

使用者定義函式是一種 Transact-SQL 例程,接受參數,執行如複雜計算等動作,並將該動作的結果以值形式回傳。 純量函式會傳回純量值,例如數位或字串。 使用者定義的數據表值函式 (TVF) 會傳回數據表。

請使用 CREATE FUNCTION 來建立一個可重複使用的 T-SQL 例程,並可用以下方式:

  • 在 Transact-SQL 陳述中,例如 SELECT。
  • 在 Warehouse 中,Transact-SQL 資料操作語句(DML) UPDATE如 、 INSERT、 DELETE和 。
  • 在應用程式中呼叫該函式。
  • 在另一個使用者定義函數的定義中。
  • 用來取代儲存程序。

如果沒有新函式,指定 CREATE OR ALTER FUNCTION 要建立一個新函式,或在單一語句中修改現有函式。

Transact-SQL 語法慣例

語法

純量函式語法

CREATE [ OR ALTER ] 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>::=
{
    [ INLINE = AUTO ]
  | [ SCHEMABINDING ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
}

內嵌數據表值函式語法

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] parameter_data_type
    [ = default ] }
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH SCHEMABINDING ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

外部函式語法

外部 UDF 是一個參考外部 Fabric 使用者資料函式的函式。

CREATE FUNCTION [ schema_name. ] function_name
[ RETURNS return_data_type ]
AS EXTERNAL FUNCTION external_function_set_name.external_function_name
[ ; ]

注意

外部 UDF 是 Fabric Data Warehouse 的預覽功能。

該CREATE FUNCTION語句會自動從參考的外部 Fabric 使用者資料函式的定義中推導出回傳類型、參數和參數類型。 如果外部 Fabric 使用者資料函式的簽章改變,您必須重新建立該函式,以同步其定義與更新的簽章。

引數

schema_name

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

function_name

用戶定義函數的名稱。 函式名稱必須遵循識別碼規則,並在資料庫及其結構中保持唯一。

對於原生 T-SQL 純量與內嵌資料表值函數定義,即使沒有參數,也請在函式名稱後加上括號。 外部函式定義則使用獨立 AS EXTERNAL FUNCTION 語法。

@ parameter_name

用戶定義函數中的參數。 你可以宣告一個或多個參數。

一個函數最多可包含 2,100 個參數。 當使用者或應用程式呼叫函式時,除非你定義該參數的預設值,否則你必須提供每個宣告參數的值。

使用 "at" 記號 ( @ ) 當作第一個字元來指定參數名稱。 參數名稱必須遵循識別碼的規則。 這些參數是函數的本機參數。 您可以在其他函式中使用相同的參數名稱。 參數只能取代常數。 它們不能取代資料表名稱、欄位名稱或其他資料庫物件的名稱。

ANSI_WARNINGS 當您在儲存程序、使用者自訂函數中傳入參數,或在批次陳述式中宣告並設定變數時,不會被執行。 例如,如果你將變數定義為 char(3),然後設定大於三個字元的值,資料就會被截斷到定義大小,SQL 陳述式就會成功。

parameter_data_type

參數數據類型。 針對 Transact-SQL 函式,允許 支援的所有純量數據類型 。

[ = 預設 ]

參數的預設值。 如果你定義 了預設 值,就可以執行函式而不指定該參數的值。

當函式的參數有預設值時,你必須在呼叫該函式時指定關鍵字 DEFAULT 以取得預設值。 這個行為與使用預存程序中具有預設值的參數不一樣,因為在預存程序中,省略參數也意味著使用預設值。

return_data_type

標量使用者定義函數的返回值。

對於 Fabric Data Warehouse 函式,你可以使用所有資料型別,除了 rowversion/時間戳記。 像 桌子 這種非純量類型是不被允許的。

對於外部 UDF(預覽),該 RETURNS 條款為可選。 若省略,回傳類型會自動從參考的 Fabric 使用者資料函式的回傳類型推斷。 你可以指定 RETURNS 子句來覆蓋推斷回傳類型。

function_body

一系列 Transact-SQL 語句。

在純量函式中, function_body 是一系列 Transact-SQL 語句,一起評估為純量值,其中包括:

  • 單一語句表達式
  • 多語句表達式(IF/ELSE 及 BEGIN/END 區塊)
  • 局部變數
  • 可用的內建 SQL 函式呼叫
  • 呼叫其他UDF
  • SELECT 陳述式,以及對資料表、檢視與內嵌資料表值函式的參考
  • 流程控制語句,包括 IF/ELSE、 WHILE、 BREAK、 及 CONTINUERETURN

對於外部 UDF(預覽版),你無法指定function_body,因為函式實作是在外部定義的,包含在相關的 Fabric 使用者資料函式中。 資料庫僅儲存函式中繼資料。 可執行邏輯位於外部函式定義中。

scalar_expression

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

select_stmt

單 SELECT 一語句,定義內嵌數據表值函式的傳回值。 對於內嵌表格值函數,則沒有函數主體。 該資料表是單一 SELECT 語句的結果集。

TABLE

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

在內嵌 TVF(預覽)中,你透過單一TABLE語句定義SELECT回傳值。 內聯函數沒有關聯的 return 變數。

<function_option>

Fabric Data Warehouse中,ENCRYPTION和 EXECUTE AS 關鍵字不支援。

支援的功能選項包括:

直列 = 自動

規定是否可以建立或修改純量使用者定義函數,而不考慮內嵌需求。 該 INLINE 條款為可選條款。 對於可線化標量 UDF,指定 INLINE = AUTO 不會改變其線化性或執行行為。

關於支援的獨立呼叫模式,請參閱 為獨立執行建立純量 UDF。

SCHEMABINDING

指定函數必須繫結到它所參考的資料庫物件。 當你指定 SCHEMABINDING時,你無法修改底層物件(例如視圖或資料表),以影響函式定義的方式。 你必須先修改或刪除函式定義,以移除對你想修改物件的依賴。

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

  • 你去掉這個函數。

  • 你ALTER輸入函式陳述,然後移除選項。SCHEMABINDING

只有當以下條件成立時,函式才能進行結構綁定:

  • 函式所參考的任何使用者定義函式也是結構綁定的。

  • 函式透過兩部分名稱來參考物件。

  • 在 UDF 主體中,你只能參考內建函式和其他 UDF 在同一資料庫中。

  • 執行該 CREATE FUNCTION 語句的使用者對函式所參考的資料庫物件擁有 REFERENCES 權限。

若要移除 SCHEMABINDING,請使用 ALTER。

在 NULL 輸入時返回 NULL |在 NULL 輸入時調用

指定 OnNULLCall 純量值函式的屬性。 如果你沒有指定這個屬性, CALLED ON NULL INPUT 預設是隱含的,即使以參數形式傳遞,函式本體仍會 NULL 執行。

作為外部 FUNCTION

參考 Fabric 使用者資料函數(UDF)。 當函式被呼叫時,執行會委派給被參考的 Fabric 使用者資料函式,邏輯會在此執行,結果會回傳給 T-SQL 呼叫者。

此子句接受以下參數:

  • 函數集名稱:包含 Fabric 使用者資料函數的函數集名稱。 每個 Fabric 使用者資料函式都必須屬於一個函式集。
  • 函式名稱:要參照的 Fabric 使用者資料函式名稱。

以下語法說明這些參數的指定方式:

CREATE FUNCTION [schema_name.]function_name
AS EXTERNAL FUNCTION <<functionset_name>>.<<python_function_name>>

由於函式是遠端執行,呼叫外部函式通常比執行原生 T-SQL 使用者定義函式的延遲更高。 當所需的商業邏輯是在 Fabric 中實作,且無法直接用 T-SQL 表達時,請使用外部函式。

該CREATE FUNCTION語句會自動從參考的外部 Fabric 使用者資料函式的定義中推導出回傳類型、參數和參數類型。 如果外部 Fabric 使用者資料函式的簽章改變,您必須重新建立該函式,以同步其定義與更新的簽章。

最佳做法

  • 把你的使用者自訂函式寫成可內列化。 欲了解更多關於內嵌概念的資訊,請參見 純量 UDF 的內聯。 關於如何讓純量 UDF 可線列的範例,請參見 Microsoft Fabric Data Warehouse 中建立純量 UDF。

  • 如果你沒有建立帶有結構綁定的使用者自訂函式,底層物件的變更會影響函式的定義,並在呼叫該函式時產生意想不到的結果。 當你指定 WITH SCHEMABINDING 建立函式的時間時,你確保後續對底層物件的變更不會改變或破壞函式的行為。

  • 如果可能,請將你的邏輯實作為 T-SQL 使用者定義函式(UDF)。 僅用於無法在 T-SQL 語言中實作的功能,使用外部 UDF。 此方法有助於降低遠端執行負擔,並通常提供更好的查詢效能。

互通性

內嵌數據表值使用者定義函式

內嵌表格值函式只接受單一 SELECT 陳述。

純量使用者定義函式

  • 當標量 UDF 非線列時, CREATE OR ALTER 語句會以自訂錯誤訊息拒絕 DDL 語句。

  • 非線性函數可透過指定 INLINE=AUTO 函數選項來建立。 非線列函式不能用於 SELECT ... FROM 使用者資料表的查詢中。 INLINE=AUTO 這並不讓功能變得不可行。

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

    • 指派陳述式。
    • 除了和 GOTO 陳述之外,控制流程陳述TRY...CATCH句。
    • DECLARE 定義本地資料變數的陳述式。
    • 呼叫內建函式。
    • 參考表/視圖/iTVF/其他純量 UDF。
  • DML 陳述句不允許出現在純量使用者定義函式中。

中繼資料

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

  • sys.sql_modules:顯示 Transact-SQL 使用者自訂函數的定義,以及可內嵌性資訊。 例如:

      SELECT 
          SCHEMA_NAME(o.schema_id) AS SchemaName,
          o.name AS FunctionName,
          m.definition AS FunctionDefinition,
          m.is_inlineable AS Inlineable,
          m.inline_eligibility_mask AS InlineEligibilityMask
      FROM sys.objects o
      JOIN sys.sql_modules m ON o.object_id = m.object_id
      WHERE o.type = 'FN';
    
  • sys.parameters:顯示使用者自訂函式中定義的參數資訊。 以下範例使用 sys.parameters 目錄檢視來顯示函式簽名,包括回傳類型與參數資料型別:

     WITH metadata AS
     (
         SELECT  o.schema_id, o.object_id, o.name, p.parameter_id, p.name AS parameter_name, t.name AS type_name
         FROM sys.objects o JOIN sys.parameters p ON p.object_id = o.object_id JOIN sys.types t ON t.user_type_id = p.user_type_id
         WHERE o.type IN ('FN', 'XF')
     ),
     signature AS (
     SELECT SCHEMA_NAME(schema_id) as schema_name, name, ANY_VALUE(CASE WHEN parameter_id = 0 THEN type_name END) return_type, CONCAT(
         SCHEMA_NAME(schema_id), '.', name, '(',STRING_AGG(CASE WHEN parameter_id > 0 THEN CONCAT(parameter_name, ' ', type_name) END, ', ' ) WITHIN GROUP (ORDER BY parameter_id),
         ') -> ',  MAX(CASE WHEN parameter_id = 0 THEN type_name END)
     ) AS signature
     FROM metadata GROUP BY schema_id, object_id, name
     )
     SELECT *
     FROM signature;
    
  • sys.sql_expression_dependencies:顯示函數所參考的底層物件。

權限

網狀架構工作區管理員、成員和參與者角色的成員可以建立函式。

純量 UDF 的內嵌

Microsoft Fabric Data Warehouse在查詢編譯時自動內嵌符合資格的純量 UDF。

表達式區塊內聯會將函式的語句(包括分支與迴圈)編譯成純量表達式,作為呼叫查詢的一部分執行。 純量 UDF 內嵌會將函式主體轉換為表達式或子查詢,作為呼叫查詢的一部分。

一個函數可以同時滿足兩種技術的要求,但引擎會根據其定義來選擇其中一種。 同一查詢中的不同函式可以使用不同的技術。

隨著 Microsoft Fabric Data Warehouse 擴充內嵌功能,現有函數會自動獲得這些功能。

支援的函數構造

下表比較了支援的函數-體結構。

函數-體構造 表達式區塊內聯 純量 UDF 函數內嵌
純量表達式、區域變數,以及 IF/ELSE Supported Supported
WHILE 迴圈,包括 BREAK 和 CONTINUE Supported 不支援
多個 RETURN 語句 Supported 不支援
NEWID() Supported 不支援
時間依賴函數 (例如, GETDATE() 以及 SYSDATETIME()) Supported 不支援
表格、檢視或內嵌 TVF 存取 不支援 Supported
對純量 UDF 的呼叫* 不支援 Supported

* 使用者自訂函式可以巢狀:一個 UDF 可以呼叫另一個。 當被呼叫的函式開始時,巢狀層級會增加;函式結束時則降低。 當 UDF 主體參考表格、檢視或內嵌 TVF 時,Microsoft Fabric Data Warehouse支援最多四個巢狀層級,否則最多可有 32 層。 超過巢狀限制會導致呼叫函式鏈失敗。

內建函數

兩種內嵌技術對內建功能(包括 系統功能)的支援有所不同。

例如,建立一個 UDF 並使用 @@ROWCOUNT 「無 INLINE = AUTO 回傳」錯誤 19841。 錯誤訊息包含以下文字:

Function <function_name> cannot be inlined.
Expression block: Unsupported built-in @@ROWCOUNT found.
Scalar UDF inlining: Unsupported built-in @@ROWCOUNT found.

如需特定函數定義,請參閱 「檢查是否可以內嵌標量 UDF」。

支援的呼叫查詢

下表比較了對可串列純量 UDF 的呼叫查詢支援情況。

查詢功能 表達式區塊內聯 純量 UDF 函數內嵌
UDF 呼叫清單,SELECTWHERE或JOIN ... ON Supported Supported
直接 UDF 呼叫或GROUP BYHAVING Supported 不支援
直接 UDF 呼叫 ORDER BY Supported 不支援
呼叫查詢中的 CTE Supported 不支援
ROLLUP、 CUBE,或 GROUPING SETS 不支援 不支援

如果任何 UDF 呼叫都依賴純量 UDF 內聯,整個查詢必須符合該技術對呼叫查詢的要求。

使用 Scalar UDF 內聯時,可以將 UDF 結果投影成SELECT清單,並依其欄位別名或序數排序,而非直接呼叫 UDF 。ORDER BY 關於查詢重寫範例,請參閱 「為 Scalar UDF 內嵌調整呼叫查詢」。 關於其他執行限制,請參閱「限制」。

檢查純量 UDF 是否可以內嵌

sys.sql_modules目錄檢視會根據函數定義來描述資格。 is_inlineable 是 1 用於可線化定義,其他 0 則如此。 inline_eligibility_mask 識別符合資格的技術:

inline_eligibility_mask 定義資格
0 兩種技巧都不是
1 純量 UDF 函數內嵌
2 表達式區塊內聯
3 兩種方法都行

這些值描述的是函式的定義,而非特定查詢的執行計畫。

請使用以下範例查詢來檢查純量 UDF 是否可內列:

SELECT
    SCHEMA_NAME(b.schema_id) AS function_schema_name,
    b.name AS function_name,
    b.type_desc AS function_type,
    a.is_inlineable,
    a.inline_eligibility_mask
FROM sys.sql_modules AS a
     INNER JOIN sys.objects AS b
         ON a.object_id = b.object_id
WHERE b.type IN ('FN');

限制

  • 在不支援的查詢情境中呼叫純量 UDF 可能會回傳錯誤 Scalar UDF execution is currently unavailable in this context.
  • 依賴純量 UDF 內聯的查詢,如果包含超過 10 個 UDF 呼叫,可能會失敗。

範例

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',
            sm.is_inlineable AS 'Inlineable'
        FROM sys.sql_modules AS sm
        INNER JOIN sys.objects AS o ON sm.object_id = o.object_id
        WHERE o.type LIKE '%' + @objectType + '%'
        );
GO

呼叫回傳所有內嵌表值函式(IF):

SELECT * FROM dbo.ModulesByType('IF'); -- SQL_INLINE_TABLE_VALUED_FUNCTION

或者,尋找所有純量函式 (FN):

SELECT * FROM dbo.ModulesByType('FN'); -- SQL_SCALAR_FUNCTION

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

這個簡單的範例使用先前建立的內嵌 TVF 來示範如何透過使用 CROSS APPLY。 在這裡,你從兩個sys.objects欄位中選取所有欄位,並選取該欄位中所有相符ModulesByType資料列的結果type。 欲了解更多使用 APPLY,請參閱 FROM 子句加上 JOIN, APPLY, PIVOT (Transact-SQL)。

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

C. 建立純量 UDF 函式

下列範例會建立可內嵌的純量 UDF,以遮罩輸入文字。

CREATE OR ALTER FUNCTION [dbo].[cleanInput] (@InputString VARCHAR(100))
    RETURNS VARCHAR(50)
    AS
    BEGIN
        DECLARE @Result VARCHAR(50);
        DECLARE @CleanedInput VARCHAR(50);

        -- Trim whitespace
        SET @CleanedInput = LTRIM(RTRIM(@InputString));

        -- Handle empty or null input
        IF @CleanedInput = '' OR @CleanedInput IS NULL
        BEGIN
            SET @Result = '';
        END
        ELSE IF LEN(@CleanedInput) <= 2
        BEGIN
            -- If string length is 1 or 2, just return the cleaned string
            SET @Result = @CleanedInput;
        END
        ELSE
        BEGIN
            -- Construct the masked string
            SET @Result = 
                LEFT(@CleanedInput, 1) +
                REPLICATE('*', LEN(@CleanedInput) - 2) +
                RIGHT(@CleanedInput, 1);
        END

        RETURN @Result
    END

您可以像這樣呼叫 函式:

DECLARE @input varchar(100) = '123456789';

SELECT dbo.cleanInput (@input) AS function_output;

在網狀架構數據倉儲中使用純量 UDF 的更多範例:

SELECT在 語句中:

SELECT TOP 10 
t.id, t.name, 
dbo.cleanInput (t.name) AS function_output
FROM dbo.MyTable AS t;

WHERE在 子句中:

 SELECT t.id, t.name, dbo.cleanInput(t.name) AS function_output
FROM dbo.MyTable AS t
WHERE dbo.cleanInput(t.name) = '1*******9';

JOIN在 子句中:

SELECT t1.id, t1.name, 
     dbo.cleanInput (t1.name) AS function_output, 
     dbo.cleanInput (t2.name) AS function_output_2
FROM dbo.MyTable1 AS t1
    INNER JOIN dbo.MyTable2 AS t2 
        ON dbo.cleanInput(t1.name)=dbo.cleanInput(t2.name);

ORDER BY在 子句中:

SELECT  t.id, t.name, dbo.cleanInput (t.name) AS function_output
FROM dbo.MyTable AS t
ORDER BY function_output;

在 Warehouse 中,你也可以從資料操作語句(如 INSERT、 、 UPDATE或 DELETE)呼叫純量 UDF 。 這些資料修改範例不適用於 SQL 分析端點。

SELECT t.id, t.name, dbo.cleanInput (t.name) AS function_output 
INTO dbo.MyTable_new
FROM dbo.MyTable AS t;

UPDATE t
SET t.mycolumn_new = dbo.cleanInput (t.name)
FROM dbo.MyTable AS t;

DELETE t
FROM dbo.MyTable AS t
WHERE dbo.cleanInput(t.name) = '1*******9';

D. 建立一個外部函式

以下範例在結構中建立一個外部geohashdbo函式,該函式在函式集中參考 geohash Fabric 使用者資料函spatial_functions式。 由於參數定義與回傳類型是從參考的 Fabric 使用者資料函式推斷而來,因此不必在語句中CREATE FUNCTION明確指定。

CREATE FUNCTION dbo.geohash
AS EXTERNAL FUNCTION spatial_functions.geohash;

一旦建立,外部函式即可像其他使用者定義或內建函式一樣用於 T-SQL 查詢。 只要支援函式呼叫,都能引用它,使 Fabric 使用者資料函式能無縫整合進 T-SQL 程式碼。

E. 建立純量 UDF 以供獨立執行

下列資料表建立與資料載入設定需要 Warehouse。 在 SQL 分析端點上,使用現有的來源支援資料表,並具備所需的欄位與資料。

CREATE TABLE dbo.Customer
(
    CustomerID INT NOT NULL,
    CustomerName VARCHAR(100) NOT NULL,
    CustomerType VARCHAR(20) NOT NULL,
    MemberSince DATE NOT NULL,
    YearToDateRevenue DECIMAL(18, 2) NOT NULL
);
GO

INSERT INTO dbo.Customer
(
    CustomerID,
    CustomerName,
    CustomerType,
    MemberSince,
    YearToDateRevenue
)
VALUES
    (1, 'Customer 1', 'Regular', '2020-01-15', 12000.00),
    (2, 'Customer 2', 'Premium', '2022-06-01', 24000.00),
    (3, 'Customer 3', 'VIP',     '2024-03-10', 36000.00);
GO

下列函數將資料表存取 GETUTCDATE()與 結合。 資料表存取會阻止表達式區塊內聯,並 GETUTCDATE() 阻止純量 UDF 內聯。

若沒有 WITH INLINE = AUTO,該定義在創建或變更時即被拒絕。 錯誤報告該函數無法內聯,並說明兩種技術的拒絕理由。 指定 WITH INLINE = AUTO 以允許建立:

CREATE OR ALTER FUNCTION dbo.GetCustomerTenureDays (@CustomerID INT)
RETURNS INT
WITH INLINE = AUTO
AS
BEGIN
    DECLARE @MemberSince DATE;

    SELECT @MemberSince = MemberSince
    FROM dbo.Customer
    WHERE CustomerID = @CustomerID;

    RETURN DATEDIFF(DAY, @MemberSince, GETUTCDATE());
END;
GO

函數被建立但仍非直列: is_inlineable 與 inline_eligibility_mask 皆 0為 。 獨立通話成功:

DECLARE @udf_output int;
SET @udf_output = dbo.GetCustomerTenureDays(1);
SELECT @udf_output as 'udf_output';

不支援在對使用者資料表查詢時呼叫該函式:

SELECT
    CustomerID,
    dbo.GetCustomerTenureDays(CustomerID) AS TenureDays
FROM dbo.Customer;

此查詢會回傳 Scalar UDF execution is currently unavailable in this context.

小提示

若要在查詢使用者資料表時使用此邏輯,請評估呼叫者目前的時間值並將其作為參數傳入。 關於重寫函數及呼叫查詢,請參閱 「將時間依賴值傳入資料存取 UDF」。

適用於:Azure Synapse Analytics

在 Azure Synapse Analytics 中建立一個用戶定義函數(UDF)。 使用者定義函數是一種 Transact-SQL 常式,它會接受參數、執行動作 (例如複雜計算) 並且將該動作的結果傳回成值。 .使用者定義的資料表值函數 (TVF) 會傳回 table 資料類型。

小提示

關於 Fabric Data Warehouse 的語法,請參見 for CREATE FUNCTION Fabric Data Warehouse 的版本。

  • 在 Azure Synapse Analytics 中, CREATE FUNCTION 可以使用內嵌數據表值函式的語法傳回數據表(預覽),也可以使用純量函式的語法傳回單一值。

  • 在 Azure Synapse Analytics 中的無伺服器 SQL 集區中,可以建立內嵌數據表值函式, CREATE FUNCTION 但不能建立純量函式。

    請使用此陳述建立一個可重複使用的例行程序,並以以下方式使用:

  • 在 Transact-SQL 語句中,例如 SELECT

  • 在調用函數

  • 在另一個使用者自訂函數的定義中

  • 若要在資料行上定義 CHECK 條件約束

  • 取代預存程序

  • 使用內嵌函式作為安全性原則的篩選述詞

Transact-SQL 語法慣例

語法

純量函式語法

-- Transact-SQL Scalar Function Syntax (in dedicated pools in Azure Synapse Analytics)
-- Not available in the serverless SQL pools in Azure Synapse Analytics

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
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 (or UPDATE )陳述句就會成功。

parameter_data_type

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

[ = 預設 ]

參數的預設值。 如果你定義 了預設 值,就可以執行函式而不指定該參數的值。

當函式的參數有預設值時,你必須在呼叫該函式時指定關鍵字 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回傳值。 內聯函數沒有關聯的 return 變數。

<function_option>

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

SCHEMABINDING

指定函數必須繫結到它所參考的資料庫物件。 當你指定 SCHEMABINDING時,你無法修改底層物件(例如視圖或資料表),以影響函式定義的方式。 你必須先修改或刪除函式定義,以移除對你想修改物件的依賴。

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

  • 你去掉這個函數。

  • 你ALTER輸入函式陳述,然後移除選項。SCHEMABINDING

只有當以下條件成立時,函式才能進行結構綁定:

  • 函式所參考的任何使用者定義函式也是結構綁定的。

  • 函式參考使用單部分或兩部分名稱。

  • 在 UDF 主體中,你只能參考內建函式和其他 UDF 在同一資料庫中。

  • 執行該 CREATE FUNCTION 語句的使用者對函式所參考的資料庫物件擁有 REFERENCES 權限。

若要移除 SCHEMABINDING,請使用 ALTER。

傳回 NULL 輸入上的 NULL | 在 NULL 輸入上呼叫

指定 OnNULLCall 純量值函式的屬性。 如果你沒有指定這個屬性, CALLED ON NULL INPUT 預設是隱含的,即使以參數形式傳遞,函式本體仍會 NULL 執行。

最佳做法

如果你沒有用 SCHEMABINDING 子句建立使用者定義函式,底層物件的變更可能會影響函式的定義,並在呼叫時造成意想不到的結果。 建立函式時請指定子句。WITH SCHEMABINDING 這個子句確保你無法修改函式定義中提到的物件,除非你同時修改函式。

互通性

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

  • 指派陳述式。

  • 控制流程語句,除了 TRY...CATCH 聲明。

  • 定義本地資料變數的 DECLARE 陳述式。

在內嵌表格值函式(預覽)中,你只能使用單一的 select 語句。

限制

你不能用使用者自訂函式來執行修改資料庫狀態的動作。

你可以巢狀使用者自訂函式。 一個使用者定義的函式可以呼叫另一個。 當被呼叫函式開始執行時,巢狀層級會遞增;當被呼叫函式執行結束時,巢狀層級則遞減。 如果你超過巢狀結構的最大層級,整個呼叫函式鏈就會失敗。

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

中繼資料

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

  • 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 權限。

範例

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

這個簡單的函式以 整數 資料型態為輸入,並回傳一個十 進位(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 池中沒有。

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

以下範例建立一個內嵌的表格值函式,透過參數過濾 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

你可以呼叫回傳所有 view (V) 物件的函式,並包含:

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

注意

內嵌資料表值函式可在無伺服器 SQL 池中使用,但預覽版則可在專用 SQL 池中提供。

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

這個簡單的範例使用先前建立的內嵌 TVF 來示範如何透過使用 CROSS APPLY。 在這個例子中,你從兩個sys.objects欄位中選取所有欄位,並選取該欄位上所有相符ModulesByType資料的結果type。 欲了解更多使用 APPLY,請參閱 FROM 子句加上 JOIN, APPLY, PIVOT (Transact-SQL)。

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

注意

內嵌資料表值函式可在無伺服器 SQL 池中使用,但預覽版則可在專用 SQL 池中提供。

後續步驟