CREATE FUNCTION (Transact-SQL)

構文

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

--Transact-SQL Inline Table-Valued Function Syntax 
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] [ READONLY ] } 
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

--Transact-SQL Multistatement Table-valued Function Syntax
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] [READONLY] } 
    [ ,...n ]
  ]
)
RETURNS @return_variable TABLE <table_type_definition>
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
        function_body 
        RETURN
    END
[ ; ]

--Transact-SQL Function Clauses 
<function_option>::= 
{
    [ ENCRYPTION ]
  | [ SCHEMABINDING ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

<table_type_definition>:: = 
( { <column_definition> <column_constraint> 
  | <computed_column_definition> } 
    [ <table_constraint> ] [ ,...n ]
) 

<column_definition>::=
{
    { column_name data_type }
    [ [ DEFAULT constant_expression ] 
      [ COLLATE collation_name ] | [ ROWGUIDCOL ]
    ]
    | [ IDENTITY [ (seed , increment ) ] ]
    [ <column_constraint> [ ...n ] ] 
}

<column_constraint>::= 
{
    [ NULL | NOT NULL ] 
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ] 
      [ WITH FILLFACTOR = fillfactor 
        | WITH ( < index_option > [ , ...n ] )
      [ ON { filegroup | "default" } ]
  | [ CHECK ( logical_expression ) ] [ ,...n ]
}

<computed_column_definition>::=
column_name AS computed_column_expression 

<table_constraint>::=
{ 
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ] 
      ( column_name [ ASC | DESC ] [ ,...n ] )
        [ WITH FILLFACTOR = fillfactor 
        | WITH ( <index_option> [ , ...n ] )
  | [ CHECK ( logical_expression ) ] [ ,...n ]
}

<index_option>::=
{ 
    PAD_INDEX = { ON | OFF } 
  | FILLFACTOR = fillfactor 
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF } 
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS ={ ON | OFF } 
}

--CLR Scalar Function Syntax
CREATE FUNCTION [ schema_name. ] function_name 
( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...n ]
)
RETURNS { return_data_type }
    [ WITH <clr_function_option> [ ,...n ] ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

--CLR Table-Valued Function Syntax
CREATE FUNCTION [ schema_name. ] function_name 
( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...n ]
)
RETURNS TABLE <clr_table_type_definition> 
    [ WITH <clr_function_option> [ ,...n ] ]
    [ ORDER ( <order_clause> ) ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

--CLR Function Clauses
<order_clause> ::= 
{
   <column_name_in_clr_table_type_definition>
   [ ASC | DESC ] 
} [ ,...n] 

<method_specifier>::=
    assembly_name.class_name.method_name

<clr_function_option>::=
}
    [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

<clr_table_type_definition>::= 
( { column_name data_type } [ ,...n ] )

引数

• schema_name
  ユーザー定義関数が属するスキーマの名前です。

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

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

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

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

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

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

• [ type_schema_name.] parameter_data_type
  パラメーターのデータ型です。このデータ型が属するスキーマを指定することもできます。Transact-SQL 関数の場合は、CLR ユーザー定義型およびユーザー定義テーブル型を含めたデータ型のうち、timestamp を除くすべてのデータ型を指定できます。CLR 関数の場合は、CLR ユーザー定義型を含めたデータ型のうち、text、ntext、image、ユーザー定義テーブル型、および timestamp を除くすべてのデータ型を指定できます。非スカラー型の cursor および table は、Transact-SQL 関数と CLR 関数の両方でパラメーターのデータ型として指定できません。

  type_schema_name を指定しない場合、データベース エンジンは次の順序で scalar_parameter_data_type を検索します。

  • SQL Server システム データ型の名前を含むスキーマ

  • 現在のデータベースにおける現在のユーザーの既定のスキーマ

  • 現在のデータベースの dbo スキーマ

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

  注
  varchar(max) データ型および varbinary(max) データ型の場合を除いて、CLR 関数には既定のパラメーター値を指定できます。

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

• READONLY
  パラメーターを関数の定義内で更新または変更できないことを示します。パラメーターの型がユーザー定義テーブル型の場合は、READONLY を指定する必要があります。

• return_data_type
  スカラー ユーザー定義関数の戻り値です。Transact-SQL 関数の場合は、CLR ユーザー定義型を含めたデータ型のうち、timestamp を除くすべてのデータ型を指定できます。CLR 関数の場合は、CLR ユーザー定義型を含めたデータ型のうち、text、ntext、image、および timestamp を除くすべてのデータ型を指定できます。非スカラー型の cursor および table は、Transact-SQL 関数と CLR 関数の両方で戻り値のデータ型として指定できません。 

• function_body
  総合して併用しても副作用 (テーブルの変更など) がない一連の Transact-SQL ステートメントが、関数の値を定義することを指定します。function_body は、スカラー関数と複数ステートメントのテーブル値関数でのみ使用されます。

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

  複数ステートメントのテーブル値関数の function_body は、TABLE 戻り変数にデータを格納する一連の Transact-SQL ステートメントです。

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

• TABLE
  テーブル値関数の戻り値がテーブルになるように指定します。テーブル値関数に渡すことができるのは、定数および @local_variables だけです。

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

  複数ステートメントのテーブル値関数の @return_variable は TABLE 変数で、その関数の値として返される行の格納および蓄積に使用されます。@return_variable は Transact-SQL 関数にのみ指定でき、CLR 関数には指定できません。

  注意
  FROM 句の複数のステートメントで構成されるテーブル値関数に結合することは可能ですが、パフォーマンスが低下する場合があります。SQL Server では、複数のステートメント関数に含まれる一部のステートメントに対して、最適化された方法をすべて使用できません。その結果、最適ではないクエリ プランを使用することになります。最大限のパフォーマンスを得るには、可能な限り、関数ではなくベース テーブル間の結合を使用します。

• select_stmt
  インライン テーブル値関数の戻り値を定義する単一の SELECT ステートメントです。

• ORDER (<order_clause>)
  テーブル値関数から結果が返される順序を指定します。詳細については、このトピックで後述する「並べ替え順序の使用に関するガイダンス」を参照してください。

• EXTERNAL NAME <method_specifier> assembly_name.class_name.method_name
  関数でバインドするアセンブリのメソッドを指定します。assembly_name は、表示がオンになっている現在のデータベースに存在する SQL Server のアセンブリと一致している必要があります。class_name は、有効な SQL Server 識別子であること、およびアセンブリにクラスとして存在していることが必要です。このクラスの名前が名前空間で修飾されており、名前空間の部分がピリオド (.) で分けられている場合は、このクラス名を角かっこ ([ ]) または引用符 (" ") で区切る必要があります。method_name は、有効な SQL Server 識別子であること、および指定されたクラスの静的メソッドとして存在していることが必要です。

  注
  既定では、SQL Server は CLR コードを実行できません。共通言語ランタイム モジュールを参照するデータベース オブジェクトを作成、変更、および削除することは可能ですが、SQL Server でこれらの参照を実行するには、clr enabled オプションを有効にする必要があります。このオプションを有効にするには、sp_configure を使用します。

• <table_type_definition> ( { <column_definition> <column_constraint>   | <computed_column_definition> }   [ <table_constraint> ] [ ,...n] )
  Transact-SQL 関数のテーブル データ型を定義します。テーブル宣言には、列の定義、および列またはテーブルの制約が含まれます。テーブルは、常にプライマリ ファイル グループに保存されます。

• < clr_table_type_definition > ( { column_namedata_type } [ ,...n] )
  CLR 関数のテーブル データ型を定義します。テーブル宣言には、列名およびデータ型のみが含まれます。テーブルは、常にプライマリ ファイル グループに保存されます。

ベスト プラクティス

ユーザー定義関数の作成時に SCHEMABINDING 句を使用しないと、基になるオブジェクトに加えられた変更が関数の定義に影響して、関数が呼び出されたときに予期しない結果が生じる可能性があります。基になるオブジェクトに対する変更によって関数が古くならないように、次のいずれかの操作を行うことをお勧めします。

• 関数を作成するときに WITH SCHEMABINDING 句を指定します。これにより、関数定義で参照されているオブジェクトは、一緒に関数も変更しない限り変更できなくなります。

• 関数の定義で指定されているオブジェクトを変更した後に sp_refreshsqlmodule ストアド プロシージャを実行します。

データ型

CLR 関数で指定するパラメーターは、以前 scalar_parameter_data_type 用に定義されていた SQL Server 型にしてください。SQL Server のシステム データ型と CLR 統合データ型または .NET Framework 共通言語ランタイム データ型との比較については、「CLR パラメーター データのマッピング」を参照してください。

SQL Server で、クラスにオーバーロードされているメソッドを正しく参照するには、<method_specifier> で指定するメソッドに以下の特性が必要です。

• [ ,...n ] での指定と同数のパラメーターを受け取る。].

• すべてのパラメーターを、参照ではなく値で受け取る。

• SQL Server 関数で指定されたパラメーター型と互換性のあるパラメーター型を使用する。

CLR 関数から返されるデータ型がテーブル型 (RETURNS TABLE) である場合、<method_specifier> のメソッドから返されるデータ型は、IEnumerator または IEnumerable である必要があります。また、関数の作成者によってインターフェイスが実装されるものと見なされます。Transact-SQL 関数とは異なり、CLR 関数では、<table_type_definition> に PRIMARY KEY、UNIQUE、または CHECK 制約を含めることができません。<table_type_definition> に指定する列のデータ型は、<method_specifier> のメソッドの実行時に返される結果セット内の、対応する列の型に一致する必要があります。この型チェックは、関数の作成時には実行されません。

CLR 関数のプログラミング方法の詳細については、「CLR ユーザー定義関数」を参照してください。

全般的な解説

スカラー値関数は、スカラー式が使用されている場所で呼び出すことができます。これには、計算列および CHECK 制約定義が含まれます。スカラー値関数は、EXECUTE ステートメントを使用して実行することもできます。スカラー値関数は、2 つ以上の要素から構成される名前を使用して呼び出す必要があります。複数の要素で構成される名前の詳細については、「Transact-SQL 構文表記規則 (Transact-SQL)」を参照してください。SELECT、INSERT、UPDATE、DELETE の各ステートメントの FROM 句でテーブル式を使用できる場合は、テーブル値関数を呼び出すことができます。詳細については、「ユーザー定義関数の実行 (データベース エンジン)」を参照してください。

相互運用性

関数で有効なステートメントは以下のとおりです。

• 代入ステートメント。

• TRY...CATCH ステートメント以外の流れ制御ステートメント。

• ローカル データ変数およびローカル カーソルを定義する DECLARE ステートメント。

• ローカル変数に値を代入する式を持つ選択リストが含まれている SELECT ステートメント。

• 関数内で宣言、オープン、クローズ、割り当ての解除を実行するローカル カーソルを参照するカーソル操作。INTO 句を使用してローカル変数に値を代入する FETCH ステートメントのみが許可され、クライアントにデータを返す FETCH ステートメントは許可されません。

• ローカルなテーブル変数を変更する、INSERT、UPDATE、および DELETE ステートメント。

• 拡張ストアド プロシージャを呼び出す EXECUTE ステートメント。

• 詳細については、「ユーザー定義関数の作成 (データベース エンジン)」を参照してください。

計算列の相互運用性

関数からの拡張ストアド プロシージャ呼び出し

制限事項と制約事項

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

出力先がテーブルである OUTPUT INTO 句をユーザー定義関数に含めることはできません。

以下の Service Broker ステートメントは、Transact-SQL ユーザー定義関数の定義に含めることができません。

• BEGIN DIALOG CONVERSATION

• END CONVERSATION

• GET CONVERSATION GROUP

• MOVE CONVERSATION

• RECEIVE

• SEND

ユーザー定義関数は入れ子にすることができます。つまり、1 つのユーザー定義関数で、別のユーザー定義関数を呼び出すことができます。呼び出された関数が実行を開始すると入れ子レベルが 1 つ上がり、呼び出された関数が実行を終了するとレベルが 1 つ下がります。ユーザー定義関数は、32 レベルまで入れ子にすることができます。入れ子レベルが最大値を超えると、関数チェーン全体の呼び出しが失敗します。Transact-SQL ユーザー定義関数からマネージ コードへの参照は、32 レベルの入れ子制限のうちの 1 レベルとカウントします。マネージ コード内で呼び出されるメソッドについては、この制限としてカウントされません。

並べ替え順序の使用に関するガイダンス

メタデータ

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

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

権限

データベースの CREATE FUNCTION 権限と、関数を作成するスキーマの ALTER 権限が必要です。関数でユーザー定義型が指定されている場合は、その型に対する EXECUTE 権限が必要です。

使用例

A. ISO 週番号を計算するスカラー値ユーザー定義関数を使用する

USE AdventureWorks2008R2;
GO
IF OBJECT_ID (N'dbo.ISOweek', N'FN') IS NOT NULL
    DROP FUNCTION dbo.ISOweek;
GO
CREATE FUNCTION dbo.ISOweek (@DATE datetime)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
     DECLARE @ISOweek int;
     SET @ISOweek= DATEPART(wk,@DATE)+1
          -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104');
--Special cases: Jan 1-3 may belong to the previous year
     IF (@ISOweek=0) 
          SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 
               AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1;
--Special case: Dec 29-31 may belong to the next year
     IF ((DATEPART(mm,@DATE)=12) AND 
          ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
          SET @ISOweek=1;
     RETURN(@ISOweek);
END;
GO
SET DATEFIRST 1;
SELECT dbo.ISOweek(CONVERT(DATETIME,'12/26/2004',101)) AS 'ISO Week';

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

USE AdventureWorks2008R2;
GO
IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN 
(
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
    FROM Production.Product AS P 
    JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
    JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
    WHERE C.StoreID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

SELECT * FROM Sales.ufn_SalesByStore (602);

C. 複数ステートメントのテーブル値関数を作成する

USE AdventureWorks2008R2;
GO
IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL
    DROP FUNCTION dbo.ufn_FindReports;
GO
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE 
(
    EmployeeID int primary key NOT NULL,
    FirstName nvarchar(255) NOT NULL,
    LastName nvarchar(255) NOT NULL,
    JobTitle nvarchar(50) NOT NULL,
    RecursionLevel int NOT NULL
)
--Returns a result set that lists all the employees who report to the 
--specific employee directly or indirectly.*/
AS
BEGIN
WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns
    AS (
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0 -- Get the initial list of Employees for Manager n
        FROM HumanResources.Employee e 
            INNER JOIN Person.Person p 
            ON p.BusinessEntityID = e.BusinessEntityID
        WHERE e.BusinessEntityID = @InEmpID
        UNION ALL
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1 -- Join recursive member to anchor
        FROM HumanResources.Employee e 
            INNER JOIN EMP_cte
            ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
            INNER JOIN Person.Person p 
            ON p.BusinessEntityID = e.BusinessEntityID
        )
-- copy the required columns to the result of the function 
   INSERT @retFindReports
   SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
   FROM EMP_cte 
   RETURN
END;
GO
-- Example invocation
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
FROM dbo.ufn_FindReports(1); 

GO

D. CLR 関数を作成する

DECLARE @SamplesPath nvarchar(1024);
-- You may have to modify the value of this variable if you have
-- installed the sample in a location other than the default location.
SELECT @SamplesPath = REPLACE(physical_name, 'Microsoft SQL Server\MSSQL10_5.MSSQLSERVER\MSSQL\DATA\master.mdf', 'Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\') 
    FROM master.sys.database_files 
    WHERE name = 'master';

CREATE ASSEMBLY [SurrogateStringFunction]
FROM @SamplesPath + 'StringManipulate\CS\StringManipulate\bin\debug\SurrogateStringFunction.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

CREATE FUNCTION [dbo].[len_s] (@str nvarchar(4000))
RETURNS bigint
AS EXTERNAL NAME [SurrogateStringFunction].[Microsoft.Samples.SqlServer.SurrogateStringFunction].[LenS];
GO

関連項目

参照

その他の技術情報

変更履歴