Создание скалярных функций

Скалярные функции — это основные средства в SQL Server, которые позволяют инкапсулировать повторно используемый логику и возвращать одно значение. Их можно использовать непосредственно в SELECT инструкциях, WHERE предложениях и других выражениях T-SQL, что делает запросы более поддерживаемыми и код более модульным.

Основные сведения о скалярных функциях

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

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

При создании скалярной функции создается повторно используемая часть логики, которую другие разработчики могут вызывать во всей базе данных. Это способствует повторному использованию кода и помогает поддерживать согласованность в приложениях.

Определение синтаксиса скалярной функции

Чтобы создать скалярную функцию, используйте инструкцию CREATE FUNCTION с определенными компонентами синтаксиса. Базовая структура включает имя функции, параметры, тип возвращаемого значения и тело функции.

Ниже приведен базовый шаблон синтаксиса:

CREATE FUNCTION schema_name.function_name 
(
    @parameter1 datatype,
    @parameter2 datatype
)
RETURNS return_datatype
AS
BEGIN
    -- Function logic here
    RETURN @result
END

Предложение RETURNS задает тип данных одного значения, возвращаемого функцией. Внутри блока BEGIN...END пишите вашу логику на T-SQL и используйте инструкцию RETURN, чтобы вернуть результат.

Например, можно создать простую функцию, которая вычисляет налог на продажу:

CREATE FUNCTION dbo.CalculateSalesTax
(
    @Amount DECIMAL(10,2),
    @TaxRate DECIMAL(5,4)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
    DECLARE @TaxAmount DECIMAL(10,2)
    SET @TaxAmount = @Amount * @TaxRate
    RETURN @TaxAmount
END

Эта функция принимает два параметра и возвращает вычисляемую сумму налога. Эту функцию можно использовать в любой SELECT инструкции.

Реализация скалярных функций с помощью бизнес-логики

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

Рассмотрим сценарий, в котором необходимо вычислить срок пребывания сотрудников в течение многих лет. Вы создаете скалярную функцию, которая принимает дату найма и возвращает количество полных лет:

CREATE FUNCTION dbo.GetEmployeeTenure
(
    @HireDate DATE
)
RETURNS INT
AS
BEGIN
    DECLARE @Tenure INT
    SET @Tenure = DATEDIFF(YEAR, @HireDate, GETDATE())
    RETURN @Tenure
END

Эту функцию можно использовать в запросах для отображения сведений о пребывании:

SELECT 
    EmployeeName,
    HireDate,
    dbo.GetEmployeeTenure(HireDate) AS YearsOfService
FROM Employees
WHERE dbo.GetEmployeeTenure(HireDate) >= 5

Этот подход обеспечивает согласованное вычисление срока пребывания в базе данных. Если бизнес-правила изменяются, вы изменяете функцию один раз, чтобы не обновлять множество запросов.

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

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

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

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

Наконец, помните о последствиях производительности. При использовании скалярной функции в предложении WHERE или списке SELECT с большими таблицами, SQL Server может выполнять функцию для каждой строки. Это может значительно повлиять на производительность запросов. Для таких сценариев рассмотрим встроенные табличные функции в качестве альтернативы.

Ниже приведен пример хорошо разработанной скалярной функции, которая соответствует следующим рекомендациям:

CREATE FUNCTION dbo.FormatPhoneNumber
(
    @PhoneNumber VARCHAR(10)
)
RETURNS VARCHAR(14)
AS
BEGIN
    DECLARE @FormattedNumber VARCHAR(14)
    
    IF LEN(@PhoneNumber) = 10
        SET @FormattedNumber = '(' + SUBSTRING(@PhoneNumber, 1, 3) + ') ' +
                               SUBSTRING(@PhoneNumber, 4, 3) + '-' +
                               SUBSTRING(@PhoneNumber, 7, 4)
    ELSE
        SET @FormattedNumber = @PhoneNumber
    
    RETURN @FormattedNumber
END

Эта функция детерминирована, не имеет побочных эффектов и выполняет простое преобразование. Он обрабатывает недопустимые входные данные корректно, возвращая исходное значение, если номер телефона не соответствует ожидаемому формату.

Изменение скалярных функций и управление ими

После создания скалярной функции его определение можно изменить с помощью инструкции ALTER FUNCTION . Синтаксис ALTER FUNCTION отражает CREATE FUNCTION, но позволяет изменить функцию без ее удаления и повторного создания, что сохраняет существующие разрешения и зависимости.

ALTER FUNCTION dbo.CalculateSalesTax
(
    @Amount DECIMAL(10,2),
    @TaxRate DECIMAL(5,4)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
    DECLARE @TaxAmount DECIMAL(10,2)
    -- Updated logic with rounding
    SET @TaxAmount = ROUND(@Amount * @TaxRate, 2)
    RETURN @TaxAmount
END

Чтобы удалить скалярную функцию, используйте инструкцию DROP FUNCTION :

DROP FUNCTION IF EXISTS dbo.CalculateSalesTax

Предложение IF EXISTS предотвращает ошибки, если функция не существует, что полезно в сценариях развертывания. Прежде чем удалить функцию, убедитесь, что другие объекты базы данных не зависят от нее, проверив системные представления, например sys.sql_expression_dependencies.