RESULTSETs の操作

このトピックでは、Snowflakeスクリプトで RESULTSETs を使用する方法について説明します。

このトピックの内容:

紹介

Snowflakeスクリプトでは、 RESULTSET はクエリの結果セットをポイントする SQL データ型です。

RESULTSET は結果への単なるポインターであるため、 RESULTSET を介して結果にアクセスするには、次のいずれかを実行する必要があります。

  • TABLE() 構文を使用して、結果をテーブルとして取得します。

  • カーソル を使用して RESULTSET を反復処理します。

これらの両方の例を以下に示します。

カーソルと RESULTSET の違いを理解する

RESULTSET と カーソル はどちらも、クエリの結果セットへのアクセスを提供します。ただし、これらのオブジェクトは次の点で異なります。

  • クエリが実行された時点。

    • カーソルの場合、カーソルに対して OPEN コマンドを実行すると、クエリが実行されます。

    • RESULTSET の場合は、クエリを RESULTSET に割り当てるとクエリが実行されます(DECLARE セクション内または BEGIN ... END ブロック内のいずれか)。

  • OPEN コマンドでのバインドのサポート。

    • カーソルを宣言するときに、バインドパラメーター(? 文字)を指定できます。後で OPEN コマンドを実行すると、 USING 句で変数をそれらのパラメーターにバインドできます。

    • RESULTSET は、 OPEN コマンドをサポートしていません。

カーソルがあり、Snowflakeスクリプトのブロックからテーブルを返す必要がある場合は、カーソルを RESULTSET_FROM_CURSOR(cursor) に渡して RESULTSET を返し、その RESULTSET を TABLE(...) に渡すことができます。 カーソルのテーブルを返す をご参照ください。

RESULTSET の宣言

ブロックの DECLARE セクションまたはブロックの BEGIN ... END セクションで RESULTSET を宣言できます。

  • DECLARE セクション内において、 RESULTSET 宣言構文 で説明されている構文を使用します。例:

    DECLARE
      ...
      res RESULTSET DEFAULT (SELECT col1 FROM mytable ORDER BY col1);
    
    Copy
  • BEGIN ... END ブロック内において、 RESULTSET 割り当て構文 で説明されている構文を使用します。例:

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

宣言された RESULTSET へのクエリの割り当て

すでに宣言されている RESULTSET にクエリの結果を割り当てるには、次の構文を使用します。

<resultset_name> := ( <query> ) ;
Copy

条件:

resultset_name

RESULTSET に付ける名前。

名前は、現在のスコープ内で一意である必要があります。

名前は、 オブジェクト識別子 の名前付け規則に従う必要があります。

query

RESULTSET に割り当てるクエリ。

例:

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

クエリに対して SQL の文字列を動的に作成する必要がある場合は、 query(EXECUTE IMMEDIATE string_of_sql) に設定します。例:

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

RESULTSET に対して queryEXECUTE IMMEDIATE ステートメントに設定することはできますが、カーソルに対しては設定できません。

RESULTSET の使用

RESULTSET のクエリは、オブジェクトがそのクエリに関連付けられているときに実行されます。例:

  • RESULTSET を宣言し、 DEFAULT 句をクエリに設定すると、その時点でクエリが実行されます。

  • := 演算子を使用してクエリを RESULTSET に割り当てると、その時点でクエリが実行されます。

注釈

RESULTSET はクエリの結果セットをポイントしているため(クエリの結果セットは含まれていません)、 RESULTSET はクエリ結果がキャッシュされている間(通常は24時間)のみ有効です。クエリ結果のキャッシュの詳細については、 保存済みのクエリ結果の使用 をご参照ください。

クエリが実行されると、カーソルを使用して結果にアクセスできます。ストアドプロシージャから結果をテーブルとして返すこともできます。

カーソルの使用による RESULTSET からのデータへのアクセス

カーソルを使用して RESULTSET からデータにアクセスするには、オブジェクト上で カーソルを宣言 します。例:

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

RESULTSET でカーソルを宣言すると、カーソルはすでに RESULTSET にあるデータにアクセスできることに注意してください。カーソルで OPEN コマンドを実行しても、 RESULTSET のクエリは再度実行されません。

次に、 カーソルを開き、カーソルを使用して データをフェッチ します。

注釈

結果に GEOGRAPHY 値が含まれる場合は、 GEOGRAPHY 入力値を期待する関数に値を渡す前に、値を GEOGRAPHY 型にキャストする必要があります。 カーソルの使用による GEOGRAPHY 値の取得 をご参照ください。

RESULTSET をテーブルとして返す

RESULTSET がポイントする結果を返す場合は、 RESULTSET を TABLE() に渡します。例:

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

これは、 TABLE()テーブル関数RESULT_SCAN など)で使用される方法と似ています。

例に示すように、テーブルを返すストアドプロシージャを作成する場合は、テーブルを返すものとしてストアドプロシージャを宣言する必要があります。

注釈

現在、 TABLE(resultset_name) 構文は、 RETURN ステートメントでのみサポートされています。

カーソルを使用して RESULTSET から行をフェッチ した場合でも、 TABLE(resultset_name) によって返されるテーブルには、(カーソルの内部行ポインターから始まる行だけでなく)すべての行が含まれていることに注意してください。

RESULTSET データ型の制限

RESULTSET はデータ型ですが、Snowflakeはまだ以下をサポートしていません。

  • 型 RESULTSET の列を宣言する。

  • 型 RESULTSET のパラメーターを宣言する。

  • ストアドプロシージャの戻り型を RESULTSET として宣言する。

Snowflakeは、Snowflakeスクリプト内でのみ RESULTSET をサポートします。

また、 RESULTSET を直接テーブルとして使用することはできません。たとえば、次は無効です。

select * from my_result_set;
Copy

RESULTSET の使用例

次のセクションでは、 RESULTSET の使用例を示します。

例に対するデータの設定

以下の例の多くでは、以下に示すテーブルとデータを使用しています。

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

例: ストアドプロシージャからテーブルを返す

次のコードは、 RESULTSET を宣言し、 RESULTSET がポイントする結果を返す方法を示しています。 CREATE PROCEDURE コマンドの RETURNS 句は、ストアドプロシージャが INTEGER型の1つの列を含むテーブルを返すことを宣言しています。

ブロック内の RETURN ステートメントは、 TABLE() 構文を使用して結果をテーブルとして返します。

ストアドプロシージャを作成します。

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

注: SnowSQL または Classic Console を使用している場合は、代わりに次の例を使用します(SnowSQL および Classic Console でのSnowflakeスクリプトの使用 を参照)。

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

ストアドプロシージャを呼び出します。

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

RESULT_SCAN 関数を使用して、ストアドプロシージャ呼び出しの結果を処理できることに注意してください。

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

例: SQL ステートメントを動的に作成する

SQL を動的に構築できます。以下は、上記のストアドプロシージャと同じクエリを実行するが、動的に構築される SQL ステートメントを使用する例です。

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

注: SnowSQL または Classic Console を使用している場合は、代わりに次の例を使用します(SnowSQL および Classic Console でのSnowflakeスクリプトの使用 を参照)。

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

例を実行するには、ストアドプロシージャを呼び出し、テーブル名を渡します。

call test_sp_dynamic('t001');

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

例: DEFAULT 句なしで RESULTSET 変数を宣言する

次のコードは、 DEFAULT 句なしで(つまり、クエリを RESULTSET に関連付けずに) RESULTSET を宣言し、後で RESULTSET をクエリに関連付ける方法を示しています。

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

注: SnowSQL または Classic Console を使用している場合は、代わりに次の例を使用します(SnowSQL および Classic Console でのSnowflakeスクリプトの使用 を参照)。

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

例: CURSOR を RESULTSET で使用する

次のコードは、 カーソル を使用して RESULTSET の行を反復処理する方法を示しています。

ストアドプロシージャを作成します。

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

注: SnowSQL または Classic Console を使用している場合は、代わりに次の例を使用します(SnowSQL および Classic Console でのSnowflakeスクリプトの使用 を参照)。

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

ストアドプロシージャを呼び出します。

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