預存程序中使用holdable cursor - PolarDB

holdable模式可以使當前遊標不被立即釋放，從而實現遊標跨事務操作。本文介紹如何在預存程序中使用holdable cursor。

背景資訊

在預存程序中使用動態資料指標時，PostgreSQL原生不支援將動態資料指標設定為holdable模式（非預存程序中支援），如果在預存程序中開啟一個動態資料指標並執行事務修改儲存操作後，再次嘗試擷取資料將會報錯，因為進行事務儲存操作時當前事務中的遊標將會被釋放。

因此PolarDB PostgreSQL版（相容Oracle）為了滿足使用者需求，支援在PLSQL中使用holdable模式開啟預存程序遊標。

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在commit後被自動釋放，故顯示遊標myref1不存在。

使用指南

設定允許單個遊標跨事務

在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中使用holdable模式開啟單個遊標的具體語句如下：

OPEN cursorname HOLD FOR SELECT ...

顯示結果如下：

POLAR-SPL Procedure successfully completed

說明加關鍵字HOLD後，顯示POLAR-SPL程式執行成功。

設定允許所有遊標跨事務
您需要在控制台開啟polar_plsql_enable_holdable_refcursor參數，使您可以使用holdable模式開啟所有遊標。
說明
- 該參數修改不需要重啟資料庫，但只對新串連生效。若您使用長串連建議修改後自行選擇時間重啟。
- 該參數預設關閉。
- 開啟後將自動將所有預存程序中的遊標設定為HOLD模式，包括顯式遊標和遊標變數。
- 事務塊中遊標不受影響。

使用HOLD模式的動態資料指標時，請確保在當前預存程序結束前使用close語句顯式關閉該遊標。
HOLD模式下的動態資料指標即使預存程序結束也不會自動釋放，其消耗的記憶體與遊標返回的查詢條目數量相關，部分情況下會寫入臨時檔案。
大量使用HOLD而不關閉會對記憶體帶來顯著壓力，造成臨時檔案堆積，也有可能導致後續的讀取資料操作變慢。