Entscheidung über das Schreiben einer gespeicherten Prozedur oder einer benutzerdefinierten Funktion

Unter diesem Thema werden die wichtigsten Unterschiede zwischen einer gespeicherten Prozedur und einer UDF beschrieben, einschließlich der Unterschiede in der Art und Weise, wie beide aufgerufen werden und welche Aufgaben sie übernehmen können.

Gespeicherte Prozeduren und UDFs unterscheiden sich zunächst einmal durch ihre typische Verwendung, wie im Folgenden beschrieben.

Zweck von gespeicherten Prozeduren

Zweck von benutzerdefinierten Funktionen

Generell zur Ausführung von administrativen Operationen durch Ausführen von SQL-Anweisungen. Der Textteil/Body einer gespeicherten Prozedur darf, muss aber nicht explizit einen Wert zurückgeben (z. B. einen Fehlerindikator).

Berechnet einen Wert, und gibt diesen zurück. Eine Funktion gibt immer explizit einen Wert zurück, indem sie einen Ausdruck angibt. Beispielweise muss der Textteil einer JavaScript-UDF eine return-Anweisung enthalten, die einen Wert zurückgibt.

Unter diesem Thema:

Kriterien für Verwendung von gespeicherter Prozedur vs. UDF

Bei der Entscheidung, ob eine gespeicherte Prozedur oder eine UDF verwendet werden soll, gilt im Allgemeinen Folgendes:

Verwenden einer gespeicherten Prozedur

Verwenden einer UDF

  • Sie migrieren eine vorhandene gespeicherte Prozedur aus einer anderen Anwendung/einem anderen System.

  • Sie müssen DDL- oder DML-Datenbankoperationen ausführen:

    • Administrative Aufgaben, einschließlich DDL wie Löschen temporärer Tabellen, Löschen von Daten, die älter als N Tage sind, oder Hinzufügen von Benutzern.

    • DML-Anweisungen (z. B.UPDATE-Anweisungen)

  • Sie migrieren eine vorhandene UDF aus einer anderen Anwendung/einem anderen System.

  • Sie benötigen eine Funktion, die als Teil einer SQL-Anweisung aufgerufen werden kann und einen Wert zurückgeben muss, der in der Anweisung weiterverwendet wird.

  • Ihre Ausgabe muss einen Wert für jede Eingabezeile oder jede Gruppe enthalten. Beispiel:

    SELECT MyFunction(col1) FROM table1;
    
    Copy
  • Sie müssen einfache Abfragen mit SQL, wie z. B. SELECT-Anweisungen, durchführen.

Unterstützte Handler-Sprachen

Wenn Sie eine Prozedur oder UDF schreiben, schreiben Sie deren Logik als Handler in einer der unterstützten Sprachen. In der folgenden Tabelle sind die unterstützten Sprachen aufgeführt.

Gespeicherte Prozeduren

Benutzerdefinierte Funktionen

Java

Java

JavaScript

JavaScript

Python

Python

Scala

Scala

Snowflake Scripting

SQL

Unterschiede in Nutzung und Verhalten

In den folgenden Abschnitten werden die spezifischen Unterschiede in den von Prozeduren und UDFs unterstützten Verhaltensweisen beschrieben.

UDFs geben einen Wert zurück; gespeicherte Prozeduren können, müssen aber nicht

  • Eine UDF gibt immer explizit einen Wert zurück, indem sie einen Ausdruck angibt. Der Zweck einer UDF darin besteht, einen Wert zu berechnen und zurückzugeben. Beispielweise muss der Textteil einer JavaScript-UDF eine return-Anweisung enthalten, die einen Wert zurückgibt.

  • Eine gespeicherte Prozedur darf, muss aber nicht explizit einen Wert zurückgeben (z. B. einen Fehlerindikator). Der Zweck einer gespeicherten Prozedur im Allgemeinen darin besteht, administrative Operationen durch Ausführung von SQL-Anweisungen durchzuführen. Wenn eine Prozedur nicht explizit einen Wert zurückgibt, dann gibt sie implizit NULL zurück.

    Beachten Sie, dass jede CREATE PROCEDURE-Anweisung eine RETURNS-Klausel enthalten muss, die einen Rückgabetyp angibt, auch wenn die Prozedur nicht explizit etwas zurückgibt. Wenn eine Prozedur nicht explizit einen Wert zurückgibt, dann gibt sie implizit NULL zurück.

    Der Code im folgenden Beispiel deklariert einen Rückgabetyp für die Prozedur mit einer RETURNS-Klausel, aber ein Wert wird nur im Fehlerfall zurückgegeben. Mit anderen Worten: Nicht jeder Codepfad gibt einen Wert zurück.

    CREATE OR REPLACE PROCEDURE do_stuff(input NUMBER)
    RETURNS VARCHAR
    LANGUAGE SQL
    AS
    $$
    DECLARE
      ERROR VARCHAR DEFAULT 'Bad input. Number must be less than 10.';
    
    BEGIN
      IF (input > 10) THEN
        RETURN ERROR;
      END IF;
    
      -- Perform an operation that doesn't return a value.
    
    END;
    $$
    ;
    
    Copy

UDF-Rückgabewerte können direkt in SQL verwendet werden; bei Rückgabewerten gespeicherter Prozeduren ist dies nicht möglich

Wenn Sie die gespeicherte Prozedur nicht über einen Snowflake Scripting-Block aufrufen, können Sie den von einer gespeicherten Prozedur zurückgegebenen Wert nicht direkt in SQL verwenden (im Gegensatz zu dem von einer Funktion zurückgegebenen Wert). Die Syntax des CALL-Befehls bietet keinen Ort zum Speichern des zurückgegebenen Werts und keine Möglichkeit, den Wert zu verarbeiten oder an eine andere Operation zu übergeben. Das bedeutet also, dass die folgende Anweisung keine gültige SQL-Anweisung ist:

y = stored_procedure1(x);                         -- Not allowed.
Copy

Wenn Sie eine gespeicherte Prozedur innerhalb eines Snowflake Scripting-Blocks aufrufen, können Sie zum Erfassen des von der gespeicherten Prozedur zurückgegebenen Werts eine Snowflake Scripting-Variable verwenden.

Sie können den Rückgabewert einer gespeicherten Prozedur auch indirekt verwenden (außerhalb eines Snowflake Scripting-Blocks), wie in der folgenden Liste beschrieben:

  • Sie können die gespeicherte Prozedur innerhalb einer anderen gespeicherten Prozedur aufrufen. Wenn z. B. Handler mit der gespeicherten Prozedur in JavaScript geschrieben ist, kann JavaScript in der äußeren gespeicherten Prozedur die Ausgabe der inneren gespeicherten Prozedur abrufen und speichern. Denken Sie jedoch daran, dass die äußere gespeicherte Prozedur (und jede innere gespeicherte Prozedur) weiter nur maximal einen Wert an den Aufrufer zurückgeben kann.

  • Sie können erst die gespeicherte Prozedur aufrufen und dann die Funktion RESULT_SCAN aufrufen und die für die gespeicherte Prozedur generierte Anweisungs-ID übergeben.

  • Sie können ein Resultset in einer temporären oder permanenten Tabelle speichern und diese Tabelle verwenden, nachdem Sie vom Aufruf der gespeicherten Prozedur zurückgekehrt sind.

  • Wenn das Datenvolumen nicht zu groß ist, können Sie mehrere Zeilen und mehrere Spalten in einem VARIANT-Wert speichern (z. B. als JSON-Wert) und diesen VARIANT-Wert zurückgeben.

UDFs können im Kontext einer anderen Anweisung aufgerufen werden; gespeicherte Prozeduren werden unabhängig voneinander aufgerufen

  • Eine UDF ergibt einen Wert und kann in Kontexten verwendet werden, in denen ein allgemeiner Ausdruck verwendet werden kann, wie z. B. die folgenden:

    SELECT MyFunction_1(column_1) FROM table1;
    
    Copy
  • Eine gespeicherte Prozedur wird nicht als Wert ausgewertet und kann nicht in allen Kontexten verwendet werden, in denen ein allgemeiner Ausdruck verwendet werden kann. So ist beispielsweise SELECT my_stored_procedure()... nicht möglich.

    Sie rufen eine gespeicherte Prozedur als unabhängige Anweisung auf, wie in dem folgenden Beispiel:

    CALL MyStoredProcedure_1(argument_1);
    
    Copy

Weitere Informationen zum Aufrufen von Funktionen und Prozeduren finden Sie unter:

Mehrere UDFs können mit einer einzigen Anweisung aufgerufen werden; eine einzelne gespeicherte Prozedur wird mit genau einer Anweisung aufgerufen

  • Eine einzelne SQL-Anweisung kann mehrere UDFs aufrufen.

  • Eine einzelne SQL-Anweisung kann nur eine gespeicherte Prozedur aufrufen.

    Ebenso kann eine gespeicherte Prozedur im Gegensatz zu einer UDF nicht als Teil eines Ausdrucks aufgerufen werden. Innerhalb einer gespeicherten Prozedur kann die gespeicherte Prozedur jedoch eine andere gespeicherte Prozedur aufrufen oder sich selbst rekursiv aufrufen. Ein Beispiel finden Sie im Abschnitt Codebeispiele Beispiele.

Weitere Informationen zum Aufrufen von Funktionen und Prozeduren finden Sie unter:

UDFs können nur mit einfachen Abfragen auf die Datenbank zugreifen; gespeicherte Prozeduren können DDL- und DML-Anweisungen ausführen

  • In einer UDF können Sie mit SQL nur Abfragen ausführen (keine DML- oder DDL- Anweisungen).

  • Innerhalb einer gespeicherten Prozedur können Sie Datenbankoperationen wie SELECT, UPDATE und CREATE ausführen:

    • In einer gespeicherten JavaScript-Prozedur können Sie zum Beispiel die JavaScript-API verwenden, um diese Operationen auszuführen.

      Das folgende Beispiel zeigt, wie eine gespeicherte Prozedur eine SQL-Anweisung erstellen und ausführen kann, die eine andere gespeicherte Prozedur aufruft: Dabei gibt $$ Anfang und Ende des JavaScript-Handler-Codes in der gespeicherten Prozedur an.

      CREATE PROCEDURE ...
        $$
        // Create a Statement object that can call a stored procedure named
        // MY_PROCEDURE().
        var stmt1 = snowflake.createStatement( { sqlText: "call MY_PROCEDURE(22)" } );
        // Execute the SQL command; in other words, call MY_PROCEDURE(22).
        stmt1.execute();
        // Create a Statement object that executes a SQL command that includes
        // a call to a UDF.
        var stmt2 = snowflake.createStatement( { sqlText: "select MY_UDF(column1) from table1" } );
        // Execute the SQL statement and store the output (the "result set") in
        // a variable named "rs", which we can access later.
        var rs = stmt2.execute();
        // etc.
        $$;
      
      Copy
    • In einer mit Snowflake Scripting geschriebenen gespeicherten Prozedur können Sie SQL-Anweisungen ausführen.

      Das folgende Beispiel zeigt, wie eine gespeicherte Prozedur eine SQL-Anweisung erstellen und ausführen kann, die eine andere gespeicherte Prozedur aufruft: Dabei gibt $$ Anfang und Ende des Snowflake Scripting-Codes in der gespeicherten Prozedur an.

      CREATE PROCEDURE ...
        -- Call a stored procedure named my_procedure().
        CALL my_procedure(22);
        -- Execute a SQL statement that includes a call to a UDF.
        SELECT my_udf(column1) FROM table1;
      
      Copy