SQL Server のユーザー定義のテーブル値関数を作成する
SQL Server のユーザー定義のテーブル値関数を作成する
前回 「 SQL Server のユーザー定義のスカラー関数を作成する 」 では、ひとつの値を返す、ユーザー定義のスカラー関数を作ってみました。
今回は、テーブルを返す、ユーザー定義のテーブル値関数を作ってみましょう。
こちら のスクリプトを実行して、テーブルを再生成しておいてください。 全てのテーブルは使いませんが、これらのテーブルが存在している前提で進めます。
ユーザー定義のテーブル値関数
ユーザー定義関数を作成するには CREATE FUNCTION を使います。 シンプルなユーザー定義のテーブル値関数を作成する構文は次の通りです。
CREATE FUNCTION [ ファンクション名 ] (
[ @入力パラメーター名 1 ] [ 入力パラメーター名 1 のデータ型 ],
[ @入力パラメーター名 2 ] [ 入力パラメーター名 2 のデータ型 ],
[ @入力パラメーター名 3 ] [ 入力パラメーター名 3 のデータ型 ],
...
)
RETURNS [ @戻り値のテーブル名 ] TABLE (
[カラム名 1] [カラム名 1 のデータ型] [NULL or NOT NULLなどのテーブル定義],
[カラム名 2] [カラム名 2 のデータ型] [NULL or NOT NULLなどのテーブル定義],
[カラム名 3] [カラム名 3 のデータ型] [NULL or NOT NULLなどのテーブル定義],
...
)
AS
BEGIN
[ ファンクションで処理したい一連の T-SQL ステートメント
@戻り値のテーブルに値をインサートする ]
RETURN;
END;
[ ファンクションで処理したい一連の T-SQL ステートメント ] にテーブルの値を挿入・変更・削除等のスコープ外のリソースを変更するようなステートメントは書けません。
ユーザー定義のスカラー関数の最後のステートメントは RETURN でなくてはいけません。
入力パラメーターには、また本サイトの T-SQL 入門では説明していませんが 「 ユーザー定義テーブル型 」 も指定することができます。
ここら辺はユーザー定義スカラー関数と同様です。
処理内容が複雑でない場合はテーブル定義を飛ばして、以下のようにインラインで SELECT 文を指定してテーブル値を返すこともできます。
...
RETURNS TABLE
AS
RETURN
(
[ SELECT 文]
);
それでは、ユーザー定義のテーブル値関数を作ってみましょう。
StudentID を入力パラメータとして受け取り、その学生がうけたテストのテスト名と、スコア、テストの平均点を持つテーブル値を返す ufnStudentTestReportGet という名前のテーブル値ファンクションを作ってみます。
CREATE FUNCTION ufnStudentTestReportGet (
@StudentID INT
)
RETURNS @StudentTestReport TABLE (
TestID INT NOT NULL,
TestName NVARCHAR(50) NULL,
Score INT NULL,
TestAvgScore DECIMAL(5,2) NULL
)
AS
BEGIN
INSERT INTO @StudentTestReport (
TestID,
TestName,
Score
)
SELECT T.TestID,
T.TestNameEn,
TR.Score
FROM TestResult AS TR
INNER JOIN Test AS T
ON TR.TestID = T.TestID
WHERE TR.StudentID = @StudentID;
UPDATE T
SET TestAvgScore = A.TestAvgScore
FROM @StudentTestReport AS T
INNER JOIN
(SELECT TestID,
AVG(CAST(Score AS DECIMAL)) AS TestAvgScore
FROM TestResult
WHERE Score IS NOT NULL
GROUP BY TestID) AS A
ON T.TestID = A.TestID;
RETURN;
END
上記のスクリプトを実行すると、ユーザー定義のテーブル値関数 ufnStudentTestReportGet ができました。
それでは、作ったユーザー定義のテーブル値関数を使ってみましょう。
現在の Test テーブルと TestResult テーブルの値は以下のようになっています。
StudentID = 1 のテスト結果を、ufnStudentTestReportGet を使って、次のよう取得することができます。
SELECT * FROM dbo.ufnStudentTestReportGet(1);
[ 実行結果 ]
StudentID = 1 のテスト結果が取得できましたね。
次は、今回作ったユーザー定義関数を、変更・削除してみましょう。
-->