Compartir vía

CREATE FUNCTION (Azure Synapse Analytics y Microsoft Fabric)

  • Artículo

Se aplica a: punto de conexión de SQL Analytics Analytics Platform System (PDW) de Azure Synapse Analytics en Microsoft Fabric Warehouse en Microsoft Fabric

Crea una función definida por el usuario (UDF) en Azure Synapse Analytics, Analytics Platform System (PDW) o Microsoft Fabric. Una función definida por el usuario es una rutina de Transact-SQL que acepta parámetros, realiza una acción, como un cálculo complejo, y devuelve el resultado de esa acción como un valor.

  • En Sistema de la plataforma de análisis (PDW), el valor devuelto debe ser un valor escalar (único).

  • En Azure Synapse Analytics, CREATE FUNCTION puede devolver una tabla con la sintaxis de las funciones insertadas con valores de tabla (versión preliminar) o puede devolver un valor único mediante la sintaxis de las funciones escalares.

  • En Microsoft Fabric y grupos de SQL sin servidor en Azure Synapse Analytics, CREATE FUNCTION puede crear funciones insertadas de valores de tabla, pero no funciones escalares. Las funciones con valores de tabla definidas por el usuario (TVF) devuelven un tipo de datos table.

    Utilice esta instrucción para crear una rutina reutilizable que se pueda utilizar de estas formas:

  • En instrucciones Transact-SQL, como SELECT

  • En las aplicaciones que llaman a la función

  • En la definición de otra función definida por el usuario

  • Para definir una restricción CHECK en una columna

  • Para reemplazar un procedimiento almacenado

  • Usar una función insertada como predicado de filtro de la directiva de seguridad

Convenciones de sintaxis de Transact-SQL

Sintaxis

Sintaxis de función escalar

-- 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 ]  
}

Sintaxis de función con valores de tabla insertada

-- 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 [ ) ]
[ ; ]

Argumentos

schema_name

Nombre del esquema al que pertenece la función definida por el usuario.

function_name

Nombre de la función definida por el usuario. Los nombres de funciones deben seguir las reglas de los identificadores y deben ser únicos en la base de datos y para su esquema.

Nota

Los paréntesis después del nombre de la función son necesarios, aunque no se especifique un parámetro.

@parameter_name

Es un parámetro de la función definida por el usuario. Es posible declarar uno o varios parámetros.

Una función puede tener un máximo de 2.100 parámetros. El usuario debe proporcionar el valor de cada parámetro declarado cuando se ejecuta la función, a menos que se defina un valor predeterminado para el parámetro.

Especifique un nombre de parámetro con una arroba ( @ ) como primer carácter. El nombre del parámetro debe cumplir las mismas reglas para identifiers. Los parámetros son locales para la función; los mismos nombres de parámetro se pueden utilizar en otras funciones. Los parámetros solamente pueden ocupar el lugar de constantes; no se pueden utilizar en lugar de nombres de tablas, nombres de columnas o nombres de otros objetos de base de datos.

Nota:

ANSI_WARNINGS no se respeta cuando se pasan parámetros en un procedimiento almacenado, una función definida por el usuario o cuando se declaran y establecen variables en una instrucción batch. Por ejemplo, si una variable se define como char(3) y después se establece en un valor de más de tres caracteres, los datos se truncan hasta el tamaño definido y la instrucción INSERT o UPDATE se ejecuta correctamente.

parameter_data_type

Es el tipo de datos del parámetro. En las funciones de Transact-SQL, se permiten todos los tipos de datos escalares admitidos en Azure Synapse Analytics. El tipo de datos timestamp (rowversion) no es un tipo admitido.

[ =default ]

Es un valor predeterminado para el parámetro. Si se define un valor default, la función se puede ejecutar sin especificar un valor para ese parámetro.

Cuando un parámetro de la función tiene un valor predeterminado, se debe especificar la palabra clave DEFAULT al llamar a la función para recuperar el valor predeterminado. Este comportamiento es distinto del uso de parámetros con valores predeterminados en los procedimientos almacenados, donde la omisión del parámetro implica especificar el valor predeterminado.

return_data_type

Es el valor devuelto de una función escalar definida por el usuario. En las funciones de Transact-SQL, se permiten todos los tipos de datos escalares admitidos en Azure Synapse Analytics. El tipo de datos rowversion/timestamp no es un tipo admitido. No se permiten los tipos de datos no escalares de cursor y tabla.

function_body

Serie de instrucciones de Transact-SQL. El function_body no puede contener una instrucción SELECT y no puede hacer referencia a datos de base de datos. El function_body no puede hacer referencia a tablas ni vistas. El cuerpo de la función puede llamar a otras funciones deterministas, pero no a funciones no deterministas.

En las funciones escalares, function_body es una serie de instrucciones de Transact-SQL que se evalúan como un valor escalar.

scalar_expression

Especifica el valor escalar que devuelve la función escalar.

select_stmt

Es la instrucción única SELECT que define el valor devuelto de una función insertada con valores de tabla. Para una función insertada con valores de tabla, no hay ningún cuerpo de función; la tabla es el conjunto de resultados de una sola SELECT instrucción.

TABLE

Especifica que el valor devuelto de la función con valores de tabla (TVF) es una tabla. Solamente se pueden pasar constantes y @local_variables a las TVF.

En las TVF insertadas (versión preliminar), el valor devuelto TABLE se define a través de una sola SELECT instrucción. Las funciones insertadas no tienen variables devueltas asociadas.

<function_option>

Especifica que la función tiene una o varias de las siguientes opciones.

SCHEMABINDING

Especifica que la función está enlazada a los objetos de base de datos a los que hace referencia. Cuando se especifica SCHEMABINDING, los objetos base no se pueden modificar de una forma que afecte a la definición de la función. En primer lugar, se debe modificar o quitar la propia definición de la función para quitar las dependencias en el objeto que se va a modificar.

El enlace de la función a los objetos a los que hace referencia solamente se quita cuando se ejecuta una de estas acciones:

  • Se quita la función.

  • La función se modifica con la instrucción ALTER sin especificar la opción SCHEMABINDING.

Una función se puede enlazar a esquema solamente si se cumplen las siguientes condiciones:

  • Las funciones definidas por el usuario a las que hace referencia la función también están enlazadas a esquema.

  • Se hace referencia a las funciones y otras funciones definidas por el usuario a las que hace referencia la función mediante un nombre de una o dos partes.

  • En el cuerpo de las funciones definidas por el usuario solo se puede hacer referencia a las funciones integradas y a otras funciones definidas por el usuario de la misma base de datos.

  • El usuario que ejecutó la CREATE FUNCTION instrucción tiene permiso REFERENCES en los objetos de base de datos a los que hace referencia la función.

Para quitar SCHEMABINDING, use ALTER.

RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT

Especifica el atributo OnNULLCall de una función con valores escalares. Si no se especifica, CALLED ON NULL INPUT está implícito de forma predeterminada y el cuerpo de la función se ejecuta incluso si NULL se pasa como argumento.

procedimientos recomendados

Si una función definida por el usuario no se crea con la cláusula SCHEMABINDING, los cambios que se realicen en los objetos subyacentes pueden afectar a la definición de la función y generar resultados inesperados al invocarla. Recomendamos implementar uno de los siguientes métodos para garantizar que la función no queda sin actualizar como consecuencia de los cambios realizados en sus objetos subyacentes:

  • Especifique la cláusula WITH SCHEMABINDING cuando vaya a crear la función. Así se asegura de que no se pueden modificar los objetos a los que se hace referencia en la definición de la función a menos que también se modifique la función.

Interoperabilidad

Las siguientes instrucciones son válidas en una función con valores escalares:

  • Instrucciones de asignación.

  • Instrucciones de control de flujo, excepto las instrucciones TRY...CATCH.

  • Instrucciones DECLARE que definen variables de datos locales.

En una función con valores de tabla insertados (versión preliminar), solo se permite una única instrucción SELECT.

Limitaciones

Las funciones definidas por el usuario no se pueden utilizar para realizar acciones que modifican el estado de la base de datos.

Las funciones definidas por el usuario se pueden anidar; es decir, una función definida por el usuario puede llamar a otra. El nivel de anidamiento aumenta cuando se empieza a ejecutar la función llamada y disminuye cuando se termina de ejecutar la función llamada. Las funciones definidas por el usuario se pueden anidar hasta un máximo de 32 niveles. Si se superan los niveles máximos de anidamiento, la cadena completa de funciones de llamada produce un error.

Los objetos, incluidas las funciones, no se pueden crear en la base de datos master del grupo de SQL sin servidor en Azure Synapse Analytics.

Metadatos

En esta sección se enumeran las vistas de catálogo del sistema que se pueden usar para devolver metadatos sobre funciones definidas por el usuario.

  • sys.sql_modules: Muestra la definición de funciones definidas por el usuario de Transact-SQL. Por ejemplo:

    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: Muestra información sobre los parámetros definidos en funciones definidas por el usuario.

  • sys.sql_expression_dependencies: Muestra los objetos subyacentes a los que hace referencia una función.

Permisos

Se requiere el permiso CREATE FUNCTION en la base de datos y el permiso ALTER en el esquema en el que se va a crear la función.

Ejemplos: Azure Synapse Analytics y Sistema de la plataforma de análisis (PDW)

A Uso de una función definida por el usuario con valores escalares para cambiar un tipo de datos

Esta función simple toma un tipo de datos int como entrada y devuelve un tipo de datos decimal (10,2) como salida.

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';

Nota

Las funciones escalares no están disponibles en los grupos de SQL sin servidor o Microsoft Fabric.

Ejemplos: Azure Synapse Analytics

A Creación de una función alineada con valores de tabla

En el ejemplo siguiente se crea una función con valores de tabla insertados para devolver cierta información clave sobre los módulos, filtrando por el parámetro objectType. Incluye un valor predeterminado para devolver todos los módulos cuando se llama a la función con el DEFAULT parámetro . En este ejemplo se usan algunas de las vistas de catálogo del sistema mencionadas en Metadatos.

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

A continuación, se puede llamar a la función para devolver todos los objetos de vista (V) con:

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

Nota

Las funciones de valores de tabla insertados están disponibles en los grupos de SQL sin servidor, pero en versión preliminar en los grupos de SQL dedicados.

B. Combinación de resultados de una función con valores de tabla insertados

En este sencillo ejemplo se usa la función con valores de tabla insertados creada anteriormente para mostrar cómo se pueden combinar sus resultados con otras tablas mediante CROSS APPLY. Aquí, se seleccionan todas las columnas de y sys.objects los resultados de ModulesByType para todas las filas que coinciden en la columna de tipo . Para obtener más información sobre cómo usar APPLY, vea Cláusula FROM más JOIN, APPLY, PIVOT.

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

Nota

Las funciones de valores de tabla insertados están disponibles en los grupos de SQL sin servidor, pero en versión preliminar en los grupos de SQL dedicados.

Paso siguiente

Creación de funciones definidas por el usuario (motor de base de datos)