Nota
L'accesso a questa pagina richiede l'autorizzazione. È possibile provare ad accedere o modificare le directory.
L'accesso a questa pagina richiede l'autorizzazione. È possibile provare a modificare le directory.
Si applica a:endpoint di analisi SQL in Microsoft Fabric e Warehouse in Microsoft Fabric
CREATE FUNCTION crea funzioni di tabelle inline e funzioni scalari.
Nota
Gli UDF esterni sono una funzione di anteprima in Fabric Data Warehouse.
Questo articolo si applica a Microsoft Fabric Data Warehouse e all'endpoint di analisi SQL di Fabric elementi. Per altre piattaforme, vedi CREATE FUNCTION (Transact-SQL).
Una funzione definita dall'utente è una routine Transact-SQL che accetta parametri, esegue un'azione come un calcolo complesso e restituisce il risultato di quell'azione come valore. Le funzioni scalari restituiscono un valore scalare, ad esempio un numero o una stringa. Le funzioni con valori di tabella definite dall'utente restituiscono una tabella.
Da usare CREATE FUNCTION per creare una routine T-SQL riutilizzabile che puoi utilizzare in questi modi:
- In Transact-SQL affermazioni come
SELECT. - In Warehouse, in Transact-SQL istruzioni di manipolazione dei dati (DML) come
UPDATE,INSERT, eDELETE. - Nelle applicazioni che chiamano la funzione.
- Nella definizione di un'altra funzione definita dall'utente.
- Per sostituire una procedura memorizzata.
Specificare CREATE OR ALTER FUNCTION di creare una nuova funzione se non esiste con quel nome, oppure modificare una funzione esistente, in una singola istruzione.
Transact-SQL Convenzioni sintattiche
Sintassi
Sintassi della funzione scalare
CREATE [ OR ALTER ] 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>::=
{
[ INLINE = AUTO ]
| [ SCHEMABINDING ]
| [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
}
Sintassi della funzione inline con valori di tabella
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] parameter_data_type
[ = default ] }
[ ,...n ]
]
)
RETURNS TABLE
[ WITH SCHEMABINDING ]
[ AS ]
RETURN [ ( ] select_stmt [ ) ]
[ ; ]
Sintassi delle funzioni esterne
Un UDF esterno è una funzione che fa riferimento a una funzione dati utente Fabric esterna.
CREATE FUNCTION [ schema_name. ] function_name
[ RETURNS return_data_type ]
AS EXTERNAL FUNCTION external_function_set_name.external_function_name
[ ; ]
Nota
Gli UDF esterni sono una funzione di anteprima in Fabric Data Warehouse.
L'istruzione CREATE FUNCTION deriva automaticamente il tipo di ritorno, i parametri e i tipi di parametri dalla definizione della funzione Fabric User Data Function esterna di riferimento. Se la firma della funzione dati utente Fabric esterna cambia, devi ricreare la funzione per sincronizzarne la definizione con la firma aggiornata.
Argomenti
schema_name
Nome dello schema a cui appartiene la funzione definita dall'utente.
function_name
Nome della funzione definita dall'utente. I nomi delle funzioni devono seguire le regole degli identificatori ed essere unici all'interno del database e del suo schema.
Per definizioni di funzioni scalari e tabelle inline T-SQL native, inserisci parentesi dopo il nome della funzione anche quando non ci sono parametri.
Le definizioni di funzioni esterne utilizzano la sintassi separata AS EXTERNAL FUNCTION .
@ parameter_name
Parametro nella funzione definita dall'utente. Puoi dichiarare uno o più parametri.
Una funzione può avere fino a 2.100 parametri. Quando un utente o un'applicazione chiama una funzione, devi fornire il valore per ogni parametro dichiarato a meno che tu non definisca un valore predefinito per il parametro.
Specificare un nome di parametro usando come primo carattere il simbolo di chiocciola (@). Il nome del parametro deve seguire le regole degli identificatori. I parametri sono locali rispetto alla funzione. Puoi usare gli stessi nomi dei parametri in altre funzioni. I parametri possono sostituire solo le costanti. Non possono essere usati al posto dei nomi delle tabelle, dei nomi delle colonne o dei nomi di altri oggetti del database.
ANSI_WARNINGS non viene rispettato quando si passa parametri in una procedura memorizzata, in una funzione definita dall'utente o quando dichiari e imposti variabili in un'istruzione batch. Ad esempio, se definisci una variabile come char(3) e poi la imposti a un valore superiore a tre caratteri, i dati vengono troncati alla dimensione definita e l'istruzione SQL ha successo.
parameter_data_type
Tipo di dati del parametro. Per le funzioni Transact-SQL sono consentiti tutti i tipi di dati scalari supportati .
[ = predefinito ]
Valore predefinito per il parametro. Se definisci un valore predefinito , puoi eseguire la funzione senza specificare un valore per quel parametro.
Quando un parametro della funzione ha un valore predefinito, devi specificare la parola chiave DEFAULT quando si chiama la funzione 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 in Fabric Data Warehouse, puoi usare tutti i tipi di dati tranne iltimestamp della versione /delle righe. I tipi non scalari come i tavoli non sono ammessi.
Per UDF esterni (Anteprima), la RETURNS clausola è opzionale. Se omesso, il tipo di ritorno viene automaticamente dedotto dal tipo di ritorno della funzione dati utente Fabric di riferimento. Puoi specificare la RETURNS clausola per sovrascrivere il tipo di ritorno inferito quando necessario.
function_body
Serie di istruzioni Transact-SQL.
Nelle funzioni scalari function_body è una serie di istruzioni Transact-SQL che insieme restituiscono un valore scalare, che può includere:
- Espressione di istruzione singola
- Espressioni multi-istruzioni (
IF/ELSEeBEGIN/ENDblocchi) - Variabili locali
- Chiamate alle funzioni SQL predefinite disponibili
- Chiamate ad altre funzioni definite dall'utente
-
SELECTistruzioni e riferimenti a tabelle, viste e funzioni a valore di tabella inline - Istruzioni di controllo del flusso, incluse
IF/ELSE,WHILE,BREAK,CONTINUE, eRETURN
Per UDF esterni (Preview), non puoi specificare il function_body perché l'implementazione della funzione è definita esternamente nella funzione Fabric User Data Function associata. Il database memorizza solo i metadati della funzione. La logica eseguibile risiede nella definizione della funzione esterna.
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 a valori di tabella inline, non esiste un corpo di funzione. La tabella è il set di risultati di un'unica SELECT istruzione.
TABLE
Specifica che il valore restituito della funzione con valori di tabella è una tabella. Puoi passare solo costanti e @local_variables ai TVF.
Nei TVF inline (anteprima), si definisce il TABLE valore di ritorno tramite una singola SELECT istruzione. Le funzioni inline non hanno variabili di ritorno associate.
<function_option>
In Fabric Data Warehouse, le ENCRYPTION parole chiave e EXECUTE AS non sono supportate.
Le opzioni di funzione supportate includono:
INLINE = AUTO
Specifica se una funzione scalare definita dall'utente può essere creata o modificata indipendentemente dai requisiti di inlining. La INLINE clausola è facoltativa. Per un UDF scalare inlineabile, specificare INLINE = AUTO non cambia la sua inlineabilità o il comportamento di esecuzione.
Per un pattern di chiamata standalone supportato, vedi Creare un UDF scalare per l'esecuzione standalone.
SCHEMABINDING
Specifica che la funzione è associata agli oggetti di database a cui fa riferimento. Quando specifichi SCHEMABINDING, non puoi modificare gli oggetti sottostanti (come una vista o una tabella, per esempio) in modo che influenzi la definizione della funzione. Devi prima modificare o eliminare la definizione di funzione per rimuovere le dipendenze dall'oggetto che vuoi modificare.
L'associazione della funzione agli oggetti cui fa riferimento viene rimossa solo quando viene eseguita una delle azioni seguenti:
Tu abbandoni la funzione.
Fai
ALTERla funzione e rimuovi l'opzioneSCHEMABINDING.
Puoi solo associare uno schema a una funzione se le seguenti condizioni sono vere:
Qualsiasi funzione definita dall'utente a cui la funzione fa riferimento è anch'essa legata allo schema.
La funzione fa riferimento agli oggetti usando un nome a due parti.
All'interno del corpo degli UDF, puoi solo fare riferimento alle funzioni integrate e ad altri UDF nello stesso database.
L'utente che esegue l'istruzione
CREATE FUNCTIONha il permesso REFERENCES sugli oggetti del database a cui la funzione fa referenza.
Per rimuovere SCHEMABINDING, usare ALTER.
RESTITUISCE NULL SU INPUT NULL | CHIAMATO SU INPUT NULL
Specifica l'attributo OnNULLCall di una funzione con valori scalari. Se non specifichi questo attributo, CALLED ON NULL INPUT è implicito di default, e il corpo della funzione viene eseguito anche se NULL viene passato come argomento.
COME ESTERNO FUNCTION
Fa riferimento a una Fabric User Data Function (UDF). Quando la funzione viene invocata, l'esecuzione viene delegata alla Fabric User Data Function di riferimento, dove la logica viene eseguita e il risultato viene restituito al chiamante T-SQL.
Questa clausola accetta i seguenti parametri:
- Nome del set di funzioni: Il nome del set di funzioni che contiene la funzione dati utente Fabric. Ogni Funzione Dati Utente Fabric deve appartenere a un set di funzioni.
- Nome funzione: Il nome della funzione dati utente Fabric a cui fare riferimento.
La seguente sintassi mostra come questi parametri sono specificati:
CREATE FUNCTION [schema_name.]function_name
AS EXTERNAL FUNCTION <<functionset_name>>.<<python_function_name>>
Poiché la funzione viene eseguita da remoto, chiamare una funzione esterna di solito comporta una latenza maggiore rispetto all'esecuzione di una funzione nativa T-SQL definita dall'utente. Utilizzare funzioni esterne quando la logica di business richiesta è implementata in Fabric e non può essere espressa direttamente in T-SQL.
L'istruzione CREATE FUNCTION deriva automaticamente il tipo di ritorno, i parametri e i tipi di parametri dalla definizione della funzione Fabric User Data Function esterna di riferimento. Se la firma della funzione dati utente Fabric esterna cambia, devi ricreare la funzione per sincronizzarne la definizione con la firma aggiornata.
Procedure consigliate
Scrivi le tue funzioni definite dall'utente in modo che siano inlineabili. Per maggiori informazioni sul concetto di inlining, vedi Inlining of scalar UDF. Per esempi di come rendere inlineabile una UDF scalare, vedi Creare una UDF scalare in Microsoft Fabric Data Warehouse.
Se non crei una funzione definita dall'utente con schemabinding, le modifiche agli oggetti sottostanti possono influenzare la definizione della funzione e causare risultati inaspettati quando la invochi. Quando specifichi
WITH SCHEMABINDINGquando crei la funzione, ti assicuri che le modifiche successive agli oggetti sottostanti non possano modificare o rompere il comportamento della funzione.Quando possibile, implementa la tua logica come una funzione T-SQL definita dall'utente (UDF). Usa un UDF esterno solo per funzionalità che non possono essere implementate nel linguaggio T-SQL. Questo approccio aiuta a minimizzare il sovraccarico dell'esecuzione remota e generalmente offre migliori prestazioni delle query.
Interoperabilità
Funzioni definite dall'utente con valori di tabella inline
Una funzione a valori di tabella inline accetta solo una singola SELECT affermazione.
Funzioni scalari definite dall'utente
Quando un UDF scalare non è inlineabile,
CREATEla dichiarazione ORALTERrifiuterebbe l'istruzione DDL con un messaggio di errore personalizzato.La funzione non inlineabile può essere creata specificando
INLINE=AUTOl'opzione funzione. Una funzione non inlineabile non può essere usata in unaSELECT ... FROMquery su una tabella utente.INLINE=AUTOnon rende la funzione inlineabile.In una funzione a valori scalari sono valide le istruzioni seguenti:
- Istruzioni di assegnazione.
- Istruzioni control-of-flow eccetto
TRY...CATCHeGOTOstatement. -
DECLAREistruzioni che definiscono variabili dati locali. - Chiamate a funzioni integrate.
- Riferimenti a tabelle/viste/iTVF/altri UDF scalari.
Le istruzioni DML non sono permesse nelle funzioni scalari definite dall'utente.
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 Transact-SQL funzioni definite dall'utente, oltre alle informazioni sull'inlineabilità. Ad esempio:
SELECT SCHEMA_NAME(o.schema_id) AS SchemaName, o.name AS FunctionName, m.definition AS FunctionDefinition, m.is_inlineable AS Inlineable, m.inline_eligibility_mask AS InlineEligibilityMask FROM sys.objects o JOIN sys.sql_modules m ON o.object_id = m.object_id WHERE o.type = 'FN';sys.parameters: Visualizza informazioni sui parametri definiti nelle funzioni definite dall'utente. Il seguente esempio utilizza la
sys.parametersvista catalogo per visualizzare le firme delle funzioni, inclusi i tipi di ritorno e i tipi di dati dei parametri:WITH metadata AS ( SELECT o.schema_id, o.object_id, o.name, p.parameter_id, p.name AS parameter_name, t.name AS type_name FROM sys.objects o JOIN sys.parameters p ON p.object_id = o.object_id JOIN sys.types t ON t.user_type_id = p.user_type_id WHERE o.type IN ('FN', 'XF') ), signature AS ( SELECT SCHEMA_NAME(schema_id) as schema_name, name, ANY_VALUE(CASE WHEN parameter_id = 0 THEN type_name END) return_type, CONCAT( SCHEMA_NAME(schema_id), '.', name, '(',STRING_AGG(CASE WHEN parameter_id > 0 THEN CONCAT(parameter_name, ' ', type_name) END, ', ' ) WITHIN GROUP (ORDER BY parameter_id), ') -> ', MAX(CASE WHEN parameter_id = 0 THEN type_name END) ) AS signature FROM metadata GROUP BY schema_id, object_id, name ) SELECT * FROM signature;sys.sql_expression_dependencies: Visualizza gli oggetti sottostanti a cui si fa riferimento da una funzione.
Autorizzazioni
I membri dei ruoli Amministratore, Membro e Collaboratore dell'area di lavoro infrastruttura possono creare funzioni.
Inlining della UDF scalare
Microsoft Fabric Data Warehouse inserisce automaticamente le UDF scalari idonee durante la compilazione delle query.
L'inlining di Expression Block compila le istruzioni della funzione, inclusi branch e loop, in un'espressione scalare che viene eseguita come parte della query chiamata. L'inlining scalare UDF trasforma il corpo della funzione in espressioni o sottoquery come parte della query chiamata.
Una funzione può soddisfare i requisiti per entrambe le tecniche, ma il motore ne seleziona una in base alla sua definizione. Funzioni diverse all'interno della stessa query possono utilizzare tecniche differenti.
Man mano che Microsoft Fabric Data Warehouse amplia le sue capacità di inlineing, le funzioni esistenti le acquisiscono automaticamente.
Costrutti di funzioni supportate
La tabella seguente confronta i costrutti funzione-corpo supportati.
| Costruzione funzione-corpo | Inlining a blocchi di espressione | Inlining di funzioni scalari definite dall'utente |
|---|---|---|
Espressioni scalari, variabili locali e IF/ELSE |
Supportato | Supportato |
WHILE anelli, inclusi BREAK e CONTINUE |
Supportato | Non supportato |
Diverse RETURN istruzioni |
Supportato | Non supportato |
NEWID() |
Supportato | Non supportato |
Funzioni dipendenti dal tempo (ad esempio, GETDATE() e SYSDATETIME()) |
Supportato | Non supportato |
| Accesso TVF a tabella, vista o inline | Non supportato | Supportato |
| Chiamate a UDF scalari* | Non supportato | Supportato |
* Le funzioni definite dall'utente possono essere annidate: una UDF può chiamarne un'altra. Il livello di annidamento aumenta quando una funzione chiamata inizia e diminuisce quando termina. Microsoft Fabric Data Warehouse supporta fino a quattro livelli di annidamento quando un corpo UDF fa riferimento a una tabella, vista o TVF inline, oppure fino a 32 livelli altrimenti. Superare il limite di annidamento provoca il fallimento della catena di funzioni chiamante.
Funzioni predefinite
Il supporto per funzioni integrate, comprese quelle di sistema, differisce tra le due tecniche di inlining.
- L'inlining di Expression Block non supporta funzioni AI, funzioni aggregate, funzioni di metadati o funzioni di sicurezza nel corpo della funzione.
- L'inlining scalare UDF presenta restrizioni funzionali integrate descritte nei requisiti di inlining scalare UDF.
Ad esempio, creare una UDF che utilizza @@ROWCOUNT senza INLINE = AUTO restituire l'errore 19841. Il messaggio di errore include il seguente testo:
Function <function_name> cannot be inlined.
Expression block: Unsupported built-in @@ROWCOUNT found.
Scalar UDF inlining: Unsupported built-in @@ROWCOUNT found.
Per una definizione specifica di funzione, vedi Verifica se un UDF scalare può essere inlineato.
Richieste di chiamata supportate
La tabella seguente confronta il supporto per calling-query per UDF scalari inlineabili.
| Funzionalità query | Inlining a blocchi di espressione | Inlining di funzioni scalari definite dall'utente |
|---|---|---|
UDF chiama nell'elenco SELECT , WHERE, oppure JOIN ... ON |
Supportato | Supportato |
Chiamate dirette UDF in GROUP BY o HAVING |
Supportato | Non supportato |
Chiamate dirette UDF ORDER BY |
Supportato | Non supportato |
| CTE nella query chiamata | Supportato | Non supportato |
ROLLUP, CUBE, oppure GROUPING SETS |
Non supportato | Non supportato |
Se qualsiasi chiamata UDF si basa sull'inlining UDF Scalare, l'intera query deve soddisfare i requisiti di calling-query di quella tecnica.
Con l'inlining scalare UDF, proiettare il risultato UDF in SELECT elenco e ordine tramite il suo alias di colonna o ordinale invece di chiamare direttamente l'UDF in ORDER BY.
Per esempi di riscrittura di query, vedi Adattare una query chiamante per l'inlining UDF Scalar.
Per altri limiti di esecuzione, vedi Limitazioni.
Controllare se una funzione definita dall'utente scalare può essere inlined
La sys.sql_modules visualizzazione del catalogo descrive l'idoneità in base alla definizione della funzione.
is_inlineable è 1 per una definizione inlineabile e 0 altro.
inline_eligibility_mask identifica le tecniche ammissibili:
inline_eligibility_mask |
Definizione di ammissibilità |
|---|---|
0 |
Nessuna delle due tecniche |
1 |
Inlining di funzioni scalari definite dall'utente |
2 |
Inlining a blocchi di espressione |
3 |
Entrambe le tecniche |
Questi valori descrivono la definizione della funzione, non il piano di esecuzione di una specifica query.
Usa la seguente query di esempio per verificare se un UDF scalare è inlineabile:
SELECT
SCHEMA_NAME(b.schema_id) AS function_schema_name,
b.name AS function_name,
b.type_desc AS function_type,
a.is_inlineable,
a.inline_eligibility_mask
FROM sys.sql_modules AS a
INNER JOIN sys.objects AS b
ON a.object_id = b.object_id
WHERE b.type IN ('FN');
Limiti
- Chiamare un UDF scalare in un contesto di query non supportato può restituire l'errore
Scalar UDF execution is currently unavailable in this context. - Una query che si basa sull'inlining scalare UDF può fallire se contiene più di 10 chiamate UDF.
Esempi
R. Creare una funzione inline con valori di tabella
Il seguente esempio crea una funzione inline a valori di tabella che restituisce informazioni chiave sui moduli, filtrando per parametro objectType . Include un valore predefinito per restituire tutti i moduli quando chiami la funzione con il DEFAULT parametro. Questo esempio utilizza alcune delle viste del catalogo di sistema menzionate nei 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',
sm.is_inlineable AS 'Inlineable'
FROM sys.sql_modules AS sm
INNER JOIN sys.objects AS o ON sm.object_id = o.object_id
WHERE o.type LIKE '%' + @objectType + '%'
);
GO
Chiama la funzione per restituire tutte le funzioni a valori di tabella inline (IF):
SELECT * FROM dbo.ModulesByType('IF'); -- SQL_INLINE_TABLE_VALUED_FUNCTION
In alternativa, trovare tutte le funzioni scalari (FN):
SELECT * FROM dbo.ModulesByType('FN'); -- SQL_SCALAR_FUNCTION
B. Combinare i risultati di una funzione inline con valori di tabella
Questo semplice esempio utilizza il TVF inline precedentemente creato per dimostrare come combinare i suoi risultati con altre tabelle usando CROSS APPLY. Qui, selezioni tutte le colonne sia da sys.objects sia dai risultati di ModulesByType per tutte le righe che corrispondono sulla type colonna. Per maggiori informazioni sull'uso APPLYdi , vedi la clausola FROM più JOIN, APPLY, PIVOT (Transact-SQL).
SELECT *
FROM sys.objects AS o
CROSS APPLY dbo.ModulesByType(o.type);
GO
C. Creare una funzione UDF scalare
Nell'esempio seguente viene creata una funzione definita dall'utente scalare inline che maschera un testo di input.
CREATE OR ALTER FUNCTION [dbo].[cleanInput] (@InputString VARCHAR(100))
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @Result VARCHAR(50);
DECLARE @CleanedInput VARCHAR(50);
-- Trim whitespace
SET @CleanedInput = LTRIM(RTRIM(@InputString));
-- Handle empty or null input
IF @CleanedInput = '' OR @CleanedInput IS NULL
BEGIN
SET @Result = '';
END
ELSE IF LEN(@CleanedInput) <= 2
BEGIN
-- If string length is 1 or 2, just return the cleaned string
SET @Result = @CleanedInput;
END
ELSE
BEGIN
-- Construct the masked string
SET @Result =
LEFT(@CleanedInput, 1) +
REPLICATE('*', LEN(@CleanedInput) - 2) +
RIGHT(@CleanedInput, 1);
END
RETURN @Result
END
È possibile chiamare la funzione come segue:
DECLARE @input varchar(100) = '123456789';
SELECT dbo.cleanInput (@input) AS function_output;
Altri esempi di come usare funzioni definite dall'utente scalari in Fabric Data Warehouse:
In un'istruzione SELECT :
SELECT TOP 10
t.id, t.name,
dbo.cleanInput (t.name) AS function_output
FROM dbo.MyTable AS t;
In una WHERE clausola:
SELECT t.id, t.name, dbo.cleanInput(t.name) AS function_output
FROM dbo.MyTable AS t
WHERE dbo.cleanInput(t.name) = '1*******9';
In una JOIN clausola:
SELECT t1.id, t1.name,
dbo.cleanInput (t1.name) AS function_output,
dbo.cleanInput (t2.name) AS function_output_2
FROM dbo.MyTable1 AS t1
INNER JOIN dbo.MyTable2 AS t2
ON dbo.cleanInput(t1.name)=dbo.cleanInput(t2.name);
In una ORDER BY clausola:
SELECT t.id, t.name, dbo.cleanInput (t.name) AS function_output
FROM dbo.MyTable AS t
ORDER BY function_output;
In Warehouse, puoi anche chiamare UDF scalari da istruzioni di manipolazione dati come INSERT, UPDATE, o DELETE. Questi esempi di modifica dei dati non si applicano all'endpoint di SQL analytics.
SELECT t.id, t.name, dbo.cleanInput (t.name) AS function_output
INTO dbo.MyTable_new
FROM dbo.MyTable AS t;
UPDATE t
SET t.mycolumn_new = dbo.cleanInput (t.name)
FROM dbo.MyTable AS t;
DELETE t
FROM dbo.MyTable AS t
WHERE dbo.cleanInput(t.name) = '1*******9';
D. Creare una funzione esterna
Il seguente esempio crea una funzione esterna nominata geohash nello dbo schema che fa riferimento alla geohash Fabric User Data Function nel spatial_functions set di funzioni. Poiché le definizioni dei parametri e il tipo di ritorno sono dedotte dalla funzione Fabric User Data Function di riferimento, non è necessario specificare esplicitamente nell'istruzioneCREATE FUNCTION.
CREATE FUNCTION dbo.geohash
AS EXTERNAL FUNCTION spatial_functions.geohash;
Una volta creata, la funzione esterna può essere utilizzata nelle query T-SQL proprio come qualsiasi altra funzione definita dall'utente o integrata. Può essere consultato ovunque siano supportate le chiamate di funzione, consentendo un'integrazione fluida delle Fabric User Data Functions nel codice T-SQL.
E. Crea un UDF scalare per un'esecuzione autonoma
La seguente configurazione per la creazione e il caricamento dei dati richiede il Magazzino. Su un endpoint SQL analytics, utilizza una tabella sorgente esistente con le colonne e i dati necessari.
CREATE TABLE dbo.Customer
(
CustomerID INT NOT NULL,
CustomerName VARCHAR(100) NOT NULL,
CustomerType VARCHAR(20) NOT NULL,
MemberSince DATE NOT NULL,
YearToDateRevenue DECIMAL(18, 2) NOT NULL
);
GO
INSERT INTO dbo.Customer
(
CustomerID,
CustomerName,
CustomerType,
MemberSince,
YearToDateRevenue
)
VALUES
(1, 'Customer 1', 'Regular', '2020-01-15', 12000.00),
(2, 'Customer 2', 'Premium', '2022-06-01', 24000.00),
(3, 'Customer 3', 'VIP', '2024-03-10', 36000.00);
GO
La funzione qui sotto combina l'accesso alla tabella con GETUTCDATE(). L'accesso alla tabella impedisce l'inlining del blocco di espressione e GETUTCDATE() impedisce l'inlining scalare UDF.
Senza WITH INLINE = AUTO, la definizione viene respinta al momento della creazione o della modifica.
L'errore indica che la funzione non può essere inlineata e fornisce le ragioni di rifiuto per entrambe le tecniche. Specifica WITH INLINE = AUTO per consentire la creazione:
CREATE OR ALTER FUNCTION dbo.GetCustomerTenureDays (@CustomerID INT)
RETURNS INT
WITH INLINE = AUTO
AS
BEGIN
DECLARE @MemberSince DATE;
SELECT @MemberSince = MemberSince
FROM dbo.Customer
WHERE CustomerID = @CustomerID;
RETURN DATEDIFF(DAY, @MemberSince, GETUTCDATE());
END;
GO
La funzione è creata ma rimane non inlineabile: is_inlineable e inline_eligibility_mask sono entrambe 0. Una chiamata autonoma ha successo:
DECLARE @udf_output int;
SET @udf_output = dbo.GetCustomerTenureDays(1);
SELECT @udf_output as 'udf_output';
La chiamata della funzione in una query su una tabella utente non è supportata:
SELECT
CustomerID,
dbo.GetCustomerTenureDays(CustomerID) AS TenureDays
FROM dbo.Customer;
Questa query restituisce Scalar UDF execution is currently unavailable in this context.
Suggerimento
Per utilizzare questa logica in una query su tabelle utente, valuta il valore in tempo corrente nel chiamante e passalo come parametro. Per la funzione riscritta e la query di chiamata, vedi Passare un valore dipendente dal tempo in un UDF di accesso ai dati.
Contenuti correlati
Si applica a:Azure Synapse Analytics
Crea una funzione definita dall'utente (UDF) in Azure Synapse Analytics. 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. Le funzioni con valori di tabella definite dall'utente restituiscono un tipo di dati table.
Suggerimento
Per la sintassi in Fabric Data Warehouse, vedi la versione di CREATE FUNCTION per Fabric Data Warehouse.
In Azure Synapse Analytics
CREATE FUNCTIONpuò 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.Nei pool SQL serverless in Azure Synapse Analytics è
CREATE FUNCTIONpossibile creare funzioni con valori di tabella inline ma non funzioni scalari.Usa questa frase per creare una routine riutilizzabile che puoi utilizzare in questi modi:
Nelle istruzioni Transact-SQL, ad esempio
SELECTNelle 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
Transact-SQL Convenzioni sintattiche
Sintassi
Sintassi della funzione scalare
-- Transact-SQL Scalar Function Syntax (in dedicated pools in Azure Synapse Analytics)
-- Not available in the serverless SQL pools in Azure Synapse Analytics
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
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 delle funzioni devono seguire le regole degli identificatori ed essere unici all'interno del database e del suo schema.
Nota
Devi includere parentesi dopo il nome della funzione anche se non specifichi un parametro.
@ parameter_name
Parametro nella funzione definita dall'utente. Puoi dichiarare uno o più parametri.
Una funzione può avere fino a 2.100 parametri. Quando un utente o un'applicazione chiama una funzione, devi fornire il valore per ogni parametro dichiarato a meno che tu non definisca un valore predefinito per il parametro.
Specificare un nome di parametro usando come primo carattere il simbolo di chiocciola (@). Il nome del parametro deve seguire le regole degli identificatori. I parametri sono locali alla funzione; Puoi usare gli stessi nomi di parametri in altre funzioni. I parametri possono sostituire solo le costanti; Non possono essere usati al posto dei nomi delle tabelle, dei nomi delle colonne o dei nomi di altri oggetti del database.
Nota
ANSI_WARNINGS non viene rispettato quando si passa parametri in una procedura memorizzata, in una funzione definita dall'utente o quando dichiari e imposti variabili in un'istruzione batch. Ad esempio, se definisci una variabile come char(3) e poi la imposti a un valore superiore a tre caratteri, i dati vengono troncati alla dimensione definita e l'istruzione INSERT o UPDATE ha successo.
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 dato timestamp (rowversion) non è un tipo supportato.
[ = predefinito ]
Valore predefinito per il parametro. Se definisci un valore predefinito , puoi eseguire la funzione senza specificare un valore per quel parametro.
Quando un parametro della funzione ha un valore predefinito, devi specificare la parola chiave DEFAULT quando si chiama la funzione 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. Iltipo di data timestamp della / delle righe non è un tipo supportato. I tipi non scalari con cursore e tabella non sono permessi.
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ò consultare tabelle o viste. Il corpo delle funzioni può chiamare altre funzioni deterministiche ma non può 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 a valori di tabella inline, non esiste un corpo di funzione; la tabella è l'insieme dei risultati di una singola SELECT affermazione.
TABLE
Specifica che il valore restituito della funzione con valori di tabella è una tabella. Puoi passare solo costanti e @local_variables ai TVF.
Nei TVF inline (anteprima), si definisce il TABLE valore di ritorno tramite una singola SELECT istruzione. Le funzioni inline non hanno variabili di ritorno associate.
<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 specifichi SCHEMABINDING, non puoi modificare gli oggetti sottostanti (come una vista o una tabella, per esempio) in modo che influenzi la definizione della funzione. Devi prima modificare o eliminare la definizione di funzione per rimuovere le dipendenze dall'oggetto che vuoi modificare.
L'associazione della funzione agli oggetti cui fa riferimento viene rimossa solo quando viene eseguita una delle azioni seguenti:
Tu abbandoni la funzione.
Fai
ALTERla funzione e rimuovi l'opzioneSCHEMABINDING.
Puoi solo associare uno schema a una funzione se le seguenti condizioni sono vere:
Qualsiasi funzione definita dall'utente a cui la funzione fa riferimento è anch'essa legata allo schema.
I riferimenti alle funzioni utilizzano nomi a una o due parti.
All'interno del corpo degli UDF, puoi solo fare riferimento alle funzioni integrate e ad altri UDF nello stesso database.
L'utente che esegue l'istruzione
CREATE FUNCTIONha il permesso REFERENCES sugli oggetti del database a cui la funzione fa referenza.
Per rimuovere SCHEMABINDING, usare ALTER.
RESTITUISCE NULL IN INPUT NULL | CHIAMATA SU INPUT NULL
Specifica l'attributo OnNULLCall di una funzione con valori scalari. Se non specifichi questo attributo, CALLED ON NULL INPUT è implicito di default, e il corpo della funzione viene eseguito anche se NULL viene passato come argomento.
Procedure consigliate
Se non crei una funzione definita dall'utente con la clausola SCHEMABIND, le modifiche agli oggetti sottostanti possono influenzare la definizione della funzione e causare risultati inaspettati quando la invochi. Specifica la WITH SCHEMABINDING clausola quando crei la funzione. Questa clausola garantisce che non si possano modificare gli oggetti riferiti nella definizione della funzione a meno che non si modifichi anche la funzione.
Interoperabilità
In una funzione a valori scalari sono valide le istruzioni seguenti:
Istruzioni di assegnazione.
Istruzioni di controllo del flusso, tranne TRY... CATCH statements.
Istruzioni DECLARE che definiscono variabili di dati locali.
In una funzione a valori di tabella inline (preview), puoi usare solo una singola istruzione select.
Limiti
Non puoi usare funzioni definite dall'utente per eseguire azioni che modificano lo stato del database.
Puoi annidare funzioni definite dall'utente. Una funzione definita dall'utente può chiamarne un'altra. Il livello di annidamento aumenta quando la funzione chiamata inizia l'esecuzione, e diminuisce quando la funzione chiamata termina l'esecuzione. Se superi i livelli massimi di annidamento, l'intera catena di funzioni chiamanti fallisce.
Non puoi creare oggetti, comprese le funzioni, nel master database del tuo 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 Transact-SQL funzioni 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 informazioni sui parametri definiti nelle funzioni definite dall'utente.
sys.sql_expression_dependencies: Visualizza gli oggetti sottostanti a cui si fa riferimento da una funzione.
Autorizzazioni
Richiede CREATE FUNCTION permesso nel database e permesso ALTER sullo schema in cui la funzione viene creata.
Esempi
R. Usare una funzione scalare-valued definita dall'utente per modificare un tipo di dati
Questa funzione semplice prende un tipo di dato int come input e restituisce un tipo di dato decimale(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.
B. Creare una funzione inline con valori di tabella
Il seguente esempio crea una funzione inline a valori di tabella che restituisce informazioni chiave sui moduli, filtrando per parametro objectType . Include un valore predefinito per restituire tutti i moduli quando chiami la funzione con il DEFAULT parametro. Questo esempio utilizza alcune delle viste del catalogo di sistema menzionate nei 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
Puoi chiamare la funzione per restituire tutti gli oggetti della vista (V) con:
select * from dbo.ModulesByType('V');
Nota
Le funzioni a valore di tabella inline sono disponibili nei pool SQL serverless, ma in anteprima nei pool SQL dedicati.
C. Combinare i risultati di una funzione inline con valori di tabella
Questo semplice esempio utilizza il TVF inline precedentemente creato per dimostrare come combinare i suoi risultati con altre tabelle usando CROSS APPLY. In questo esempio, selezioni tutte le colonne sia dai sys.objects risultati che per ModulesByType tutte le righe che corrispondono sulla type colonna. Per maggiori informazioni sull'uso APPLYdi , vedi la clausola FROM più JOIN, APPLY, PIVOT (Transact-SQL).
SELECT *
FROM sys.objects o
CROSS APPLY dbo.ModulesByType(o.type);
GO
Nota
Le funzioni a valore di tabella inline sono disponibili nei pool SQL serverless, ma in anteprima nei pool SQL dedicati.