SQL Server Table-valued Functions (日本語)

Summary: このチュートリアルでは、インラインTable-valued FunctionやMulti-statement valued functionなど、SQL ServerのTable-valued Functionの使い方を学びます

What is a table-valued function in SQL Server

Table-valued Functionは、テーブル型のデータを返すユーザー定義関数です。 table-valued関数の戻り値の型はテーブルなので、テーブルを使用するのと同じようにtable-valued関数を使用することができます。

table-valued関数の作成

次の記述例では、製品名、モデル年、特定のモデル年の定価を含む製品のリストを返すtable-valued関数を作成しています:

CREATE FUNCTION udfProductInYear ( @model_year INT)RETURNS TABLEASRETURN SELECT product_name, model_year, list_price FROM production.products WHERE model_year = @model_year;
Code language: SQL (Structured Query Language) (sql)

構文は、ユーザー定義関数を作成するものと同様です。

RETURNS TABLEBEGIN...ENDのステートメントはありません。

udfProductInYear@model_yearINT@model_yearというパラメータに等しい製品を返します。

表価関数が作成されると、次の図のように、プログラマビリティ >> 表価関数の下に表示されます。

上記の関数は、1つのSELECTステートメントの結果セットを返すので、インラインTable-valued Functionとも呼ばれます。

表計算関数の実行

表計算関数を実行するには、SELECTFROM節で使用します。

SELECT * FROM udfProductInYear(2017);
Code language: SQL (Structured Query Language) (sql)

この例では、モデルイヤーが2017である製品を選択しました。

以下のように、table-valued関数から返す列を指定することもできます:

SELECT product_name, list_priceFROM udfProductInYear(2018);
Code language: SQL (Structured Query Language) (sql)

以下は部分的な出力です。

Table-valued Functionの修正

Table-valued Functionを修正するには、以下のようにします。 CREATEALTERを使用します。

例えば、次のステートメントは、既存のパラメーターを変更し、パラメーターを1つ追加することで、udfProductInYearを変更します。

ALTER FUNCTION udfProductInYear ( @start_year INT, @end_year INT)RETURNS TABLEASRETURN SELECT product_name, model_year, list_price FROM production.products WHERE model_year BETWEEN @start_year AND @end_year
Code language: SQL (Structured Query Language) (sql)

udfProductInYear関数は、モデル年が開始年と終了年の間にある製品を返すようになりました。

次のステートメントは、udfProductInYear20172018の間にある製品を取得します：

SELECT product_name, model_year, list_priceFROM udfProductInYear(2017,2018)ORDER BY product_name;
Code language: SQL (Structured Query Language) (sql)

以下はその部分的な出力です。

Multi-statement Table-valued Function (MSTVF)

。

Multi-statement table-valued function (MSTVF)

Multi-statement table-valued function (MSTVF)は、複数のステートメントの結果を返す表計算関数です。

マルチステートメントテーブル値関数は、関数内で複数のクエリを実行し、結果を返されるテーブルに集約することができるため、非常に便利です。 関数内では、1つまたは複数のクエリを実行し、データをこのテーブル変数に挿入します。

次の udfContacts() 関数は、スタッフと顧客を 1 つの連絡先リストにまとめます：

CREATE FUNCTION udfContacts() RETURNS @contacts TABLE ( first_name VARCHAR(50), last_name VARCHAR(50), email VARCHAR(255), phone VARCHAR(25), contact_type VARCHAR(20) )ASBEGIN INSERT INTO @contacts SELECT first_name, last_name, email, phone, 'Staff' FROM sales.staffs; INSERT INTO @contacts SELECT first_name, last_name, email, phone, 'Customer' FROM sales.customers; RETURN;END;
Code language: SQL (Structured Query Language) (sql)

次のステートメントは、マルチステートメントのテーブル値関数 udfContacts の実行方法を示しています。

SELECT * FROM udfContacts();
Code language: SQL (Structured Query Language) (sql)

Output:

テーブル値付き関数を使用する場合

一般的にテーブル値付き関数はパラメーター化されたビューとして使用します。

このチュートリアルでは、インラインテーブル値関数やマルチステートメントテーブル値関数など、SQL Serverのテーブル値関数について学びました

テーブル値関数は、ストアドプロシージャと比較して、テーブルが使用されている場所であればどこでも使用できるため、より柔軟性があります。