Возвращающие табличное значение определяемые пользователем функции
Определяемые пользователем функции, возвращающие тип данных table, могут быть полноценной альтернативой представлениям. Ссылка на эти функции происходит как на возвращающие табличное значение функции. Возвращающая табличное значение функция, определяемая пользователем, может быть использована там, где в запросах Transact-SQL разрешены табличные выражения или выражения представлений. В то время как представления ограничены одной инструкцией SELECT, определяемые пользователем функции могут содержать дополнительные инструкции, обеспечивающие более эффективную логику, чем та, которая возможна в представлениях.
Возвращающая табличное значение функция, определяемая пользователем, также может заменять хранимые процедуры, возвращающие один результирующий набор. На таблицу, возвращаемую определяемой пользователем функцией, можно ссылаться в предложении FROM инструкции Transact-SQL, в котором нельзя ссылаться на хранимые процедуры, возвращающие результирующие наборы.
Компоненты возвращающей табличное значение функции, определяемой пользователем
В возвращающей табличное значение функции, определяемой пользователем:
предложение RETURNS определяет имя локальной возвращаемой переменной для таблицы, которую возвращает эта функция. Предложение RETURNS также определяет формат таблицы. Область видимости имени локальной возвращаемой переменной является локальной в этой функции;
инструкции Transact-SQL в теле функции создают и вставляют строки в возвращаемую переменную, определенную предложением RETURNS;
при выполнении инструкции RETURN строки, вставленные в переменную, возвращаются в качестве выходных табличных данных функции. Инструкция RETURN не может иметь аргумента.
Ни одна из инструкций Transact-SQL в возвращающей табличное значение функции не может возвращать результирующий набор непосредственно пользователю. Единственные данные, которые функция может вернуть пользователю, это таблица table, возвращаемая этой функцией.
.gif "Примечание") Примечание |
|---|
Параметр таблицы text in row автоматически устанавливается в 256 для таблицы, возвращаемой определяемой пользователем функцией. Этот параметр не может быть изменен. Инструкции READTEXT, WRITETEXT и UPDATETEXT не могут быть использованы для считывания или записи сегментов любых столбцов типа text, ntext или image в таблице. Дополнительные сведения см. в разделе Данные в строке. |
Примеры
В следующем примере создается функция dbo.ufnGetContactInformation и демонстрируются компоненты возвращающей табличное значение функции. В этой функции именем локальной возвращаемой переменной является @retContactInformation. Инструкции в теле функции вставляют строки в эту переменную для создания табличных результатов, возвращаемых этой функцией.
USE AdventureWorks2008R2;
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 = BusinessEntityID,
@FirstName = FirstName,
@LastName = LastName
FROM Person.Person
WHERE BusinessEntityID = @ContactID;
-- Get contact job title
SELECT @JobTitle =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'EM')
THEN (SELECT JobTitle
FROM HumanResources.Employee AS e
WHERE e.BusinessEntityID = @ContactID)
-- Check for vendor
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'VC')
THEN (SELECT ct.Name
FROM Person.ContactType AS ct
INNER JOIN Person.BusinessEntityContact AS bec
ON bec.ContactTypeID = ct.ContactTypeID
WHERE bec.PersonID = @ContactID)
-- Check for store
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'SC')
THEN (SELECT ct.Name
FROM Person.ContactType AS ct
INNER JOIN Person.BusinessEntityContact AS bec
ON bec.ContactTypeID = ct.ContactTypeID
WHERE bec.PersonID = @ContactID)
ELSE NULL
END;
-- Get contact type
SET @ContactType =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'EM')
THEN 'Employee'
-- Check for vendor
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'VC')
THEN 'Vendor Contact'
-- Check for store
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'SC')
THEN 'Store Contact'
-- Check for individual consumer
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'IN')
THEN 'Consumer'
-- Check for general contact
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'GC')
THEN 'General Contact'
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
Следующий пример использует вызов возвращающей табличное значение функции dbo.ufnGetContactInformation в предложении FROM инструкции SELECT.
USE AdventureWorks2008R2;
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(1209);
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(5);
GO