Verwenden von Cursors¶

Einführung¶

Um Daten aus den Ergebnissen einer Abfrage abzurufen, können Sie einen Cursor verwenden. Um über die Zeilen in den Ergebnissen zu iterieren, können Sie einen Cursor in Schleifen verwenden.

Um einen Cursor zu verwenden, gehen Sie wie folgt vor:

1. Das Deklarieren eines Cursors erfolgt im Abschnitt DECLARE. Die Deklaration enthält die Abfrage für den Cursor.

2. Bevor Sie den Cursor zum ersten Mal verwenden, führen Sie den Befehl OPEN zum Öffnen des Cursors aus. Damit wird die Abfrage ausgeführt, und die Ergebnisse werden in den Cursor geladen.

3. Durch Ausführen des Befehls FETCH können Sie eine oder mehrere Zeilen abrufen und diese Zeilen verarbeiten.

4. Wenn Sie mit den Ergebnissen fertig sind oder den Cursor nicht mehr benötigen, führen Sie den Befehl CLOSE zum Schließen des Cursors aus.

Einrichten der Daten für die Beispiele¶

Für die Beispiele in diesem Abschnitt werden die folgenden Daten verwendet:

CREATE OR REPLACE TABLE invoices (id INTEGER, price NUMBER(12, 2));

INSERT INTO invoices (id, price) VALUES
  (1, 11.11),
  (2, 22.22);

Deklarieren eines Cursors¶

Sie können einen Cursor für eine SELECT-Anweisung oder eine RESULTSET-Anweisung deklarieren.

Sie deklarieren einen Cursor im Abschnitt DECLARE eines Blocks oder im Abschnitt BEGIN … END des Blocks:

• Verwenden Sie innerhalb des Abschnitts DECLARE die unter Syntax der Cursordeklaration beschriebene Syntax.

  So können Sie beispielsweise einen Cursor für eine Abfrage deklarieren:

  DECLARE
    ...
    c1 CURSOR FOR SELECT price FROM invoices;
  

  Copy

  So können Sie einen Cursor für ein RESULTSET deklarieren:

  DECLARE
    ...
    res RESULTSET DEFAULT (SELECT price FROM invoices);
    c1 CURSOR FOR res;
  

  Copy

• Innerhalb des BEGIN … END-Blocks ist die unter Syntax der Cursorzuweisung beschriebene Syntax zu verwenden. Beispiel:

  BEGIN
    ...
    LET c1 CURSOR FOR SELECT price FROM invoices;
  

  Copy

In der SELECT-Anweisung können Sie Bindungsparameter (?-Zeichen) angeben, die Sie beim Öffnen des Cursors an Variablen binden können. Um Variablen an die Parameter zu binden, geben Sie die Variablen in der USING-Klausel des OPEN-Befehls an. Beispiel:

DECLARE
  id INTEGER DEFAULT 0;
  minimum_price NUMBER(13,2) DEFAULT 22.00;
  maximum_price NUMBER(13,2) DEFAULT 33.00;
  c1 CURSOR FOR SELECT id FROM invoices WHERE price > ? AND price < ?;
BEGIN
  OPEN c1 USING (minimum_price, maximum_price);
  FETCH c1 INTO id;
  RETURN id;
END;

Hinweis: Wenn Sie SnowSQL, die Classic Console oder die execute_stream- oder execute_string-Methode im Python-Konnektor-Code verwenden, benutzen Sie stattdessen das folgende Beispiel (siehe Verwenden von Snowflake Scripting in SnowSQL, in der Classic Console und im Python-Konnektor):

EXECUTE IMMEDIATE $$
DECLARE
  id INTEGER DEFAULT 0;
  minimum_price NUMBER(13,2) DEFAULT 22.00;
  maximum_price NUMBER(13,2) DEFAULT 33.00;
  c1 CURSOR FOR SELECT id FROM invoices WHERE price > ? AND price < ?;
BEGIN
  OPEN c1 USING (minimum_price, maximum_price);
  FETCH c1 INTO id;
  RETURN id;
END;
$$
;

+-----------------+
| anonymous block |
|-----------------|
|               2 |
+-----------------+

Öffnen eines Cursors¶

Obwohl die Anweisung zum Deklarieren eines Cursors auch die mit diesem Cursor verbundene Abfrage definiert, wird die Abfrage erst ausgeführt, wenn Sie den Cursor durch Ausführen des Befehls OPEN öffnen. Beispiel:

OPEN c1;

Wenn Ihre Abfrage Bindungsparameter (?-Zeichen) enthält, fügen Sie eine USING-Klausel hinzu, um die Liste der Variablen anzugeben, die an diese Parameter gebunden werden sollen. Beispiel:

LET c1 CURSOR FOR SELECT id FROM invoices WHERE price > ? AND price < ?;
OPEN c1 USING (minimum_price, maximum_price);

Durch Öffnen des Cursors wird die Abfrage ausgeführt, die angegebenen Zeilen im Cursor werden abgerufen, und es wird ein interner Zeiger eingerichtet, der auf die erste Zeile zeigt. Sie können den FETCH-Befehl verwenden, um mit dem Cursor einzelne Zeilen abzurufen (zu lesen).

Wenn die Abfragedefinition auf der äußersten Ebene kein ORDER BY enthält, hat das Resultset wie bei jeder SQL-Abfrage keine definierte Reihenfolge. Wenn das Resultset für den Cursor erstellt ist, bleibt die Reihenfolge der Zeilen so lange bestehen, bis der Cursor geschlossen wird. Wenn Sie den Cursor erneut deklarieren oder öffnen, können die Zeilen in einer anderen Reihenfolge stehen. Wenn Sie den Cursor schließen und die zugrunde liegende Tabelle aktualisiert wird, bevor Sie den Cursor wieder öffnen, kann sich das Resultset ändern.

Verwenden eines Cursors zum Abrufen von Daten¶

Verwenden Sie den Befehl FETCH, um die aktuelle Zeile aus dem Resultset abzurufen und den internen Zeiger für die aktuelle Zeile so zu verschieben, dass er auf die nächste Zeile im Resultset zeigt.

In der INTO-Klausel geben Sie die Variablen an, die die Werte der Zeile aufnehmen sollen.

Beispiel:

FETCH c1 INTO var_for_column_value;

Wenn die Anzahl der Variablen nicht mit der Anzahl der Ausdrücke in der SELECT-Klausel der Cursor-Deklaration übereinstimmt, versucht Snowflake, die Variablen mit den Spalten nach Position abzugleichen:

• Wenn es mehr Variablen als Spalten gibt, lässt Snowflake die verbleibenden Variablen unbesetzt.

• Wenn es mehr Spalten als Variablen gibt, ignoriert Snowflake die restlichen Spalten.

Jeder nachfolgende FETCH-Befehl, den Sie ausführen, ruft die nächste Zeile ab, bis die letzte Zeile abgerufen wurde. Wenn Sie versuchen, mit FETCH eine Zeile nach der letzten Zeile abzurufen, erhalten Sie NULL-Werte.

Bei einem RESULTSET oder Cursor werden zum Zeitpunkt der Abfrageausführung nicht unbedingt alle Zeilen des Resultsets zwischengespeichert. Bei FETCH-Operationen können Latenzen auftreten.

Verwenden eines Cursors zum Abrufen eines GEOGRAPHY-Werts¶

Wenn die Ergebnisse eine Spalte vom Typ GEOGRAPHY enthalten, ist der Werte in der Spalte vom Typ OBJECT und nicht vom Typ GEOGRAPHY. Das bedeutet, dass Sie diesen Wert nicht direkt an Geodatenfunktionen übergeben können, die nur ein GEOGRAPHY-Objekt als Eingabe akzeptieren:

DECLARE
  geohash_value VARCHAR;
BEGIN
  LET res RESULTSET := (SELECT TO_GEOGRAPHY('POINT(1 1)') AS GEOGRAPHY_VALUE);
  LET cur CURSOR FOR res;
  FOR row_variable IN cur DO
    geohash_value := ST_GEOHASH(row_variable.geography_value);
  END FOR;
  RETURN geohash_value;
END;

001044 (42P13): Uncaught exception of type 'EXPRESSION_ERROR' on line 7 at position 21 : SQL compilation error: ...
Invalid argument types for function 'ST_GEOHASH': (OBJECT)

Um das Problem zu umgehen, müssen Sie den Spaltenwert in den Typ GEOGRAPHY umwandeln:

geohash_value := ST_GEOHASH(TO_GEOGRAPHY(row_variable.geography_value));

Zurückgeben einer Tabelle für einen Cursor¶

Wenn Sie eine Tabelle mit Daten von einem Cursor zurückgeben müssen, können Sie den Cursor an RESULTSET_FROM_CURSOR(cursor) übergeben, was Sie wiederum an TABLE(...) übergeben können.

Der folgende Block gibt eine Tabelle mit Daten aus einem Cursor zurück:

DECLARE
  c1 CURSOR FOR SELECT * FROM invoices;
BEGIN
  OPEN c1;
  RETURN TABLE(RESULTSET_FROM_CURSOR(c1));
END;

Hinweis: Wenn Sie SnowSQL, die Classic Console oder die execute_stream- oder execute_string-Methode im Python-Konnektor-Code verwenden, benutzen Sie stattdessen das folgende Beispiel (siehe Verwenden von Snowflake Scripting in SnowSQL, in der Classic Console und im Python-Konnektor):

EXECUTE IMMEDIATE $$
DECLARE
  c1 CURSOR FOR SELECT * FROM invoices;
BEGIN
  OPEN c1;
  RETURN TABLE(RESULTSET_FROM_CURSOR(c1));
END;
$$
;

+----+-------+
| ID | PRICE |
|----+-------|
|  1 | 11.11 |
|  2 | 22.22 |
+----+-------+

Auch wenn Sie den Cursor bereits verwendet haben, um Zeilen abzurufen, gibt RESULTSET_FROM_CURSOR dennoch ein RESULTSET zurück, das alle Zeilen enthält und nicht nur die Zeilen, die mit dem internen Zeilenzeiger beginnen.

Wie oben gezeigt, ruft das Beispiel die erste Zeile ab und setzt den internen Zeilenzeiger auf die zweite Zeile. RESULTSET_FROM_CURSOR gibt ein RESULTSET zurück, das beide Zeilen enthält (nicht nur die zweite Zeile).

Schließen eines Cursors¶

Wenn Sie mit dem Resultset fertig sind, schließen Sie den Cursor, indem Sie den Befehl CLOSE ausführen. Beispiel:

CLOSE c1;

Sie können den Befehl FETCH nicht auf einem geschlossenen Cursor ausführen.

Außerdem wird der aktuelle Zeilenzeiger beim Schließen des Cursors ungültig. Wenn Sie den Cursor erneut öffnen, zeigt der Zeiger auf die erste Zeile des neuen Resultsets.

Beispiel für die Verwendung eines Cursors¶

In diesem Beispiel werden die Daten verwendet, die Sie in Einrichten der Daten für die Beispiele eingerichtet haben.

Hier ist ein anonymer Block, der einen Cursor verwendet, um zwei Zeilen zu lesen und die Preise in diesen Zeilen zu summieren:

DECLARE
  row_price FLOAT;
  total_price FLOAT;
  c1 CURSOR FOR SELECT price FROM invoices;
BEGIN
  row_price := 0.0;
  total_price := 0.0;
  OPEN c1;
  FETCH c1 INTO row_price;
  total_price := total_price + row_price;
  FETCH c1 INTO row_price;
  total_price := total_price + row_price;
  CLOSE c1;
  RETURN total_price;
END;

Hinweis: Wenn Sie SnowSQL, die Classic Console oder die execute_stream- oder execute_string-Methode im Python-Konnektor-Code verwenden, benutzen Sie stattdessen das folgende Beispiel (siehe Verwenden von Snowflake Scripting in SnowSQL, in der Classic Console und im Python-Konnektor):

EXECUTE IMMEDIATE $$
DECLARE
    row_price FLOAT;
    total_price FLOAT;
    c1 CURSOR FOR SELECT price FROM invoices;
BEGIN
    row_price := 0.0;
    total_price := 0.0;
    OPEN c1;
    FETCH c1 INTO row_price;
    total_price := total_price + row_price;
    FETCH c1 INTO row_price;
    total_price := total_price + row_price;
    CLOSE c1;
    RETURN total_price;
END;
$$
;

+-----------------+
| anonymous block |
|-----------------|
|           33.33 |
+-----------------+

Sie können das gleiche Ergebnis erzielen, indem Sie einen Cursor mit einer FOR-Schleife verwenden:

DECLARE
  total_price FLOAT;
  c1 CURSOR FOR SELECT price FROM invoices;
BEGIN
  total_price := 0.0;
  FOR record IN c1 DO
    total_price := total_price + record.price;
  END FOR;
  RETURN total_price;
END;

Hinweis: Wenn Sie SnowSQL, die Classic Console oder die execute_stream- oder execute_string-Methode im Python-Konnektor-Code verwenden, benutzen Sie stattdessen das folgende Beispiel (siehe Verwenden von Snowflake Scripting in SnowSQL, in der Classic Console und im Python-Konnektor):

EXECUTE IMMEDIATE $$
DECLARE
  total_price FLOAT;
  c1 CURSOR FOR SELECT price FROM invoices;
BEGIN
  total_price := 0.0;
  FOR record IN c1 DO
    total_price := total_price + record.price;
  END FOR;
  RETURN total_price;
END;
$$
;

+-----------------+
| anonymous block |
|-----------------|
|           33.33 |
+-----------------+

Problembehandlung bei Cursors¶

Im folgenden Abschnitt werden häufige Probleme mit Cursors beschrieben und jeweils eine mögliche Ursache und Lösung benannt.