テーブル値関数を作成する

完了済み

テーブル値関数 を使用すると、結果セットを返す再利用可能なコンポーネントに複雑なクエリ ロジックをカプセル化できます。 これらの関数は、テーブルやビューと同様にクエリで直接呼び出すことができます。これにより、コードのモジュール性と保守性が高くなります。

データベース アプリケーションをビルドするときは、多くの場合、入力パラメーターに基づいてフィルター処理または計算されたデータ セットを取得する必要があります。 テーブル値関数は、パラメーターを受け取ってテーブルを返す関数にクエリ ロジックをパッケージ化することで、この問題を解決します。 ストアド プロシージャとは異なり、 JOIN 句や SELECT ステートメントでテーブル値関数を使用できるため、関数の結果をデータ ソースとして柔軟に処理できます。

テーブル値関数の型について

SQL Server には、それぞれ異なるシナリオに適した 2 種類のテーブル値関数が用意されています。

インライン テーブル値関数

1 つの SELECT ステートメントを含み、結果を直接返します。 インライン関数では、テーブル構造を定義しません。SQL Server は、 SELECT ステートメントからテーブル構造を推論します。 クエリ オプティマイザーは、インラインテーブル値関数をパラメーター付きのビューのように扱い、多くの場合、より優れた実行プランを生成します。

複数ステートメント テーブル値関数

BEGIN...END ブロックを使用し、返されたテーブルの構造を明示的に宣言します。 この型を使用すると、複数のステートメントを実行したり、複雑な計算を実行したり、結果セットを繰り返し作成したりする必要がある場合をより詳細に制御できます。 ただし、オプティマイザーがこれらの関数を異なる方法で処理するため、この柔軟性にはパフォーマンスのトレードオフが伴います。

これらの関数の選択は、特定の要件によって異なります。 パラメーターを使用した単純なクエリでは、インライン関数のパフォーマンスが向上します。 結果セットを構築するために手続き型ロジックまたは複数のステップが必要な場合は、複数ステートメント関数が必要になります。

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

インライン テーブル値関数は、クエリをパラメーター化する簡潔な方法を提供します。 1 つの RETURN ステートメントの後に SELECT クエリを続けて定義します。

次の例は、特定の顧客の注文を取得するインライン関数を示しています。

CREATE FUNCTION dbo.GetCustomerOrders
(
    @CustomerID INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT 
        OrderID,
        OrderDate,
        TotalAmount,
        Status
    FROM Sales.Orders
    WHERE CustomerID = @CustomerID
);

テーブルと同様に、クエリでこの関数を使用できるようになりました。

SELECT OrderID, OrderDate, TotalAmount
FROM dbo.GetCustomerOrders(1001)
WHERE OrderDate >= '2024-01-01';

この関数は、顧客 ID パラメーターを受け取り、その顧客の注文のみを返します。 必要に応じて、結果をさらにフィルター処理、 JOIN、集計できます。 この方法では、顧客のフィルター処理ロジックをカプセル化しながら、メイン クエリをクリーンに保ちます。

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

複数ステートメントのテーブル値関数を使用すると、結果セットを構築するために複数の操作を実行する必要がある場合に柔軟性が高くなります。

複数の集計を使用して製品売上の概要を計算する関数を考えてみましょう。

CREATE FUNCTION dbo.GetProductSalesSummary
(
    @StartDate DATE,
    @EndDate DATE
)
RETURNS @SalesSummary TABLE
(
    ProductID INT,
    ProductName NVARCHAR(100),
    TotalQuantity INT,
    TotalRevenue DECIMAL(18,2),
    AveragePrice DECIMAL(18,2)
)
AS
BEGIN
    INSERT INTO @SalesSummary
    SELECT 
        p.ProductID,
        p.ProductName,
        SUM(od.Quantity) AS TotalQuantity,
        SUM(od.Quantity * od.UnitPrice) AS TotalRevenue,
        AVG(od.UnitPrice) AS AveragePrice
    FROM Production.Products p
    INNER JOIN Sales.OrderDetails od ON p.ProductID = od.ProductID
    INNER JOIN Sales.Orders o ON od.OrderID = o.OrderID
    WHERE o.OrderDate BETWEEN @StartDate AND @EndDate
    GROUP BY p.ProductID, p.ProductName;
    
    RETURN;
END;

特定の列とデータ型を使用してテーブル変数 @SalesSummary を明示的に宣言する方法に注意してください。 関数本体は、このテーブル変数にデータを挿入し、それを返します。 この構造体を使用すると、必要に応じて、処理ロジック、エラー処理、または条件付きステートメントを追加できます。

クエリでテーブル値関数を使用する

テーブル値関数はクエリにシームレスに統合され、強力なデータ取得パターンが可能になります。

関数の結果を他のテーブルと結合できます。

SELECT 
    c.CustomerName,
    s.ProductName,
    s.TotalRevenue
FROM Customers c
CROSS APPLY dbo.GetProductSalesSummary('2024-01-01', '2024-12-31') s
WHERE s.TotalRevenue > 10000
ORDER BY s.TotalRevenue DESC;

CROSS APPLY演算子は Customers テーブルから各行の関数を呼び出しますが、この例では、関数パラメーターは定数です。 列の値をパラメーターとして渡すと、 CROSS APPLY が特に役立ちます。

SELECT 
    c.CustomerName,
    o.OrderID,
    o.TotalAmount
FROM Customers c
CROSS APPLY dbo.GetCustomerOrders(c.CustomerID) o
WHERE o.Status = 'Completed';

このクエリは、顧客ごとに完了したすべての注文を取得し、テーブル値関数でクエリ内で行ごとの処理を有効にする方法を示します。 この関数は相関サブクエリとして機能しますが、読みやすさと再利用性が向上します。

行ごとの評価を必要としないインライン テーブル値関数の場合は、 INNER JOIN 構文も使用できます。

SELECT 
    c.CustomerName,
    o.OrderDate,
    o.TotalAmount
FROM Customers c
INNER JOIN dbo.GetCustomerOrders(c.CustomerID) o ON 1=1
WHERE YEAR(o.OrderDate) = 2024;

これらの手法を使用すると、より単純なテスト済み関数コンポーネントから複雑なクエリを作成し、コードの保守性と開発効率の両方を向上させることができます。