ストアドプロシージャでのholdableカーソルの使用 - PolarDB

保持可能モードは、現在のカーソルが直ちに解放されないことを保証する。このようにして、トランザクション間でカーソル操作を実行できます。このトピックでは、ストアドプロシージャでholdableカーソルを使用する方法について説明します。

背景情報

ネイティブPostgreSQLのストアドプロシージャで動的カーソルを使用する場合、動的カーソルを保持可能モードに設定することはできません。ただし、非ストアドプロシージャは、保持可能モードの動的カーソルをサポートします。ストアドプロシージャで動的カーソルを開き、トランザクションを変更および保存した後、データを再度取得しようとすると、エラーが報告されます。この問題は、現在のトランザクションのカーソルがトランザクションの保存時に解放されるために発生します。

ビジネス要件を満たすために、PolarDB for PostgreSQL (Compatible with Oracle) では、PLSQLの保持可能モードを使用してストアドプロシージャでカーソルを開くことができます。

次の例は、ネイティブPostgreSQLのストアドプロシージャでエラーが報告される一般的なシナリオを示しています:

CREATE TABLE test001(id numeric);
INSERT INTO test001 VALUES (1), (2), (3);
CREATE OR REPLACE PROCEDURE testcur_001
IS
    DECLARE
        myref1 refcursor;
        i numeric;
    BEGIN
        OPEN myref1 FOR SELECT * from test001;
        commit;
        fetch myref1 into i;
        dbms_output.put_line(i);
        close myref1;
    END;

EXEC testcur_001;
DROP TABLE test001;

次の結果が返されます。

ERROR:  cursor "myref1" does not exist  
CONTEXT:  polar-spl function testcur_001() line 8 at FETCH

説明

myref1カーソルが存在しないため, エラーが返されます。この問題の原因は、カーソルがコミットされた後に自動的に解放されることです。

Usage

トランザクション間で単一のカーソルを許可する
PLSQLでholdableモードを使用して単一のカーソルを開くには、openステートメントのFORフィールドの前にHOLDキーワードを追加します。
例:
```
CREATE TABLE test001(id numeric);

INSERT INTO test001 VALUES (1), (2), (3);

CREATE OR REPLACE PROCEDURE testcur_001
IS
    DECLARE
        myref1 refcursor;
        i numeric;
    BEGIN
        OPEN myref1 HOLD FOR SELECT * from test001;
        commit;
        fetch myref1 into i;
        dbms_output.put_line(i);
        close myref1;
    END;

EXEC testcur_001;

DROP TABLE test001;
```
説明
次のステートメントを実行すると、PLSQLで保持可能モードを使用して単一のカーソルを開くことができます。
```
OPEN cursorname HOLD FOR SELECT ...
```
次の結果が返されます。
```
POLAR-SPL Procedure successfully completed       
```
ました。
説明
HOLDキーワードを追加すると、POLAR-SPLプログラムが期待どおりに実行されます。
トランザクション間のすべてのカーソルを許可
コンソールでpolar_plsql_enable_holdable_refcursorパラメーターを有効にする必要があります。この方法で、holdableモードを使用してすべてのカーソルを開くことができます。
説明
- このパラメーターは、データベースを再起動しなくても変更できます。新しいパラメーター値は、新しい接続に対してのみ有効です。永続的な接続を使用する場合は、このパラメーターの変更後に接続を再開する時間を選択することを推奨します。
- デフォルトでは、この機能は無効化されています。
- このパラメーターを有効にすると、明示的なカーソルやカーソル変数を含むすべてのストアドプロシージャのカーソルが自動的にHOLDモードに設定されます。
- トランザクションブロックのカーソルは影響を受けません。
HOLDモードの動的カーソルを使用する場合は、現在のストアドプロシージャが終了する前に、closeステートメントを使用してカーソルが明示的に閉じられていることを確認してください。
HOLDモードのダイナミックカーソルは、ストアドプロシージャが終了しても自動的には解除されません。消費されるメモリは、カーソルによって返されるクエリエントリの数によって異なります。場合によっては、一時ファイルが書き込まれます。
HOLDモードが多数のカーソルに使用され、クローズされていない場合、大量のメモリが消費されます。この場合、一時ファイルが蓄積され、後続のデータは低速で読み出される。