表值用户定义函数
返回 table 数据类型的用户定义函数功能强大，可以替代视图。这些函数称为表值函数。在 Transact-SQL 查询中允许使用表或视图表达式的情况下，可以使用表值用户定义函数。视图受限于单个 SELECT 语句，而用户定义函数可包含更多语句，这些语句的逻辑功能可以比视图中的逻辑功能更加强大。
表值用户定义函数还可以替换返回单个结果集的存储过程。可以在 Transact-SQL 语句的 FROM 子句中引用由用户定义函数返回的表，但不能引用返回结果集的存储过程。
表值用户定义函数的组件
在表值用户定义函数中：
- RETURNS 子句为函数返回的表定义局部返回变量名。RETURNS 子句还定义表的格式。局部返回变量名的作用域位于函数内。
- 函数体中的 Transact-SQL 语句生成行并将其插入 RETURNS 子句定义的返回变量中。
- 当执行 RETURN 语句时，插入变量的行将作为函数的表格输出返回。RETURN 语句不能有参数。
表值函数中的 Transact-SQL 语句都无法将结果集直接返回给用户。函数可以返回给用户的唯一信息是该函数返回的 table。
.gif "ms191165.note(zh-cn,SQL.90).gif") 注意： |
|---|
| 对于用户定义函数返回的表，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
请参阅
概念
内联用户定义函数
确定性函数和不确定性函数
将存储过程重写为函数