Condividi tramite

CREATE FUNCTION (Azure Synapse Analytics e Microsoft Fabric)

  • Articolo

Si applica a: Endpoint di analisi SQL di Azure Synapse Analytics Platform System (PDW) in Microsoft Fabric Warehouse in Microsoft Fabric

Crea una funzione definita dall'utente in Azure Synapse Analytics, piattaforma di analisi (PDW) o Microsoft Fabric. Una funzione definita dall'utente è una routine Transact-SQL che accetta parametri, esegue un'azione, ad esempio un calcolo complesso, e restituisce il risultato di tale azione sotto forma di valore.

  • Nella piattaforma di strumenti analitici (PDW) il valore restituito deve essere un valore scalare (singolo).

  • In Azure Synapse Analytics CREATE FUNCTION può restituire una tabella usando la sintassi per le funzioni con valori di tabella inline (anteprima) oppure può restituire un singolo valore usando la sintassi per le funzioni scalari.

  • In Microsoft Fabric e pool SQL serverless in Azure Synapse Analytics CREATE FUNCTION può creare funzioni con valori di tabella inline ma non funzioni scalari. Le funzioni con valori di tabella definite dall'utente restituiscono un tipo di dati table.

    Utilizzare questa istruzione per creare una routine riutilizzabile che può essere utilizzata in queste modalità:

  • Nelle istruzioni Transact-SQL, ad esempio SELECT

  • Nelle applicazioni che chiamano la funzione.

  • Nella definizione di un'altra funzione definita dall'utente.

  • Per definire un vincolo CHECK su una colonna.

  • Per sostituire una stored procedure.

  • Usare una funzione inline come predicato di filtro per un criterio di sicurezza

Convenzioni relative alla sintassi Transact-SQL

Sintassi

Sintassi della funzione scalare

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

Sintassi della funzione inline con valori di tabella

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

Argomenti

schema_name

Nome dello schema a cui appartiene la funzione definita dall'utente.

function_name

Nome della funzione definita dall'utente. I nomi di funzione devono essere conformi alle regole per gli identificatori e devono essere univoci all'interno del database e rispetto al relativo schema.

Nota

È necessario apporre le parentesi dopo il nome della funzione anche se non viene specificato alcun parametro.

@parameter_name

Parametro della funzione definita dall'utente. È possibile dichiarare uno o più parametri.

Una funzione può avere al massimo 2.100 parametri. Il valore di ciascun parametro dichiarato deve essere specificato dall'utente quando viene eseguita la funzione, a meno che non venga definito un valore predefinito per tale parametro.

Specificare un nome di parametro usando come primo carattere il simbolo di chiocciola (@). I nomi di parametro devono essere conformi alle regole per gli identificatori. I parametri sono locali rispetto alla funzione. È pertanto possibile utilizzare gli stessi nomi di parametro in altre funzioni. I parametri possono rappresentare solo costanti, non nomi di tabella, di colonna o di altri oggetti di database.

Nota

ANSI_WARNINGS non viene rispettato quando si passano parametri in una stored procedure, una funzione definita dall'utente o quando si dichiarano e si impostano variabili in un'istruzione batch. Se, ad esempio, la variabile viene definita come char(3) e quindi impostata su un valore maggiore di tre caratteri, i dati verranno troncati alla dimensione definita e l'istruzione INSERT o UPDATE avrà esito positivo.

parameter_data_type

Tipo di dati del parametro. Per le funzioni Transact-SQL sono consentiti tutti i tipi di dati scalari supportati in Azure Synapse Analytics. Il tipo di dati timestamp (rowversion) non è supportato.

[ =default ]

Valore predefinito del parametro. Se viene definito un valore default, è possibile eseguire la funzione senza specificare un valore per il parametro corrispondente a tale valore.

Se a un parametro della funzione è associato un valore predefinito, alla chiamata della funzione è necessario specificare la parola chiave DEFAULT per recuperare il valore predefinito. Questo comportamento risulta diverso dall'utilizzo di parametri con valore predefinito nelle stored procedure in cui l'omissione del parametro implica l'utilizzo del valore predefinito.

return_data_type

Valore restituito di una funzione scalare definita dall'utente. Per le funzioni Transact-SQL sono consentiti tutti i tipi di dati scalari supportati in Azure Synapse Analytics. Il tipo di dati timestamp rowversion/non è un tipo supportato. I tipi non scalari cursore e tabella non sono consentiti.

function_body

Serie di istruzioni Transact-SQL. Il function_body non può contenere un'istruzione SELECT e non può fare riferimento ai dati del database. Il function_body non può fare riferimento a tabelle o viste. Il corpo della funzione può chiamare altre funzioni deterministiche, ma non è possibile chiamare funzioni non deterministiche.

Nelle funzioni scalari function_body corrisponde a una serie di istruzioni Transact-SQL che in combinazione restituiscono un valore scalare.

scalar_expression

Specifica il valore scalare restituito dalla funzione scalare.

select_stmt

Istruzione singola SELECT che definisce il valore restituito di una funzione con valori di tabella inline. Per una funzione inline con valori di tabella, non esiste alcun corpo della funzione; la tabella è il set di risultati di una singola SELECT istruzione.

TABLE

Specifica che il valore restituito della funzione con valori di tabella è una tabella. Alle funzioni con valori di tabella è possibile passare solo costanti e @local_variables.

Nelle funzioni CONF inline (anteprima), il valore restituito TABLE viene definito tramite una singola SELECT istruzione. Alle funzioni inline non sono associate variabili restituite.

<function_option>

Specifica che la funzione dispone di una o più delle opzioni seguenti.

SCHEMABINDING

Specifica che la funzione è associata agli oggetti di database a cui fa riferimento. Quando la clausola SCHEMABINDING viene specificata, non è possibile apportare agli oggetti di base modifiche che hanno effetto sulla definizione della funzione. È necessario prima modificare o eliminare la definizione della funzione per rimuovere le dipendenze dall'oggetto da modificare.

L'associazione della funzione agli oggetti cui fa riferimento viene rimossa solo quando viene eseguita una delle azioni seguenti:

  • La funzione viene eliminata.

  • La funzione viene modificata tramite l'istruzione ALTER senza specificare l'opzione SCHEMABINDING.

Una funzione può essere associata a uno schema solo se vengono soddisfatte le condizioni seguenti:

  • Le funzioni definite dall'utente a cui la funzione fa riferimento sono anch'esse associate a uno schema.

  • Alle funzioni e alle altre funzioni definite dall'utente a cui fa riferimento la funzione viene fatto riferimento tramite un nome composto da una o due parti.

  • Alle funzioni predefinite e alle altre funzioni definite dall'utente nello stesso database è possibile fare riferimento solo all'interno del corpo di funzioni definite dall'utente.

  • L'utente che ha eseguito l'istruzione dispone dell'autorizzazione CREATE FUNCTION REFERENCES per gli oggetti di database a cui fa riferimento la funzione.

Per rimuovere SCHEMABINDING, usare ALTER.

RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT

Specifica l'attributo OnNULLCall di una funzione a valori scalari. Se non specificato, CALLED ON NULL INPUT è implicito per impostazione predefinita e il corpo della funzione viene eseguito anche se NULL viene passato come argomento.

Procedure consigliate

Se una funzione definita dall'utente non viene creata tramite la clausola SCHEMABINDING, le modifiche apportate agli oggetti sottostanti possono influire sulla definizione della funzione e produrre risultati imprevisti quando viene richiamata. È consigliabile implementare uno dei metodi seguenti per assicurarsi che la funzione non diventi obsoleta in seguito a modifiche degli oggetti sottostanti:

  • Specificare la clausola WITH SCHEMABINDING quando si crea la funzione. In questo modo, gli oggetti a cui si fa riferimento nella definizione della funzione possono essere modificati solo se viene modificata anche la funzione.

Interoperabilità

In una funzione a valori scalari sono valide le istruzioni seguenti:

  • Istruzioni di assegnazione.

  • Istruzioni per il controllo di flusso, escluse le istruzioni TRY...CATCH.

  • Istruzioni DECLARE che definiscono le variabili dati locali.

In una funzione inline con valori di tabella (anteprima) è consentita una sola istruzione SELECT.

Limiti

Non è possibile utilizzare funzioni definite dall'utente per eseguire azioni che modificano lo stato del database.

È possibile nidificare le funzioni definite dall'utente, ovvero una funzione definita dall'utente ne può richiamare un'altra. Il livello di nidificazione aumenta all'avvio della funzione richiamata e diminuisce al termine dell'esecuzione della funzione. Le funzioni definite dall'utente possono essere nidificate fino a un massimo di 32 livelli. Se viene superato il livello massimo di nidificazioni, l'intera sequenza di funzioni chiamanti ha esito negativo.

Gli oggetti, incluse le funzioni, non possono essere creati nel master database del pool SQL serverless in Azure Synapse Analytics.

Metadati UFX

Nella sezione seguente vengono elencate le viste del catalogo di sistema usate per restituire i metadati sulle funzioni definite dall'utente.

  • sys.sql_modules: visualizza la definizione delle funzioni Transact-SQL definite dall'utente. Ad esempio:

    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: visualizza le informazioni sui parametri definiti nelle funzioni definite dall'utente.

  • sys.sql_expression_dependencies: visualizza gli oggetti sottostanti a cui fa riferimento una funzione.

Autorizzazioni

È necessario disporre dell'autorizzazione CREATE FUNCTION nel database e dell'autorizzazione ALTER per lo schema in cui la funzione è in fase di creazione.

Esempi: Azure Synapse Analytics e Piattaforma di strumenti analitici (PDW)

R. Usare una funzione scalare-valued definita dall'utente per modificare un tipo di dati

Questa semplice funzione accetta un tipo di dati int come input e restituisce un tipo di dati decimal(10,2) come output.

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

Le funzioni scalari non sono disponibili nei pool SQL serverless o in Microsoft Fabric.

Esempi: Azure Synapse Analytics

R. Creare una funzione inline con valori di tabella

L'esempio seguente crea una funzione inline con valori di tabella per restituire alcune informazioni chiave nei moduli, applicando un filtro in base al parametro objectType. Include un valore predefinito per restituire tutti i moduli quando la funzione viene chiamata con il DEFAULT parametro . Questo esempio usa alcune delle viste del catalogo di sistema indicate in Metadati.

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

La funzione può quindi essere chiamata per restituire tutti gli oggetti View (V) con:

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

Nota

Le funzioni con valori di tabella inline sono disponibili nei pool SQL serverless, ma sono in anteprima nei pool SQL dedicati.

B. Combinare i risultati di una funzione inline con valori di tabella

Questo semplice esempio usa la funzione inline con valori di tabella creata in precedenza per dimostrare in che modo è possibile combinare i risultati con altre tabelle usando Cross Apply. In questo caso vengono selezionate tutte le colonne di e sys.objects i risultati di ModulesByType per tutte le righe corrispondenti alla colonna di tipo . Per altri dettagli sull'uso di apply, vedere la clausola FROM con JOIN, APPLY, PIVOT.

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

Nota

Le funzioni con valori di tabella inline sono disponibili nei pool SQL serverless, ma sono in anteprima nei pool SQL dedicati.

Passaggio successivo

Creare funzioni definite dall'utente (motore di database)