Поделиться через

CREATE FUNCTION (Azure Synapse Analytics и Microsoft Fabric)

  • Статья

Область применения: конечная точка аналитики платформы Аналитики Azure Synapse Analytics (PDW) в хранилище Microsoft Fabric в Microsoft Fabric

Создает определяемую пользователем функцию (UDF) в Azure Synapse Analytics, системе платформы аналитики (PDW) или Microsoft Fabric. Определяемая пользователем функция представляет собой подпрограмму Transact-SQL, которая принимает параметры, выполняет действие, например сложное вычисление, а затем возвращает результат этого действия в виде значения.

  • В системе платформы аналитики (PDW) возвращаемое значение должно быть скалярным (одним) значением.

  • В Azure Synapse Analytics CREATE FUNCTION может возвращать таблицу с помощью синтаксиса встроенных табличных функций (предварительная версия) или возвращать одно значение с помощью синтаксиса скалярных функций.

  • В Microsoft Fabric и бессерверных пулах SQL в Azure Synapse Analytics CREATE FUNCTION может создавать встроенные функции табличного значения, но не скалярные функции. Определяемые пользователем табличные функции (TVFs) возвращают тип данных таблицы.

    При помощи этой инструкции можно создать подпрограмму, которую можно повторно использовать следующими способами.

  • В инструкциях Transact-SQL, таких как SELECT

  • В приложениях, вызывающих функцию.

  • В определении другой пользовательской функции.

  • Для определения ограничения CHECK на столбец.

  • Для замены хранимой процедуры.

  • Использование встроенной функции в качестве предиката фильтра для политики безопасности

Соглашения о синтаксисе Transact-SQL

Синтаксис

Синтаксис скалярной функции

-- Transact-SQL Scalar Function Syntax  (in dedicated pools in Azure Synapse Analytics and Parallel Data Warehouse)
-- Not available in the serverless SQL pools in Azure Synapse Analytics or Microsoft Fabric

CREATE FUNCTION [ schema_name. ] function_name   
( [ { @parameter_name [ AS ] parameter_data_type   
    [ = default ] }   
    [ ,...n ]  
  ]  
)  
RETURNS return_data_type  
    [ WITH <function_option> [ ,...n ] ]  
    [ AS ]  
    BEGIN   
        function_body   
        RETURN scalar_expression  
    END  
[ ; ]  
  
<function_option>::=   
{  
    [ SCHEMABINDING ]  
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]  
}

Встроенный синтаксис функции с табличным значением

-- Transact-SQL Inline Table-Valued Function Syntax
-- Preview in dedicated SQL pools in Azure Synapse Analytics
-- Available in the serverless SQL pools in Azure Synapse Analytics and Microsoft Fabric
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] parameter_data_type
    [ = default ] }
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH SCHEMABINDING ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

Аргументы

schema_name

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

function_name

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

Примечание.

Даже при отсутствии аргументов скобки после имени функции обязательны.

@parameter_name

Аргумент пользовательской функции. Может быть объявлен один или несколько аргументов.

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

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

Примечание.

ANSI_WARNINGS не учитывается при передаче параметров в хранимой процедуре, определяемой пользователем функции или при объявлении и установке переменных в инструкции пакетной службы. Например, если объявить переменную как char(3), а затем присвоить ей значение длиннее трех символов, данные будут усечены до размера переменной, а инструкция INSERT или UPDATE завершится без ошибок.

parameter_data_type

Тип данных параметра. Для функций Transact-SQL допускаются все скалярные типы данных, которые поддерживаются в Azure Synapse Analytics. Тип данных timestamp (rowversion) не поддерживается.

[ =default ]

Значение по умолчанию для аргумента. Если определено значение default, то функция выполняется даже в том случае, если для данного параметра значение не указано.

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

return_data_type

Возвращаемое значение скалярной пользовательской функции. Для функций Transact-SQL допускаются все скалярные типы данных, которые поддерживаются в Azure Synapse Analytics. Тип данных метки времени rowversion/не поддерживается. Нескалярные типы курсора и таблицы не допускаются.

function_body

Ряд инструкций Transact-SQL. Function_body не может содержать инструкцию SELECT и не может ссылаться на данные базы данных. Function_body не может ссылаться на таблицы или представления. Аргумент function_body может вызывать другие детерминированные функции, но не может вызывать недетерминированные.

Для скалярных функций function_body представляет собой ряд инструкций Transact-SQL, совместное выполнение которых вычисляет скалярное выражение.

scalar_expression

Указывает скалярное значение, возвращаемое скалярной функцией.

select_stmt

Является одной SELECT инструкцией, определяющей возвращаемое значение встроенной табличной функции. Для встроенной табличной функции нет текста функции; Таблица — это результирующий набор одной SELECT инструкции.

TABLE

Указывает, что возвращаемым значением функции с табличным значением (TVF) является таблица. Функциям с табличным значением могут передаваться только константы и @local_variables.

В встроенных ТВФ (предварительная версия) возвращаемое значение TABLE определяется с помощью одной SELECT инструкции. Встроенные функции не имеют соответствующих возвращаемых переменных.

<function_option>

Указывает, что функция имеет один или несколько следующих параметров.

SCHEMABINDING

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

Привязка функции к ссылающимся на нее объектам удаляется в следующих случаях:

  • При удалении функции.

  • При изменении функции инструкцией ALTER, если не указан параметр SCHEMABINDING.

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

  • Любые пользовательские функции, на которые ссылается данная функция, также привязаны к схеме.

  • Функции и другие определяемые пользователем функции, на которые ссылается данная функция, указываются как одно- или двухкомпонентное имя.

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

  • Пользователь, выполнивший инструкцию CREATE FUNCTION , имеет разрешение REFERENCES на объекты базы данных, на которые ссылается функция.

Чтобы удалить SCHEMABINDING, используйте ALTER.

RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT

Указывает атрибут OnNULLCall скалярной функции. Если значение не указано, по умолчанию подразумевается, CALLED ON NULL INPUT а тело функции выполняется даже в том случае, если NULL передается в качестве аргумента.

Рекомендации

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

  • Укажите при создании функции предложение WITH SCHEMABINDING. Это обеспечит невозможность изменения объектов, на которые ссылается определение функции, если при этом не изменяется сама функция.

Совместимость

В скалярных функциях допустимы следующие инструкции с одиночным значением.

  • Инструкции присваивания.

  • Инструкции управления потоком, за исключением инструкций TRY...CATCH.

  • Инструкции DECLARE, объявляющие локальные переменные.

Во встроенной функции с табличным значением (предварительная версия) допускается только одна инструкция SELECT.

Ограничения

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

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

Объекты, включая функции, нельзя создавать в master базе данных бессерверного пула SQL в Azure Synapse Analytics.

Метаданные

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

  • sys.sql_modules: отображает определение определяемых пользователем функций Transact-SQL. Например:

    SELECT definition, type   
FROM sys.sql_modules AS m  
JOIN sys.objects AS o   
    ON m.object_id = o.object_id   
    AND type = ('FN');

  • sys.parameters: выводит сведения о параметрах, определенных в определяемых пользователем функциях.

  • sys.sql_expression_dependencies: отображает базовые объекты, на которые ссылается функция.

Разрешения

Требуется разрешение CREATE FUNCTION на базу данных и разрешение ALTER на схему, в которой создается функция.

Примеры: Azure Synapse Analytics и система платформы аналитики (PDW)

А. Использование скалярной определяемой пользователем функции для изменения типа данных

Эта скалярная функция принимает тип данных int и возвращает тип данных decimal(10,2).

CREATE FUNCTION dbo.ConvertInput (@MyValueIn int)  
RETURNS decimal(10,2)  
AS  
BEGIN  
    DECLARE @MyValueOut int;  
    SET @MyValueOut= CAST( @MyValueIn AS decimal(10,2));  
    RETURN(@MyValueOut);  
END;  
GO  
  
SELECT dbo.ConvertInput(15) AS 'ConvertedValue';

Примечание.

Скалярные функции недоступны в бессерверных пулах SQL или Microsoft Fabric.

Примеры: Azure Synapse Analytics

А. Создание встроенной табличной функции

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

CREATE FUNCTION dbo.ModulesByType(@objectType CHAR(2) = '%%')
RETURNS TABLE
AS
RETURN
(
    SELECT 
        sm.object_id AS 'Object Id',
        o.create_date AS 'Date Created',
        OBJECT_NAME(sm.object_id) AS 'Name',
        o.type AS 'Type',
        o.type_desc AS 'Type Description', 
        sm.definition AS 'Module Description'
    FROM sys.sql_modules AS sm  
    JOIN sys.objects AS o ON sm.object_id = o.object_id
    WHERE o.type like '%' + @objectType + '%'
);
GO

Эту функцию можно вызвать, чтобы получить все объекты представления (V), следующим образом:

select * from dbo.ModulesByType('V');

Примечание.

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

B. Объединение результатов встроенной табличной функции

В этом простом примере используется ранее созданная встроенная функция и показано объединение ее результатов с другими таблицами с помощью инструкции CROSS APPLY. Здесь мы выбираем все столбцы из sys.objects обоих столбцов и результаты ModulesByType всех строк, соответствующих столбцу типа . Дополнительные сведения об использовании инструкции APPLY см. в статье Предложение FROM и JOIN, APPLY, PIVOT (Transact-SQL).

SELECT * 
FROM sys.objects o
CROSS APPLY dbo.ModulesByType(o.type);
GO

Примечание.

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

Следующий шаг

Создание определяемых пользователем функций (ядро СУБД)