Verwenden von gespeicherten Prozeduren

Gespeicherte Prozeduren dienen der Erstellung modularen Codes zur Abbildung komplexer Geschäftslogik durch Kombination mehrerer SQL-Anweisungen mit prozeduraler Logik.

Unter diesem Thema:

Erstellen einer gespeicherten Prozedur

DDL für gespeicherte Prozeduren

Gespeicherte Prozeduren sind First-Class-Datenbankobjekte. Die folgenden DDL-Befehle gelten für gespeicherte Prozeduren:

Darüber hinaus bietet Snowflake folgenden Befehl zum Ausführen gespeicherter Prozeduren:

Implementierung und API

In Snowflake gespeicherte Prozeduren werden in JavaScript geschrieben, das durch Aufruf der JavaScript-API SQL-Anweisungen ausführen kann. Diese API ist ähnlich, aber nicht identisch mit den APIs in Snowflake-Konnektoren und -Treibern (Node.js, JDBC, Python usw.).

Mithilfe der API können Sie folgende Operationen ausführen:

  • Ausführen einer SQL-Anweisung

  • Abrufen der Ergebnisse einer Abfrage (d. h. eines Resultset)

  • Abrufen von Metadaten zum Resultset (Anzahl der Spalten, Datentypen der Spalten usw.)

Diese Operationen lassen sich ausführen, indem Methoden für folgende Objekte aufgerufen werden:

  • snowflake – Verfügt über Methoden zum Erstellen eines Statement-Objekts und zum Ausführen eines SQL-Befehls.

  • Statement – Hilft dabei, vorbereitete Anweisungen auszuführen und Metadaten für die vorbereiteten Anweisungen aufzurufen. Außerdem kann ein ResultSet-Objekt zurückerhalten werden.

  • ResultSet – Enthält die Ergebnisse einer Abfrage (z. B. die Datenzeilen, die für eine SELECT-Anweisung abgerufen wurden).

  • SfDate – Ist eine Erweiterung von JavaScript-Date (mit zusätzlichen Methoden) und dient als Rückgabetyp für die Snowflake SQL-Datentypen TIMESTAMP_LTZ, TIMESTAMP_NTZ und TIMESTAMP_TZ.

Diese Objekte sind unter API für gespeicherte Prozeduren ausführlich beschrieben.

Eine typische gespeicherte Prozedur enthält Code, der dem folgenden Pseudocode ähnelt:

var my_sql_command1 = "delete from history_table where event_year < 2016";
var statement1 = snowflake.createStatement(my_sql_command1);
statement1.execute();

var my_sql_command2 = "delete from log_table where event_year < 2016";
var statement2 = snowflake.createStatement(my_sql_command2);
statement2.execute();

In diesem Code wird ein Objekt mit dem Namen snowflake verwendet. Dabei handelt es sich um ein spezielles Objekt, das ohne Deklaration vorhanden ist. Das Objekt wird im Kontext jeder gespeicherten Prozedur bereitgestellt und macht die API verfügbar, sodass Sie mit dem Server interagieren können.

Die anderen Variablen (z. B. statement1) werden mit JavaScript var-Anweisungen erstellt. Beispiel:

var statement1 = ...;

Wie im obigen Codebeispiel gezeigt, können Sie mit dem snowflake-Objekt ein Statement-Objekt erstellen, indem Sie über die API eine der Methoden aufrufen.

Im folgenden Beispiel wird ein ResultSet abgerufen und durchlaufen:

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

Der Abschnitt Beispiele (am Ende unter diesem Thema) enthält zusätzliche Beispiele, in denen die einzelnen Objekte und viele der Methoden der JavaScript-API für gespeicherte Prozedur ausgeführt werden.

Zuordnen von SQL- und JavaScript-Datentypen

Beim Aufrufen, Verwenden und Empfangen von Werten aus gespeicherten Prozeduren müssen Sie häufig Snowflake-SQL-Datentypen in JavaScript-Datentypen konvertieren und umgekehrt.

Eine Konvertierung von SQL in JavaScript kann in folgenden Fällen erfolgen:

  • Aufrufen einer gespeicherten Prozedur mit einem Argument. Das Argument ist ein SQL-Datentyp. Wenn das Argument innerhalb der gespeicherten Prozedur in einer JavaScript-Variablen gespeichert ist, muss es konvertiert werden.

  • Beim Abrufen eines Werts aus einem ResultSet-Objekt in eine JavaScript-Variable. Das ResultSet enthält den Wert als SQL-Datentyp, und die JavaScript-Variable muss den Wert als einen der JavaScript-Datentypen speichern.

Eine Konvertierung von JavaScript in SQL kann in folgenden Fällen erfolgen:

  • Zurückgeben eines Werts aus der gespeicherten Prozedur. Die return-Anweisung enthält normalerweise eine JavaScript-Variable, die in einen SQL-Datentyp konvertiert werden muss.

  • Beim dynamischen Erstellen einer SQL-Anweisung, die einen Wert in einer JavaScript-Variable verwendet.

  • Beim Binden des Werts einer JavaScript-Variablen an eine vorbereitet Anweisung.

In den folgenden Abschnitten wird erläutert, wie sich Daten von SQL nach JavaScript oder von JavaScript nach SQL konvertieren lassen.

Konvertieren von SQL nach JavaScript

Die folgende Tabelle enthält die Snowflake SQL-Datentypen sowie die entsprechenden JavaScript-Datentypen:

SQL-Datentyp

JavaScript-Datentyp

Anmerkungen

ARRAY

JSON

BOOLEAN

boolean

DATE

date

REAL, FLOAT, FLOAT8, FLOAT4, DOUBLE, DOUBLE PRECISION

number

TIME

string

TIMESTAMP, TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ

date oder SfDate

Wenn ein Zeitstempel als Argument an eine gespeicherte Prozedur übergeben wird, wird dieser Zeitstempel in ein JavaScript-date-Objekt konvertiert. In anderen Fällen (z. B. beim Abrufen von ResultSet) wird ein Zeitstempel in ein SfDate-Objekt konvertiert. Weitere Informationen zum Datentyp SfDate, der kein standardmäßiger JavaScript-Datentyp ist, finden Sie unter API für gespeicherte Prozeduren.

VARCHAR, CHAR, CHARACTER, STRING, TEXT

string

VARIANT

JSON

Wenn Ihre SQL-Anweisung beispielsweise eine VARIANT-Spalte aus einer Snowflake-Tabelle auswählt, muss die JavaScript-Variable vom Typ JSON sein, wenn Sie den Wert von ResultSet in eine JavaScript-Variable kopieren.

Nicht alle Snowflake-SQL-Datentypen haben einen entsprechenden JavaScript-Datentyp. Beispielsweise unterstützt JavaScript die Datentypen INTEGER oder NUMBER nicht direkt. In diesen Fällen sollten Sie den SQL-Datentyp in einen geeigneten alternativen Datentyp konvertieren. Beispielsweise können Sie einen SQL INTEGER- in einen SQL FLOAT-Wert konvertieren, der sich dann in einen JavaScript-Wert vom Datentyp number konvertieren lässt.

In der folgenden Tabelle werden entsprechende Konvertierungen für die inkompatiblen SQL-Datentypen angezeigt:

Inkompatibler SQL-Datentyp

Kompatibler SQL-Datentyp

INTEGER

FLOAT

NUMBER, NUMERIC, DECIMAL

FLOAT

BINARY

Uint8Array

OBJECT

Uint8Array

Konvertieren von JavaScript nach SQL

Bei der Rückgabe von Werten

Der Rückgabetyp einer gespeicherten Prozedur wird in der Definition der gespeicherten Prozedur deklariert. Wenn die return-Anweisung in JavaScript einen Datentyp zurückgibt, der sich vom deklarierten Rückgabetyp der gespeicherten Prozedur unterscheidet, wird der JavaScript-Wert nach Möglichkeit in den Datentyp SQL umgewandelt. Wenn beispielsweise eine Zahl zurückgegeben wird, in der gespeicherten Prozedur jedoch eine Zeichenfolge als Rückgabewert deklariert ist, wird die Zahl innerhalb von JavaScript in eine Zeichenfolge konvertiert und dann in die in der SQL-Anweisung zurückgegebene Zeichenfolge kopiert. (Beachten Sie, dass bestimmte JavaScript-Programmierfehler, z. B. das Zurückgeben des falschen Typs, durch dieses Verhalten verborgen werden können.)

Wenn keine gültige Umwandlung für die Konvertierung vorhanden ist, tritt ein Fehler auf.

Beim Binden von Werten

Wenn Sie JavaScript-Variablen an SQL-Anweisungen binden, führt Snowflake eine Konvertierung von JavaScript-Datentypen in SQL-Datentypen durch. Sie können Variablen der folgenden JavaScript-Datentypen binden:

  • Zahl

  • Zeichenfolge

  • SfDate. (Weitere Details zum Datentyp SfDate, der kein standardmäßiger JavaScript-Datentyp ist, finden Sie unter API für gespeicherte Prozeduren.)

Weitere Informationen zum Binden, einschließlich einiger Beispiele, finden Sie unter Binden von Variablen.

Zusätzliche Informationen zur Konvertierung von Datentypen

Möglicherweise sind auch folgende Themen hilfreich:

Diese Informationen zu Datentypen, der Großschreibung von Argumentnamen und NULL-Werten gelten sowohl für gespeicherte Prozeduren als auch für benutzerdefinierte Funktionen (UDFs).

Informationen zu Snowflake-SQL-Datentypen finden Sie unter Zusammenfassung der Datentypen.

Namenskonventionen für gespeicherte Prozeduren

Gespeicherte Prozeduren sind Datenbankobjekte, d. h. sie werden in einer festgelegten Datenbank und einem festgelegten Schema erstellt. Sie verfügen über einen vollqualifizierten Namen, der durch ihren Namespace in Form von Datenbank.Schema.Prozedurname definiert wird. Beispiel:

CALL temporary_db_qualified_names_test.temporary_schema_1.stproc_pi();

Beim Aufrufen ohne den vollqualifizierten Namen werden gespeicherte Prozeduren für die aktuell in der Sitzung verwendete Datenbank und das aktuell verwendete Schema aufgelöst.

Überladen von Namen

Snowflake unterstützt das Überladen von Namen für gespeicherte Prozeduren. Verschiedene in demselben Schema gespeicherte Prozeduren können den gleichen Namen haben, sofern sich ihre Signaturen unterscheiden, entweder durch die Anzahl der Argumente oder die Argumenttypen. Wenn eine überladene gespeicherte Prozedur aufgerufen wird, überprüft Snowflake die Argumente und ruft die korrekt gespeicherte Prozedur auf.

Ein Beispiel finden Sie unter Überladen der Namen gespeicherter Prozeduren.

Seien Sie vorsichtig, wenn Sie die Überladung verwenden. Durch die Kombination aus automatischer Typkonvertierung und Überladung können kleine Benutzerfehler schnell zu unerwarteten Ergebnissen führen. Ein Beispiel finden Sie unter Überladen der Namen gespeicherter Prozeduren.

Mögliche Konflikte mit systemdefinierten Funktionen und benutzerdefinierten Funktionen

Gespeicherte Prozeduren und benutzerdefinierte Funktionen können denselben Namen tragen, wenn sie unterschiedliche Zahlen oder Datentypen von Argumenten aufweisen.

Allerdings erlaubt Snowflake keine Erstellung von gespeicherten Prozeduren, die den gleichen Namen tragen wie eine systemdefinierte Funktion.

Transaktionsmanagement

Eine gespeicherte Prozedur kann komplett innerhalb einer Transaktion oder außerhalb einer Transaktion aufgerufen werden. Transaktionssteuerbefehle (BEGIN, COMMIT, ROLLBACK) sind in einer gespeicherten Prozedur jedoch nicht zulässig.

Der folgende Pseudocode zeigt eine gespeicherte Prozedur, die vollständig innerhalb einer Transaktion aufgerufen wird:

BEGIN;
W;
CALL MyProcedure();   -- executes X and Y
Z;
COMMIT;

Wenn eine gespeicherte Prozedur außerhalb einer expliziten Transaktion aufgerufen wird, wird jede Anweisung in der gespeicherten Prozedur als separate Transaktion ausgeführt.

Denken Sie daran, dass DDL-Anweisungen (CREATE TABLE usw.) einen impliziten COMMIT-Vorgang verursachen. Solche Anweisungen sollten nicht in einer gespeicherten Prozedur verwendet werden, wenn die Prozedur innerhalb einer Transaktion aufgerufen wird. Der folgende Pseudocode zeigt beispielsweise, was nicht getan werden sollte:

CREATE PROCEDURE do_not_call_inside_transaction...
AS
$$
    ...
    var stmt = snowflake.createStatement(
        {sqlText: "CREATE TABLE not_a_good_idea_here..."}
        );
    var rs = stmt.execute();
    ...
$$;

BEGIN;
...
CALL do_not_call_inside_transaction();   -- Tries to do an implicit commit due to DDL.
...
COMMIT;

Das Ändern der AUTOCOMMIT-Einstellung innerhalb einer gespeicherten Prozedur ist nicht zulässig.

Allgemeine Hinweise

Symmetrischer Code

Wenn Sie mit Programmierung in Assemblersprache vertraut sind, ist die folgende Analogie möglicherweise hilfreich. In Assemblersprache erstellen und heben Funktionen ihre Umgebungen häufig symmetrisch auf. Beispiel:

-- Set up.
push a;
push b;
...
-- Clean up in the reverse order that you set up.
pop b;
pop a;

Möglicherweise möchten Sie folgenden Ansatz für Ihre gespeicherten Prozeduren verwenden:

  • Wenn eine gespeicherte Prozedur temporäre Änderungen an Ihrer Sitzung vornimmt, sollten diese Änderungen rückgängig gemacht werden, bevor die Rückgabe erfolgt.

  • Wenn eine gespeicherte Prozedur eine Ausnahmebehandlung oder Verzweigung oder eine andere Logik nutzt, die sich darauf auswirkt, welche Anweisungen betroffen sind, müssen Sie unabhängig davon, welche Verzweigungen Sie bei einem bestimmten Aufruf verwenden, alles bereinigen, was Sie erstellt haben.

Ihr Code könnte dann beispielsweise wie der folgende Pseudocode aussehen:

create procedure f() ...
    $$
    set x;
    set y;
    try  {
       set z;
       -- Do something interesting...
       ...
       unset z;
       }
    catch  {
       -- Give error message...
       ...
       unset z;
       }
    unset y;
    unset x;
    $$
    ;

Zeilenfortsetzung

SQL-Anweisungen können sehr lang sein, und es ist nicht immer praktisch, sie in eine einzelne Zeile einzufügen. JavaScript behandelt einen Zeilenumbruch als das Ende einer Anweisung. Wenn Sie eine lange SQL-Anweisung auf mehrere Zeilen aufteilen möchten, können Sie die üblichen JavaScript-Verfahren für den Umgang mit langen Zeichenfolgen verwenden, darunter:

  • Setzen Sie unmittelbar vor dem Zeilenende ein Backslash (Zeilenfortsetzungszeichen). Beispiel:

    var sql_command = "SELECT * \
                           FROM table1;";
    
  • Verwenden Sie um die Zeichenfolge herum Backticks (einfache Backquotes) anstelle von doppelten Anführungszeichen. Beispiel:

    var sql_command = `SELECT *
                           FROM table1;`;
    
  • Akkumulieren Sie die Zeichenfolge. Beispiel:

    var sql_command = "SELECT col1, col2"
    sql_command += "     FROM table1"
    sql_command += "     WHERE col1 >= 100"
    sql_command += "     ORDER BY col2;"
    

Aufrufen einer gespeicherten Prozedur

Verwenden Sie zum Ausführen einer gespeicherten Prozedur eine CALL-Anweisung. Beispiel:

call stproc1(5.14::FLOAT);

Jedes Argument für eine gespeicherte Prozedur kann ein allgemeiner Ausdruck sein:

CALL stproc1(2 * 5.14::FLOAT);

Ein Argument kann eine Unterabfrage sein:

CALL stproc1(SELECT COUNT(*) FROM stproc_test_table1);

Sie können nur eine gespeicherte Prozedur pro CALL-Anweisung aufrufen. Zum Beispiel schlägt die folgende Aussagen fehl:

call proc1(1), proc2(2);                          -- Not allowed

Außerdem können Sie den CALL einer gespeicherten Prozedur nicht als Teil eines Ausdrucks verwenden. Beispielsweise schlagen alle folgenden Anweisungen fehl:

call proc1(1) + proc1(2);                         -- Not allowed
call proc1(1) + 1;                                -- Not allowed
call proc1(proc2(x));                             -- Not allowed
select * from (call proc1(1));                    -- Not allowed

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 Beispiele (unter diesem Thema).

Vorsicht

Verschachtelte Aufrufe können die maximal zulässige Stapeltiefe überschreiten. Seien Sie daher vorsichtig, wenn Sie Aufrufe verschachteln, insbesondere bei Gebrauch der Rekursion.

Berechtigungen

Gespeicherte Prozeduren nutzen zwei Arten von Berechtigungen:

  • Berechtigungen direkt für die gespeicherte Prozedur selbst.

  • Berechtigungen für die Datenbankobjekte (z. B. Tabellen), auf die die gespeicherte Prozedur zugreift.

Berechtigungen für gespeicherte Prozeduren

Ähnlich wie bei anderen Datenbankobjekten (Tabellen, Ansichten, UDFs usw.) ist eine Rolle der Eigentümer einer gespeicherten Prozedur; dabei verfügt eine gespeicherte Prozedur über eine oder mehrere Berechtigungen, die anderen Rollen erteilt werden können.

Derzeit sind für gespeicherte Prozeduren die folgenden Berechtigungen gültig:

  • USAGE

  • OWNERSHIP

Damit eine Rolle eine gespeicherte Prozedur verwenden kann, muss die Rolle entweder Eigentümer sein oder über die Berechtigung USAGE für die gespeicherte Prozedur verfügen.

Berechtigungen für Datenbankobjekte, auf die gespeicherte Prozeduren zugreifen

Dieses Thema wird unter Erläuterungen zu gespeicherten Prozeduren mit Aufruferrechten und Eigentümerrechten behandelt.

Hinweise zu gespeicherten Prozeduren

JavaScript-Nummernbereich

Der Bereich für Zahlen mit intakter Genauigkeit reicht von

-(2^53 -1)

bis

(2^53 -1)

Der Bereich der gültigen Werte in den Snowflake-Datentypen NUMBER(p, s) und DOUBLE ist größer. Wenn Sie einen Wert aus Snowflake abrufen und in einer numerischen JavaScript-Variablen speichern, kann dies zu Genauigkeitsverlusten führen. Beispiel:

CREATE OR REPLACE FUNCTION num_test(a double)
  RETURNS string
  LANGUAGE JAVASCRIPT
AS
$$
  return A;
$$
;
select hash(1) AS a, 
       num_test(hash(1)) AS b, 
       a - b;
+----------------------+----------------------+------------+
|                    A | B                    |      A - B |
|----------------------+----------------------+------------|
| -4730168494964875235 | -4730168494964875000 | -235.00000 |
+----------------------+----------------------+------------+

Die ersten beiden Spalten sollten übereinstimmen und die dritte sollte 0,0 enthalten.

Das Problem betrifft benutzerdefinierte JavaScript-Funktionen (UDFs) und gespeicherte Prozeduren.

Wenn das Problem in gespeicherten Prozeduren bei Verwendung von getColumnValue() auftritt, können Sie das Problem möglicherweise vermeiden, indem Sie einen Wert als Zeichenfolge abrufen, z. B. mit:

getColumnValueAsString()

Sie können dann die Zeichenfolge aus der gespeicherten Prozedur zurückgeben und die Zeichenfolge in einen numerischen Datentyp in SQL umwandeln.

Fehlerbehandlung in JavaScript

Da eine gespeicherte Prozedur in JavaScript verfasst wird, kann sie die Try/Catch-Syntax von JavaScript verwenden.

Die gespeicherte Prozedur kann eine vordefinierte Ausnahme oder eine benutzerdefinierte Ausnahme auslösen. Ein einfaches Beispiel für das Auslösen einer benutzerdefinierten Ausnahme finden Sie hier.

Einschränkungen bei gespeicherten Prozeduren

Gespeicherte Prozeduren unterliegen den folgenden Einschränkungen:

  • Derzeit sind Transaktionssteuerungsbefehle (BEGIN, COMMIT, ROLLBACK) in einer gespeicherten Prozedur nicht zulässig. Das bedeutet, dass eine gespeicherte Prozedur vollständig innerhalb einer einzelnen Transaktion ausgeführt wird (explizit oder implizit).

  • Der JavaScript-Code kann die JavaScript-Funktion eval() nicht aufrufen.

  • Die JavaScript-Befehle einer gespeicherten Prozedur können keine Bibliotheken anderer Anbieter importieren. Durch das Zulassen von Bibliotheken, die von Drittanbietern stammen, können Sicherheitslücken entstehen.

  • Obwohl gespeicherte Prozeduren Verschachtelung und Rekursion ermöglichen, beträgt die maximale Stapeltiefe derzeit ungefähr 8 und kann geringer ausfallen, wenn einzelne gespeicherte Prozeduren in der Aufrufkette große Mengen an Ressourcen verbrauchen.

  • In seltenen Fällen kann es zu einem Deadlock kommen, wenn zu viele gespeicherte Prozeduren gleichzeitig aufgerufen werden.

Groß-/Kleinschreibung in JavaScript-Argumenten

Bei Argumentnamen wird im SQL-Abschnitt des Codes einer gespeicherten Prozedur nicht zwischen Groß-/Kleinschreibung unterschieden, im JavaScript-Abschnitt jedoch schon.

Bei gespeicherten Prozeduren (und UDFs), die JavaScript verwenden, werden Bezeichner (z. B. Argumentnamen) im SQL-Abschnitt der Anweisung automatisch in Großbuchstaben konvertiert (sofern Sie den Bezeichner nicht in doppelte Anführungszeichen setzen), während Argumentnamen im JavaScript-Abschnitt in der ursprünglichen Schreibweise verbleiben. Das kann dazu führen, dass Ihre gespeicherte Prozedur fehlschlägt, ohne eine explizite Fehlermeldung zurückzugeben, da die Argumente nicht einsehbar sind.

Hier ist ein Beispiel für eine gespeicherte Prozedur, bei der der Name eines Arguments im JavaScript-Code nicht mit dem Namen des Arguments im SQL-Code übereinstimmt, weil die Groß-/Kleinschreibung anders ist:

Im folgenden Beispiel ist die erste Zuweisungsanweisung falsch, da der Name argument1 in Kleinbuchstaben geschrieben ist.

CREATE PROCEDURE f(argument1 VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
$$
var local_variable1 = argument1;  // Incorrect
var local_variable2 = ARGUMENT1;  // Correct
$$;

Eine konsistente Verwendung von Bezeichnern in Großbuchstaben (insbesondere von Argumentnamen) in Ihren SQL-Anweisungen und im JavaScript-Code führt tendenziell zu weniger Fehlern im Hintergrund.

JavaScript-Trennzeichen

Der JavaScript-Teil des Codes der gespeicherten Prozedur muss in einfache Anführungszeichen ' oder doppelte Dollarzeichen $$ eingeschlossen werden.

Die Verwendung von $$ erleichtert die Verarbeitung von JavaScript-Code, der einfache Anführungszeichen enthält, die nicht mit Escape-Zeichen umschlossen sind.

Binden von Variablen

Durch das Binden einer Variablen an eine SQL-Anweisung können Sie den Wert der Variablen in der Anweisung verwenden.

Sie können sowohl NULL- als auch Nicht-NULL-Werte binden.

Der Datentyp der Variablen sollte für die Verwendung des Werts in der SQL-Anweisung geeignet sein. Derzeit können nur JavaScript-Variablen vom Typ Zahl (number), Zeichenfolge (string) und SfDate gebunden werden. (Weitere Details zur Zuordnung zwischen SQL-Datentypen und JavaScript-Datentypen finden Sie unter Zuordnen von SQL- und JavaScript-Datentypen.)

Dies ist ein kurzes Beispiel zum Binden:

var stmt = snowflake.createStatement(
   {
   sqlText: "INSERT INTO table2 (col1, col2) VALUES (?, ?);",
   binds:["LiteralValue1", variable2]
   }
);

Hier ist ein vollständigeres Beispiel. In diesem Beispiel werden TIMESTAMP-Informationen gebunden. Da die direkte Bindung von SQL-TIMESTAMP-Daten nicht unterstützt wird, übergibt dieses Beispiel den Zeitstempel als VARCHAR und bindet diesen dann an die Anweisung. Beachten Sie, dass die SQL-Anweisung selbst den VARCHAR-Wert durch Aufrufen der TO_TIMESTAMP()-Funktion in einen TIMESTAMP-Wert konvertiert:

Diese einfache Funktion gibt TRUE zurück, wenn der angegebene Zeitstempel vor dem jetzigen Zeitpunkt liegt, und FALSE, wenn dies nicht der Fall ist.

CREATE OR REPLACE PROCEDURE right_bind(TIMESTAMP_VALUE VARCHAR)
RETURNS BOOLEAN
LANGUAGE JAVASCRIPT
AS
$$
var cmd = "SELECT CURRENT_DATE() > TO_TIMESTAMP(:1, 'YYYY-MM-DD HH24:MI:SS')";
var stmt = snowflake.createStatement(
          {
          sqlText: cmd,
          binds: [TIMESTAMP_VALUE]
          }
          );
var result1 = stmt.execute();
result1.next();
return result1.getColumnValue(1);
$$
;
CALL right_bind('2019-09-16 01:02:03');
+------------+
| RIGHT_BIND |
|------------|
| True       |
+------------+

Hier sehen Sie, wie Sie VARCHAR, TIMESTAMP_LTZ und andere Datentypen an eine INSERT-Anweisung binden. TIMESTAMP_LTZ bindet eine SfDate-Variable, die in der gespeicherten Prozedur erstellt wird.

Erstellen Sie eine Tabelle.

CREATE TABLE table1 (v VARCHAR, 
                     ts1 TIMESTAMP_LTZ(9), 
                     int1 INTEGER,
                     float1 FLOAT,
                     numeric1 NUMERIC(10,9),
                     ts_ntz1 TIMESTAMP_NTZ,
                     date1 DATE,
                     time1 TIME
                     );

Erstellen Sie eine gespeicherte Prozedur. Diese Prozedur akzeptiert einen VARCHAR-Wert und konvertiert den VARCHAR-Wert mit SQL in einen TIMESTAMP_LTZ-Wert. Die Prozedur ruft dann den konvertierten Wert aus einem ResultSet ab. Der Wert wird in einer JavaScript-Variablen vom Typ SfDate gespeichert. Die gespeicherte Prozedur bindet dann sowohl den ursprünglichen VARCHAR-Wert als auch den TIMESTAMP_LTZ-Wert an eine INSERT-Anweisung. Dies demonstriert auch die Bindung von numerischen JavaScript-Daten.

CREATE OR REPLACE PROCEDURE string_to_timestamp_ltz(TSV VARCHAR) 
RETURNS TIMESTAMP_LTZ 
LANGUAGE JAVASCRIPT 
AS 
$$ 
    // Convert the input varchar to a TIMESTAMP_LTZ.
    var sql_command = "SELECT '" + TSV + "'::TIMESTAMP_LTZ;"; 
    var stmt = snowflake.createStatement( {sqlText: sql_command} ); 
    var resultSet = stmt.execute(); 
    resultSet.next(); 
    // Retrieve the TIMESTAMP_LTZ and store it in an SfDate variable.
    var my_sfDate = resultSet.getColumnValue(1); 

    f = 3.1415926;

    // Specify that we'd like position-based binding.
    sql_command = `INSERT INTO table1 VALUES(:1, :2, :3, :4, :5, :6, :7, :8);` 
    // Bind a VARCHAR, a TIMESTAMP_LTZ, a numeric to our INSERT statement.
    result = snowflake.execute(
        { 
        sqlText: sql_command, 
        binds: [TSV, my_sfDate, f, f, f, my_sfDate, my_sfDate, '12:30:00.123' ] 
        }
        ); 

    return my_sfDate; 
$$ ; 

Rufen Sie die Prozedur auf.

CALL string_to_timestamp_ltz('2008-11-18 16:00:00');
+-------------------------------+
| STRING_TO_TIMESTAMP_LTZ       |
|-------------------------------|
| 2008-11-18 16:00:00.000 -0800 |
+-------------------------------+

Stellen Sie sicher, dass die Zeile eingefügt wurde.

SELECT * FROM table1;
+---------------------+-------------------------------+------+----------+-------------+-------------------------+------------+----------+
| V                   | TS1                           | INT1 |   FLOAT1 |    NUMERIC1 | TS_NTZ1                 | DATE1      | TIME1    |
|---------------------+-------------------------------+------+----------+-------------+-------------------------+------------+----------|
| 2008-11-18 16:00:00 | 2008-11-18 16:00:00.000 -0800 |    3 | 3.141593 | 3.141593000 | 2008-11-18 16:00:00.000 | 2008-11-18 | 12:30:00 |
+---------------------+-------------------------------+------+----------+-------------+-------------------------+------------+----------+

Weitere Beispiele zum Binden von Daten in JavaScript finden Sie unter Datenbindung in Anweisungsparametern.

Code-Anforderungen

Der JavaScript-Code muss ein einzelnes Literal-JavaScript-Objekt definieren, damit die gespeicherte Prozedur gültig ist.

Wenn der JavaScript-Code diese Anforderung nicht erfüllt, wird die gespeicherte Prozedur zwar erstellt; sie wird beim Aufrufen aber fehlschlagen.

Laufzeitfehler

Die meisten Fehler in gespeicherten Prozeduren treten zur Laufzeit auf, da der JavaScript-Code zum Zeitpunkt der Ausführung der gespeicherten Prozedur interpretiert wird und nicht zum Zeitpunkt der Erstellung der gespeicherten Prozedur.

Unterstützung für dynamische SQL

Gespeicherte Prozeduren können zur dynamischen Erstellung von SQL-Anweisungen dienen. Sie können beispielsweise eine SQL-Befehlszeichenfolge erstellen, die eine Mischung aus vorkonfigurierten SQL- und Benutzereingaben enthält (z. B. Kontonummer eines Benutzers).

Beispiele finden Sie unter Dynamisches Erstellen einer SQL-Anweisung und im Abschnitt Beispiele.

Synchrone API

Die API für gespeicherte Snowflake-Prozeduren ist synchron. Innerhalb einer gespeicherten Prozedur können Sie immer nur jeweils einen Thread ausführen.

Beachten Sie, dass dies nicht mit der Regel zur Ausführung von JavaScript mit dem Konnektor Node.js übereinstimmt, bei dem Sie asynchrone Threads ausführen können.

Einschleusung von SQL-Befehlen

Gespeicherte Prozeduren können eine SQL-Anweisung dynamisch erstellen und ausführen. Dadurch werden jedoch Angriffe durch Einschleusung von SQL-Befehlen möglich, insbesondere wenn Sie die SQL-Anweisung mithilfe von Eingaben aus einer öffentlichen oder nicht vertrauenswürdigen Quelle erstellen.

Sie können das Risiko einer Einschleusung von SQL-Befehlen minimieren, indem Sie Parameter binden, anstatt Text zu verketten. Ein Beispiel für das Binden von Variablen finden Sie unter Binden von Variablen.

Falls Sie sich für eine Verkettung entscheiden, sollten Sie die Eingaben sorgfältig prüfen, wenn Sie SQL dynamisch mit Eingaben aus öffentlichen Quellen erstellen. Möglicherweise sollten Sie auch andere Vorsichtsmaßnahmen ergreifen, z. B. Abfragen mithilfe einer Rolle mit eingeschränkten Berechtigungen (z. B. Nur-Lese-Zugriff oder Zugriff auf bestimmte Tabellen oder Ansichten).

Weitere Informationen zu Angriffen durch Einschleusung von SQL finden Sie unter SQL-Injection (in Wikipedia).

Entwurfstipps für gespeicherte Prozeduren

Im Folgenden finden Sie einige Tipps zum Entwerfen einer gespeicherten Prozedur:

  • Welche Ressourcen, z. B. Tabellen, benötigt die gespeicherte Prozedur?

  • Welche Berechtigungen werden benötigt?

    Überlegen Sie, auf welche Datenbankobjekte zugegriffen wird, von welchen Rollen Ihre gespeicherte Prozedur ausgeführt werden darf und welche Berechtigungen diese Rollen benötigen.

    Wenn die Prozedur eine gespeicherte Prozedur mit Aufruferrechten sein soll, möchten Sie möglicherweise eine Rolle zum Ausführen dieser bestimmten Prozedur oder einer Gruppe verwandter Prozeduren erstellen. Anschließend können Sie dieser Rolle alle erforderlichen Berechtigungen erteilen und diese Rolle dann den entsprechenden Benutzern zuweisen.

  • Soll die gespeicherte Prozedur mit Aufruferrechten oder mit Eigentümerrechten ausgeführt werden? Weitere Informationen zu diesem Thema finden Sie unter Erläuterungen zu gespeicherten Prozeduren mit Aufruferrechten und Eigentümerrechten.

  • Wie soll die Prozedur mit Fehlern umgehen, z. B. was soll die Prozedur tun, wenn eine erforderliche Tabelle fehlt oder ein Argument ungültig ist?

  • Soll die gespeicherte Prozedur ihre Aktivitäten oder Fehler protokollieren, indem sie beispielsweise in eine Protokolltabelle schreibt?

  • Erläuterungen darüber, wann eine gespeicherte Prozedur und wann eine UDF verwendet werden sollte, finden Sie unter Entscheidung über das Erstellen einer gespeicherten Prozedur oder einer UDF.

Dokumentieren gespeicherter Prozeduren

Gespeicherte Prozeduren werden normalerweise so geschrieben, dass sie wiederverwendet und häufig auch freigegeben werden können. Durch die Dokumentation gespeicherter Prozeduren können diese einfacher verwendet und verwaltet werden.

Im Folgenden finden Sie einige allgemeine Empfehlungen zur Dokumentation gespeicherter Prozeduren.

In der Regel gibt es mindestens zwei Benutzergruppen, die Informationen zu einer gespeicherten Prozedur benötigen:

  • Benutzer/Aufrufer

  • Programmierer/Autoren

Dokumentieren Sie für Benutzer (und Programmierer) Folgendes:

  • Name der gespeicherten Prozedur

  • Speicherort der gespeicherten Prozedur (Datenbank und Schema)

  • Zweck der gespeicherten Prozedur

  • Name, Datentyp und Bedeutung der einzelnen Eingabeparameter

  • Name, Datentyp und Bedeutung des Rückgabewerts. Wenn der Rückgabewert ein komplexer Typ ist, z. B. VARIANT, der Unterfelder enthält, dokumentieren Sie diese Unterfelder.

  • Wenn sich die gespeicherte Prozedur auf Daten aus ihrer Umgebung stützt, z. B. Sitzungsvariablen oder Sitzungsparameter, dokumentieren Sie die Namen, Zwecke und gültige Werte dieser Daten.

  • Zurückgegebene Fehler, ausgelöste Ausnahmen usw.

  • Rollen oder Berechtigungen, die zum Ausführen der Prozedur erforderlich sind. (Weitere Informationen zu diesem Thema finden Sie in den Erläuterungen zu Rollen unter Entwurfstipps für gespeicherte Prozeduren.)

  • Ob es sich bei der gespeicherten Prozedur um eine Prozedur mit Aufruferrechten oder mit Eigentümerrechten handelt.

  • Alle Voraussetzungen, z. B. Tabellen, die vorhanden sein müssen, bevor die Prozedur aufgerufen wird.

  • Alle Ausgaben (außer dem Rückgabewert), zum Beispiel neue Tabellen, die erstellt werden.

  • Alle „Nebenwirkungen“, z. B. Änderungen der Berechtigungen, Löschen alter Daten usw. Die meisten gespeicherten Prozeduren (im Gegensatz zu Funktionen) werden speziell wegen ihrer Nebeneffekte aufgerufen, weniger wegen ihrer Rückgabewerte. Stellen Sie daher sicher, dass Sie diese Auswirkungen dokumentieren.

  • Wenn nach dem Ausführen der gespeicherten Prozedur eine Bereinigung erforderlich ist, dokumentieren Sie diese Bereinigung.

  • Ob die Prozedur als Teil einer Transaktion mit mehreren Anweisungen aufgerufen werden kann (mit AUTOCOMMIT=FALSE), oder ob sie außerhalb einer Transaktion ausgeführt werden soll (mit AUTOCOMMIT=TRUE).

  • Ein Beispiel für einen Anruf und ein Beispiel für die Rückgabewerte.

  • Einschränkungen (falls zutreffend). Angenommen, die Prozedur liest eine Tabelle ein und gibt einen VARIANT-Wert zurück, der Informationen aus jeder Zeile der Tabelle enthält. Es ist möglich, dass der VARIANT-Wert größer wird als die maximale zulässige Größe von VARIANT. Daher müssen Sie dem Aufrufer möglicherweise eine Vorstellung von der maximalen Anzahl von Zeilen in der Tabelle geben, auf die die Prozedur zugreift.

  • Warnungen (falls zutreffend).

  • Tipps zur Problembehandlung

Für Programmierer:

  • Die Autoren

  • Erläuterungen, warum die Prozedur als Prozedur mit Aufruferrechten bzw. als Prozedur mit Eigentümerrechten erstellt wurde. Der Grund hierfür ist möglicherweise nicht offensichtlich.

  • Gespeicherte Prozeduren können verschachtelt sein, die Tiefe der Verschachtelung ist jedoch begrenzt. Wenn Ihre gespeicherte Prozedur andere gespeicherte Prozeduren aufruft und wahrscheinlich von anderen gespeicherten Prozeduren aufgerufen wird, sollten Sie die maximale bekannte Tiefe des Aufrufstapels Ihrer gespeicherten Prozedur angeben, damit die Aufrufer abschätzen können, ob ihre gespeicherte Prozedur die maximale Tiefe des Aufrufstapels überschreitet.

  • Tipps zum Debuggen

Speicherort und Format dieser Informationen bestimmen Sie. Sie können die Informationen beispielsweise im HTML-Format auf einer internen Website speichern. Überlegen Sie sich, wo Ihre Organisation ähnliche Informationen für andere Produkte oder ähnliche Informationen für andere Snowflake-Funktionen wie Ansichten, benutzerdefinierte Funktionen usw. speichert, bevor Sie entscheiden, wo die Speicherung erfolgen soll.

Weitere Tipps:

  • Fügen Sie Kommentare in den Quellcode ein, wie Sie es für jedes Stück Quellcode tun sollten.

    • Denken Sie daran, dass Reverse Engineering aus Code schwierig ist. Beschreiben Sie nicht nur die Funktionsweise Ihres Algorithmus, sondern auch den Zweck dieses Algorithmus.

  • In gespeicherten Prozeduren ist ein optionaler COMMENT zulässig, der mit der Anweisung CREATE PROCEDURE oder ALTER PROCEDURE angegeben werden kann. Andere Personen können diesen Kommentar lesen, indem sie den Befehl SHOW PROCEDURES ausführen.

  • Wenn möglich, sollten Sie eine Masterkopie des CREATE PROCEDURE-Befehls jeder gespeicherten Prozedur in einem Quellcodeverwaltungssystem aufbewahren. Die Time Travel-Funktion von Snowflake gilt nicht für gespeicherte Prozeduren. Wenn Sie also nach älteren Versionen von gespeicherten Prozeduren suchen, müssen Sie dies außerhalb von Snowflake tun. Wenn kein Quellcodeverwaltungssystem verfügbar ist, können Sie ein solches teilweise simulieren, indem Sie die CREATE PROCEDURE-Befehle im VARCHAR-Feld einer Tabelle speichern und jede neue Version hinzufügen (ohne die älteren Versionen zu ersetzen).

  • Verwenden Sie eine Namenskonvention, um Informationen zu gespeicherten Prozeduren bereitzustellen. Beispielsweise kann ein Präfix oder Suffix im Namen angeben, ob die Prozedur eine gespeicherte Prozedur mit Aufruferrechten oder eine gespeicherte Prozedur mit Eigentümerrechten ist. (Sie können beispielsweise cr_ (für „Caller’s Rights“) als Präfix für die Aufruferrechte verwenden.)

  • Mit dem Befehl SHOW PROCEDURES können Sie die Datentypen und die Reihenfolge der Eingabeargumente sowie den Kommentar anzeigen. Beachten Sie jedoch, dass hier nur die Namen und Datentypen der Argumente angezeigt werden, eine Erklärung der Argumente erfolgt nicht.

  • Wenn Sie über die entsprechenden Berechtigungen verfügen, können Sie mit dem Befehl DESCRIBE PROCEDURE Folgendes anzeigen:

    • Namen und Datentypen der Argumente

    • Hauptteil der Prozedur und ob die Prozedur als Eigentümer oder Aufrufer ausgeführt wird

    • Datentyp des Rückgabewerts

    • Andere nützliche Informationen

Beispiele

Grundlegende Beispiele

Das folgende Beispiel veranschaulicht die grundlegende Syntax zum Erstellen und Aufrufen einer gespeicherten Prozedur. Es wird weder SQL noch prozeduraler Code ausgeführt. Es bietet jedoch später einen Ausgangspunkt für realistischere Beispiele:

create or replace procedure sp_pi()
    returns float not null
    language javascript
    as
    $$
    return 3.1415926;
    $$
    ;

Beachten Sie, dass das Trennzeichen $$ den Anfang und das Ende des JavaScript-Codes markiert.

Rufen Sie nun die soeben erstellte Prozedur auf:

CALL sp_pi();
+-----------+
|     SP_PI |
|-----------|
| 3.1415926 |
+-----------+

Das folgende Beispiel zeigt, wie eine SQL-Anweisung innerhalb einer gespeicherten Prozedur ausgeführt wird:

  1. Erstellen Sie eine Tabelle:

    CREATE TABLE stproc_test_table1 (num_col1 numeric(14,7));
    
  2. Erstellen Sie eine gespeicherte Prozedur. Dadurch wird eine Zeile in eine vorhandene Tabelle mit dem Namen stproc_test_table1 eingefügt und der Wert „Erfolgreich abgeschlossen“ zurückgegeben. Der zurückgegebene Wert ist aus einer SQL-Perspektive nicht besonders nützlich, ermöglicht es Ihnen aber, Statusinformationen (z. B. „Erfolgreich abgeschlossen“ oder „Fehlgeschlagen“) an den Benutzer zurückzugeben.

    create or replace procedure stproc1(FLOAT_PARAM1 FLOAT)
        returns string
        language javascript
        strict
        execute as owner
        as
        $$
        var sql_command = 
         "INSERT INTO stproc_test_table1 (num_col1) VALUES (" + FLOAT_PARAM1 + ")";
        try {
            snowflake.execute (
                {sqlText: sql_command}
                );
            return "Succeeded.";   // Return a success/error indicator.
            }
        catch (err)  {
            return "Failed: " + err;   // Return a success/error indicator.
            }
        $$
        ;
    
  3. Rufen Sie die gespeicherte Prozedur auf:

    call stproc1(5.14::FLOAT);
    +------------+
    | STPROC1    |
    |------------|
    | Succeeded. |
    +------------+
    
  4. Bestätigen Sie, dass die gespeicherte Prozedur die Zeile eingefügt hat:

    select * from stproc_test_table1;
    +-----------+
    |  NUM_COL1 |
    |-----------|
    | 5.1400000 |
    +-----------+
    

Im folgenden Beispiel wird ein Ergebnis abgerufen:

  1. Erstellen Sie eine Prozedur zum Zählen der Zeilen einer Tabelle (entspricht select count(*) from Tabelle):

    create or replace procedure get_row_count(table_name VARCHAR)
      returns float not null
      language javascript
      as
      $$
      var row_count = 0;
      // Dynamically compose the SQL statement to execute.
      var sql_command = "select count(*) from " + TABLE_NAME;
      // Run the statement.
      var stmt = snowflake.createStatement(
             {
             sqlText: sql_command
             }
          );
      var res = stmt.execute();
      // Get back the row count. Specifically, ...
      // ... get the first (and in this case only) row from the result set ...
      res.next();
      // ... and then get the returned value, which in this case is the number of
      // rows in the table.
      row_count = res.getColumnValue(1);
      return row_count;
      $$
      ;
    
  2. Fragen Sie die gespeicherte Prozedur, wie viele Zeilen sich in der Tabelle befinden:

    call get_row_count('stproc_test_table1');
    +---------------+
    | GET_ROW_COUNT |
    |---------------|
    |             3 |
    +---------------+
    
  3. Prüfen Sie selbstständig, ob Sie die richtige Zahl erhalten haben:

    select count(*) from stproc_test_table1;
    +----------+
    | COUNT(*) |
    |----------|
    |        3 |
    +----------+
    

Beispiel für eine rekursive gespeicherte Prozedur

Das folgende Beispiel veranschaulicht eine einfache, aber nicht besonders realistische rekursive gespeicherte Prozedur:

create or replace table stproc_test_table2 (col1 FLOAT);
create or replace procedure recursive_stproc(counter FLOAT)
    returns varchar not null
    language javascript
    as
    -- "$$" is the delimiter that shows the beginning and end of the stored proc.
    $$
    var counter1 = COUNTER;
    var returned_value = "";
    var accumulator = "";
    var stmt = snowflake.createStatement(
        {
        sqlText: "INSERT INTO stproc_test_table2 (col1) VALUES (?);",
        binds:[counter1]
        }
        );
    var res = stmt.execute();
    if (COUNTER > 0)
        {
        stmt = snowflake.createStatement(
            {
            sqlText: "call recursive_stproc (?);",
            binds:[counter1 - 1]
            }
            );
        res = stmt.execute();
        res.next();
        returned_value = res.getColumnValue(1);
        }
    accumulator = accumulator + counter1 + ":" + returned_value;
    return accumulator;
    $$
    ;
call recursive_stproc(4.0::FLOAT);
+------------------+
| RECURSIVE_STPROC |
|------------------|
| 4:3:2:1:0:       |
+------------------+
SELECT * 
    FROM stproc_test_table2
    ORDER BY col1;
+------+
| COL1 |
|------|
|    0 |
|    1 |
|    2 |
|    3 |
|    4 |
+------+

Dynamisches Erstellen einer SQL-Anweisung

Das folgende Beispiel zeigt, wie eine SQL-Anweisung dynamisch erstellt wird:

Bemerkung

Wie in Einschleusung von SQL-Befehlen (unter diesem Thema) angegeben, sollten Sie bei Verwendung von dynamischer SQL Maßnahmen gegen Angriffe ergreifen.

  1. Erstellen Sie die gespeicherte Prozedur. Mit dieser Prozedur können Sie den Namen einer Tabelle übergeben und die Anzahl der Zeilen in dieser Tabelle abrufen (entspricht select count(*) from Tabellenname):

    create or replace procedure get_row_count(table_name VARCHAR)
        returns float 
        not null
        language javascript
        as
        $$
        var row_count = 0;
        // Dynamically compose the SQL statement to execute.
        // Note that we uppercased the input parameter name.
        var sql_command = "select count(*) from " + TABLE_NAME;
        // Run the statement.
        var stmt = snowflake.createStatement(
               {
               sqlText: sql_command
               }
            );
        var res = stmt.execute();
        // Get back the row count. Specifically, ...
        // ... first, get the first (and in this case only) row from the
        //  result set ...
        res.next();
        // ... then extract the returned value (which in this case is the
        // number of rows in the table).
        row_count = res.getColumnValue(1);
        return row_count;
        $$
        ;
    
  2. Rufen Sie die gespeicherte Prozedur auf:

    call get_row_count('stproc_test_table1');
    +---------------+
    | GET_ROW_COUNT |
    |---------------|
    |             3 |
    +---------------+
    
  3. Zeigen Sie die Ergebnisse von select count(*) für dieselbe Tabelle an:

    SELECT COUNT(*) FROM stproc_test_table1;
    +----------+
    | COUNT(*) |
    |----------|
    |        3 |
    +----------+
    

Abrufen von Metadaten zum Resultset

In diesem Beispiel wird gezeigt, wie eine kleine Menge von Metadaten aus einem Resultset abgerufen wird:

create or replace table stproc_test_table3 (
    n10 numeric(10,0),     /* precision = 10, scale = 0 */
    n12 numeric(12,4),     /* precision = 12, scale = 4 */
    v1 varchar(19)         /* scale = 0 */
    );
create or replace procedure get_column_scale(column_index float)
    returns float not null
    language javascript
    as
    $$
    var stmt = snowflake.createStatement(
        {sqlText: "select n10, n12, v1 from stproc_test_table3;"}
        );
    stmt.execute();  // ignore the result set; we just want the scale.
    return stmt.getColumnScale(COLUMN_INDEX); // Get by column index (1-based)
    $$
    ;
call get_column_scale(1);
+------------------+
| GET_COLUMN_SCALE |
|------------------|
|                0 |
+------------------+
call get_column_scale(2);
+------------------+
| GET_COLUMN_SCALE |
|------------------|
|                4 |
+------------------+
call get_column_scale(3);
+------------------+
| GET_COLUMN_SCALE |
|------------------|
|                0 |
+------------------+

Abfangen eines Fehlers mit Try/Catch

In diesem Beispiel wird die Verwendung eines JavaScript Try/Catch-Blocks zum Abfangen eines Fehlers in einer gespeicherten Prozedur dargestellt:

  1. Erstellen Sie die gespeicherte Prozedur:

        create procedure broken()
          returns varchar not null
          language javascript
          as
          $$
          var result = "";
          try {
              snowflake.execute( {sqlText: "Invalid Command!;"} );
              result = "Succeeded";
              }
          catch (err)  {
              result =  "Failed: Code: " + err.code + "\n  State: " + err.state;
              result += "\n  Message: " + err.message;
              result += "\nStack Trace:\n" + err.stackTraceTxt; 
              }
          return result;
          $$
          ;
    
  2. Rufen Sie die gespeicherte Prozedur auf. Dadurch sollten ein Fehler mit der Fehlernummer und weitere Informationen zurückgegeben werden:

        -- This is expected to fail.
        call broken();
    +---------------------------------------------------------+
    | BROKEN                                                  |
    |---------------------------------------------------------|
    | Failed: Code: 100183                                    |
    |   State: P0000                                          |
    |   Message: SQL compilation error:                       |
    | syntax error line 1 at position 0 unexpected 'Invalid'. |
    | Stack Trace:                                            |
    | At Snowflake.execute, line 4 position 20                |
    +---------------------------------------------------------+
    

Das folgende Beispiel veranschaulicht das Auslösen einer benutzerdefinierten Ausnahme:

  1. Erstellen Sie die gespeicherte Prozedur:

    CREATE OR REPLACE PROCEDURE validate_age (age float)
    RETURNS VARCHAR
    LANGUAGE JAVASCRIPT
    EXECUTE AS CALLER
    AS $$
        try {
            if (AGE < 0) {
                throw "Age cannot be negative!";
            } else {
                return "Age validated.";
            }
        } catch (err) {
            return "Error: " + err;
        }
    $$;
    
  2. Rufen Sie die gespeicherte Prozedur mit gültigen und ungültigen Werten auf:

    CALL validate_age(50);
    +----------------+
    | VALIDATE_AGE   |
    |----------------|
    | Age validated. |
    +----------------+
    CALL validate_age(-2);
    +--------------------------------+
    | VALIDATE_AGE                   |
    |--------------------------------|
    | Error: Age cannot be negative! |
    +--------------------------------+
    

Protokollieren von Fehlern

Das folgende Beispiel protokolliert Fehler in einer Tabelle. In einem realistischeren Beispiel würden Sie wahrscheinlich auch den Zeitstempel für den Zeitpunkt angeben, zu dem der Fehler aufgetreten ist (mithilfe der Funktion CURRENT_TIMESTAMP).

CREATE OR REPLACE TABLE error_log (error_code number, error_state string, error_message string, stack_trace string);

CREATE OR REPLACE PROCEDURE broken() 
RETURNS varchar 
NOT NULL 
LANGUAGE javascript 
AS $$
var result;
try {
    snowflake.execute({ sqlText: "Invalid Command!;" });
    result = "Succeeded";
} catch (err) {
    result = "Failed";
    snowflake.execute({
      sqlText: `insert into error_log VALUES (?,?,?,?)`
      ,binds: [err.code, err.state, err.message, err.stackTraceTxt]
      });
}
return result;
$$;
call broken();
+--------+
| BROKEN |
|--------|
| Failed |
+--------+
select * from error_log;
+------------+-------------+---------------------------------------------------------+------------------------------------------+
| ERROR_CODE | ERROR_STATE | ERROR_MESSAGE                                           | STACK_TRACE                              |
|------------+-------------+---------------------------------------------------------+------------------------------------------|
|     100183 | P0000       | SQL compilation error:                                  | At Snowflake.execute, line 4 position 14 |
|            |             | syntax error line 1 at position 0 unexpected 'Invalid'. |                                          |
+------------+-------------+---------------------------------------------------------+------------------------------------------+

Protokollieren von Fehlern (Version 2)

Im Folgenden wird zum Protokollieren von Meldungen in einer temporären Tabelle eine gespeicherte Prozedur verwendet. In diesem Beispiel kann der Aufrufer den Namen der Protokolltabelle angeben und die Tabelle erstellen, falls sie noch nicht vorhanden ist. In diesem Beispiel kann der Anrufer die Protokollierung auf einfache Weise aktivieren und deaktivieren.

Beachten Sie auch, dass eine dieser gespeicherten Prozeduren eine kleine JavaScript-Funktion erstellt, die wiederverwendet werden kann. In langen gespeicherten Prozeduren mit sich wiederholendem Code kann das Erstellen von JavaScript-Funktionen innerhalb der gespeicherten Prozedur praktisch sein.

Erstellen Sie die Prozeduren:

CREATE or replace PROCEDURE do_log(MSG STRING)
    RETURNS STRING
    LANGUAGE JAVASCRIPT
    EXECUTE AS CALLER
    AS $$
 
    // See if we should log - checks for session variable do_log = true.
    try {
       var stmt = snowflake.createStatement( { sqlText: `select $do_log` } ).execute();
    } catch (ERROR){
       return; //swallow the error, variable not set so don't log
    }
    stmt.next();
    if (stmt.getColumnValue(1)==true){ //if the value is anything other than true, don't log
       try {
           snowflake.createStatement( { sqlText: `create temp table identifier ($log_table) if not exists (ts number, msg string)`} ).execute();
           snowflake.createStatement( { sqlText: `insert into identifier ($log_table) values (:1, :2)`, binds:[Date.now(), MSG] } ).execute();
       } catch (ERROR){
           throw ERROR;
       }
    }
 $$
;

CREATE or replace PROCEDURE my_test()
    RETURNS STRING
    LANGUAGE JAVASCRIPT
    EXECUTE AS CALLER
    AS $$

    // Define the SP call as a function - it's cleaner this way.
    // Add this function to your stored procs
    function log(msg){
        snowflake.createStatement( { sqlText: `call do_log(:1)`, binds:[msg] } ).execute();
        }

    // Now just call the log function anytime...
    try {
        var x = 10/10;
        log('log this message'); //call the log function
        //do some stuff here
        log('x = ' + x.toString()); //log the value of x 
        log('this is another log message'); //throw in another log message
    } catch(ERROR) {
        log(ERROR); //we can even catch/log the error messages
        return ERROR;
    }

    $$
;

Aktivieren Sie die Protokollierung:

set do_log = true; --true to enable logging, false (or undefined) to disable
set log_table = 'my_log_table';  -- The name of the temp table where log messages go.

Rufen Sie die Prozedur auf:

CALL my_test();
+---------+
| MY_TEST |
|---------|
| NULL    |
+---------+

Überprüfen Sie, ob die Tabelle erstellt und die Meldungen protokolliert wurden:

select msg 
    from my_log_table 
    order by 1;
+-----------------------------+
| MSG                         |
|-----------------------------|
| log this message            |
| this is another log message |
| x = 1                       |
+-----------------------------+

Überladen der Namen gespeicherter Prozeduren

Wie in Namenskonventionen für gespeicherte Prozeduren (unter diesem Thema) beschrieben, können Sie gespeicherte Prozedurnamen überladen. Beispiel:

  1. Erstellen Sie zuerst zwei gespeicherte Prozeduren, die denselben Namen, aber unterschiedlich viele Argumente haben.

    create or replace procedure stproc1(FLOAT_PARAM1 FLOAT)
        returns string
        language javascript
        strict
        as
        $$
        return FLOAT_PARAM1;
        $$
        ;
    
    create or replace procedure stproc1(FLOAT_PARAM1 FLOAT, FLOAT_PARAM2 FLOAT)
        returns string
        language javascript
        strict
        as
        $$
        return FLOAT_PARAM1 * FLOAT_PARAM2;
        $$
        ;
    
  2. Rufen Sie als Nächstes die beiden Prozeduren auf:

    call stproc1(5.14::FLOAT);
    +---------+
    | STPROC1 |
    |---------|
    | 5.14    |
    +---------+
    
    call stproc1(5.14::FLOAT, 2.00::FLOAT);
    +---------+
    | STPROC1 |
    |---------|
    | 10.28   |
    +---------+
    

Bemerkung

Für einige andere Operationen auf gespeicherten Prozeduren müssen Sie auch die Datentypen der Argumente angeben. Beispielsweise erfordern GRANT und REVOKE die Argumenttypen sowie den Namen der gespeicherten Prozedur.

Das nächste Beispiel für Überladung zeigt, wie durch die Kombination von Überladung und automatischer Typkonvertierung unerwartete Ergebnisse auftreten können:

Erstellen Sie eine gespeicherte Prozedur mit einem FLOAT-Parameter:

CREATE PROCEDURE add_pi(PARAM_1 FLOAT)
    RETURNS FLOAT
    LANGUAGE JAVASCRIPT
    AS $$
        return PARAM_1 + 3.1415926;
    $$;

Rufen Sie die gespeicherte Prozedur auf: Übergeben Sie beim ersten Mal einen FLOAT-Wert. Übergeben Sie beim zweiten Mal eine VARCHAR-Wert. Der VARCHAR-Wert wird in einen FLOAT-Wert konvertiert, und die Ausgabe jedes Aufrufs ist identisch:

CALL add_pi(1.0);
+-----------+
|    ADD_PI |
|-----------|
| 4.1415926 |
+-----------+
CALL add_pi('1.0');
+-----------+
|    ADD_PI |
|-----------|
| 4.1415926 |
+-----------+

Erstellen Sie nun eine überladene gespeicherte Prozedur mit einem VARCHAR-Parameter:

CREATE PROCEDURE add_pi(PARAM_1 VARCHAR)
    RETURNS VARCHAR
    LANGUAGE JAVASCRIPT
    AS $$
        return PARAM_1 + '3.1415926';
    $$;

Verwenden Sie jetzt genau dieselben CALLs wie zuvor. Beachten Sie den Unterschied in der Ausgabe zwischen diesen beiden CALLs und den beiden vorherigen CALLs.

CALL add_pi(1.0);
+-----------+
|    ADD_PI |
|-----------|
| 4.1415926 |
+-----------+
CALL add_pi('1.0');
+--------------+
| ADD_PI       |
|--------------|
| 1.03.1415926 |
+--------------+

Verwenden von RESULT_SCAN zum Abrufen des Ergebnisses einer gespeicherten Prozedur

In diesem Beispiel wird gezeigt, wie Sie mit der Funktion RESULT_SCAN das Ergebnis einer CALL-Anweisung abrufen können:

  1. Erstellen und laden Sie die Tabelle:

    CREATE TABLE western_provinces(ID INT, province VARCHAR);
    
    INSERT INTO western_provinces(ID, province) VALUES
        (1, 'Alberta'),
        (2, 'British Columbia'),
        (3, 'Manitoba')
        ;
    
  2. Erstellen Sie die gespeicherte Prozedur. Diese Prozedur gibt eine gut formatierte Zeichenfolge zurück, die wie ein Resultset mit drei Zeilen aussieht, aber tatsächlich eine einzelne Zeichenfolge ist:

    CREATE OR REPLACE PROCEDURE read_western_provinces()
      RETURNS VARCHAR NOT NULL
      LANGUAGE JAVASCRIPT
      AS
      $$
      var return_value = "";
      try {
          var command = "SELECT * FROM western_provinces ORDER BY province;"
          var stmt = snowflake.createStatement( {sqlText: command } );
          var rs = stmt.execute();
          if (rs.next())  {
              return_value += rs.getColumnValue(1);
              return_value += ", " + rs.getColumnValue(2);
              }
          while (rs.next())  {
              return_value += "\n";
              return_value += rs.getColumnValue(1);
              return_value += ", " + rs.getColumnValue(2);
              }
          }
      catch (err)  {
          result =  "Failed: Code: " + err.code + "\n  State: " + err.state;
          result += "\n  Message: " + err.message;
          result += "\nStack Trace:\n" + err.stackTraceTxt;
          }
      return return_value;
      $$
      ;
    
  3. Rufen Sie die gespeicherte Prozedur auf, und rufen Sie mit RESULT_SCAN die Ergebnisse ab:

    CALL read_western_provinces();
    +------------------------+
    | READ_WESTERN_PROVINCES |
    |------------------------|
    | 1, Alberta             |
    | 2, British Columbia    |
    | 3, Manitoba            |
    +------------------------+
    SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
    +------------------------+
    | READ_WESTERN_PROVINCES |
    |------------------------|
    | 1, Alberta             |
    | 2, British Columbia    |
    | 3, Manitoba            |
    +------------------------+
    

Sie können komplexere Operationen auf den von der Funktion RESULT_SCAN zurückgegebenen Wert ausführen. Da in diesem Fall der Rückgabewert eine einzelne Zeichenfolge ist, möchten Sie möglicherweise die einzelnen „Zeilen“ extrahieren, die in der Zeichenfolge enthalten zu sein scheinen, und diese Zeilen in einer anderen Tabelle speichern.

Das folgende Beispiel, das eine Fortsetzung des vorherigen Beispiels darstellt, veranschaulicht eine Methode dafür:

  1. Erstellen Sie eine Tabelle zur langfristigen Speicherung. Diese Tabelle enthält den Provinznamen und die Provinz-ID, nachdem Sie sie aus der vom Befehl CALL zurückgegebenen Zeichenfolge extrahiert haben:

    CREATE TABLE all_provinces(ID INT, province VARCHAR);
    
  2. Rufen Sie die gespeicherte Prozedur auf, rufen Sie das Ergebnis mit RESULT_SCAN ab, extrahieren Sie die drei Zeilen aus der Zeichenfolge, und fügen Sie sie in die Tabelle ein:

    INSERT INTO all_provinces
      WITH 
        one_string (string_col) AS
          (SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))),
        three_strings (one_row) AS
          (SELECT VALUE FROM one_string, LATERAL SPLIT_TO_TABLE(one_string.string_col, '\n'))
      SELECT
             STRTOK(one_row, ',', 1) AS ID,
             STRTOK(one_row, ',', 2) AS province
        FROM three_strings
        WHERE NOT (ID IS NULL AND province IS NULL);
    +-------------------------+
    | number of rows inserted |
    |-------------------------|
    |                       3 |
    +-------------------------+
    
  3. Stellen Sie sicher, dass dies funktioniert hat, indem Sie die Zeilen in der Tabelle anzeigen:

    SELECT ID, province 
        FROM all_provinces;
    +----+-------------------+
    | ID | PROVINCE          |
    |----+-------------------|
    |  1 |  Alberta          |
    |  2 |  British Columbia |
    |  3 |  Manitoba         |
    +----+-------------------+
    

Hier ist ungefähr derselbe Code, aber in kleineren Schritten:

  1. Erstellen Sie eine Tabelle namens one_string. In dieser Tabelle wird das Ergebnis des Befehls CALL temporär gespeichert. Da das Ergebnis von CALL eine einzelne Zeichenfolge ist, speichert die Tabelle nur einen VARCHAR-Wert.

    CREATE TRANSIENT TABLE one_string(string_col VARCHAR);
    
  2. Rufen Sie die gespeicherte Prozedur auf, rufen Sie mit RESULT_SCAN das Ergebnis (eine Zeichenfolge) ab, und speichern Sie das Ergebnis in der Zwischentabelle namens one_string:

    CALL read_western_provinces();
    +------------------------+
    | READ_WESTERN_PROVINCES |
    |------------------------|
    | 1, Alberta             |
    | 2, British Columbia    |
    | 3, Manitoba            |
    +------------------------+
    INSERT INTO one_string
        SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
    +-------------------------+
    | number of rows inserted |
    |-------------------------|
    |                       1 |
    +-------------------------+
    

    Dies zeigt die neue Zeile in der Tabelle one_string. Denken Sie daran, dass die Ausgabe zwar so formatiert ist, dass sie wie drei Zeilen aussieht, es sich in Wahrheit jedoch um eine einzige Zeichenfolge handelt:

    SELECT string_col FROM one_string;
    +---------------------+
    | STRING_COL          |
    |---------------------|
    | 1, Alberta          |
    | 2, British Columbia |
    | 3, Manitoba         |
    +---------------------+
    -- Show that it's one string, not three rows:
    SELECT '>>>' || string_col || '<<<' AS string_col 
        FROM one_string;
    +---------------------+
    | STRING_COL          |
    |---------------------|
    | >>>1, Alberta       |
    | 2, British Columbia |
    | 3, Manitoba<<<      |
    +---------------------+
    SELECT COUNT(*) FROM one_string;
    +----------+
    | COUNT(*) |
    |----------|
    |        1 |
    +----------+
    

    Die folgenden Befehle zeigen, wie Sie mehrere Zeilen aus der Zeichenfolge extrahieren können:

    SELECT * FROM one_string, LATERAL SPLIT_TO_TABLE(one_string.string_col, '\n');
    +---------------------+-----+-------+---------------------+
    | STRING_COL          | SEQ | INDEX | VALUE               |
    |---------------------+-----+-------+---------------------|
    | 1, Alberta          |   1 |     1 | 1, Alberta          |
    | 2, British Columbia |     |       |                     |
    | 3, Manitoba         |     |       |                     |
    | 1, Alberta          |   1 |     2 | 2, British Columbia |
    | 2, British Columbia |     |       |                     |
    | 3, Manitoba         |     |       |                     |
    | 1, Alberta          |   1 |     3 | 3, Manitoba         |
    | 2, British Columbia |     |       |                     |
    | 3, Manitoba         |     |       |                     |
    +---------------------+-----+-------+---------------------+
    SELECT VALUE FROM one_string, LATERAL SPLIT_TO_TABLE(one_string.string_col, '\n');
    +---------------------+
    | VALUE               |
    |---------------------|
    | 1, Alberta          |
    | 2, British Columbia |
    | 3, Manitoba         |
    +---------------------+
    
  3. Erstellen Sie als nächstes eine Tabelle namens three_strings. Diese Tabelle enthält das Ergebnis, nachdem Sie sie in einzelne Zeilen/Zeichenfolgen aufgeteilt haben:

    CREATE TRANSIENT TABLE three_strings(string_col VARCHAR);
    
  4. Konvertieren Sie nun diese eine Zeichenfolge der Tabelle one_string in drei separate Zeichenfolgen, und zeigen Sie, dass es sich nun tatsächlich um drei Zeichenfolgen handelt:

    INSERT INTO three_strings
      SELECT VALUE FROM one_string, LATERAL SPLIT_TO_TABLE(one_string.string_col, '\n');
    +-------------------------+
    | number of rows inserted |
    |-------------------------|
    |                       3 |
    +-------------------------+
    SELECT string_col 
        FROM three_strings;
    +---------------------+
    | STRING_COL          |
    |---------------------|
    | 1, Alberta          |
    | 2, British Columbia |
    | 3, Manitoba         |
    +---------------------+
    SELECT COUNT(*) 
        FROM three_strings;
    +----------+
    | COUNT(*) |
    |----------|
    |        3 |
    +----------+
    
  5. Konvertieren Sie die drei Zeichenfolgen in unserer Langzeittabelle mit dem Namen all_provinces nun in drei Zeilen:

    INSERT INTO all_provinces
      SELECT 
             STRTOK(string_col, ',', 1) AS ID, 
             STRTOK(string_col, ',', 2) AS province 
        FROM three_strings
        WHERE NOT (ID IS NULL AND province IS NULL);
    +-------------------------+
    | number of rows inserted |
    |-------------------------|
    |                       3 |
    +-------------------------+
    
  6. Zeigen Sie die drei Zeilen in der Langzeittabelle an:

    SELECT ID, province 
        FROM all_provinces;
    +----+-------------------+
    | ID | PROVINCE          |
    |----+-------------------|
    |  1 |  Alberta          |
    |  2 |  British Columbia |
    |  3 |  Manitoba         |
    +----+-------------------+
    SELECT COUNT(*) 
        FROM all_provinces;
    +----------+
    | COUNT(*) |
    |----------|
    |        3 |
    +----------+
    

Zurückgeben eines Arrays an Fehlermeldungen

Ihre gespeicherte Prozedur führt möglicherweise mehr als eine SQL-Anweisung aus, und Sie wollen ggf. eine Status-/Fehlermeldung für jede der SQL-Anweisungen zurückgeben. Eine gespeicherte Prozedur gibt jedoch eine einzelne Zeile zurück; sie dient nicht dazu, mehrere Zeilen zurückzugeben.

Wenn alle Ihre Meldungen in einen einzelnen Wert vom Typ ARRAY passen, können Sie mit einigem Zusatzaufwand alle Meldungen von einer gespeicherten Prozedur abrufen.

Das folgende Beispiel zeigt eine Möglichkeit dafür (die angezeigten Fehlermeldungen sind nicht real, aber Sie können diesen Code erweitern, um mit Ihren tatsächlichen SQL-Anweisungen zu funktionieren):

CREATE OR REPLACE PROCEDURE sp_return_array()
      RETURNS VARIANT NOT NULL
      LANGUAGE JAVASCRIPT
      AS
      $$
      // This array will contain one error message (or an empty string) 
      // for each SQL command that we executed.
      var array_of_rows = [];

      // Artificially fake the error messages.
      array_of_rows.push("ERROR: The foo was barred.")
      array_of_rows.push("WARNING: A Carrington Event is predicted.")

      return array_of_rows;
      $$
      ;
CALL sp_return_array();
+-----------------------------------------------+
| SP_RETURN_ARRAY                               |
|-----------------------------------------------|
| [                                             |
|   "ERROR: The foo was barred.",               |
|   "WARNING: A Carrington Event is predicted." |
| ]                                             |
+-----------------------------------------------+
-- Now get the individual error messages, in order.
SELECT INDEX, VALUE 
    FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) AS res, LATERAL FLATTEN(INPUT => res.$1)
    ORDER BY index
    ;
+-------+---------------------------------------------+
| INDEX | VALUE                                       |
|-------+---------------------------------------------|
|     0 | "ERROR: The foo was barred."                |
|     1 | "WARNING: A Carrington Event is predicted." |
+-------+---------------------------------------------+

Beachten Sie, dass dies keine allgemeine Lösung ist. Die maximale Größe von ARRAY-Datentypen ist begrenzt, und Ihr gesamtes Resultset muss in ein einzelnes ARRAY passen.

Zurückgeben eines Resultset

In diesem Abschnitt wird das in Zurückgeben eines Arrays an Fehlermeldungen beschriebene vorherige Beispiel erweitert. Dieses Beispiel ist allgemeiner gehalten und erlaubt es Ihnen, ein Resultset aus einer Abfrage zurückzugeben.

Eine gespeicherte Prozedur gibt eine einzelne Zeile zurück, die eine einzelne Spalte enthält. Sie ist nicht dafür geeignet, ein Resultset zurückzugeben. Wenn Ihr Resultset jedoch klein genug ist, um in einen einzelnen Wert vom Typ VARIANT oder ARRAY zu passen, können Sie das Resultset von einer gespeicherten Prozedur mit zusätzlichem Code zurückgeben:

CREATE TABLE return_to_me(col_i INT, col_v VARCHAR);
INSERT INTO return_to_me (col_i, col_v) VALUES
    (1, 'Ariel'),
    (2, 'October'),
    (3, NULL),
    (NULL, 'Project');
-- Create the stored procedure that retrieves a result set and returns it.
CREATE OR REPLACE PROCEDURE sp_return_table(TABLE_NAME VARCHAR, COL_NAMES ARRAY)
      RETURNS VARIANT NOT NULL
      LANGUAGE JAVASCRIPT
      AS
      $$
      // This variable will hold a JSON data structure that holds ONE row.
      var row_as_json = {};
      // This array will contain all the rows.
      var array_of_rows = [];
      // This variable will hold a JSON data structure that we can return as
      // a VARIANT.
      // This will contain ALL the rows in a single "value".
      var table_as_json = {};

      // Run SQL statement(s) and get a resultSet.
      var command = "SELECT * FROM " + TABLE_NAME;
      var cmd1_dict = {sqlText: command};
      var stmt = snowflake.createStatement(cmd1_dict);
      var rs = stmt.execute();

      // Read each row and add it to the array we will return.
      var row_num = 1;
      while (rs.next())  {
        // Put each row in a variable of type JSON.
        row_as_json = {};
        // For each column in the row...
        for (var col_num = 0; col_num < COL_NAMES.length; col_num = col_num + 1) {
          var col_name = COL_NAMES[col_num];
          row_as_json[col_name] = rs.getColumnValue(col_num + 1);
          }
        // Add the row to the array of rows.
        array_of_rows.push(row_as_json);
        ++row_num;
        }
      // Put the array in a JSON variable (so it looks like a VARIANT to
      // Snowflake).  The key is "key1", and the value is the array that has
      // the rows we want.
      table_as_json = { "key1" : array_of_rows };

      // Return the rows to Snowflake, which expects a JSON-compatible VARIANT.
      return table_as_json;
      $$
      ;
CALL sp_return_table(
        -- Table name.
        'return_to_me',
        -- Array of column names.
        ARRAY_APPEND(TO_ARRAY('COL_I'), 'COL_V')
        );
+--------------------------+
| SP_RETURN_TABLE          |
|--------------------------|
| {                        |
|   "key1": [              |
|     {                    |
|       "COL_I": 1,        |
|       "COL_V": "Ariel"   |
|     },                   |
|     {                    |
|       "COL_I": 2,        |
|       "COL_V": "October" |
|     },                   |
|     {                    |
|       "COL_I": 3,        |
|       "COL_V": null      |
|     },                   |
|     {                    |
|       "COL_I": null,     |
|       "COL_V": "Project" |
|     }                    |
|   ]                      |
| }                        |
+--------------------------+
-- Use "ResultScan" to get the data from the stored procedure that
-- "did not return a result set".
-- Use "$1:key1" to get the value corresponding to the JSON key named "key1".
SELECT $1:key1 FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
+------------------------+
| $1:KEY1                |
|------------------------|
| [                      |
|   {                    |
|     "COL_I": 1,        |
|     "COL_V": "Ariel"   |
|   },                   |
|   {                    |
|     "COL_I": 2,        |
|     "COL_V": "October" |
|   },                   |
|   {                    |
|     "COL_I": 3,        |
|     "COL_V": null      |
|   },                   |
|   {                    |
|     "COL_I": null,     |
|     "COL_V": "Project" |
|   }                    |
| ]                      |
+------------------------+
-- Now get what we really want.
SELECT VALUE:COL_I AS col_i, value:COL_V AS col_v
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) AS res, LATERAL FLATTEN(input => res.$1)
  ORDER BY COL_I;
+-------+-----------+
| COL_I | COL_V     |
|-------+-----------|
| 1     | "Ariel"   |
| 2     | "October" |
| 3     | null      |
| null  | "Project" |
+-------+-----------+

Hier sehen Sie, wie Sie die beiden vorherigen Zeilen zu einer einzigen Zeile kombinieren:

CALL sp_return_table(
        -- Table name.
        'return_to_me',
        -- Array of column names.
        ARRAY_APPEND(TO_ARRAY('COL_I'), 'COL_V')
        );
+--------------------------+
| SP_RETURN_TABLE          |
|--------------------------|
| {                        |
|   "key1": [              |
|     {                    |
|       "COL_I": 1,        |
|       "COL_V": "Ariel"   |
|     },                   |
|     {                    |
|       "COL_I": 2,        |
|       "COL_V": "October" |
|     },                   |
|     {                    |
|       "COL_I": 3,        |
|       "COL_V": null      |
|     },                   |
|     {                    |
|       "COL_I": null,     |
|       "COL_V": "Project" |
|     }                    |
|   ]                      |
| }                        |
+--------------------------+
SELECT VALUE:COL_I AS col_i, value:COL_V AS col_v
       FROM (SELECT $1:key1 FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))) AS res,
            LATERAL FLATTEN(input => res.$1)
       ORDER BY COL_I;
+-------+-----------+
| COL_I | COL_V     |
|-------+-----------|
| 1     | "Ariel"   |
| 2     | "October" |
| 3     | null      |
| null  | "Project" |
+-------+-----------+

Der Einfachheit halber können Sie die vorhergehende Zeile in eine Ansicht einschließen. Diese Ansicht konvertiert außerdem die Zeichenfolge ‚null‘ in eine wahre NULL. Sie müssen die Ansicht nur einmal erstellen. Sie müssen jedoch die gespeicherte Prozedur jedes Mal aufrufen, bevor Sie aus dieser Ansicht auswählen. Denken Sie daran, dass der Aufruf von RESULT_SCAN in der Ansicht aus der letzten Anweisung stammt, die der folgende CALL sein muss:

CREATE VIEW stproc_view (col_i, col_v) AS 
  SELECT NULLIF(VALUE:COL_I::VARCHAR, 'null'::VARCHAR), 
         NULLIF(value:COL_V::VARCHAR, 'null'::VARCHAR)
    FROM (SELECT $1:key1 AS tbl FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))) AS res, 
         LATERAL FLATTEN(input => res.tbl);
CALL sp_return_table(
        -- Table name.
        'return_to_me',
        -- Array of column names.
        ARRAY_APPEND(TO_ARRAY('COL_I'), 'COL_V')
        );
+--------------------------+
| SP_RETURN_TABLE          |
|--------------------------|
| {                        |
|   "key1": [              |
|     {                    |
|       "COL_I": 1,        |
|       "COL_V": "Ariel"   |
|     },                   |
|     {                    |
|       "COL_I": 2,        |
|       "COL_V": "October" |
|     },                   |
|     {                    |
|       "COL_I": 3,        |
|       "COL_V": null      |
|     },                   |
|     {                    |
|       "COL_I": null,     |
|       "COL_V": "Project" |
|     }                    |
|   ]                      |
| }                        |
+--------------------------+
SELECT * 
    FROM stproc_view
    ORDER BY COL_I;
+-------+---------+
| COL_I | COL_V   |
|-------+---------|
| 1     | Ariel   |
| 2     | October |
| 3     | NULL    |
| NULL  | Project |
+-------+---------+

Sie können sie auch als echte Ansicht verwenden (d. h. eine Teilmenge davon auswählen):

CALL sp_return_table(
        -- Table name.
        'return_to_me',
        -- Array of column names.
        ARRAY_APPEND(TO_ARRAY('COL_I'), 'COL_V')
        );
+--------------------------+
| SP_RETURN_TABLE          |
|--------------------------|
| {                        |
|   "key1": [              |
|     {                    |
|       "COL_I": 1,        |
|       "COL_V": "Ariel"   |
|     },                   |
|     {                    |
|       "COL_I": 2,        |
|       "COL_V": "October" |
|     },                   |
|     {                    |
|       "COL_I": 3,        |
|       "COL_V": null      |
|     },                   |
|     {                    |
|       "COL_I": null,     |
|       "COL_V": "Project" |
|     }                    |
|   ]                      |
| }                        |
+--------------------------+
SELECT COL_V 
    FROM stproc_view
    WHERE COL_V IS NOT NULL
    ORDER BY COL_V;
+---------+
| COL_V   |
|---------|
| Ariel   |
| October |
| Project |
+---------+

Beachten Sie, dass dies keine universelle Lösung ist. Die maximale Größe der Datentypen VARIANT und ARRAY ist begrenzt, und Ihr gesamtes Resultset muss in eine einzelne VARIANT oder ARRAY passen.

Schutz der Privatsphäre

Dieses Beispiel veranschaulicht eine gespeicherte Prozedur, die für einen Onlinehändler nützlich ist. Diese gespeicherte Prozedur respektiert die Privatsphäre der Kunden und schützt gleichzeitig die berechtigten Interessen sowohl des Einzelhändlers als auch der Kunden. Wenn ein Kunde den Einzelhändler aus Datenschutzgründen dazu auffordert, seine Daten zu löschen, löscht diese gespeicherte Prozedur die meisten Kundendaten, belässt jedoch das Kaufprotokoll des Kunden, wenn eine der folgenden Bedingungen erfüllt ist:

  • Bei jedem gekauften Artikel, für den eine Garantie gilt, die noch nicht abgelaufen ist.

  • Wenn der Kunde noch Geld schuldet (oder dem Kunden eine Rückerstattung geschuldet wird).

In einer realistischeren Version würden einzelne Zeilen gelöscht, für die eine Zahlung erfolgt ist und die Garantie abgelaufen ist.

  1. Beginnen Sie, indem Sie die Tabellen erstellen und laden:

    create table reviews (customer_ID VARCHAR, review VARCHAR);
    create table purchase_history (customer_ID VARCHAR, price FLOAT, paid FLOAT,
                                   product_ID VARCHAR, purchase_date DATE);
    
    insert into purchase_history (customer_ID, price, paid, product_ID, purchase_date) values 
        (1, 19.99, 19.99, 'chocolate', '2018-06-17'::DATE),
        (2, 19.99,  0.00, 'chocolate', '2017-02-14'::DATE),
        (3, 19.99,  19.99, 'chocolate', '2017-03-19'::DATE);
    
    insert into reviews (customer_ID, review) values (1, 'Loved the milk chocolate!');
    insert into reviews (customer_ID, review) values (2, 'Loved the dark chocolate!');
    
  2. Erstellen Sie die gespeicherte Prozedur:

    create or replace procedure delete_nonessential_customer_data(customer_ID varchar)
        returns varchar not null
        language javascript
        as
        $$
    
        // If the customer posted reviews of products, delete those reviews.
        var sql_cmd = "DELETE FROM reviews WHERE customer_ID = " + CUSTOMER_ID;
        snowflake.execute( {sqlText: sql_cmd} );
    
        // Delete any other records not needed for warranty or payment info.
        // ...
    
        var result = "Deleted non-financial, non-warranty data for customer " + CUSTOMER_ID;
    
        // Find out if the customer has any net unpaid balance (or surplus/prepayment).
        sql_cmd = "SELECT SUM(price) - SUM(paid) FROM purchase_history WHERE customer_ID = " + CUSTOMER_ID;
        var stmt = snowflake.createStatement( {sqlText: sql_cmd} );
        var rs = stmt.execute();
        // There should be only one row, so should not need to iterate.
        rs.next();
        var net_amount_owed = rs.getColumnValue(1);
    
        // Look up the number of purchases still under warranty...
        var number_purchases_under_warranty = 0;
        // Assuming a 1-year warranty...
        sql_cmd = "SELECT COUNT(*) FROM purchase_history ";
        sql_cmd += "WHERE customer_ID = " + CUSTOMER_ID;
        // Can't use CURRENT_DATE() because that changes. So assume that today is 
        // always June 15, 2019.
        sql_cmd += "AND PURCHASE_DATE > dateadd(year, -1, '2019-06-15'::DATE)";
        var stmt = snowflake.createStatement( {sqlText: sql_cmd} );
        var rs = stmt.execute();
        // There should be only one row, so should not need to iterate.
        rs.next();
        number_purchases_under_warranty = rs.getColumnValue(1);
    
        // Check whether need to keep some purchase history data; if not, then delete the data.
        if (net_amount_owed == 0.0 && number_purchases_under_warranty == 0)  {
            // Delete the purchase history of this customer ...
            sql_cmd = "DELETE FROM purchase_history WHERE customer_ID = " + CUSTOMER_ID;
            snowflake.execute( {sqlText: sql_cmd} );
            // ... and delete anything else that that should be deleted.
            // ...
            result = "Deleted all data, including financial and warranty data, for customer " + CUSTOMER_ID;
            }
        return result;
        $$
        ;
    
  3. Zeigen Sie die Daten in den Tabellen an, bevor Sie irgendwelche dieser Daten löschen:

    SELECT * FROM reviews;
    +-------------+---------------------------+
    | CUSTOMER_ID | REVIEW                    |
    |-------------+---------------------------|
    | 1           | Loved the milk chocolate! |
    | 2           | Loved the dark chocolate! |
    +-------------+---------------------------+
    SELECT * FROM purchase_history;
    +-------------+-------+-------+------------+---------------+
    | CUSTOMER_ID | PRICE |  PAID | PRODUCT_ID | PURCHASE_DATE |
    |-------------+-------+-------+------------+---------------|
    | 1           | 19.99 | 19.99 | chocolate  | 2018-06-17    |
    | 2           | 19.99 |  0    | chocolate  | 2017-02-14    |
    | 3           | 19.99 | 19.99 | chocolate  | 2017-03-19    |
    +-------------+-------+-------+------------+---------------+
    
  4. Kunde #1 hat eine noch gültige Garantie. Die gespeicherte Prozedur löscht die von ihm veröffentlichten Bewertungskommentare, behält jedoch aufgrund der Garantie seinen Kaufdatensatz bei:

    call delete_nonessential_customer_data(1);
    +---------------------------------------------------------+
    | DELETE_NONESSENTIAL_CUSTOMER_DATA                       |
    |---------------------------------------------------------|
    | Deleted non-financial, non-warranty data for customer 1 |
    +---------------------------------------------------------+
    SELECT * FROM reviews;
    +-------------+---------------------------+
    | CUSTOMER_ID | REVIEW                    |
    |-------------+---------------------------|
    | 2           | Loved the dark chocolate! |
    +-------------+---------------------------+
    SELECT * FROM purchase_history;
    +-------------+-------+-------+------------+---------------+
    | CUSTOMER_ID | PRICE |  PAID | PRODUCT_ID | PURCHASE_DATE |
    |-------------+-------+-------+------------+---------------|
    | 1           | 19.99 | 19.99 | chocolate  | 2018-06-17    |
    | 2           | 19.99 |  0    | chocolate  | 2017-02-14    |
    | 3           | 19.99 | 19.99 | chocolate  | 2017-03-19    |
    +-------------+-------+-------+------------+---------------+
    
  5. Kunde #2 schuldet noch Geld. Die gespeicherte Prozedur löscht seine Bewertungskommentare, behält jedoch seinen Kaufdatensatz bei:

    call delete_nonessential_customer_data(2);
    +---------------------------------------------------------+
    | DELETE_NONESSENTIAL_CUSTOMER_DATA                       |
    |---------------------------------------------------------|
    | Deleted non-financial, non-warranty data for customer 2 |
    +---------------------------------------------------------+
    SELECT * FROM reviews;
    +-------------+--------+
    | CUSTOMER_ID | REVIEW |
    |-------------+--------|
    +-------------+--------+
    SELECT * FROM purchase_history;
    +-------------+-------+-------+------------+---------------+
    | CUSTOMER_ID | PRICE |  PAID | PRODUCT_ID | PURCHASE_DATE |
    |-------------+-------+-------+------------+---------------|
    | 1           | 19.99 | 19.99 | chocolate  | 2018-06-17    |
    | 2           | 19.99 |  0    | chocolate  | 2017-02-14    |
    | 3           | 19.99 | 19.99 | chocolate  | 2017-03-19    |
    +-------------+-------+-------+------------+---------------+
    
  6. Kunde #3 schuldet kein Geld (und wird auch kein Geld geschuldet). Seine Garantie ist abgelaufen, sodass die gespeicherte Prozedur sowohl die Bewertungskommentare als auch die Kaufdatensätze löscht:

    call delete_nonessential_customer_data(3);
    +-------------------------------------------------------------------------+
    | DELETE_NONESSENTIAL_CUSTOMER_DATA                                       |
    |-------------------------------------------------------------------------|
    | Deleted all data, including financial and warranty data, for customer 3 |
    +-------------------------------------------------------------------------+
    SELECT * FROM reviews;
    +-------------+--------+
    | CUSTOMER_ID | REVIEW |
    |-------------+--------|
    +-------------+--------+
    SELECT * FROM purchase_history;
    +-------------+-------+-------+------------+---------------+
    | CUSTOMER_ID | PRICE |  PAID | PRODUCT_ID | PURCHASE_DATE |
    |-------------+-------+-------+------------+---------------|
    | 1           | 19.99 | 19.99 | chocolate  | 2018-06-17    |
    | 2           | 19.99 |  0    | chocolate  | 2017-02-14    |
    +-------------+-------+-------+------------+---------------+
    

Verwenden von Sitzungsvariablen bei gespeicherten Prozeduren mit Aufrufer- und Eigentümerrechten

Diese Beispiele veranschaulichen einen der Hauptunterschiede zwischen gespeicherten Prozeduren mit Aufruferrechten und gespeicherten Prozeduren mit Eigentümerrechten. Sie versuchen, Sitzungsvariablen auf zwei Arten zu verwenden:

  • Legen Sie eine Sitzungsvariable fest, bevor Sie die gespeicherte Prozedur aufrufen, und verwenden Sie dann die Sitzungsvariable innerhalb der gespeicherten Prozedur.

  • Legen Sie eine Sitzungsvariable innerhalb der gespeicherten Prozedur fest, und verwenden Sie diese, nachdem Sie von den gespeicherten Prozeduren zurückgekehrt sind.

Sowohl die Verwendung der Sitzungsvariablen als auch das Festlegen der Sitzungsvariablen funktionieren in der gespeicherten Prozedur mit Aufruferrechten ordnungsgemäß. Beide schlagen fehl, wenn eine gespeicherte Prozedur mit Eigentümerrechten verwendet wird, selbst wenn der Aufrufer der Eigentümer ist.

Gespeicherte Prozedur mit Aufruferrechten

Das folgende Beispiel zeigt eine gespeicherte Prozedur mit Aufruferrechten.

  1. Erstellen und laden Sie eine Tabelle:

    create table sv_table (f float);
    insert into sv_table (f) values (49), (51);
    
  2. Legen Sie eine Sitzungsvariable fest:

    set SESSION_VAR1 = 50;
    
  3. Erstellen Sie eine gespeicherte Prozedur mit Aufruferrechten, die eine Sitzungsvariable verwendet und eine weitere definiert:

    create procedure session_var_user()
      returns float
      language javascript
      EXECUTE AS CALLER
      as
      $$
      // Set the second session variable
      var stmt = snowflake.createStatement(
          {sqlText: "set SESSION_VAR2 = 'I was set inside the StProc.'"}
          );
      var rs = stmt.execute();  // we ignore the result in this case
      // Run a query using the first session variable
      stmt = snowflake.createStatement(
          {sqlText: "select f from sv_table where f > $SESSION_VAR1"}
          );
      rs = stmt.execute();
      rs.next();
      var output = rs.getColumnValue(1);
      return output;
      $$
      ;
    
  4. Rufen Sie die Prozedur auf:

    CALL session_var_user();
    +------------------+
    | SESSION_VAR_USER |
    |------------------|
    |               51 |
    +------------------+
    
  5. Zeigen Sie den Wert der Sitzungsvariablen an, die in der gespeicherten Prozedur festgelegt wurde:

    SELECT $SESSION_VAR2;
    +------------------------------+
    | $SESSION_VAR2                |
    |------------------------------|
    | I was set inside the StProc. |
    +------------------------------+
    

Bemerkung

Obwohl Sie eine Sitzungsvariable in einer gespeicherten Prozedur festlegen und nach dem Ende der Prozedur gesetzt lassen können, wird dies von Snowflake nicht empfohlen.

Gespeicherte Prozedur mit Eigentümerrechten

Das folgende Beispiel zeigt eine gespeicherte Prozedur mit Eigentümerrechten.

  1. Erstellen Sie eine gespeicherte Prozedur mit Eigentümerrechten, die eine Sitzungsvariable verwendet:

    create procedure cannot_use_session_vars()
      returns float
      language javascript
      EXECUTE AS OWNER
      as
      $$
      // Run a query using the first session variable
      var stmt = snowflake.createStatement(
          {sqlText: "select f from sv_table where f > $SESSION_VAR1"}
          );
      var rs = stmt.execute();
      rs.next();
      var output = rs.getColumnValue(1);
      return output;
      $$
      ;
    
  2. Rufen Sie die Prozedur auf (sie sollte fehlschlagen):

    100183 (P0000): Execution error in store procedure CANNOT_USE_SESSION_VARS:
    Use of session variable '$SESSION_VAR1' is not allowed in owners rights stored procedure
    At Statement.execute, line 6 position 16
    
  3. Erstellen Sie eine gespeicherte Prozedur mit Eigentümerrechten, die versucht, eine Sitzungsvariable festzulegen:

    create procedure cannot_set_session_vars()
      returns float
      language javascript
      EXECUTE AS OWNER
      as
      $$
      // Set the second session variable
      var stmt = snowflake.createStatement(
          {sqlText: "set SESSION_VAR2 = 'I was set inside the StProc.'"}
          );
      var rs = stmt.execute();  // we ignore the result in this case
      return 3.0;   // dummy value.
      $$
      ;
    
  4. Rufen Sie die Prozedur auf (sie sollte fehlschlagen):

    100183 (P0000): Execution error in store procedure CANNOT_SET_SESSION_VARS:
    Stored procedure execution error: Unsupported statement type 'SET'.
    At Statement.execute, line 6 position 16
    

Problembehandlung

Eine allgemeine Methode zur Problembehandlung besteht darin, einen JavaScript-Try/Catch-Block zu verwenden, um den Fehler abzufangen und Fehlerinformationen anzuzeigen. Das Fehlerobjekt enthält:

  • Fehlercode

  • Fehlermeldung.

  • Fehlerstatus.

  • Stapel-Trace zum Zeitpunkt des Fehlers.

Weitere Informationen, einschließlich eines Beispiels zur Verwendung dieser Informationen, finden Sie unter Abfangen eines Fehlers mit Try/Catch (unter diesem Thema).

In den folgenden Abschnitten finden Sie zusätzliche Empfehlungen zum Debuggen bestimmter Probleme.

Gespeicherte Prozedur oder UDF gibt unerwartet NULL zurück

Ursache

Ihre gespeicherte Prozedur/UDF verfügt über einen Parameter, und innerhalb der Prozedur/UDF wird auf den Parameter durch seinen Namen in Kleinbuchstaben verwiesen; Snowflake hat den Namen jedoch automatisch in Großbuchstaben konvertiert.

Lösung

Entweder:

  • Verwenden Sie Großbuchstaben für den Variablennamen im JavaScript-Code; oder

  • setzen Sie den Variablennamen im SQL-Code in doppelte Anführungszeichen.

Weitere Details dazu finden Sie unter JavaScript-Argumente und zurückgegebene Werte.

Ausführung der gespeicherten Prozedur wird nie beendet

Ursache

Möglicherweise enthält Ihr JavaScript-Code eine Endlosschleife.

Lösung

Suchen Sie nach Endlosschleifen, und beseitigen Sie diese.

Fehler: Failed: empty argument passed

Ursache

Ihre gespeicherte Prozedur enthält möglicherweise „sqltext“, obwohl sie „sqlText“ enthalten soll (im ersten Fall in Kleinbuchstaben geschrieben, im zweiten Fall in gemischten Groß- und Kleinbuchstaben).

Lösung

Verwenden Sie „sqlText“.

Fehler: JavaScript out of memory error: UDF thread memory limit exceeded

Ursache

Möglicherweise enthält Ihr JavaScript-Code eine Endlosschleife.

Lösung

Suchen Sie nach Endlosschleifen, und beseitigen Sie diese. Stellen Sie insbesondere sicher, dass Sie den Aufruf der nächsten Zeile beenden, wenn das Resultset aufgebraucht ist (d. h. wenn resultSet.next() false zurückgibt).