• Статья

Типы функций

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) возвращает сумму всех значений параметра.

Журнал изменений

Обновленное содержимое

Удалено неверное содержимое по встроенным скалярным функциям.