カーソルの操作

カーソルを使用して、一度に1行ずつクエリ結果を反復処理できます。

このトピックの内容:

紹介

クエリの結果からデータを取得するには、カーソルを使用します。 ループ でカーソルを使用して、結果の行を反復処理できます。

カーソルを使用するには、次を実行します。

  1. DECLARE セクションで、 カーソルを宣言 します。宣言には、カーソルのクエリが含まれます。

  2. OPEN コマンドを実行して、 カーソルを開きます。これにより、クエリが実行され、結果がカーソルにロードされます。

  3. FETCH コマンドを実行して、 1つ以上の行をフェッチ し、それらの行を処理します。

  4. 結果が完了したら、 CLOSE コマンドを実行して、 カーソルを閉じます

例に対するデータの設定

このセクションの例では、次のデータを使用しています。

CREATE OR REPLACE TABLE invoices (id INTEGER, price NUMBER(12, 2));

INSERT INTO invoices (id, price) VALUES
  (1, 11.11),
  (2, 22.22);
Copy

カーソルの宣言

SELECT ステートメントまたは RESULTSET のカーソルを宣言できます。

ブロックの DECLARE セクションまたはブロックの BEGIN ... END セクションでカーソルを宣言します。

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

    たとえば、クエリのカーソルを宣言するには、次を実行します。

    DECLARE
      ...
      c1 CURSOR FOR SELECT price FROM invoices;
    
    Copy

    RESULTSET のカーソルを宣言するには、

    DECLARE
      ...
      res RESULTSET DEFAULT (SELECT price FROM invoices);
      c1 CURSOR FOR res;
    
    Copy
  • BEGIN ... END ブロック内において、 カーソル割り当て構文 で説明されている構文を使用します。例:

    BEGIN
      ...
      LET c1 CURSOR FOR SELECT price FROM invoices;
    
    Copy

SELECT ステートメントでは、カーソルを開いたときに変数にバインドできるバインドパラメーター(? 文字)を指定できます。変数をパラメーターにバインドするには、 OPEN コマンドの USING 句で変数を指定します。例:

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

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

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

カーソルを開く

カーソルを宣言するステートメントは、そのカーソルに関連付けられたクエリを定義しますが、 OPEN コマンドを実行してカーソルを開くまで、クエリは実行されません。例:

OPEN c1;
Copy

注釈

  • FOR ループでカーソルを使用する場合は、カーソルを明示的に開く必要はありません。

  • RESULTSET オブジェクトのカーソルを宣言すると、オブジェクトをクエリに関連付けるときにクエリが実行されます。この場合は、カーソルを開いてもクエリは再度実行されません。

クエリにバインドパラメーター(? 文字)が含まれている場合は、 USING 句を追加して、それらのパラメータにバインドする変数のリストを指定します。例:

LET c1 CURSOR FOR SELECT id FROM invoices WHERE price > ? and price < ?;
OPEN c1 USING (minimum_price, maximum_price);
Copy

カーソルを開くと、クエリが実行され、指定された行がカーソルに取得され、最初の行をポイントする内部ポインターが設定されます。 カーソルを使用して個々の行をフェッチ(読み取り)する ために、 FETCH コマンドを使用できます。

他の SQL クエリと同様に、クエリ定義に最外部レベルの ORDER BY が含まれていない場合、結果セットには定義された順序がありません。カーソルの結果セットが作成されると、カーソルが閉じられるまで行の順序が保持されます。カーソルを再度宣言または開くと、行の順序が異なる場合があることに注意してください。同様に、カーソルを閉じて、カーソルを再度開く前に基になるテーブルが更新された場合は、結果セットも変更される可能性があります。

カーソルを使用したデータのフェッチ

FETCH コマンドを使用して、結果セットから現在の行を取得し、内部の現在の行ポインターを進めて、結果セットの次の行をポイントします。

INTO 句で、行の値を保持するために使用する変数を指定します。

例:

FETCH c1 INTO var_for_column_value;
Copy

変数の数がカーソル宣言の SELECT 句の式の数と一致しない場合、Snowflakeは変数を位置によって列と一致させようとします。

  • 列よりも多くの変数がある場合、Snowflakeは残りの変数を未設定のままにします。

  • 変数よりも多くの列がある場合、Snowflakeは残りの列を無視します。

後続の各 FETCH コマンドは、最後の行がフェッチされるまで次の行を取得します。最後の行よりも後の行を FETCH しようとすると、 NULL 値を取得します。

RESULTSET または CURSOR は、クエリの実行時に結果セットのすべての行をキャッシュする必要はありません。FETCH 操作で遅延が発生する可能性があります。

カーソルの使用による GEOGRAPHY 値の取得

結果に型 GEOGRAPHY の列が含まれている場合、列の値の型は GEOGRAPHY ではなく OBJECT です。これは、 GEOGRAPHY オブジェクトを入力として受け入れる 地理空間関数 にこの値を直接渡すことができないことを意味します。

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

これを回避するには、列の値を GEOGRAPHY 型にキャストします。

geohash_value := ST_GEOHASH(TO_GEOGRAPHY(row_variable.geography_value));
Copy

カーソルのテーブルを返す

カーソルからデータのテーブルを返す必要がある場合は、カーソルを RESULTSET_FROM_CURSOR(cursor) に渡すことができます。その次に、カーソルを TABLE(...) に渡すことができます。

次のブロックは、カーソルからデータのテーブルを返します。

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

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

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

この例では、次の出力が生成されます。

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

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

上に示したように、この例では最初の行をフェッチし、内部行ポインターを2番目の行に設定します。 RESULTSET_FROM_CURSOR は、(2番目の行だけでなく)両方の行を含む RESULTSET を返します。

カーソルを閉じる

結果セットが完了したら、 CLOSE コマンドを実行してカーソルを閉じます。例:

CLOSE c1;
Copy

注釈

FOR ループでカーソルを使用する場合は、カーソルを明示的に閉じる必要はありません。

閉じたカーソルに対しては、 FETCH コマンドを実行することはできません。

また、カーソルを閉じると、現在の行ポインターが無効になります。カーソルをもう一度開くと、ポインターは新しい結果セットの最初の行をポイントします。

カーソルの使用例

この例では、 例に対するデータの設定 で設定したデータを使用しています。

カーソルを使用して2つの行を読み取り、それらの行の価格を合計する、ストアドプロシージャを次に示します。

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

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

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

この例では、次の出力が生成されます。

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

ループの使用例は、 FOR ループ のドキュメントに含まれています。

カーソルに関する問題のトラブルシューティング

症状: カーソルがすべての行ではなく、2行ごとに取得しているように見えます。

考えられる原因:

FOR <記録> IN <カーソル> ループ内で FETCH を実行した可能性があります。カーソル上の FOR ループは、自動的に次行をフェッチします。ループ内で別のフェッチを実行すると、2行ごとに取得されます。

考えられる解決策:

FOR ループ内の不要な FETCH を削除します。

症状: FETCH コマンドが予期しない NULL 値を取得します。

考えられる原因:

FOR <記録> IN <カーソル> ループ内で FETCH を実行した可能性があります。カーソル上の FOR ループは、自動的に次行をフェッチします。ループ内で別のフェッチを実行すると、2行ごとに取得されます。行数が奇数の場合、最後の FETCH は最後の行を超えた行をフェッチしようとし、値は NULL になります。

考えられる解決策:

FOR ループ内の不要な FETCH を削除します。