• 项目

表值用户定义函数

返回 table 数据类型的用户定义函数功能强大，可以替代视图。 这些函数称为表值函数。 在 Transact-SQL 查询中允许使用表或视图表达式的情况下，可以使用表值用户定义函数。 视图受限于单个 SELECT 语句，而用户定义函数可包含更多语句，这些语句的逻辑功能可以比视图中的逻辑功能更加强大。

表值用户定义函数还可以替换返回单个结果集的存储过程。 可以在 Transact-SQL 语句的 FROM 子句中引用由用户定义函数返回的表，但不能引用返回结果集的存储过程。

表值用户定义函数的组件

在表值用户定义函数中：

  • RETURNS 子句为函数返回的表定义局部返回变量名。 RETURNS 子句还定义表的格式。 局部返回变量名的作用域位于函数内。

  • 函数体中的 Transact-SQL 语句生成行并将其插入 RETURNS 子句定义的返回变量中。

  • 当执行 RETURN 语句时，插入变量的行将作为函数的表格输出返回。 RETURN 语句不能有参数。

表值函数中的 Transact-SQL 语句都无法将结果集直接返回给用户。 函数可以返回给用户的唯一信息是该函数返回的 table。

注意注意

对于用户定义函数返回的表，text in row 表选项自动设置为 256。 该值无法更改。 不能使用 READTEXT、WRITETEXT 和 UPDATETEXT 语句对该表中任何 text、ntext 或 image 列的部分进行读写操作。 有关详细信息，请参阅行内数据。

示例

下面的示例创建 dbo.ufnGetContactInformation 函数并说明此表值函数的组件。 在此函数中，本地返回变量名为 @retContactInformation。 函数体中的语句将行插入此变量中来生成该函数返回的表结果。

USE AdventureWorks;
GO
IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL
    DROP FUNCTION dbo.ufnGetContactInformation;
GO
CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE 
(
    -- Columns returned by the function
    ContactID int PRIMARY KEY NOT NULL, 
    FirstName nvarchar(50) NULL, 
    LastName nvarchar(50) NULL, 
    JobTitle nvarchar(50) NULL, 
    ContactType nvarchar(50) NULL
)
AS 
-- Returns the first name, last name, job title, and contact type for the specified contact.
BEGIN
    DECLARE 
        @FirstName nvarchar(50), 
        @LastName nvarchar(50), 
        @JobTitle nvarchar(50), 
        @ContactType nvarchar(50);
    -- Get common contact information
    SELECT 
        @ContactID = ContactID, 
        @FirstName = FirstName, 
        @LastName = LastName
    FROM Person.Contact 
    WHERE ContactID = @ContactID;
    SELECT @JobTitle = 
        CASE 
            -- Check for employee
            WHEN EXISTS(SELECT * FROM HumanResources.Employee AS e 
                WHERE e.ContactID = @ContactID) 
                THEN (SELECT Title 
                    FROM HumanResources.Employee 
                    WHERE ContactID = @ContactID)
            -- Check for vendor
            WHEN EXISTS(SELECT * FROM Purchasing.VendorContact AS vc 
                    INNER JOIN Person.ContactType AS ct 
                    ON vc.ContactTypeID = ct.ContactTypeID 
                WHERE vc.ContactID = @ContactID) 
                THEN (SELECT ct.Name 
                    FROM Purchasing.VendorContact AS vc 
                        INNER JOIN Person.ContactType AS ct 
                        ON vc.ContactTypeID = ct.ContactTypeID 
                    WHERE vc.ContactID = @ContactID)
            -- Check for store
            WHEN EXISTS(SELECT * FROM Sales.StoreContact AS sc 
                    INNER JOIN Person.ContactType AS ct 
                    ON sc.ContactTypeID = ct.ContactTypeID 
                WHERE sc.ContactID = @ContactID) 
                THEN (SELECT ct.Name 
                    FROM Sales.StoreContact AS sc 
                        INNER JOIN Person.ContactType AS ct 
                        ON sc.ContactTypeID = ct.ContactTypeID 
                    WHERE ContactID = @ContactID)
            ELSE NULL 
        END;
    SET @ContactType = 
        CASE 
            -- Check for employee
            WHEN EXISTS(SELECT * FROM HumanResources.Employee AS e 
                WHERE e.ContactID = @ContactID) 
                THEN 'Employee'
            -- Check for vendor
            WHEN EXISTS(SELECT * FROM Purchasing.VendorContact AS vc 
                    INNER JOIN Person.ContactType AS ct 
                    ON vc.ContactTypeID = ct.ContactTypeID 
                WHERE vc.ContactID = @ContactID) 
                THEN 'Vendor Contact'
            -- Check for store
            WHEN EXISTS(SELECT * FROM Sales.StoreContact AS sc 
                    INNER JOIN Person.ContactType AS ct 
                    ON sc.ContactTypeID = ct.ContactTypeID 
                WHERE sc.ContactID = @ContactID) 
                THEN 'Store Contact'
            -- Check for individual consumer
            WHEN EXISTS(SELECT * FROM Sales.Individual AS i 
                WHERE i.ContactID = @ContactID) 
                THEN 'Consumer'
        END;
    -- Return the information to the caller
    IF @ContactID IS NOT NULL 
    BEGIN
        INSERT @retContactInformation
        SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
    END;
    RETURN;
END;
GO

下面的示例在两个 SELECT 语句的 FROM 子句中使用表值函数 dbo.ufnGetContactInformation。

USE AdventureWorks;
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(2200);
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(5);
GO