CREATE FUNCTION (Azure Synapse Analytics および Microsoft Fabric)
適用対象: Azure Synapse Analytics
Microsoft Fabric のAnalytics Platform System (PDW)
SQL 分析エンドポイント
Microsoft Fabric のウェアハウス
Azure Synapse Analytics、Analytics Platform System (PDW)、または Microsoft Fabric でユーザー定義関数 (UDF) を作成します。 ユーザー定義の関数は、パラメーターを受け取り、複雑な計算などの操作を実行する Transact-SQL ルーチンであり、そのアクションの結果を値として返します。
Analytics Platform System (PDW) では、戻り値はスカラー (単一) 値である必要があります。
Azure Synapse Analytics では、CREATE FUNCTION で、インライン テーブル値関数 (プレビュー) の構文を使用してテーブルを返したり、スカラー関数の構文を使用して 1 つの値を返すことができます。
Microsoft Fabric および Azure Synapse Analytics のサーバーレス SQL プールでは、CREATE FUNCTION を使用してインライン テーブル値関数を作成できますが、スカラー関数は作成できません。 ユーザー定義テーブル値関数 (TVF) は、table データ型を返します。
このステートメントを使用して、次の方法で使用できる再利用可能なルーチンを作成します。
Transact-SQL ステートメントでは、次のような
SELECT
関数を呼び出すアプリケーション内で使用する
別のユーザー定義関数の定義内で使用する
列の CHECK 制約を定義する
ストアド プロシージャを置換する
セキュリティ ポリシーのフィルター述語としてのインライン関数を使用します。
構文
スカラー関数の構文
-- 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/timestamp データ型はサポートされていない型です。 カーソルとテーブルの非スカラー型を指定することはできません。
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 戻り値は 1 つの 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');
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 プールではプレビュー段階です。