如何在存储过程中使用holdable cursor_云原生数据库 PolarDB(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而不关闭会对内存带来显著压力，造成临时文件堆积，也有可能导致后续的读取数据操作变慢。