CREATE FUNCTION (Azure Synapse Analytics y Microsoft Fabric)

Se aplica a:Azure Synapse AnalyticsAnalytics Platform System (PDW)Punto de conexión de análisis SQL en Microsoft FabricAlmacenamiento de Microsoft Fabric

Crea una función definida por el usuario 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

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

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.

@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 que los identificadores. 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.

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 timestamp (rowversion) 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. function_body no puede contener una instrucción SELECT ni hacer referencia a datos de la base de datos. 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 SELECT individual que define el valor devuelto de una función insertada con valores de tabla. Las funciones insertada con valores de tabla no tienen cuerpo; la tabla es el conjunto de resultados de una sola instrucción SELECT.

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 funciones con valores de tabla insertados (versión preliminar), el valor devuelto de TABLE se define mediante una única instrucción SELECT. Las funciones insertadas no tienen variables devueltas asociadas.

<function_option>::=

Especifica que la función tendrá una o más 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 instrucción CREATE FUNCTION tiene permisos REFERENCES para 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, se utiliza CALLED ON NULL INPUT de manera predeterminada. Esto significa que el cuerpo de la función se ejecuta aunque se envíe NULL 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 al 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 y restricciones

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

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. Usar una función con valores escalares definida por el usuario para modificar 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';  

Ejemplos: Azure Synapse Analytics

A. Crear 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 parámetro DEFAULT. 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');

B. Combinación de los 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 sys.objects y los resultados de ModulesByType de todas las filas que coincidan con la columna type. 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

Pasos siguientes

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