カーソルの操作¶
カーソルを使用して、一度に1行ずつクエリ結果を反復処理できます。
このトピックの内容:
紹介¶
クエリの結果からデータを取得するには、カーソルを使用します。 ループ でカーソルを使用して、結果の行を反復処理できます。
カーソルを使用するには、次を実行します。
例に対するデータの設定¶
このセクションの例では、次のデータを使用しています。
CREATE OR REPLACE TABLE invoices (id INTEGER, price NUMBER(12, 2)); INSERT INTO invoices (id, price) VALUES (1, 11.11), (2, 22.22);
カーソルの宣言¶
SELECT ステートメントまたは RESULTSET のカーソルを宣言できます。
ブロックの DECLARE セクションまたはブロックの BEGIN ... END セクションでカーソルを宣言します。
DECLARE セクション内において、 カーソル宣言構文 で説明されている構文を使用します。
たとえば、クエリのカーソルを宣言するには、次を実行します。
DECLARE ... c1 CURSOR FOR SELECT price FROM invoices;
RESULTSET のカーソルを宣言するには、
DECLARE ... res RESULTSET DEFAULT (SELECT price FROM invoices); c1 CURSOR FOR res;
BEGIN ... END ブロック内において、 カーソル割り当て構文 で説明されている構文を使用します。例:
BEGIN ... LET c1 CURSOR FOR SELECT price FROM invoices;
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;注: 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; $$ ;
カーソルを開く¶
カーソルを宣言するステートメントは、そのカーソルに関連付けられたクエリを定義しますが、 OPEN コマンドを実行してカーソルを開くまで、クエリは実行されません。例:
OPEN c1;
注釈
FOR ループでカーソルを使用する場合は、カーソルを明示的に開く必要はありません。
RESULTSET オブジェクトのカーソルを宣言すると、オブジェクトをクエリに関連付けるときにクエリが実行されます。この場合は、カーソルを開いてもクエリは再度実行されません。
クエリにバインドパラメーター（? 文字）が含まれている場合は、 USING 句を追加して、それらのパラメータにバインドする変数のリストを指定します。例:
LET c1 CURSOR FOR SELECT id FROM invoices WHERE price > ? and price < ?; OPEN c1 USING (minimum_price, maximum_price);
カーソルを開くと、クエリが実行され、指定された行がカーソルに取得され、最初の行をポイントする内部ポインターが設定されます。 カーソルを使用して個々の行をフェッチ（読み取り）する ために、 FETCH コマンドを使用できます。
他の SQL クエリと同様に、クエリ定義に最外部レベルの ORDER BY が含まれていない場合、結果セットには定義された順序がありません。カーソルの結果セットが作成されると、カーソルが閉じられるまで行の順序が保持されます。カーソルを再度宣言または開くと、行の順序が異なる場合があることに注意してください。同様に、カーソルを閉じて、カーソルを再度開く前に基になるテーブルが更新された場合は、結果セットも変更される可能性があります。
カーソルを使用したデータのフェッチ¶
FETCH コマンドを使用して、結果セットから現在の行を取得し、内部の現在の行ポインターを進めて、結果セットの次の行をポイントします。
INTO 句で、行の値を保持するために使用する変数を指定します。
例:
FETCH c1 INTO var_for_column_value;
変数の数がカーソル宣言の 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;
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)
これを回避するには、列の値を GEOGRAPHY 型にキャストします。
geohash_value := ST_GEOHASH(TO_GEOGRAPHY(row_variable.geography_value));
カーソルのテーブルを返す¶
カーソルからデータのテーブルを返す必要がある場合は、カーソルを RESULTSET_FROM_CURSOR(cursor) に渡すことができます。その次に、カーソルを TABLE(...) に渡すことができます。
次のブロックは、カーソルからデータのテーブルを返します。
DECLARE c1 CURSOR FOR SELECT * FROM invoices; BEGIN OPEN c1; RETURN TABLE(RESULTSET_FROM_CURSOR(c1)); END;注: 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; $$ ;
この例では、次の出力が生成されます。
+----+-------+ | ID | PRICE | |----+-------| | 1 | 11.11 | | 2 | 22.22 | +----+-------+
すでにカーソルを使用して 行をフェッチ した場合でも、 RESULTSET_FROM_CURSOR は、内部行ポインタから始まる行だけでなく、すべての行を含む RESULTSET を返すことに注意してください。
上に示したように、この例では最初の行をフェッチし、内部行ポインターを2番目の行に設定します。 RESULTSET_FROM_CURSOR は、（2番目の行だけでなく）両方の行を含む RESULTSET を返します。
カーソルを閉じる¶
結果セットが完了したら、 CLOSE コマンドを実行してカーソルを閉じます。例:
CLOSE c1;
注釈
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;注: 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; $$ ;
この例では、次の出力が生成されます。
+-----------------+ | anonymous block | |-----------------| | 33.33 | +-----------------+
ループの使用例は、 FOR ループ のドキュメントに含まれています。
カーソルに関する問題のトラブルシューティング¶
症状: カーソルがすべての行ではなく、2行ごとに取得しているように見えます。
- 考えられる原因:
FOR <記録> IN <カーソル>ループ内でFETCHを実行した可能性があります。カーソル上のFORループは、自動的に次行をフェッチします。ループ内で別のフェッチを実行すると、2行ごとに取得されます。- 考えられる解決策:
FORループ内の不要なFETCHを削除します。
症状: FETCH コマンドが予期しない NULL 値を取得します。
- 考えられる原因:
FOR <記録> IN <カーソル>ループ内でFETCHを実行した可能性があります。カーソル上のFORループは、自動的に次行をフェッチします。ループ内で別のフェッチを実行すると、2行ごとに取得されます。行数が奇数の場合、最後のFETCHは最後の行を超えた行をフェッチしようとし、値は NULL になります。- 考えられる解決策:
FORループ内の不要なFETCHを削除します。