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而不关闭会对内存带来显著压力，造成临时文件堆积，也有可能导致后续的读取数据操作变慢。