Verwenden von Cursors¶

Einführung¶

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

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. Durch Ausführen des Befehls OPEN können Sie den Cursor öffnen. 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, führen Sie den Befehl CLOSE aus, um den Cursor zu schließen.

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

Copy

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;

Copy

Hinweis: Wenn Sie SnowSQL oder die klassische Weboberfläche verwenden, benutzen Sie stattdessen dieses Beispiel (siehe Verwenden von Snowflake Scripting in SnowSQL und über die klassische Weboberfläche):

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

Copy

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

Copy

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

Copy

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 auch 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 für die Werte der Zeile verwendet werden sollen.

Beispiel:

FETCH c1 INTO var_for_column_value;

Copy

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

Copy

Hinweis: Wenn Sie SnowSQL oder die klassische Weboberfläche verwenden, benutzen Sie stattdessen dieses Beispiel (siehe Verwenden von Snowflake Scripting in SnowSQL und über die klassische Weboberfläche):

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

Copy

Dieses Beispiel ergibt die folgende Ausgabe:

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

Copy

Auch wenn Sie den Cursor bereits verwendet haben, um Zeilen abzurufen, gibt RESULTSET_FROM_CURSOR dennoch ein RESULTSET zurückgibt, das alle Zeilen enthält, 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;

Copy

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 eine gespeicherte Prozedur, die 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;

Copy

Hinweis: Wenn Sie SnowSQL oder die klassische Weboberfläche verwenden, benutzen Sie stattdessen dieses Beispiel (siehe Verwenden von Snowflake Scripting in SnowSQL und über die klassische Weboberfläche):

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

Copy

Dieses Beispiel ergibt die folgende Ausgabe:

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

Copy

Ein Beispiel für die Verwendung einer Schleife ist in der Dokumentation zu FOR-Schleifen enthalten.

Problembehandlung bei Cursors¶

Symptom: Der Cursor scheint jede zweite Zeile abzurufen, anstatt jede Zeile.

Mögliche Ursache:

Sie könnten FETCH innerhalb einer FOR <Datensatz> IN <Cursor>-Schleife ausgeführt haben. Eine FOR-Schleife über einem Cursor ruft automatisch die nächste Zeile ab. Wenn Sie innerhalb der Schleife einen weiteren Abruf ausführen, erhalten Sie jede zweite Zeile.

Mögliche Lösung:

Entfernen Sie alle nicht benötigten FETCH-Befehle innerhalb einer FOR-Schleife.

Symptom: Ihr FETCH-Befehl ruft unerwartete NULL-Werte ab.

Mögliche Ursache:

Sie könnten FETCH innerhalb einer FOR <Datensatz> IN <Cursor>-Schleife ausgeführt haben. Eine FOR-Schleife über einem Cursor ruft automatisch die nächste Zeile ab. Wenn Sie innerhalb der Schleife einen weiteren Abruf ausführen, erhalten Sie jede zweite Zeile. Bei einer ungeraden Anzahl von Zeilen versucht der letzte FETCH, eine Zeile nach der letzten Zeile abzurufen, wodurch NULL-Werte zurückgegeben werden.

Mögliche Lösung:

Entfernen Sie alle nicht benötigten FETCH-Befehle innerhalb einer FOR-Schleife.