Что такое пользовательские функции?sql-67

Пользовательские функции (User-Defined Functions, UDF) — это именованные блоки кода, которые принимают параметры, выполняют определенные действия и возвращают результат. Они позволяют инкапсулировать сложную логику для повторного использования в SQL-запросах.

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

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

CREATE FUNCTION dbo.CalculateAge(@BirthDate DATE)
RETURNS INT
AS
BEGIN
    RETURN DATEDIFF(YEAR, @BirthDate, GETDATE()) -
           CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @BirthDate, GETDATE()), @BirthDate) > GETDATE()
                THEN 1 ELSE 0 END
END;
  • Возвращают одно значение определенного типа данных
  • Могут использоваться в SELECT, WHERE, ORDER BY
  • Пример вызова: 
    SELECT dbo.CalculateAge('1990-05-15') AS Age;

2. Табличные функции

a) Встроенные табличные функции

CREATE FUNCTION dbo.GetEmployeesByDepartment(@DeptID INT)
RETURNS TABLE
AS
RETURN
(
    SELECT EmployeeID, Name, Position
    FROM Employees
    WHERE DepartmentID = @DeptID
);
  • Возвращают таблицу как результат SELECT
  • Нет тела функции (только RETURN)
  • Пример вызова: 
    SELECT * FROM dbo.GetEmployeesByDepartment(3);

b) Многострочные табличные функции

CREATE FUNCTION dbo.GetEmployeeHierarchy(@EmpID INT)
RETURNS @Result TABLE (EmployeeID INT, Name NVARCHAR(100), Level INT)
AS
BEGIN
    INSERT INTO @Result
    SELECT EmployeeID, Name, 0
    FROM Employees WHERE EmployeeID = @EmpID;

    -- Рекурсивно добавляем подчиненных
    INSERT INTO @Result
    SELECT e.EmployeeID, e.Name, r.Level + 1
    FROM Employees e
    JOIN @Result r ON e.ManagerID = r.EmployeeID;

    RETURN;
END;
  • Возвращают таблицу через временную переменную
  • Могут содержать сложную логику
  • Пример вызова: 
    SELECT * FROM dbo.GetEmployeeHierarchy(100);

Ключевые характеристики UDF

  1. Параметры:

    • Могут принимать входные параметры
    • Не поддерживают выходные параметры

  2. Детерминированность:

    • Функция может быть детерминированной (всегда один результат для одних входных данных)
    • или недетерминированной (разные результаты при одинаковых входных данных)

  3. Ограничения:

    • Не могут изменять состояние базы данных
    • Не могут вызывать хранимые процедуры
    • Не могут использовать динамический SQL (кроме функций с WITH EXECUTE AS)

Преимущества пользовательских функций

  1. Повторное использование кода - одна функция может использоваться в многих местах
  2. Упрощение сложных запросов - скрытие сложной логики за простым интерфейсом
  3. Безопасность - могут использоваться для контроля доступа к данным
  4. Согласованность - гарантия одинаковой обработки данных во всем приложении

Недостатки и ограничения

  1. Производительность:

    • Скалярные функции могут сильно снижать производительность в больших запросах
    • Вызываются для каждой строки в результирующем наборе

  2. Ограничения функциональности:

    • Не могут выполнять DML-операции (INSERT, UPDATE, DELETE)
    • Не могут использовать временные таблицы (кроме табличных функций)

  3. Сложность отладки - труднее дебагать, чем хранимые процедуры

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

  1. Используйте INLINE-функции вместо скалярных где возможно
  2. Помечайте детерминированные функции как WITH SCHEMABINDING
  3. Избегайте сложных вычислений в скалярных функциях
  4. Рассмотрите CLR-функции для сложной логики

Пример замены скалярной функции на INLINE

-- Медленная скалярная функция
CREATE FUNCTION dbo.GetOrderTotal(@OrderID INT)
RETURNS DECIMAL(18,2)
AS
BEGIN
    DECLARE @Total DECIMAL(18,2);
    SELECT @Total = SUM(Quantity * UnitPrice)
    FROM OrderDetails WHERE OrderID = @OrderID;
    RETURN @Total;
END;

-- Быстрая INLINE замена
CREATE FUNCTION dbo.GetOrderTotalFast(@OrderID INT)
RETURNS TABLE
AS
RETURN
(
    SELECT SUM(Quantity * UnitPrice) AS Total
    FROM OrderDetails WHERE OrderID = @OrderID
);

-- Использование
SELECT o.OrderID, t.Total
FROM Orders o
CROSS APPLY dbo.GetOrderTotalFast(o.OrderID) t;

Рекомендации по использованию

  1. Для простых преобразований - используйте скалярные функции
  2. Для возврата наборов данных - всегда используйте табличные функции
  3. Для сложных вычислений - рассмотрите CLR-функции
  4. Для часто используемой логики - создавайте функции с WITH SCHEMABINDING

Резюмируем

Пользовательские функции в SQL Server:

  1. Инкапсулируют логику для повторного использования
  2. Бывают скалярные (одно значение) и табличные (набор строк)
  3. Улучшают читаемость кода, но могут снижать производительность
  4. Имеют ограничения по сравнению с хранимыми процедурами
  5. Требуют аккуратного использования в запросах

Правильное применение UDF делает код:

  • Более модульным
  • Легче поддерживаемым
  • Более безопасным
  • Но требует баланса с производительностью

Вопросы

Собеседования