CREATE FUNCTION (Azure Synapse Analytics und Microsoft Fabric)

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

Erstellt eine benutzerdefinierte Funktion 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, wie 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

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

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 bei der Übergabe von Parametern in einer gespeicherten Prozedur oder in einer benutzerdefinierten Funktion oder beim Deklarieren und Festlegen von Variablen in einer Batchanweisung nicht berücksichtigt. 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 timestamp (rowversion) wird nicht unterstützt. Der Cursor und die Tabelle von nicht skalaren Typen sind nicht zulässig.

function_body
Sequenz von Transact-SQL-Anweisungen. function_body kann keine SELECT-Anweisung enthalten und kann nicht auf Datenbankdaten verweisen. function_body kann nicht auf Tabellen oder Sichten 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 Inline-Tabellenwertfunktion definiert. Bei einer Inlinefunktion mit Tabellenrückgabe gibt es keinen Funktionshauptteil; 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-Tabellenwertfunktionen (Vorschau) wird der TABLE-Rückgabewert durch eine einzige SELECT-Anweisung definiert. Inlinefunktionen haben keine zugeordneten Rückgabevariablen.

<function_option>::=

Gibt an, dass die Funktion mindestens über eine der folgenden Optionen verfügen wird.

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, besitzt REFERENCES-Berechtigungen für die Datenbankobjekte, auf die die Funktion verweist.

Zum Entfernen von SCHEMABINDING verwenden Sie ALTER.

RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT
Gibt das OnNULLCall-Attribut einer Skalarwertfunktion an. Wenn das Attribut nicht angegeben ist, wird standardmäßig CALLED ON NULL INPUT verwendet. Dies bedeutet, dass der Hauptteil der Funktion ausgeführt wird, selbst wenn NULL als ein 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.

Einschränkungen

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

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, durch den sie alle Module zurückgibt, 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 der Ergebnisse einer Inline-Tabellenwertfunktion

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 aus beiden sys.objects und die Ergebnisse von ModulesByType für alle Zeilen aus, deren type-Spalte übereinstimmt. 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ächste Schritte