CREATE FUNCTION (Azure Synapse Analytics および Microsoft Fabric)

[アーティクル]
05/23/2023

適用対象: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 で、インラインテーブル値関数 (プレビュー) の構文を使用してテーブルを返したり、スカラー関数の構文を使用して 1 つの値を返すことができます。
Microsoft Fabric および Azure Synapse Analytics のサーバーレス SQL プールでは、CREATE FUNCTION を使用してインラインテーブル値関数を作成できますが、スカラー関数は作成できません。ユーザー定義テーブル値関数 (TVF) は、table データ型を返します。

このステートメントを使用して、次の方法で使用できる再利用可能なルーチンを作成します。
SELECT などの Transact-SQL ステートメント内で使用する
関数を呼び出すアプリケーション内で使用する
別のユーザー定義関数の定義内で使用する
列の CHECK 制約を定義する
ストアドプロシージャを置換する
セキュリティポリシーのフィルター述語としてのインライン関数を使用します。

Transact-SQL 構文表記規則

構文

-- 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
ユーザー定義関数の名前です。関数名は、識別子のルールに従っている必要があります。また、データベース内、およびそのスキーマに対して一意である必要があります。

Note

パラメーターを指定しない場合でも、関数名の後にはかっこが必要です。

@parameter_name
ユーザー定義関数内のパラメーターです。 1 つ以上のパラメーターを宣言できます。

1 つの関数では、最高 2,100 個のパラメーターを使用できます。宜言した各パラメーターの値は、関数の実行時に、ユーザーが指定する必要があります (そのパラメーターの既定値が定義されていない場合)。

最初の文字をアットマーク (@) にしてパラメーター名を指定します。パラメーター名は識別子のルールに従っている必要があります。パラメーターは関数に対してローカルです。同じパラメーター名を他の関数で使用できます。パラメーターは定数の代わりとしてのみ使用できます。パラメーターは、テーブル名、列名、またはその他のデータベースオブジェクト名の代わりに使用することはできません。

Note

ストアドプロシージャまたはユーザー定義関数でパラメーターを渡すとき、あるいはバッチステートメントで変数を宣言して設定するときには、ANSI_WARNINGS が無視されます。たとえば、変数を char(3) と定義し、これに 4 文字以上の値を設定すると、データが定義されたサイズに合わせて切り捨てられてから、INSERT または UPDATE ステートメントが成功します。

parameter_data_type
パラメーターデータ型。 Transact-SQL 関数は、Azure Synapse Analytics でサポートされるすべてのスカラーデータ型を許可します。タイムスタンプ (rowversion) データ型はサポートされていません。

[ =default ]
パラメーターの既定値です。 default 値が定義されている場合は、パラメーターに値を指定せずに関数を実行できます。

関数のパラメーターに既定値がある場合に、既定値を取得する目的でその関数を呼び出すときは、DEFAULT キーワードを指定する必要があります。この動作は、ストアドプロシージャで既定値を持つパラメーターを使用する場合とは異なります。ストアドプロシージャの場合は、パラメーターを省略すると既定値が暗黙的に使用されます。

return_data_type
スカラーユーザー定義関数の戻り値です。 Transact-SQL 関数は、Azure Synapse Analytics でサポートされるすべてのスカラーデータ型を許可します。タイムスタンプ (rowversion) データ型はサポートされていません。カーソルとテーブルの非スカラー型を指定することはできません。

function_body
一連の Transact-SQL ステートメント。 Function_body は SELECT ステートメントを含めることはできませんし、データベースのデータを参照することはできません。 Function_body は、テーブルまたはビューを参照できません。関数の本体では、その他の決定的な関数を呼び出すことができますが、非決定的関数を呼び出すことはできません。

スカラー関数の function_body は、総合してスカラー値と評価される一連の Transact-SQL ステートメントです。

scalar_expression
スカラー関数が返すスカラー値を指定します。

select_stmt: インラインテーブル値関数の戻り値を定義する単一の SELECT ステートメントです。インラインテーブル値関数の場合、テーブルは単一の SELECT ステートメントの結果セットであり、関数の本体がありません。

TABLE: テーブル値関数 (TVF) の戻り値がテーブルになるように指定します。 TVF に渡すことができるのは、定数と @local_variables のみです。

インライン TVF (プレビュー) の TABLE 戻り値は、単一の SELECT ステートメントを使用して定義されます。インライン関数には、関連付けられている戻り変数はありません。

<function_option>::=

関数に以下のオプションを 1 つ以上指定します。

SCHEMABINDING
参照するデータベースオブジェクトに対して、その関数がバインドされるように指定します。 SCHEMABINDING を指定した場合、ベースオブジェクトに対して関数定義に影響を与えるような変更は行えません。まず関数定義を変更または削除して、変更するオブジェクトとの依存関係を解消する必要があります。

関数が参照するオブジェクトへのバインドは、次のいずれかの操作が行われた場合にのみ削除されます。

関数を削除した場合。
関数を、SCHEMABINDING オプションを指定せずに ALTER ステートメントを使用して変更した場合。

関数をスキーマにバインドできるのは、次の条件が満たされている場合に限られます。

関数によって参照されているすべてのユーザー定義関数もスキーマにバインドします。
関数と関数によって参照されるその他の UDF は、1 つのパーツまたは 2 つのパーツの名前を使用して参照されます。
同じデータベース内の組み込み関数とその他の 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 ステートメントを 1 つだけ使用できます。

制限事項と制約事項

ユーザー定義関数は、データベースの状態を変更するアクションの実行に使用することはできません。

ユーザー定義関数は入れ子にすることができます。つまり、1 つのユーザー定義関数で、別のユーザー定義関数を呼び出すことができます。呼び出された関数が実行を開始すると入れ子レベルが 1 つ上がり、呼び出された関数が実行を終了するとレベルが 1 つ下がります。ユーザー定義関数は、32 レベルまで入れ子にすることができます。入れ子レベルが最大値を超えると、関数チェーン全体の呼び出しが失敗します。

関数などのオブジェクトを、Azure Synapse Analytics のサーバーレス SQL プールの master データベースに作成することはできません。

Metadata

このセクションでは、ユーザー定義関数に関するメタデータを返すために使用できるシステムカタログビューを示します。

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

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

Note

スカラー関数は、サーバーレス 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');

Note

インラインテーブル値関数は、サーバーレス SQL プールで使用できますが、専用 SQL プールではプレビュー段階です。

B. インラインテーブル値関数の結果を結合する

このシンプルな例は、先に作成したインライン TVF を使用し、クロス適用によって結果を他のテーブルと結合する方法を示したものです。ここでは、type 列で一致するすべての行を対象に、sys.objects と ModulesByType の結果の両方からすべての列を選択しています。 APPLY の使用方法について詳しくは、「FROM 句と JOIN、APPLY、PIVOT」を参照してください。

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

Note

インラインテーブル値関数は、サーバーレス SQL プールで使用できますが、専用 SQL プールではプレビュー段階です。

次の手順

ユーザー定義関数の作成 (データベースエンジン)

CREATE FUNCTION (Azure Synapse Analytics および Microsoft Fabric)

構文

引数

ベストプラクティス

相互運用性

制限事項と制約事項

Metadata

アクセス許可

例: Azure Synapse Analytics、Analytics Platform System (PDW)

A. データ型を変更するために、スカラー値ユーザー定義関数を使用する

例: Azure Synapse Analytics

A. インラインテーブル値関数を作成する

B. インラインテーブル値関数の結果を結合する

次の手順

フィードバック

フィードバック

その他のリソース

CREATE FUNCTION (Azure Synapse Analytics および Microsoft Fabric)

構文

引数

ベスト プラクティス

相互運用性

制限事項と制約事項

Metadata

アクセス許可

例: Azure Synapse Analytics、Analytics Platform System (PDW)

A. データ型を変更するために、スカラー値ユーザー定義関数を使用する

例: Azure Synapse Analytics

A. インライン テーブル値関数を作成する

B. インライン テーブル値関数の結果を結合する

次の手順

フィードバック

フィードバック

その他のリソース

ベストプラクティス

A. インラインテーブル値関数を作成する

B. インラインテーブル値関数の結果を結合する