Verwenden von RESULTSETs¶

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.

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

Copy

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.

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

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

Copy

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;

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

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

Copy

Rufen Sie die gespeicherte Prozedur auf:

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

Copy

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

Copy

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;

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

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

Copy

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

call test_sp_dynamic('t001');

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

Copy

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;

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

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

Copy

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;

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

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

Copy

Rufen Sie die gespeicherte Prozedur auf:

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

Copy