Cursors - PolarDB - Alibaba Cloud ドキュメントセンター

このトピックでは、PL/SQLステートメントで使用されるカーソルについて説明します。

カーソル変数を宣言

PL/SQLのカーソルは、特別なデータ型のrefcursorであるカーソル変数を介してアクセスされます。 refcursor型の変数として宣言することで、カーソル変数を作成できます。または、次のカーソル宣言構文を使用してカーソル変数を作成することもできます。

name [ [ NO ] SCROLL ] CURSOR [ (引数) ] FORクエリ;

FORは、Oracleとの互換性のためにISに置き換えることができます。 SCROLLを指定すると、カーソルを逆方向にスクロールできます。 NO SCROLLが指定された場合、後方フェッチは拒否される。 SCROLLとNO SCROLLの両方が指定されていない場合、逆方向フェッチが許可されているかどうかを判断します。 引数は、コンマ (,) で区切られたname '''datatypeペアのリストを示します。 引数は、指定されたクエリのパラメーター値に置き換えられる変数の名前を定義します。変数名を置き換えるために使用される実際の値は、カーソルを開いたときに指定されます。

例：

DECLARE
        curs1 refcursor;
        SELECT * FROM tenk1のcurs2カーソル。
        curs3 CURSOR (キー整数) FOR SELECT * FROM tenk1 WHERE unique1 = key;

3つの変数はrefcursor型です。 curs1はすべてのクエリに使用できます。 curs2は特定のクエリにバインドされています。 curs3はパラメータ化されたクエリにバインドされます。 キーは、カーソルを開いたときにINTEGER型のパラメータ値に置き換えられます。 curs1は、特定のクエリにバインドされていないため、バインドされていないカーソル変数と見なされます。

Open cursors

カーソルを使用して行を取得する前に、カーソルを開いておく必要があります。この操作は、DECLARE CURSORステートメントの実行に相当します。 PL/SQLには、OPENステートメントの3つの形式があります。 OPENステートメントの2つの形式はアンバウンドカーソル変数に使用され、3つ目はバウンドカーソル変数に使用されます。

説明

FORステートメントを実行すると、カーソルを明示的に開かずにバインドされたカーソル変数を使用できます。

OPEN FORクエリ

OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR query;

カーソル変数が開かれ、実行されるクエリが指定される。カーソルを開くことはできません。カーソルはバインドされていないカーソル変数として宣言されている必要があります。これは単純なrefcursor変数です。クエリは、SELECTステートメント、または行を返すEXPLAINなどのステートメントである必要があります。このクエリは、PL/SQLの他のSQL文と同じ方法で処理されます。PL/SQL変数名が置き換えられ、再利用できるようにクエリプランがキャッシュされます。 PL/SQL変数名がカーソルクエリ内の値に置き換えられる場合、その値は、カーソルが開かれたときにカーソルが有する値である。その後の変数の変更は、カーソルの動作には影響しません。 SCROLLオプションとNO SCROLLオプションは、バインドされたカーソルに対して同じ意味を持ちます。

例：

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

OPEN FOR EXECUTE

OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR EXECUTE query_string
                                         [USING式 [, ... ] ];

カーソル変数が開かれ、指定されたクエリが実行されます。カーソルを開くことはできません。カーソルはバインドされていないカーソル変数として宣言されている必要があります。これは単純なrefcursor変数です。クエリは、EXECUTEステートメントと同じ方法で文字列式として指定されます。これにより、クエリプランが実行ごとに異なるように柔軟性が提供されます。これは、ステートメント文字列の変数名が置き換えられないことも示しています。 EXECUTEステートメントと同様に、format() メソッドとusingパラメーターを使用して、パラメーター値を動的ステートメントに挿入できます。 SCROLLオプションとNO SCROLLオプションは、バインドされたカーソルに対して同じ意味を持ちます。

例：

OPEN curs1 FOR EXECUTEフォーマット ('SELECT * FROM % I WHERE col1 = $1 '、tabname) キー値;

この例では、format() メソッドを使用して、テーブル名がクエリに挿入されます。 col1の比較値は、usingパラメータを使用して挿入されます。したがって、比較値を参照する必要はない。

バインドされたカーソルを開く

OPEN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ];

この形式のOPENステートメントは、宣言されたときにクエリにバインドされるカーソル変数を開くために使用されます。カーソルを開くことはできません。カーソルがパラメータを受け取ると宣言されている場合にのみ、実際のパラメータ値式のリストを表示する必要があります。これらの値は、クエリ内の変数名を置き換えるために使用されます。

カーソルがバインドされているクエリプランは、キャッシュ可能と見なされます。この場合、EXECUTEステートメントに相当するものは存在しません。カーソルのスクロール動作がすでに決まっているため、SCROLLおよびNO SCROLLをOPEN文に指定することはできません。

パラメーター値は、位置表記または名前付き表記を使用して渡すことができます。位置表記では、すべてのパラメータが順番に指定されます。名前付き表記では、:= を使用して各パラメーターを指定し、パラメーター名とパラメーター式を区切ります。位置表記と名前付き表記を一緒に使用できます。

例：

開くcurs2;
    OPEN curs3(42);
    OPEN curs3 (キー:= 42);

各例では、前述のサンプルカーソル宣言が使用されます。変数名は、カーソルがバインドされているクエリで置き換えられます。したがって、次のいずれかの方法を使用して値をカーソルに渡すことができます。OPENステートメントで明示的なパラメーターを指定するか、クエリでPL/SQL変数を暗黙的に参照します。ただし、カーソルがクエリにバインドされる前に宣言された変数の名前のみを、カーソルのパラメーター値で置き換えることができます。どちらの場合も、カーソルを開いたときに渡される値が指定されます。次のサンプルコードは、バインドされたカーソルを開く別の方法を示しています。これには、前述のcurs3の例と同じ効果があります。

DECLARE
        キー整数;
        curs4選択のためのカーソル * テン1からWHERE unique1=キー;
    BEGIN
        キー:= 42;
        開いたcurs4;

カーソルの使用

カーソルを開いた後、このトピックで説明されているステートメントを使用してカーソルを操作できます。

これらの操作は、カーソルを開くのと同じ関数で指定する必要はありません。関数からrefcursor値を返し、呼び出し元がカーソルに対して操作を実行できるようにすることができます。内部では、refcursor値は、カーソルのクエリを含むポータルの文字列名です。この名前は、ポータルを邪魔することなく、他のrefcursor変数に渡すか割り当てることができます。

トランザクション終了後、すべてのポータルは暗黙的に閉じられます。したがって、refcursor値は、トランザクションが終了する前にのみ、開かれたカーソルを参照するために使用できます。

フェッチ

FETCH [ direction { FROM | IN } ] カーソルINTO target;

FETCHステートメントは、SELECT intoステートメントと同じ方法で、カーソルから次の行を移動先に検索します。宛先は、行変数、レコード変数、または単純変数のコンマ区切りリストにすることができます。次の行が利用できない場合、宛先はNULLに設定されます。特別な変数FOUNDは、行が得られたかどうかを決定するためにチェックできる。

direction句は、複数の行をフェッチできるものを除いて、FETCHステートメントで許可されているバリアントの1つにすることができます。バリアントは、NEXT、PRIOR、FIRST、LAST、ABSOLUTE count、RELATIVE count、FORWARD、またはBACKWARDです。 direction句を省略することは、NEXT句を指定することと同じです。 count句を使用する場合、count句は整数式にすることができます。これは、整数定数のみを許可するFETCHステートメントとは異なります。カーソルを宣言またはSCROLLオプションで開かない限り、後方移動が必要なdirection句の値は失敗する可能性があります。

cursorは、開いているカーソルポータルを参照するrefcursor変数の名前である必要があります。

例：

FETCH curs1にrowvar;
    FETCH curs2 INTO foo、bar、baz;
    最後にcurs3からx、yにフェッチします。
    FETCH RELATIVE -2からcurs4へx;

移動

MOVE [ direction { FROM | IN } ] カーソル;

MOVEステートメントは、データを取得せずにカーソルの位置を変更します。 MOVEステートメントはFETCHステートメントと同じように機能しますが、MOVEステートメントはカーソルの位置を変更するだけで、移動先の行は返されません。移動する行が存在するかどうかを決定するために、特殊変数FOUNDをチェックすることができる。

例：

移動curs1;
    curs3から最後に移動します。
    RELATIVE -2をcurs4から移動します。
    curs4から2を前進させます。

現在の場所で更新 /削除

UPDATEテーブルSET... カーソルの現在の場所;
    カーソルの現在のテーブルから削除します。

カーソルがテーブル行に配置されると、その行を識別するためにカーソルを使用することによって、その行を更新または削除することができる。カーソルクエリは限られています。特に、グループ化は許可されない。カーソルでFOR UPDATEステートメントを使用することを推奨します。

例：

UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;

閉じる

近いカーソル;

CLOSEステートメントは、開いたカーソルの基になるポータルを閉じます。このステートメントを使用して、トランザクションが終了する前にリソースを解放したり、カーソル変数を解放して再度開くことができます。

例：

近いcurs1;

リターンカーソル

PL/SQL関数は、呼び出し元にカーソルを返すことができます。これは、特に非常に大きな結果セットで、複数の行または列を返すのに役立ちます。これを行うために、関数はカーソルを開き、カーソル名を呼び出し元に返します。または、関数は、呼び出し元によって指定された、または呼び出し元に知られているポータル名を使用してカーソルを開きます。次に、呼び出し元はカーソルから行をフェッチできます。カーソルは、呼び出し元が閉じるか、トランザクションが終了すると自動的に閉じることができます。

カーソルに使用されるポータル名は、プログラマが指定することも、自動的に生成することもできます。ポータル名を指定するには、変数を開く前にrefcursor変数に文字列を割り当てます。 refcursor変数の文字列値は、基になるポータルの名前としてOPENステートメントで使用されます。ただし、refcursor変数の値がnullの場合、OPENステートメントは、既存のポータル名と競合しない名前を自動的に生成し、その名前をrefcursor変数に割り当てます。

説明

バインドされたカーソル変数は、独自の名前を含むように初期化されます。したがって、デフォルトでは、基になるポータル名はカーソル変数名と同じです。プログラマがカーソルを開く前に値を割り当てて変数名を上書きすると、基礎となるポータル名はカーソル変数名とは異なります。デフォルトでは、バインドされていないカーソル変数の値はnullです。したがって、アンバインドされていないカーソル変数の名前が上書きされない限り、一意の名前が自動的に生成されます。

次のサンプルコードは、呼び出し元がカーソル名を指定する方法の例を示しています。

CREATE TABLEテスト (colテキスト);
    テスト値 ('123') に挿入します。

    CREATE FUNCTION reffunc(refcursor) RETURN refcursor IS
    BEGIN
        テストからのSELECT colのためのOPEN $1;
        RETURN $1;
    END; 

    BEGIN;
    SELECT reffunc('funccursor');
    functusorですべてをフェッチします。
    コミット;

次のサンプルコードは、カーソル名が自動的に生成される方法の例を示しています。

CREATE FUNCTION refunc2 () RETURN refcursor IS
    DECLARE
        ref refcursor;
    BEGIN
        テストからの選択colのための開いたref;
        リターンref;
    END; 

    -- トランザクションではカーソルが必要です。 
    BEGIN;
    SELECT reffunc2();

          reffunc2
    --------------------
     <名前のないカーソル1>
    （1行）

    FETCH ALL IN "<名前のないカーソル1>";
    コミット;

次のサンプルコードは、単一の関数から複数のカーソルを返す方法の例を示しています。

CREATE FUNCTION myfunc(refcursor, refcursor) RETURN SETOF refcursor IS
    BEGIN
        SELECT * FROM table_1のOPEN $1;
        RETURN NEXT $1;
        SELECT * FROM table_2のOPEN $2;
        RETURN NEXT $2;
    END;
    

    -- トランザクションではカーソルが必要です。 
    BEGIN;

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

    すべてをaからフェッチします。
    bからすべてのフェッチ;
    コミット;

ループの結果をカーソル

FORステートメントのバリアントでは、カーソルによって返された行を反復処理できます。構文：

[ <<ラベル>> ]
    レコードのためにbound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ] ループ
        statements
    END LOOP [ラベル];

カーソル変数は、宣言時にクエリにバインドされている必要があり、すでに開くことはできません。 FORステートメントは自動的にカーソルを開き、ループが終了するとカーソルを再び閉じます。カーソルがパラメータを受け取ると宣言されている場合にのみ、実際のパラメータ値式のリストを指定する必要があります。これらの値は、OPENステートメントと同じ方法で、クエリ内の変数名を置き換えます。

変数recordvarは自動的にrecord型として定義され、ループ内にのみ存在します。変数名の既存の定義はループ内で無視されます。このレコード変数には、カーソルによって返された各行が順次割り当てられ、ループ本体が実行されます。