Verwenden von RESULTSETs¶

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. Wenn Sie später 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. Sie können jedoch Variablen in SQL-Befehlen binden, bevor Sie das Resultset zurückgeben.

Im Allgemeinen ist es einfacher, eine RESULTSET zu verwenden, wenn Sie eine Tabelle zurückgeben möchten, die das Resultset einer Abfrage enthält. Sie können jedoch auch eine Tabelle aus einem Snowflake Scripting-Block mit einem Cursor zurückgeben. Dazu können Sie den Cursor an RESULTSET_FROM_CURSOR(cursor) übergeben, um ein RESULTSET zurückzugeben, und dieses RESULTSET an TABLE(...) weitergeben. 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);
  

  Copy

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

  Copy

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 der RESULTSET.

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 eine SQL-Zeichenfolge dynamisch 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;

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

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.

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

• Zurückgeben eines RESULTSET als Tabelle

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

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

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

• Beispiel: Zurückgeben einer Tabelle aus einer gespeicherten Prozedur

• Beispiel: Dynamisches Erstellen der SQL-Anweisung

• Beispiel: Deklarieren einer RESULTSET-Variable ohne DEFAULT-Klausel

• Beispiel: Verwenden eines CURSOR mit einem RESULTSET

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

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;

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

CALL test_sp();

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

SELECT *
    FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
    ORDER BY 1;

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

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 IDENTIFIER(?) ORDER BY a;';
BEGIN
  res := (EXECUTE IMMEDIATE :query USING(table_name));
  RETURN TABLE(res);
END;

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 IDENTIFIER(?) ORDER BY a;';
  BEGIN
    res := (EXECUTE IMMEDIATE :query USING(table_name));
    RETURN TABLE(res);
  END
$$
;

CALL test_sp_dynamic('t001');

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

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;

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

CALL test_sp_02();

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

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;

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

CALL test_sp_03();

+------------+
| TEST_SP_03 |
|------------|
| 3          |
+------------+