Типы функций
SQL Server 2008 поддерживает как определяемые пользователем функции, так и встроенные (системные) функции.
Скалярные функции
Пользовательские скалярные функции возвращают одно значение типа данных, заданного в предложении RETURNS. Простые скалярные функции не имеют текста функции, скалярное значение получается из единственной инструкции функции (часто это инструкция SELECT). Скалярная функция из нескольких инструкций имеет текст, определяемый блоком BEGIN...END, и содержит последовательность инструкций Transact-SQL, возвращающих одно значение. Такие функции могут возвращать любые типы данных, кроме text, ntext, image, cursor, spatial, hierarchyID и timestamp.
В следующем примере одна скалярная функция ufnGetInventoryStock используется для получения сведений о количестве товаров с идентификаторами ProductModelID от 75 до 80.
USE AdventureWorks;
GO
SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply
FROM Production.Product
WHERE ProductModelID BETWEEN 75 and 80;
GO
В следующем примере создается скалярная функция из нескольких инструкций. Эта функция имеет одно входное значение ProductID и возвращает общее количество указанного товара на складе как одно значение.
USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL
DROP FUNCTION ufnGetInventoryStock;
GO
CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)
RETURNS int
AS
-- Returns the stock level for the product.
BEGIN
DECLARE @ret int;
SELECT @ret = SUM(p.Quantity)
FROM Production.ProductInventory p
WHERE p.ProductID = @ProductID
AND p.LocationID = '6';
IF (@ret IS NULL)
SET @ret = 0;
RETURN @ret;
END;
GO
Функции, возвращающие табличное значение
Пользовательские табличные функции возвращают тип данных table. Встроенная возвращающая табличное значение функция не имеет тела, таблица является результирующим набором одной инструкции.
Следующий пример демонстрирует создание встроенной возвращающей табличное значение функции. Функция имеет один входной параметр — идентификатор клиента (магазина) — и возвращает столбцы ProductID, Name и столбец YTD Total со сведениями о продажах продукта за текущий год.
USE AdventureWorks;
GO
IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN
(
SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'YTD Total'
FROM Production.Product AS P
JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
WHERE SH.CustomerID = @storeid
GROUP BY P.ProductID, P.Name
);
GO
В следующем примере функция вызывается с идентификатором 602.
SELECT * FROM Sales.ufn_SalesByStore (602);
В возвращающей табличное значение функции с несколькими инструкциями блок BEGIN...END определяет текст функции и содержит последовательность инструкций Transact-SQL, которые создают и вставляют строки в табличные результаты.
Следующий пример демонстрирует создание возвращающей табличное значение функции. Функция принимает один входной параметр EmployeeID и возвращает список всех сотрудников, прямо или косвенно подчиняющихся заданному сотруднику с идентификатором 109. Затем идентификатор сотрудника 109 используется в качестве входного параметра в примере, который возвращает список сотрудников в таблице результатов.
USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufn_FindReports;
GO
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE
(
EmployeeID int primary key NOT NULL,
Name nvarchar(255) NOT NULL,
Title nvarchar(50) NOT NULL,
EmployeeLevel int NOT NULL,
Sort nvarchar (255) NOT NULL
)
--Returns a result set that lists all the employees who report to the
--specific employee directly or indirectly.*/
AS
BEGIN
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS
(SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName),
e.Title,
e.EmployeeID,
1,
CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName)
FROM HumanResources.Employee AS e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
WHERE e.EmployeeID = @InEmpID
UNION ALL
SELECT CONVERT(Varchar(255), REPLICATE ('| ' , EmployeeLevel) +
c.FirstName + ' ' + c.LastName),
e.Title,
e.EmployeeID,
EmployeeLevel + 1,
CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' +
LastName)
FROM HumanResources.Employee as e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
-- copy the required columns to the result of the function
INSERT @retFindReports
SELECT EmployeeID, Name, Title, EmployeeLevel, Sort
FROM DirectReports
RETURN
END;
GO
-- Example invocation
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM dbo.ufn_FindReports(109)
ORDER BY Sort;
GO
Встроенные функции
Встроенные функции SQL Server помогают выполнять различные операции. Их нельзя изменить. Встроенные функции можно использовать в инструкциях Transact-SQL для:
получения сведений из системных таблиц SQL Server, не обращаясь к ним напрямую. Дополнительные сведения см. в разделе Использование системных функций.
выполнения распространенных задач, например: SUM, GETDATE или IDENTITY. Дополнительные сведения см. в разделе Функции (Transact-SQL).
Встроенные функции возвращают скалярные значения или значения типа table. Например, функция @@ERROR возвращает 0, если последняя инструкция Transact-SQL была выполнена успешно. Если инструкция выполнилась с ошибкой, функция @@ERROR возвращает ее номер. Функция SUM(parameter) возвращает сумму всех значений параметра.
Журнал изменений
Обновленное содержимое |
|---|
Удалено неверное содержимое по встроенным скалярным функциям. |
См. также