CREATE FUNCTION (Azure Synapse Analytics und Microsoft Fabric)

  • Artikel

Gilt für:Azure Synapse AnalyticsAnalytics Platform System (PDW)SQL Analytics-Endpunkt in Microsoft FabricWarehouse in Microsoft Fabric

Erstellt eine benutzerdefinierte Funktion (UDF) in Azure Synapse Analytics, Analytics Platform System (PDW) oder Microsoft Fabric. Eine benutzerdefinierte Funktion ist eine Transact-SQL-Routine, die Parameter annimmt, eine Aktion ausführt (z. B. eine komplexe Berechnung) und das Ergebnis dieser Aktion als Wert zurückgeben kann.

  • In Analytics-Plattformsystem (PDW) muss der Rückgabewert ein Skalarwert (Einzelwert) sein.

  • In Azure Synapse Analytics kann CREATE FUNCTION durch Verwenden der Syntax für Inline-Tabellenwertfunktionen (Vorschau) eine Tabelle zurückgeben oder durch Verwenden der Syntax für Skalarfunktionen einen Einzelwert zurückgeben.

  • In Microsoft Fabric und serverlosen SQL-Pools in Azure Synapse Analytics können mit CREATE FUNCTION Inline-Tabellenwertfunktionen, aber keine Skalarfunktionen erstellt werden. Benutzerdefinierte Tabellenwertfunktionen (TVFs) geben einen table-Datentyp zurück.

    Verwenden Sie diese Anweisung zum Erstellen einer wiederverwendbaren Routine, die auf folgende Weise verwendet werden kann:

  • In Transact-SQL-Anweisungen, z. B. SELECT

  • In Anwendungen, die die Funktion aufrufen

  • Bei der Definition einer anderen benutzerdefinierten Funktion

  • Zum Definieren einer CHECK-Einschränkung für eine Spalte

  • Zum Ersetzen einer gespeicherten Prozedur

  • Zum Verwenden einer Inlinefunktion als Filterprädikat für eine Sicherheitsrichtlinie

Transact-SQL-Syntaxkonventionen

Syntax

Syntax der skalaren Funktion

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

Syntax der Inlinetabellenwertfunktion

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

Argumente

schema_name

Der Name des Schemas, zu dem die benutzerdefinierte Funktion gehört.

function_name

Der Name der benutzerdefinierten Funktion. Funktionsnamen müssen den Regeln für Bezeichner entsprechen und innerhalb der Datenbank und für jedes Schema eindeutig sein.

Hinweis

Auf den Funktionsnamen müssen Klammern folgen, selbst wenn kein Parameter angegeben ist.

@parameter_name

Ein Parameter in der benutzerdefinierten Funktion. Ein oder mehrere Parameter können deklariert werden.

Eine Funktion kann maximal 2.100 Parameter haben. Der Benutzer muss beim Ausführen einer Funktion den Wert jedes deklarierten Parameters angeben (sofern kein Standardwert für den betreffenden Parameter definiert ist).

Geben Sie einen Parameternamen an, der mit dem Zeichen ( @ ) beginnt. Der Parametername muss den Regeln für Bezeichner entsprechen. Parameter gelten lokal in der jeweiligen Funktion. Dieselben Parameternamen können in anderen Funktionen verwendet werden. Parameter können nur den Platz von Konstanten einnehmen. Sie können nicht anstelle von Tabellennamen, Spaltennamen oder Namen anderer Datenbankobjekte verwendet werden.

Hinweis

ANSI_WARNINGS wird nicht berücksichtigt, wenn Sie Parameter in einer gespeicherten Prozedur, benutzerdefinierten Funktion oder beim Deklarieren und Festlegen von Variablen in einer Batch-Anweisung übergeben. Wird beispielsweise eine Variable als char(3) definiert und dann auf einen Wert festgelegt, der länger als drei Zeichen ist, werden die Daten auf die definierte Größe abgeschnitten, und die Anweisung INSERT oder UPDATE wird erfolgreich ausgeführt.

parameter_data_type

Der Parameterdatentyp. Für Transact-SQL-Funktionen sind alle skalaren Datentypen zulässig, die in Azure Synapse Analytics unterstützt werden. Der Datentyp timestamp (rowversion) wird nicht unterstützt.

[ =default ]

Ein Standardwert für den Parameter. Wenn ein default-Wert definiert ist, kann die Funktion ausgeführt werden, ohne dass ein Wert für diesen Parameter angegeben werden muss.

Wenn ein Parameter der Funktion über einen Standardwert verfügt, muss beim Aufrufen der Funktion das DEFAULT-Schlüsselwort angegeben werden, um den Standardwert abzurufen. In diesem Punkt gibt es einen Unterschied zum Verwenden von Parametern in einer gespeicherten Prozedur. Fehlt im Aufruf einer gespeicherten Prozedur ein Parameter, der einen Standardwert hat, wird automatisch dieser Standardwert verwendet.

return_data_type

Der Rückgabewert einer benutzerdefinierten Skalarfunktion. Für Transact-SQL-Funktionen sind alle skalaren Datentypen zulässig, die in Azure Synapse Analytics unterstützt werden. Der Datentyp "Rowversion/timestamp" ist kein unterstützter Typ. Der Cursor und die Tabelle von nicht skalaren Typen sind nicht zulässig.

function_body

Sequenz von Transact-SQL-Anweisungen. Die function_body kann keine SELECT-Anweisung enthalten und kann nicht auf Datenbankdaten verweisen. Der function_body kann nicht auf Tabellen oder Ansichten verweisen. function_body kann andere deterministische Funktionen aufrufen, jedoch keine nicht deterministischen Funktionen aufrufen.

In Skalarfunktionen entspricht function_body einer Reihe von Transact-SQL-Anweisungen, die zusammen einen Skalarwert ergeben.

scalar_expression

Gibt den skalaren Wert an, den die Skalarfunktion zurückgibt.

select_stmt

Ist die einzelne SELECT Anweisung, die den Rückgabewert einer Inlinetabellenwertfunktion definiert. Für eine inlinetabellenwertige Funktion gibt es keinen Funktionstext; die Tabelle ist das Resultset einer einzelnen SELECT Anweisung.

TABLE

Gibt an, dass der Rückgabewert der Tabellenwertfunktion eine Tabelle ist. Nur Konstanten und @local_variables können an Tabellenwertfunktionen übergeben werden.

In inline TVFs (Vorschau) wird der TABLE-Rückgabewert über eine einzelne SELECT Anweisung definiert. Inlinefunktionen haben keine zugeordneten Rückgabevariablen.

<function_option>

Gibt an, dass die Funktion über eine oder mehrere der folgenden Optionen verfügt.

SCHEMABINDING

Gibt an, dass die Funktion an die Datenbankobjekte gebunden ist, auf die sie verweist. Wenn SCHEMABINDING angegeben ist, können an Basisobjekten keine Änderungen vorgenommen werden, die die Funktionsdefinition betreffen können. Zunächst muss die Funktionsdefinition selbst geändert oder gelöscht werden, um Abhängigkeiten in dem zu ändernden Objekt zu entfernen.

Die Bindung der Funktion an die Objekte, auf die sie verweist, wird nur bei einer der folgenden Aktionen entfernt:

  • Die Funktion wird gelöscht.

  • Die Funktion wird mithilfe der ALTER-Anweisung geändert, wobei die Option SCHEMABINDING nicht angegeben ist.

Eine Funktion kann nur dann schemagebunden sein, wenn die folgenden Bedingungen erfüllt sind:

  • Alle benutzerdefinierten Funktionen, auf die die Funktion verweist, sind ebenfalls schemagebunden.

  • Auf die Funktionen und die anderen benutzerdefinierten Funktionen, auf die die Funktion verweist, wird unter Verwendung eines einteiligen oder zweiteiligen Namens verwiesen.

  • Nur auf integrierte Funktionen und andere benutzerdefinierte Funktionen in der gleichen Datenbank kann im Text der benutzerdefinierten Funktionen verwiesen werden.

  • Der Benutzer, der die CREATE FUNCTION Anweisung ausgeführt hat, verfügt über DIE REFERENCES-Berechtigung für die Datenbankobjekte, auf die die Funktion verweist.

Um SCHEMABINDING zu entfernen, verwenden Sie ALTER.

RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT

Gibt das OnNULLCall-Attribut einer Skalarwertfunktion an. Wenn nicht angegeben, CALLED ON NULL INPUT wird standardmäßig impliziert, und der Funktionstext wird auch dann ausgeführt, wenn NULL er als Argument übergeben wird.

Bewährte Methoden

Wenn eine benutzerdefinierte Funktion nicht mit der SCHEMABINDING-Klausel erstellt wurde, können sich die an zugrunde liegenden Objekten vorgenommenen Änderungen auf die Definition der Funktion auswirken und bei Aufruf der Funktion zu unerwarteten Ergebnissen führen. Es wird empfohlen, eine der folgenden Methoden zu implementieren, damit die Funktion aufgrund von Änderungen an den zugrunde liegenden Objekten nicht veraltet ist:

  • Geben Sie beim Erstellen der Funktion die WITH SCHEMABINDING-Klausel an. Hiermit wird sichergestellt, dass die Objekte, auf die in der Funktionsdefinition verwiesen wird, nicht geändert werden können, es sei denn, die Funktion wird auch geändert.

Interoperabilität

Die folgenden Anweisungen sind in Skalarwertfunktionen gültig:

  • Zuweisungsanweisungen

  • Anweisungen zur Ablaufsteuerung, mit Ausnahme von TRY...CATCH-Anweisungen

  • DECLARE-Anweisungen zum Definieren lokaler Datenbankvariablen.

In einer Inline-Tabellenwertfunktion (Vorschau) ist nur eine einzelne SELECT-Anweisung zulässig.

Begrenzungen

Mit benutzerdefinierten Funktionen können keine Aktionen ausgeführt werden, die den Status einer Datenbank ändern.

Benutzerdefinierte Funktionen können geschachtelt werden. Dies bedeutet, dass eine benutzerdefinierte Funktion eine andere aufrufen kann. Die Schachtelungsebene wird um eins erhöht, wenn die aufgerufene Funktion mit der Ausführung beginnt, und wird wieder um eins erniedrigt, wenn die aufgerufene Funktion die Ausführung beendet. Benutzerdefinierte Funktionen unterstützen bis zu 32 geschachtelte Ebenen. Ein Überschreiten der maximalen Schachtelungsebenen verursacht das Fehlschlagen der gesamten Funktionsaufrufskette.

Objekte, einschließlich Funktionen, können nicht in der master-Datenbank Ihres serverlosen SQL-Pools in Azure Synapse Analytics erstellt werden.

Metadaten

In diesem Abschnitt werden die Systemkatalogsichten aufgelistet, die Sie verwenden können, um Metadaten zu benutzerdefinierten Funktionen zurückzugeben.

  • sys.sql_modules zeigt die Definition von benutzerdefinierten Transact-SQL-Funktionen an. Beispiel:

    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: Zeigt Informationen zu den Parametern an, die in benutzerdefinierten Funktionen definiert sind.

  • sys.sql_expression_dependencies: Zeigt die zugrunde liegenden Objekte an, auf die von einer Funktion verwiesen wird.

Berechtigungen

Erfordert die CREATE FUNCTION-Berechtigung in der Datenbank und die ALTER-Berechtigung für das Schema, in dem die Funktion erstellt wird.

Beispiele: Azure Synapse Analytics und Analytics-Plattformsystem (PDW)

A. Verwenden einer benutzerdefinierten Skalarwertfunktion zum Ändern eines Datentyps

Diese einfache Funktion verwendet einen int-Datentyp als Eingabe und gibt einen decimal(10,2) -Datentyp als Ausgabe zurück.

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

Hinweis

Skalarfunktionen sind in den serverlosen SQL-Pools und in Microsoft Fabric nicht verfügbar.

Beispiele: Azure Synapse Analytics

A. Erstellen einer Inline-Tabellenwertfunktion

Im folgenden Beispiel wird eine Inline-Tabellenwertfunktion erstellt, um einige wichtige Informationen zu Modulen zurückzugeben, die nach dem objectType-Parameter gefiltert werden. Sie enthält einen Standardwert, um alle Module zurückzugeben, wenn die Funktion mit dem DEFAULT Parameter aufgerufen wird. In diesem Beispiel werden einige der Systemkatalogansichten verwendet, die in Metadaten erwähnt werden.

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

Anschließend kann die Funktion aufgerufen werden, um folgendermaßen alle Ansichtsobjekte (V) zurückzugeben:

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

Hinweis

Inline-Tabellenwertfunktionen sind in den serverlosen SQL-Pools verfügbar, aber in den dedizierten SQL-Pools in der Vorschau.

B. Kombinieren von Ergebnissen einer Inlinetabellenwertfunktion

In diesem einfachen Beispiel wird die zuvor erstellte Tabellenwertfunktion verwendet, um zu zeigen, wie ihre Ergebnisse mithilfe von CROSS APPLY mit anderen Tabellen kombiniert werden können. Hier wählen wir alle Spalten und sys.objects die Ergebnisse ModulesByType aller Zeilen aus, die in der Typspalte übereinstimmen. Weitere Details zum Verwenden von APPLY finden Sie unter FROM-Klausel plus JOIN, APPLY, PIVOT.

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

Hinweis

Inline-Tabellenwertfunktionen sind in den serverlosen SQL-Pools verfügbar, aber in den dedizierten SQL-Pools in der Vorschau.

Nächster Schritt

Erstellen von benutzerdefinierten Funktionen (Datenbank-Engine)