SQL Server表值函數

在本教學中，將學習如何使用SQL Server表值函數，包括內聯表值函數和多語句值函數。

SQL Server中的表值函數簡介

表值函數是用戶定義的函數，它返回表類型的數據。表值函數的返回類型是一個表，因此，可以像使用表一樣使用表值函數。

創建表值函數

以下語句示例創建一個表值函數，該函數返回產品列表，包括產品名稱，型號年份和價格：

CREATE FUNCTION udfProductInYear (
    @model_year INT
)
RETURNS TABLE
AS
RETURN
    SELECT
        product_name,
        model_year,
        list_price
    FROM
        production.products
    WHERE
        model_year = @model_year;

語法類似於創建用戶定義函數的語法。

RETURNS TABLE指定該函數將返回一個表。如上所見，沒有BEGIN ... END語句。 該語句只是查詢production.products表中的數據。

udfProductInYear函數接受一個名為@model_year，類型為INT的參數。 它返回模型年份等於@model_year參數的值的產品。

當創建了表值函數，就可以在：可編程性 -> 函數 -> 表值函數 下找到它，如下圖所示：

上面的函數返回單個SELECT語句的結果集，因此，它也稱為內聯表值函數。

執行表值函數

要執行表值函數，請在SELECT語句的FROM子句中使用它：

SELECT
    *
FROM
    udfProductInYear(2017);

執行上面查詢語句，得到以下結果：

在此示例中，選擇了型號年份為2017年的產品。
還可以指定從表值函數返回的列，如下所示：

SELECT
    product_name,
    list_price
FROM
    udfProductInYear(2018);

執行上面查詢語句，得到以下結果：

修改表值函數

要修改表值函數，請使用ALTER關鍵字。腳本的其餘部分是相同的。

例如，以下語句通過更改現有參數並添加一個參數來修改udfProductInYear表值函數：

ALTER FUNCTION udfProductInYear (
    @start_year INT,
    @end_year INT
)
RETURNS TABLE
AS
RETURN
    SELECT
        product_name,
        model_year,
        list_price
    FROM
        production.products
    WHERE
        model_year BETWEEN @start_year AND @end_year

udfProductInYear函數現在返回模型年份在起始年份和結束年份之間的產品。

以下語句調用udfProductInYear函數來獲取模型年份介於2017年和2018年之間的產品：

SELECT
    product_name,
    model_year,
    list_price
FROM
    udfProductInYear(2017,2018)
ORDER BY
    product_name;

執行上面查詢語句，得到以下結果：

多語句表值函數(MSTVF)

多語句表值函數或MSTVF是一個表值函數，它返回多個語句的結果。

多語句表值函數非常有用，因為可以在函數內執行多個查詢並將結果聚合到返回的表中。

要定義多語句表值函數，可以使用表變數作為返回值。 在函數內部，執行一個或多個查詢並將數據插入此表變數。

以下udfContacts()函數將員工和客戶合併到一個聯繫人列表中：

CREATE FUNCTION udfContacts()
    RETURNS @contacts TABLE (
        first_name VARCHAR(50),
        last_name VARCHAR(50),
        email VARCHAR(255),
        phone VARCHAR(25),
        contact_type VARCHAR(20)
    )
AS
BEGIN
    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;

以下語句說明了如何執行多語句表值函數udfContacts：

SELECT
    *
FROM
    udfContacts();

執行上面查詢語句，得到以下結果：

何時使用表值函數

通常使用表值函數作為參數化視圖。與存儲過程相比，表值函數更靈活，因為可以在使用表的任何地方使用它們。

在本教學中，瞭解了SQL Server表值函數，包括內聯表值函數和多語句表值函數。