Schreiben von gespeicherten Prozeduren in JavaScript¶

Unter diesem Thema wird erklärt, wie Sie JavaScript-Code für eine gespeicherte Prozedur schreiben.

Bemerkung

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

Sie können während der Ausführung Ihres Handler-Codes Protokoll- und Ablaufverfolgungsdaten erfassen. Weitere Informationen dazu finden Sie unter Protokollierung, Ablaufverfolgung und Metriken.

Erläuterungen zur JavaScript-API¶

Die JavaScript-API für gespeicherte Prozeduren 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 in JavaScript 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.

Weitere Informationen zur Zuordnung von JavaScript-Datentypen zu SQL-Datentypen in Snowflake finden Sie unter Zuordnung von Datentypen zwischen SQL und JavaScript.

Allgemeine Hinweise¶

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;"

Hinweise zu gespeicherten Prozeduren in JavaScript¶

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.

Sie können Ihre SQL-Anweisungen innerhalb eines Try-Blocks ausführen. Wenn ein Fehler auftritt, kann Ihr Catch-Block alle Anweisungen zurücksetzen (wenn Sie die Anweisungen in eine Transaktion setzen). Der Abschnitt „Beispiele“ enthält ein Beispiel für das Rollback einer Transaktion in einer gespeicherten Prozedur.

Einschränkungen bei gespeicherten Prozeduren¶

Gespeicherte Prozeduren unterliegen den folgenden Einschränkungen:

Der JavaScript-Code kann die JavaScript-Funktion eval() nicht aufrufen.
Gespeicherte JavaScript-Prozeduren unterstützen den Zugriff auf die JavaScript-Standardbibliothek. Beachten Sie, dass dadurch viele Objekte und Methoden ausgeschlossen werden, die normalerweise von Browsern bereitgestellt werden. Es gibt keinen Mechanismus zum Importieren, Einschließen oder Aufrufen zusätzlicher Bibliotheken. Durch das Zulassen von Bibliotheken, die von Drittanbietern stammen, können Sicherheitslücken entstehen.
JavaScript-Code wird in einem eingeschränkten Modul ausgeführt, wodurch Systemaufrufe aus dem JavaScript-Kontext (z. B. kein Netzwerk- und Festplattenzugriff) verhindert werden und die für das Modul verfügbare Systemressourcen, insbesondere der Arbeitsspeicher, eingeschränkt 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
AS
$$
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.

Überladen der Namen gespeicherter Prozeduren¶

Informationen zum Überladen von Namen und zu Namenskonventionen finden Sie unter Benennen und Überladen von Prozeduren und UDFs.

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 Binden von 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.

Codelänge¶

Snowflake begrenzt die maximale Größe des JavaScript-Quellcodes im Textteil einer gespeicherten JavaScript-Prozedur. Snowflake empfiehlt eine Begrenzung auf 100 KB. (Da der Code in komprimierter Form gespeichert ist, hängt das genaue Limit von der Komprimierbarkeit des Codes ab.)

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.

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:

Erstellen Sie eine Tabelle:
CREATE TABLE stproc_test_table1 (num_col1 numeric(14,7));

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.
        }
    $$
    ;

Rufen Sie die gespeicherte Prozedur auf:

call stproc1(5.14::FLOAT);
+------------+
| STPROC1    |
|------------|
| Succeeded. |
+------------+

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:

Erstellen einer Prozedur zum Zählen der Zeilen einer Tabelle (entspricht select count(*) from table):

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;
  $$
  ;

Fragen Sie die gespeicherte Prozedur, wie viele Zeilen sich in der Tabelle befinden:

call get_row_count('stproc_test_table1');
+---------------+
| GET_ROW_COUNT |
|---------------|
|             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.

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 table_name):

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;
    $$
    ;

Rufen Sie die gespeicherte Prozedur auf:

call get_row_count('stproc_test_table1');
+---------------+
| GET_ROW_COUNT |
|---------------|
|             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:

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;
      $$
      ;

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: 1003                                      |
|   State: 42000                                          |
|   Message: SQL compilation error:                       |
| syntax error line 1 at position 0 unexpected 'Invalid'. |
| Stack Trace:                                            |
| Snowflake.execute, line 4 position 20                   |
+---------------------------------------------------------+

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

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;
    }
$$;

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! |
+--------------------------------+

Verwenden von Transaktionen in gespeicherten Prozeduren¶

Im folgenden Beispiel werden mehrere zusammenhängende Anweisungen in eine Transaktion gepackt und try/catch für Commit oder Rollback verwendet. Der Parameter force_failure ermöglicht dem Aufrufer die Wahl zwischen erfolgreicher Ausführung und absichtlichem Fehler.

-- Create the procedure
CREATE OR REPLACE PROCEDURE cleanup(force_failure VARCHAR)
  RETURNS VARCHAR NOT NULL
  LANGUAGE JAVASCRIPT
  AS
  $$
  var result = "";
  snowflake.execute( {sqlText: "BEGIN WORK;"} );
  try {
      snowflake.execute( {sqlText: "DELETE FROM child;"} );
      snowflake.execute( {sqlText: "DELETE FROM parent;"} );
      if (FORCE_FAILURE === "fail")  {
          // To see what happens if there is a failure/rollback,
          snowflake.execute( {sqlText: "DELETE FROM no_such_table;"} );
          }
      snowflake.execute( {sqlText: "COMMIT WORK;"} );
      result = "Succeeded";
      }
  catch (err)  {
      snowflake.execute( {sqlText: "ROLLBACK WORK;"} );
      return "Failed: " + err;   // Return a success/error indicator.
      }
  return result;
  $$
  ;

CALL cleanup('fail');

CALL cleanup('do not fail');

Protokollieren von Fehlern¶

Sie können Protokoll- und Ablaufverfolgungsdaten von JavaScript-Handler-Code erfassen, indem Sie das snowflake-Objekt der JavaScript-API verwenden. Damit werden Protokollmeldungen und Ablaufverfolgungsdaten in einer Ereignistabelle gespeichert, die Sie mit Abfragen analysieren können.

Weitere Informationen dazu finden Sie unter:

Verwenden von RESULT_SCAN zum Abrufen des Ergebnisses einer gespeicherten Prozedur¶

Im folgenden Beispiel wird die Verwendung der Funktion RESULT_SCAN zum Abrufen und Verarbeiten des Ergebnisses einer CALL-Anweisung gezeigt:

Erstellen und Laden der Tabelle:

CREATE TABLE western_provinces(ID INT, province VARCHAR);

INSERT INTO western_provinces(ID, province) VALUES
    (1, 'Alberta'),
    (2, 'British Columbia'),
    (3, 'Manitoba')
    ;

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;
  $$
  ;

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.

Tipp

Sie können auch den Pipe-Operator (->>) anstelle der Funktion RESULT_SCAN zum Ausführen einer CALL-Anweisung und Verarbeitung des Resultsets mit einem einzigen -Befehl verwenden.

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

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

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 |
+-------------------------+

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:

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

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         |
+---------------------+

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

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 |
+----------+

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 |
+-------------------------+

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.

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

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 should be deleted.
        // ...
        result = "Deleted all data, including financial and warranty data, for customer " + CUSTOMER_ID;
        }
    return result;
    $$
    ;

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    |
+-------------+-------+-------+------------+---------------+

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    |
+-------------+-------+-------+------------+---------------+

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    |
+-------------+-------+-------+------------+---------------+

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.

Erstellen und laden Sie eine Tabelle:

create table sv_table (f float);
insert into sv_table (f) values (49), (51);

Legen Sie eine Sitzungsvariable fest:
set SESSION_VAR1 = 50;

Erstellen Sie eine gespeicherte Prozedur mit Aufruferrechten, die eine Sitzungsvariable nutzt und eine andere 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;
  $$
  ;

Rufen Sie die Prozedur auf:

CALL session_var_user();
+------------------+
| SESSION_VAR_USER |
|------------------|
|               51 |
+------------------+

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.

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;
  $$
  ;

Rufen Sie die Prozedur auf (sie sollte fehlschlagen):
CALL cannot_use_session_vars();

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.
  $$
  ;

Rufen Sie die Prozedur auf (sie sollte fehlschlagen):
CALL cannot_set_session_vars();

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.
Stacktrace 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 „sqlText“ erforderlich wäre (im ersten Fall in Kleinbuchstaben geschrieben, im zweiten Fall mit gemischter Groß-/Kleinschreibung).
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).

Schreiben von gespeicherten Prozeduren in JavaScript¶

Erläuterungen zur JavaScript-API¶

Zuordnen von SQL- und JavaScript-Datentypen¶

Allgemeine Hinweise¶

Zeilenfortsetzung¶

Hinweise zu gespeicherten Prozeduren in JavaScript¶

JavaScript-Nummernbereich¶

Fehlerbehandlung in JavaScript¶

Einschränkungen bei gespeicherten Prozeduren¶

Groß-/Kleinschreibung in JavaScript-Argumenten¶

JavaScript-Trennzeichen¶

Überladen der Namen gespeicherter Prozeduren¶

Binden von Variablen¶

Code-Anforderungen¶

Codelänge¶

Laufzeitfehler¶

Unterstützung für dynamische SQL¶

Synchrone API¶

Beispiele¶

Grundlegende Beispiele¶

Beispiel für eine rekursive gespeicherte Prozedur¶

Dynamisches Erstellen einer SQL-Anweisung¶

Abrufen von Metadaten zum Resultset¶

Abfangen eines Fehlers mit Try/Catch¶

Verwenden von Transaktionen in gespeicherten Prozeduren¶

Protokollieren von Fehlern¶

Verwenden von RESULT_SCAN zum Abrufen des Ergebnisses einer gespeicherten Prozedur¶

Zurückgeben eines Arrays an Fehlermeldungen¶

Zurückgeben eines Resultset¶

Schutz der Privatsphäre¶

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

Gespeicherte Prozedur mit Aufruferrechten¶

Gespeicherte Prozedur mit Eigentümerrechten¶

Problembehandlung¶

Gespeicherte Prozedur oder UDF gibt unerwartet NULL zurück¶

Ausführung der gespeicherten Prozedur wird nie beendet¶

Fehler: Failed: empty argument passed¶

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

Fehler: `Failed: empty argument passed`¶

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