無料スクリプト配布のPHP.TO   PHPの実用的なtips PHPマニュアル MySQLマニュアル Apacheマニュアル PostgreSQLマニュアル マニュアル検索    

40.7. カーソル

問い合わせ全体を一度に実行するのではなく、 カーソル を設定して、問い合わせをカプセル化し、問い合わせの結果を一度に数行ずつ読み取ることができます。 これを行う理由の1つは、結果内に多数の行がある場合のメモリの枯渇を防ぐことです。 (しかし、 PL/pgSQL ユーザは通常これを心配する必要はありません。 FOR ループは自動的にカーソルを内部的に使用してメモリの問題を防ぐからです。) より興味深い使用方法として、呼び出し元が行を読み取ることをできるように、作成されたカーソルへの参照を返す方法があります。 これにより、関数から大量の行集合を返す際の効率が向上します。

40.7.1. カーソル変数の宣言

PL/pgSQL におけるカーソルへのアクセスは全て、カーソル変数を経由します。 カーソル変数は、常に特殊な refcursor データ型です。 カーソル変数を作成する1つの方法は、単に refcursor 型の変数として宣言することです。 他の方法は、カーソル宣言構文を使用することです。 以下にその一般形を示します。



name

 [
 [
 NO 
] SCROLL 
] CURSOR [
 ( 

arguments

 ) 
] FOR 

query

;

Oracle との互換性のため、 FOR IS に置き換えることができます。) もし SCROLL を指定すれば、カーソルは逆方向に移動できます。 もし NO SCROLL を指定すれば、逆方向の行の取り出しはできません。 どちらも指定しない時、逆方向に取り出しできるかは問い合わせに依存します。 もし arguments があれば、 name datatype をカンマで区切ったリストで、与えられた問い合わせ内のパラメータ値として置換される名前を定義します。 その名前に実際に置換される値は、カーソルを開いた時点より後に指定されます。

以下に例を示します。

DECLARE
    curs1 refcursor;
    curs2 CURSOR FOR SELECT * FROM tenk1;
    curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key;

これら3つの変数は全て refcursor データ型を持ちますが、最初のものは全ての問い合わせに使用でき、2番目には完全な問い合わせが既に バウンド されています(結び付けられています)。 また、最後のものには、パラメータ付きの問い合わせがバウンドされています ( key はカーソルが開いた時に整数パラメータ値に置き換えられます)。 curs1 変数は、特定の問い合わせに結び付けられていませんので、 アンバウンド であると呼ばれます。

40.7.2. カーソルを開く

カーソルを使用して行を取り出す前に、 開かれる 必要があります。 (これは DECLARE CURSOR SQLコマンドの動作と同じです。) PL/pgSQL には3種類の OPEN 文があり、そのうちの2つはアンバウンドカーソル変数を使用し、残りの1つはバウンドカーソル変数を使用します。

注意: バウンドカーソル変数は 項40.7.4 で説明されている FOR 文で、明示的にカーソルを開かなくても使用することができます。

40.7.2.1. OPEN FOR query

OPEN 

unbound_cursorvar

 [
 [
 NO 
] SCROLL 
] FOR 

query

;

カーソル変数は開かれ、実行するよう指定した問い合わせが付与されます。 既に開いたカーソルを開くことはできず、また、アンバウンドカーソル変数として(つまり、単なる refcursor 変数として)宣言されていなければなりません。 この問い合わせは SELECT 文であるか、または( EXPLAIN のように)何らかの行を返すものでなければなりません。 この問い合わせは、他の PL/pgSQL のSQL文と同様の方法で扱われます。 PL/pgSQL の変数名は置き換えられ、問い合わせ計画は再利用できるようにキャッシュされます。 PL/pgSQL 変数がカーソルを使用する問い合わせに代入された時、変数は OPEN 時の値となり、その後の変更はカーソルの動きに影響しません。 SCROLL および NO SCROLL オプションの意味はバウンドカーソルと同様です。

以下に例を示します。

OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;

40.7.2.2. OPEN FOR EXECUTE

OPEN 

unbound_cursorvar

 [
 [
 NO 
] SCROLL 
] FOR EXECUTE 

query_string


                                     [
 USING 

expression

 [
, ... 
] 
];

カーソル変数は開かれ、実行するよう指定した問い合わせが付与されます。 既に開いたカーソルを開くことはできず、また、アンバウンドカーソル変数として(つまり、単なる refcursor 変数として)宣言されていなければなりません。 問い合わせは、 EXECUTE コマンドと同じ方法による文字列式として指定されます。 通常と同様に、これにより、次回に実行する際に違った問い合わせを計画できる柔軟性が得られます( 項40.10.2 参照)。 また、変数置換がコマンド文字列上で行われないことも意味します。 EXECUTE と同様に USING を介して動的コマンドにパラメータ値を挿入することができます。 SCROLL および NO SCROLL オプションの意味はバウンドカーソルと同様です。

以下に例を示します。

OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident(tabname)
                                        || ' WHERE col1 = $1' USING keyvalue;

この例では、テーブル名は問い合わせ内にテキストとして埋め込まれます。 このためSQLインジェクションに対抗するために quote_ident() の使用を勧めます。 col1 との比較値は USING 経由で埋め込まれますので、引用符を付ける必要がありません。

40.7.2.3. バウンドカーソルを開く

OPEN 

bound_cursorvar

 [
 ( [
 

argument_name

 := 
] 

argument_value

 [
, ...
] ) 
];

宣言時に問い合わせが結び付いたカーソル変数を開くために使用される OPEN の形式です。 既に開いたカーソルを開くことはできません。 実引数の式のリストはカーソルが引数を取るものと宣言された場合にのみ現れます。 これらの値は問い合わせの中で置き換えられます。 バウンドカーソルの問い合わせ計画は常にキャッシュ可能とみなされます。 この場合、 EXECUTE と等価なものはありません。 SCROLL および NO SCROLL OPEN において指定できないことに注意してください。 カーソル移動の仕様はすでに決まっているからです。

位置的 表記または 記名的 表記を使用して、引数の値を渡すことができます。 位置的表記では、全ての引数が順番に指定されます。 記名的表記では、引数の式と区別するために := を使用して、各々の引数の名前が指定されます。 項4.3 に記述した関数呼び出しと同様に、位置的表記と記名的表記を混用できます。

例を示します(ここでは上例のカーソル宣言を使用します)。

OPEN curs2;
OPEN curs3(42);
OPEN curs3(key := 42);

変数の代入はバウンドカーソルの問い合わせで行われるため、カーソルへ値を渡す方法が2つあります。 OPEN コマンドの明確な引数とするものと、問い合わせにおける PL/pgSQL 変数として暗黙的に参照するものです。 しかし、バウンドカーソルの宣言より前に宣言した変数だけが代入されます。 どちらの場合も、 OPEN の実行時に変数値が決まります。 例えば、上例の curs3 と同じ結果を取得する方法を、以下に示します。

DECLARE
    key integer;
    curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key;
BEGIN
    key := 42;
    OPEN curs4;

40.7.3. カーソルの使用

カーソルを開いてから、ここで説明する文を使用してカーソルを扱うことができます。

これらの操作は、カーソルを開始するために開いた関数内で行う必要はありません。 関数から refcursor 値を返し、呼び出し元でそのカーソルの操作をさせることもできます。 (内部的には refcursor 値は、カーソルへの有効な問い合わせを持つポータルの名前を示す単なる文字列です。 この名前は、ポータルを壊すことなく、他の refcursor 型の変数に代入することで、他に渡すことができます。)

全てのポータルは、暗黙的にトランザクションの終わりで閉ざされます。 したがって、 refcursor 値はそのトランザクションの終わりまでの間のみ開いたカーソルへの参照として有効です。

40.7.3.1. FETCH

FETCH [
 

direction

 { FROM | IN } 
] 

cursor

 INTO 

target

;

FETCH SELECT INTO と同様に、カーソルから次の行を抽出し、対象に格納します。 対象とは、行変数、レコード変数、または単純な変数をカンマで区切ったリストです。 SELECT INTO の場合と同様、特殊な FOUND 変数を検査することで、行が入手できたかどうかを確認することができます。

direction 句は複数行を取り出すことができるコマンドを除き、SQL FETCH で許可されたどのようなコマンドも可能です。 すなわち、以下のものです。 NEXT PRIOR FIRST LAST ABSOLUTE count RELATIVE count FORWARD または BACKWARD direction 句の省略は、 NEXT の指定と同じです。 SCROLL オプションを用いてカーソルを宣言または開かないと、 direction の値による逆方向への移動の要求は失敗します。

cursor 名は、開いているカーソルのポータルを参照する refcursor 変数名でなければなりません。

例:

FETCH curs1 INTO rowvar;
FETCH curs2 INTO foo, bar, baz;
FETCH LAST FROM curs3 INTO x, y;
FETCH RELATIVE -2 FROM curs4 INTO x;

40.7.3.2. MOVE

MOVE [
 

direction

 { FROM | IN } 
] 

cursor

;

MOVE コマンドは、データを取り出さないでカーソルの位置を変更します。 移動先の行を返さないでカーソルの位置だけを変更することを除けば、 FETCH コマンドと同一の働きをします。 SELECT INTO と同様に、特殊な変数 FOUND を用いて、移動先に行が存在するかどうかを検査できます。

direction 句は、 FETCH SQLコマンドで許されている以下の値のいずれかを取ることができます。 NEXT PRIOR FIRST LAST ABSOLUTE count RELATIVE count ALL FORWARD [ count | ALL ]、または BACKWARD [ count | ALL ] direction 句の省略は、 NEXT の指定と同じです。 SCROLL オプションを用いてカーソルを宣言または開かないと、 direction の値による逆方向への移動の要求は失敗します。

例:

MOVE curs1;
MOVE LAST FROM curs3;
MOVE RELATIVE -2 FROM curs4;
MOVE FORWARD 2 FROM curs4;

40.7.3.3. UPDATE/DELETE WHERE CURRENT OF

UPDATE 

table

 SET ... WHERE CURRENT OF 

cursor

;
DELETE FROM 

table

 WHERE CURRENT OF 

cursor

;

カーソルの位置をテーブルの行に変更すれば、カーソルによって特定した行を更新または消去できます。 カーソル問い合わせは何が許されているのか(特にグループ化しないとき)についての制限があり、それはカーソル内で FOR UPDATE を使用することが最善です。 より詳細については DECLARE マニュアルページを参照下さい。

以下に例を示します。

UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;

40.7.3.4. CLOSE

CLOSE 

cursor

;

CLOSE はポータルの背後にあるカーソルを閉じます。 これを使用してトランザクションの終わりよりも前にリソースを解放することができ、また、カーソル変数を解放し、再度開くことができます。

例:

CLOSE curs1;

40.7.3.5. カーソルを返す

PL/pgSQL 関数では、呼び出し元にカーソルを返すことができます。 この方法は、関数から複数行または複数列を返す場合、特にその結果集合が非常に大きい場合に有用です。 これを行うには、関数はカーソルを開き、呼び出し元にカーソル名を返します(もしくは、もし呼び出し元でポータル名がわかっていれば、単純に指定されたポータル名を使用してカーソルを開きます)。 これにより、呼び出し元はカーソルから行を取り出すことができるようになります。 カーソルは呼び出し元で閉じることができます。 または、トランザクションが終了した際に自動的に閉じられます。

カーソル用のポータル名は、プログラマが指定するか、または自動的に生成されます。 ポータル名を指定するには、開く前に、単に refcursor 変数に文字列を代入します。 refcursor 変数の文字列値は OPEN によって、背後のポータル名として使用されます。 しかし、 refcursor 変数がNULLの場合、 OPEN は自動的に既存のポータルと競合しない名前を生成し、それを refcursor 変数に代入します。

注意: バウンドカーソル変数は、その名前を表現する文字列値で初期化されます。 そのため、プログラマがカーソルを開く前に代入により上書きしない限り、ポータル名はカーソル変数と同じになります。 しかし、アンバウンドカーソル変数の初期値はデフォルトでNULLです。 そのため、上書きされていない場合に自動的に生成される一意な名前を受け取ります。

以下の例は、呼び出し元でカーソル名を指定する方法を示しています。

CREATE TABLE test (col text);
INSERT INTO test VALUES ('123');

CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
    OPEN $1 FOR SELECT col FROM test;
    RETURN $1;
END;
' LANGUAGE plpgsql;

BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;

以下の例では、自動的に生成されたカーソル名を使用しています。

CREATE FUNCTION reffunc2() RETURNS refcursor AS '
DECLARE
    ref refcursor;
BEGIN
    OPEN ref FOR SELECT col FROM test;
    RETURN ref;
END;
' LANGUAGE plpgsql;

-- カーソルを使用するには、トランザクション内部である必要があります。
BEGIN;
SELECT reffunc2();

      reffunc2      
--------------------
 <unnamed cursor 1>
(1 row)

FETCH ALL IN "<unnamed cursor 1>";
COMMIT;

以下の例は単一関数から複数のカーソルを返す方法を示しています。

CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$
BEGIN
    OPEN $1 FOR SELECT * FROM table_1;
    RETURN NEXT $1;
    OPEN $2 FOR SELECT * FROM table_2;
    RETURN NEXT $2;
END;
$$ LANGUAGE plpgsql;

-- カーソルを使用するには、トランザクション内部である必要があります。
BEGIN;

SELECT * FROM myfunc('a', 'b');

FETCH ALL FROM a;
FETCH ALL FROM b;
COMMIT;

40.7.4. カーソル結果に対するループ

カーソルで返される行に対して反復することができる FOR 文の亜種があります。 構文は以下のようになります。

[
 <<

label

>> 
]
[
 <<

label

>> 
]
FOR 

recordvar

 IN 

bound_cursorvar

 [
 ( [
 

argument_name

 := 
] 

argument_value

 [
, ...
] ) 
] LOOP
    

statements


END LOOP [
 

label

 
];

カーソル変数は宣言されたとき、何らかの問い合わせとバウンドされていなければならず、また既に開かれていては なりません FOR 文は自動的にカーソルを開き、ループから抜けたときに再度閉じます。 実際の引数値式のリストは、カーソルが引数を取ることを宣言された場合に限ってのみ出現できます。 これらの値は、 OPEN 過程と同じ方法で、問い合わせの中で置換されます( 項40.7.2.3 を参照してください)。

recordvar 変数は、 record 型として自動的に定義され、ループ内でのみ存在します (存在するいかなる変数名の定義もループ内では無視されます)。 カーソルによって返されたそれぞれの行はこのレコード変数に引き続いて割り当てられ、ループ本体が実行されます。


powered by SEO.CUG.NET