SQL Server 入門 SQL Server 入門

ホーム > SQL Server のユーザー定義関数 > SQL Server のユーザー定義のテーブル値関数を作成する

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

SQL Server のユーザー定義のテーブル値関数を作成する 1

上記のスクリプトを実行すると、ユーザー定義のテーブル値関数 ufnStudentTestReportGet ができました。

SQL Server のユーザー定義のテーブル値関数を作成する 2


それでは、作ったユーザー定義のテーブル値関数を使ってみましょう。

現在の Test テーブルと TestResult テーブルの値は以下のようになっています。

SQL Server のユーザー定義のテーブル値関数を作成する 3

StudentID = 1 のテスト結果を、ufnStudentTestReportGet を使って、次のよう取得することができます。

SELECT  *
FROM    dbo.ufnStudentTestReportGet(1);

[ 実行結果 ]
SQL Server のユーザー定義のテーブル値関数を作成する 4

StudentID = 1 のテスト結果が取得できましたね。


次は、今回作ったユーザー定義関数を、変更・削除してみましょう。

次へ SQL Server のユーザー定義関数を変更・削除する

-->
SQL Server 関連の人気書籍
ホーム > SQL Server のユーザー定義関数 > SQL Server のユーザー定義のテーブル値関数を作成する