Schreiben von gespeicherten Prozeduren in JavaScript¶

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

  Copy

• Verwenden Sie um die Zeichenfolge herum Backticks (einfache Backquotes) anstelle von doppelten Anführungszeichen. Beispiel:

  var sql_command = `SELECT *
                         FROM table1;`;
  

  Copy

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

  Copy

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

Copy

select hash(1) AS a, 
       num_test(hash(1)) AS b, 
       a - b;
+----------------------+----------------------+------------+
|                    A | B                    |      A - B |
|----------------------+----------------------+------------|
| -4730168494964875235 | -4730168494964875000 | -235.00000 |
+----------------------+----------------------+------------+

Copy

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.

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.

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

Copy

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

Copy

CALL right_bind('2019-09-16 01:02:03');
+------------+
| RIGHT_BIND |
|------------|
| True       |
+------------+

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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.

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

Copy

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

Copy

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

   Copy

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

   Copy

3. Rufen Sie die gespeicherte Prozedur auf:

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

   Copy

4. Bestätigen Sie, dass die gespeicherte Prozedur die Zeile eingefügt hat:

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

   Copy

Im folgenden Beispiel wird ein Ergebnis abgerufen:

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

   Copy

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

   Copy

3. Prüfen Sie selbstständig, ob Sie die richtige Zahl erhalten haben:

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

   Copy

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

Copy

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

Copy

call recursive_stproc(4.0::FLOAT);
+------------------+
| RECURSIVE_STPROC |
|------------------|
| 4:3:2:1:0:       |
+------------------+

Copy

SELECT * 
    FROM stproc_test_table2
    ORDER BY col1;
+------+
| COL1 |
|------|
|    0 |
|    1 |
|    2 |
|    3 |
|    4 |
+------+

Copy

Dynamisches Erstellen einer SQL-Anweisung¶

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

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

   Copy

2. Rufen Sie die gespeicherte Prozedur auf:

   call get_row_count('stproc_test_table1');
   +---------------+
   | GET_ROW_COUNT |
   |---------------|
   |             3 |
   +---------------+
   

   Copy

3. Zeigen Sie die Ergebnisse von select count(*) für dieselbe Tabelle an:

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

   Copy

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

Copy

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

Copy

call get_column_scale(1);
+------------------+
| GET_COLUMN_SCALE |
|------------------|
|                0 |
+------------------+

Copy

call get_column_scale(2);
+------------------+
| GET_COLUMN_SCALE |
|------------------|
|                4 |
+------------------+

Copy

call get_column_scale(3);
+------------------+
| GET_COLUMN_SCALE |
|------------------|
|                0 |
+------------------+

Copy

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

   Copy

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

   Copy

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

   Copy

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

   Copy

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:

• Protokollierung von Meldungen in JavaScript

• Ausgeben von Ablaufverfolgungsereignissen in JavaScript

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 der Tabelle:

   CREATE TABLE western_provinces(ID INT, province VARCHAR);
   

   Copy

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

   Copy

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

   Copy

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

   Copy

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

   Copy

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

   Copy

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

   Copy

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

   Copy

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

   Copy

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

   Copy

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

   Copy

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

   Copy

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

   Copy

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

   Copy

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

   Copy

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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

   Copy

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

   Copy

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

   Copy

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

   Copy

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

   Copy

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

   Copy

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

   Copy

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