Вопрос 25: Функции, определенные пользователем (UDF).

Определяемые пользователем функции SQL Server представляют собой подпрограммы, которые принимают параметры, выполняют действие, например, сложные вычисления, и возвращают результат этого действия в виде значения. Возвращаемое значение может быть либо единичным скалярным значением, либо результирующим набором.

UDF позволяют:

1. Делают возможным модульное программирование.

2. Позволяют ускорить выполнение. Как и хранимые процедуры, определяемые пользователем функции Transact-SQL снижают стоимость компиляции кода Transact-SQL, кэшируя и повторно используя планы выполнения. Это означает, что для определяемых пользователем функций нет необходимости выполнять повторный синтаксический анализ и оптимизацию при каждом вызове, что значительно ускоряет их выполнение.

   Функции CLR дают значительное преимущество в производительности по сравнению с функциями Transact-SQL для вычислительных задач, работы со строками и бизнес-логикой. Функции Transact-SQL лучше подходят для логики с интенсивным доступом к данным.

3. Позволяют уменьшить сетевой трафик.

Типы функций

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

  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;
  

• Функции с табличными значениями Определяемые пользователем функции с табличным значением возвращают данные типа table. Встроенная функция с табличным значением не имеет текста, таблица является результирующим набором одной инструкции.

  CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)  
  RETURNS TABLE  
  AS  
  RETURN   
  (  
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS '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  
    JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID  
    WHERE C.StoreID = @storeid  
    GROUP BY P.ProductID, P.Name  
  );
  

  CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)  
  RETURNS @retFindReports TABLE   
  (  
    EmployeeID int primary key NOT NULL,  
    FirstName nvarchar(255) NOT NULL,  
    LastName nvarchar(255) NOT NULL,  
    JobTitle nvarchar(50) NOT NULL,  
    RecursionLevel int NOT NULL  
  )
  AS  
  BEGIN 
   INSERT @retFindReports  
   SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel  
   FROM EMP_cte   
   RETURN  
  END;
  

• Системные функции SQL Server предоставляет множество системных функций для выполнения различных операций. Их нельзя изменить.

Инструкции, допустимые в функциях

К типам инструкций, допустимым внутри функций, относятся следующие:

• Инструкции DECLARE, используемые для определения переменных и курсоров, локальных для данной функции.
• Присвоение значений объектам, локальным для данной функции, например присвоение значений скалярным и табличным локальным переменным с помощью инструкции SET.
• Операции над курсорами, обращающиеся к локальным курсорам и выполняющие их объявление, открытие, закрытие и освобождение внутри функции. Инструкции FETCH, возвращающие данные клиенту, запрещены. Разрешены только инструкции FETCH, присваивающие значения локальным переменным с помощью предложения INTO.
• Инструкции управления потоком, за исключением инструкций TRY...CATCH.
• Инструкции SELECT, содержащие списки выборки с выражениями, присваивающими значения переменным, локальным для данной функции.
• Инструкции UPDATE, INSERT и DELETE, изменяющие табличные переменные, локальные для данной функции.
• Инструкции EXECUTE, вызывающие расширенную хранимую процедуру.