Übersicht zu gespeicherten Prozeduren

Gespeicherte Prozeduren sind Funktionen ähnlich. 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.

Eine gespeicherte Prozedur gibt einen einzelnen Wert zurück. Obwohl Sie in einer gespeicherten Prozedur SELECT-Anweisungen ausführen können, müssen die Ergebnisse in der gespeicherten Prozedur verwendet oder auf einen einzelnen Wert beschränkt werden, der zurückgegeben wird.

Gespeicherte Snowflake-Prozeduren nutzen JavaScript und in den meisten Fällen SQL:

  • JavaScript liefert die Kontrollstrukturen (Verzweigungen und Schleifen).

  • SQL wird ausgeführt, indem Funktionen in einer JavaScript-API aufgerufen werden.

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.

Gespeicherte Prozeduren werden als unabhängige Anweisungen aufgerufen

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

CALL MyStoredProcedure1(argument_1);             -- Stored Procedure call

SELECT MyFunction1(column_1) FROM table1;        -- Function call

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

Eine gespeicherte Prozedur kann einen Wert zurückgeben, muss jedoch keinen Wert zurückgeben.

Eine Funktion hingegen muss einen Wert zurückgeben.

Von gespeicherten Prozeduren zurückgegebene Werte 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 über eine API auf die Datenbank zugreifen und verschachtelte Abfragen ausgeben

Snowflake bietet eine JavaScript-API (in Form von JavaScript-Objekten und -Methoden). Über die API können gespeicherte Prozeduren Datenbankoperationen wie SELECT, UPDATE und CREATE 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.
  $$;

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;
    

Hinweise zur Migration

Viele Datenbanksysteme implementieren gespeicherte Prozeduren ausschließlich in SQL. Um eine gespeicherte SQL-Prozedur aus einem anderen Datenbanksystem nach Snowflake zu migrieren, betten Sie die SQL in JavaScript ein, wie in den Beispielen unter Verwenden von gespeicherten Prozeduren gezeigt.