Verwenden von RESULTSETs

Unter diesem Thema wird erklärt, wie RESULTSETs in Snowflake Scripting verwendet werden.

Unter diesem Thema:

Einführung

In Snowflake Scripting ist ein RESULTSET ein SQL-Datentyp, der auf die Ergebnismenge einer Abfrage verweist.

Da ein RESULTSET nur ein Zeiger auf die Ergebnisse ist, müssen Sie eine der folgenden Möglichkeiten nutzen, um über das RESULTSET auf die Ergebnisse zuzugreifen:

  • Verwenden Sie die Syntax TABLE(), um die Ergebnisse in Form einer Tabelle abzurufen.

  • Iterieren Sie über das RESULTSET mit einem Cursor.

Im Folgenden finden Sie Beispiele für beide Varianten.

Erläuterungen zum Unterschied zwischen Cursor und RESULTSET

Ein RESULTSET und ein Cursor bieten beide Zugang zum Resultset einer Abfrage. Diese Objekte unterscheiden sich jedoch in den folgenden Punkten:

  • Zeitpunkt, zu dem die Abfrage ausgeführt wird.

    • Bei einem Cursor wird die Abfrage ausgeführt, wenn Sie den Befehl OPEN auf dem Cursor ausführen.

    • Bei einem RESULTSET wird die Abfrage ausgeführt, wenn Sie die Abfrage dem RESULTSET zuweisen (entweder im Abschnitt DECLARE oder im BEGIN … END-Block).

  • Unterstützung für das Binden im Befehl OPEN.

    • Wenn Sie einen Cursor deklarieren, können Sie Bindungsparameter (?-Zeichen) angeben. Später, wenn Sie den Befehl OPEN ausführen, können Sie in der USING-Klausel Variablen an diese Parameter binden.

    • RESULTSET bietet keine Unterstützung für den Befehl OPEN.

Hinweis: Wenn Sie einen Cursor haben und eine Tabelle aus dem Snowflake Scripting-Block zurückgeben müssen, dann können Sie den Cursor an RESULTSET_FROM_CURSOR(cursor) übergeben, damit ein RESULTSET zurückgegeben wird und dieses RESULTSET dann an TABLE(...) übergeben werden kann. Siehe Zurückgeben einer Tabelle für einen Cursor.

Deklarieren eines RESULTSET

Sie können ein RESULTSET im Abschnitt DECLARE eines Blocks oder im Abschnitt BEGIN … END des Blocks deklarieren.

  • Verwenden Sie innerhalb des Abschnitts DECLARE die unter Syntax der RESULTSET-Deklaration beschriebene Syntax. Beispiel:

    DECLARE
      ...
      res RESULTSET DEFAULT (SELECT col1 FROM mytable ORDER BY col1);
    
  • Innerhalb des BEGIN … END-Blocks ist die unter Syntax der RESULTSET-Zuweisung beschriebene Syntax zu verwenden. Beispiel:

    BEGIN
      ...
      LET res RESULTSET := (SELECT col1 FROM mytable ORDER BY col1);
    

Zuweisen einer Abfrage zu einem deklarierten RESULTSET

Um das Ergebnis einer Abfrage einem bereits deklarierten RESULTSET zuzuweisen, verwenden Sie die folgende Syntax:

<resultset_name> := ( <query> ) ;

Wobei:

resultset_name

Der Name, den das RESULTSET erhalten soll.

Der Name muss innerhalb des aktuellen Sichtbarkeitsbereichs eindeutig sein.

Der Name muss den Snowflake-Regeln für Bezeichner folgen (siehe unter Objektbezeichner).

query

Die Abfrage, die dem RESULTSET zugewiesen werden soll.

Beispiel:

DECLARE
  res RESULTSET;
BEGIN
  res := (SELECT col1 FROM mytable ORDER BY col1);
  ...

Wenn Sie für die Abfrage dynamisch eine SQL-Zeichenfolge erstellen müssen, setzen Sie query auf (EXECUTE IMMEDIATE string_of_sql). Beispiel:

DECLARE
  res RESULTSET;
  col_name VARCHAR;
  select_statement VARCHAR;
BEGIN
  col_name := 'col1';
  select_statement := 'SELECT ' || col_name || ' FROM mytable';
  res := (EXECUTE IMMEDIATE :select_statement);
  RETURN TABLE(res);
END;

Beachten Sie, dass Sie zwar query auf eine EXECUTE IMMEDIATE-Anweisung für ein RESULTSET setzen können, dies aber nicht für einen Cursor möglich ist.

Verwenden eines RESULTSET

Die Abfrage für ein RESULTSET wird ausgeführt, wenn das Objekt dieser Abfrage zugeordnet wird. Beispiel:

  • Wenn Sie ein RESULTSET deklarieren und die DEFAULT-Klausel auf eine Abfrage setzen, wird die Abfrage zu diesem Zeitpunkt ausgeführt.

  • Wenn Sie den Operator := verwenden, um eine Abfrage einem RESULTSET zuzuordnen, wird die Abfrage zu diesem Zeitpunkt ausgeführt.

Bemerkung

Da ein RESULTSET auf die Ergebnismenge einer Abfrage verweist (und nicht die Ergebnismenge einer Abfrage enthält), ist ein RESULTSET nur so lange gültig, wie die Abfrageergebnisse zwischengespeichert werden (normalerweise 24 Stunden). Weitere Einzelheiten zur Zwischenspeicherung von Abfrageergebnissen finden Sie unter Verwenden von persistent gespeicherten Abfrageergebnissen.

Sobald die Abfrage ausgeführt wurde, können Sie mithilfe eines Cursors auf die Ergebnisse zugreifen. Sie können die Ergebnisse auch in Form einer Tabelle aus einer gespeicherten Prozedur zurückgeben.

Verwenden eines Cursors für den Zugriff auf Daten eines RESULTSET

Um einen Cursor für den Zugriff auf die Daten eines RESULTSET zu verwenden, deklarieren Sie den Cursor auf dem Objekt. Beispiel:

DECLARE
  ...
  res RESULTSET DEFAULT (SELECT col1 FROM mytable ORDER BY col1);
  c1 CURSOR FOR res;

Beachten Sie, dass beim Deklarieren eines Cursors auf einem RESULTSET der Cursor Zugriff auf die Daten erhält, die sich bereits im RESULTSET befinden. Wenn der OPEN-Befehl auf dem Cursor ausgeführt wird, führt dies nicht zur erneuten Ausführung der Abfrage des RESULTSET.

Sie können dann den Cursor öffnen und den Cursor verwenden, um die Daten abzurufen.

Bemerkung

Wenn die Ergebnisse GEOGRAPHY-Werte enthalten, müssen Sie diese Werte erst in den Typ GEOGRAPHY umwandeln, bevor Sie sie an Funktionen übergeben, die Werte vom Typ GEOGRAPHY als Eingabe erwarten. Siehe Verwenden eines Cursors zum Abrufen eines GEOGRAPHY-Werts.

Zurückgeben eines RESULTSET als Tabelle

Wenn Sie die Ergebnisse zurückgeben möchten, auf die RESULTSET zeigt, übergeben Sie RESULTSET an TABLE(). Beispiel:

CREATE PROCEDURE f()
  RETURNS TABLE(column_1 INTEGER, column_2 VARCHAR)
  ...
    RETURN TABLE(my_resultset_1);
  ...

Dies ist vergleichbar mit der Art und Weise, wie TABLE() mit Tabellenfunktionen verwendet wird (wie z. B. RESULT_SCAN)

Wenn Sie wie im Beispiel gezeigt eine gespeicherte Prozedur schreiben, die eine Tabelle zurückgibt, müssen Sie die gespeicherte Prozedur so deklarieren, dass sie eine Tabelle zurückgibt.

Bemerkung

Derzeit wird die Syntax TABLE(resultset_name) nur in der RETURN-Anweisung unterstützt.

Hinweis: Wenn Sie einen Cursor verwendet haben, um Zeilen aus dem RESULTSET abzurufen, enthält die von TABLE(resultset_name) zurückgegebene Tabelle immer noch alle Zeilen (nicht nur die Zeilen, die beim internen Zeilenzeiger des Cursors beginnen).

Einschränkungen des Datentyps RESULTSET

Obwohl RESULTSET ein Datentyp ist, unterstützt Snowflake Folgendes noch nicht:

  • Deklarieren einer Spalte vom Typ RESULTSET

  • Deklarieren eines Parameters vom Typ RESULTSET

  • Deklarieren des Rückgabetyps einer gespeicherten Prozedur als RESULTSET

Snowflake unterstützt RESULTSET nur innerhalb von Snowflake Scripting.

Darüber hinaus können Sie RESULTSET nicht direkt als Tabelle verwenden. Beispielsweise wird Folgendes nicht unterstützt:

select * from my_result_set;

Beispiele für die Verwendung eines RESULTSET

Die folgenden Abschnitte enthalten Beispiele für die Verwendung eines RESULTSET:

Einrichten der Daten für die Beispiele

In vielen der unten aufgeführten Beispiele werden die folgenden Tabellen und Daten verwendet:

CREATE TABLE t001 (a INTEGER, b VARCHAR);
INSERT INTO t001 (a, b) VALUES
    (1, 'row1'),
    (2, 'row2');

Beispiel: Zurückgeben einer Tabelle aus einer gespeicherten Prozedur

Der folgende Code zeigt, wie ein RESULTSET deklariert wird und wie die Ergebnisse zurückgegeben werden, auf die das RESULTSET zeigt. Die RETURNS-Klausel im CREATE PROCEDURE-Befehl deklariert, dass die gespeicherte Prozedur eine Tabelle zurückgibt, die eine Spalte vom Typ INTEGER enthält.

Die RETURN-Anweisung innerhalb des Blocks verwendet die TABLE()-Syntax, um die Ergebnisse in Form einer Tabelle zurückzugeben.

Erstellen Sie die gespeicherte Prozedur:

CREATE OR REPLACE PROCEDURE test_sp()
RETURNS TABLE(a INTEGER)
LANGUAGE SQL
AS
  DECLARE
    res RESULTSET default (select a from t001 order by a);
  BEGIN
    RETURN TABLE(res);
  END;

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

CREATE OR REPLACE PROCEDURE test_sp()
RETURNS TABLE(a INTEGER)
LANGUAGE SQL
AS
$$
    DECLARE
        res RESULTSET default (select a from t001 order by a);
    BEGIN
        RETURN TABLE(res);
    END;
$$;

Rufen Sie die gespeicherte Prozedur auf:

CALL test_sp();
+---+
| A |
|---|
| 1 |
| 2 |
+---+

Beachten Sie, dass Sie die Funktion RESULT_SCAN verwenden können, um die Ergebnisse des Aufrufs der gespeicherten Prozedur zu verarbeiten:

SELECT *
    FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
    ORDER BY 1;
+---+
| A |
|---|
| 1 |
| 2 |
+---+

Beispiel: Dynamisches Erstellen der SQL-Anweisung

Sie können den SQL-Code dynamisch konstruieren. Im folgenden Beispiel wird dieselbe Abfrage ausgeführt wie in der gespeicherten Prozedur oben, allerdings wird dabei eine dynamisch erstellte SQL-Anweisung verwendet:

CREATE OR REPLACE PROCEDURE test_sp_dynamic(table_name VARCHAR)
RETURNS TABLE(a INTEGER)
LANGUAGE SQL
AS
  DECLARE
    res RESULTSET;
    query VARCHAR DEFAULT 'SELECT a FROM ' || :table_name || ' ORDER BY a';
  BEGIN
    res := (EXECUTE IMMEDIATE :query);
    RETURN TABLE (res);
  END;

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

CREATE OR REPLACE PROCEDURE test_sp_dynamic(table_name VARCHAR)
RETURNS TABLE(a INTEGER)
LANGUAGE SQL
AS
$$
  DECLARE
    res RESULTSET;
    query VARCHAR DEFAULT 'SELECT a FROM ' || :table_name || ' ORDER BY a';
  BEGIN
    res := (EXECUTE IMMEDIATE :query);
    RETURN TABLE (res);
  END;
$$
;

Um das Beispiel auszuführen, rufen Sie die gespeicherte Prozedur auf und übergeben den Tabellennamen:

call test_sp_dynamic('t001');

+---+
| A |
|---|
| 1 |
| 2 |
+---+

Beispiel: Deklarieren einer RESULTSET-Variable ohne DEFAULT-Klausel

Der folgende Code zeigt, wie ein RESULTSET ohne DEFAULT-Klausel deklariert wird (d. h. ohne Zuordnen einer Abfrage zu dem RESULTSET), und wie dann später das RESULTSET mit einer Abfrage verknüpft wird.

CREATE OR REPLACE PROCEDURE test_sp_02()
RETURNS TABLE(a INTEGER)
LANGUAGE SQL
AS
  DECLARE
    res RESULTSET;
  BEGIN
    res := (select a from t001 order by a);
    RETURN TABLE(res);
  END;

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

CREATE OR REPLACE PROCEDURE test_sp_02()
RETURNS TABLE(a INTEGER)
LANGUAGE SQL
AS
$$
    DECLARE
        res RESULTSET;
    BEGIN
        res := (select a from t001 order by a);
        RETURN TABLE(res);
    END;
$$;

Beispiel: Verwenden eines CURSOR mit einem RESULTSET

Der folgende Code zeigt, wie ein Cursor verwendet wird, um über die Zeilen in einem RESULTSET zu iterieren:

Erstellen Sie die gespeicherte Prozedur:

CREATE OR REPLACE PROCEDURE test_sp_03()
RETURNS VARCHAR
LANGUAGE SQL
AS

  DECLARE
    accumulator INTEGER DEFAULT 0;
    res1 RESULTSET DEFAULT (select a from t001 order by a);
    cur1 CURSOR FOR res1;
  BEGIN
    FOR row_variable IN cur1 DO
      accumulator := accumulator + row_variable.a;
    END FOR;
    RETURN accumulator::VARCHAR;
  END;

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

CREATE OR REPLACE PROCEDURE test_sp_03()
RETURNS INTEGER
LANGUAGE SQL
AS
$$
    DECLARE
        accumulator INTEGER DEFAULT 0;
        res1 RESULTSET DEFAULT (select a from t001 order by a);
        cur1 CURSOR FOR res1;
    BEGIN
        FOR row_variable IN cur1 DO
                accumulator := accumulator + row_variable.a;
        END FOR;
        RETURN accumulator;
    END;
$$;

Rufen Sie die gespeicherte Prozedur auf:

CALL test_sp_03();
+------------+
| TEST_SP_03 |
|------------|
| 3          |
+------------+