Übersicht zu gespeicherten Prozeduren

Wie bei Funktionen wird eine gespeicherte Prozedur einmal erstellt und kann dann mehrmals ausgeführt werden. Eine gespeicherte Prozedur wird mit einem CREATE PROCEDURE-Befehl erstellt und mit einem CALL-Befehl ausgeführt.

Sie können eine gespeicherte Prozedur in einer der folgenden Sprachen schreiben:

Eine gespeicherte Prozedur kann einzelne Werte oder (bei Verwendung von Snowflake Scripting) Tabellendaten zurückgeben.

Bemerkung

Um eine anonyme Prozedur sowohl zu erstellen als auch aufzurufen, verwenden Sie CALL (mit anonymen Prozeduren). Das Erstellen und Aufrufen einer anonymen Prozedur erfordert keine Rolle mit CREATE PROCEDURE-Schemaberechtigungen.

Unter diesem Thema:

Vorteile gespeicherter Prozeduren

Gespeicherte Prozeduren erlauben Folgendes:

  • Verwendung prozeduraler Logik (Verzweigungen und Schleifen), die von reinem SQL nicht unterstützt wird

  • Behandlung von Fehlern

  • Dynamisches Erstellen von SQL-Anweisungen und deren Ausführung

  • Schreiben von Code, der mit den Berechtigungen der Rolle ausgeführt wird, der die Prozedur gehört, und nicht mit den Berechtigungen der Rolle, die die Prozedur ausführt. Auf diese Weise können Eigentümer gespeicherter Prozeduren die Befugnis zum Ausführen bestimmter Operationen an Benutzer delegieren, die diese sonst nicht ausführen könnten. Es gibt bei diesen gespeicherten Prozeduren mit Eigentümerrechten jedoch einige Einschränkungen.

Eine häufige Verwendung für gespeicherte Prozeduren besteht darin, das Automatisieren von Aufgaben zu unterstützen, die mehrere SQL-Anweisungen erfordern und häufig ausgeführt werden. Angenommen, Sie möchten eine Datenbank bereinigen, indem Sie alle Daten löschen, die älter als ein bestimmtes Datum sind. Sie können mehrere DELETE-Anweisungen schreiben, von denen jede Daten aus einer bestimmten Tabelle löscht. Sie können alle diese Anweisungen in eine einzelne gespeicherte Prozedur einfügen und einen Parameter übergeben, der das Datum des Stichtags angibt. Dann können Sie einfach die Prozedur aufrufen, um die Datenbank zu bereinigen. Wenn sich Ihre Datenbank ändert, können Sie die Prozedur aktualisieren, um zusätzliche Tabellen zu bereinigen. Wenn mehrere Benutzer den Bereinigungsbefehl verwenden, können sie die Prozedur aufrufen, anstatt sich jeden Tabellennamen zu merken und jede Tabelle einzeln zu bereinigen.

Unterschiede zwischen gespeicherten Prozeduren und UDFs

Sowohl gespeicherte Prozeduren als auch UDFs (benutzerdefinierte Funktionen) erleichtern das Schreiben von modularem Code. Es gibt jedoch wichtige Unterschiede zwischen UDFs und gespeicherten Prozeduren.

Die Rückgabe eines Werts aus einer gespeicherten Prozedur ist optional

Sowohl Funktionen als auch gespeicherte Prozeduren können einen Wert zurückgeben. Allerdings:

  • Der Zweck einer Funktion ist es, einen Wert zu berechnen und zurückzugeben. Eine Funktion gibt immer explizit einen Wert zurück, indem sie einen Ausdruck angibt (je nach Sprache entweder mit oder ohne Schlüsselwörter wie return). Beispielweise muss der Textteil einer JavaScript-UDF eine return-Anweisung enthalten, die einen Wert zurückgibt.

  • Der Zweck einer gespeicherten Prozedur ist im Allgemeinen die Ausführung von SQL-Anweisungen (z. B. zur Durchführung von Verwaltungsoperationen). Der Textteil einer gespeicherten Prozedur darf, muss aber nicht explizit einen Wert zurückgeben (z. B. einen Fehlerindikator).

    Beachten Sie, dass jede CREATE PROCEDURE-Anweisung eine RETURNS-Klausel enthalten muss, die einen Rückgabetyp definiert, auch wenn die Prozedur nicht explizit etwas zurückgibt.

    Im folgenden Pseudocode ist beispielsweise die RETURNS-Klausel obligatorisch, die RETURN-Anweisung jedoch optional:

    CREATE PROCEDURE f()
    RETURNS INTEGER          -- required
    LANGUAGE SQL
    AS
    $$
        ...
        RETURN error_code;   -- optional
    $$
    ;
    

    Wenn eine Prozedur nicht explizit einen Wert zurückgibt, dann gibt sie implizit NULL zurück.

Gespeicherte Prozeduren werden als unabhängige Anweisungen aufgerufen

Eine Funktion wertet einen Wert aus und kann in Kontexten verwendet werden, in denen ein allgemeiner Ausdruck verwendet werden kann (z. B. SELECT my_function() ...).

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.

Eine gespeicherte Prozedur wird als unabhängige Anweisung. Der folgende Code veranschaulicht den Unterschied zwischen dem Aufrufen einer gespeicherten Prozedur und dem Aufrufen einer Funktion:

CALL MyStoredProcedure_1(argument_1);

SELECT MyFunction_1(column_1) FROM table1;

Rückgabewerte von gespeicherten Prozeduren können nicht direkt in SQL verwendet werden

Der von einer gespeicherten Prozedur zurückgegebene Wert kann im Gegensatz zu dem von einer Funktion zurückgegebenen Wert nicht direkt in SQL verwendet werden.

Obwohl eine gespeicherte Prozedur einen Wert zurückgeben kann, bietet die Syntax des CALL-Befehls keinen Ort zum Speichern des zurückgegebenen Werts und keine Möglichkeit, den Wert zu verarbeiten oder an eine andere Operation zu übergeben. In SQL gibt es keine Möglichkeit, eine Anweisung wie die folgende zu erstellen:

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

Es gibt jedoch indirekte Möglichkeiten für die Verwendung des Rückgabewerts einer gespeicherten Prozedur:

  • Sie können die gespeicherte Prozedur in einer anderen gespeicherten Prozedur aufrufen; das JavaScript in der äußeren gespeicherten Prozedur kann 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.

Eine gespeicherte Prozedur pro CALL-Anweisung

Eine einzelne ausführbare Anweisung kann nur eine gespeicherte Prozedur aufrufen. Im Gegensatz dazu kann eine einzelne SQL-Anweisung mehrere Funktionen aufrufen.

Ebenso kann eine gespeicherte Prozedur im Gegensatz zu einer Funktion 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 hierfür finden Sie im Abschnitt mit Codebeispielen Beispiele.

Weitere Informationen zum Aufrufen einer gespeicherten Prozedur finden Sie unter Aufrufen einer gespeicherten Prozedur.

Gespeicherte Prozeduren können auf Datenbanken zugreifen und verschachtelte Abfragen erstellen

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

  • Bei einer in JavaScript geschriebenen gespeicherten Prozedur können Sie diese Operationen mithilfe der JavaScript-API (in Form von JavaScript-Objekten und -Methoden) 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 JavaScript-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.
      $$;
    
  • 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;
    

Anders als gespeicherte Prozeduren haben UDFs (einschließlich JavaScript-UDFs) keinen Zugriff auf eine API, die Datenbankoperationen ausführen kann.

Entscheidung über das Erstellen einer gespeicherten Prozedur oder einer UDF

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

Erstellen Sie eine gespeicherte Prozedur, wenn:
  • Sie eine vorhandene gespeicherte Prozedur aus einer anderen Anwendung/einem anderen System migrieren.

  • Sie Datenbankoperationen ausführen müssen:

    • Typische Abfragen und DML, z. B. SELECT, UPDATE usw.

    • 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.

Erstellen Sie eine UDF, wenn:
  • Sie eine vorhandene UDF aus einer anderen Anwendung/einem anderen System migrieren.

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

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

    select MyFunction(col1) from table1;
    
    select MyAggregateFunction(col1) from table1 group by col2;
    
Zurück zum Anfang