Типы функций

Статья
12/15/2008

SQL Server 2005 поддерживает пользовательские и встроенные системные функции.

Скалярные функции

Пользовательские скалярные функции возвращают одно значение типа данных, заданного в предложении RETURNS. Встроенная скалярная функция не имеет тела, скалярное значение является результатом одной инструкции. Скалярная функция из нескольких инструкций имеет тело, ограниченное блоком BEGIN...END, и содержит последовательность инструкций Transact-SQL, возвращающих одно значение. Такие функции могут возвращать любые типы данных, кроме text, ntext, image, cursor и timestamp.

Следующий пример демонстрирует создание скалярной функции из нескольких инструкций. Функция имеет один входной параметр 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

В следующем примере функция 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

Возвращающие табличное значение функции

Пользовательские возвращающие табличное значение функции возвращают значение типа 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.

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

См. также

Другие ресурсы

Основные сведения о пользовательских функциях

Справка и поддержка

Получение помощи по SQL Server 2005

Share via