PHP小码农

Appearance

游标声明与打开 ​

游标（Cursor） 是 PL/pgSQL 中在一组查询结果上逐行推进的机制。它把「一次取出全部行」的 SELECT 拆成「打开 → 取一行 → 再取一行 → 关闭」的流式过程，从而把内存占用从「结果集大小」降到「一行大小」。本节聚焦游标的声明（DECLARE）与打开（OPEN）：refcursor 为什么是唯一的游标变量类型、绑定游标与未绑定游标的区别、SCROLL 与 NO SCROLL 的行为差异、OPEN ... FOR 三种形式的适用边界。读完本节，你能准确判断「该用游标还是该用 FOR ... IN 循环」，并写出正确的打开语句。

游标不是「慢」的代名词

很多资料把游标描述成「性能差的逐行处理」。这只说对了一半：游标的价值在于边算边取——当结果集很大、而你只打算处理前若干行，或者每一行的处理代价很高、需要尽早释放锁时，游标能显著降低内存与延迟。真正的问题是「用游标做本可以由一条 SQL 完成的事」。本节只讲机制，选型对照放在隐式游标一节。

前置知识

阅读本节前，建议先了解：EXIT / CONTINUE / RETURN

基础概念 ​

在 PL/pgSQL 里，「游标」这个词同时指代三样东西，先分清楚它们才不会混淆：

概念术语说明
游标变量Cursor VariablePL/pgSQL 中的变量，类型固定为 refcursor
门户Portal服务端内部结构，真正持有查询结果与读取位置
游标名Cursor Name字符串，用来在服务端标识一个门户

三者关系可以用一句话概括：游标变量（或字符串）指向门户，门户指向结果。

text
PL/pgSQL 变量层                服务端内部层            数据层
┌──────────────────┐          ┌──────────────┐       ┌──────────────┐
│ cur refcursor    │ ───────► │ Portal       │ ────► │ 查询结果行流 │
│ (值 = '名字')    │          │ (读取位置)   │       │ ...          │
└──────────────────┘          └──────────────┘       └──────────────┘

这里有一条贯穿全节的核心事实：refcursor 是 PL/pgSQL 里唯一的游标变量数据类型。你不需要（也不能）为每条查询定义一个「专门的游标类型」——所有游标变量都是 refcursor，靠运行时的值（一个字符串）去区分。

游标变量与普通变量的区别 ​

为什么游标要用一个特殊类型？因为游标变量的值不是「数据」，而是「名字」。

对比项普通变量（如 int）游标变量（refcursor）
存储内容具体的值一个字符串（门户名）
赋值来源:= 表达式:= 字符串，或 OPEN 时自动生成
生命周期随变量作用域随事务结束自动关闭
能否跨函数传递能能（作为函数参数/返回值）

正因为裸 refcursor 的初始值是「名字」而不是「结果」，它默认未绑定（unbound）——你必须先 OPEN ... FOR 给它一个查询，它才有内容可读。

游标的三种形态 ​

按「查询何时确定」，游标变量分为三种形态：

形态声明写法查询在哪确定典型用途
未绑定游标cur refcursor;运行时 OPEN ... FOR动态 SQL、返回游标给客户端
绑定游标cur CURSOR FOR SELECT ...;声明时写死固定查询、需要传参
带参绑定游标cur CURSOR (p int) FOR SELECT ...;声明时写死，参数运行时给固定查询 + 参数化

这三者共享同一套 FETCH / MOVE / CLOSE 操作，差别只在「怎么打开」。

绑定游标的性质 ​

绑定游标（也叫「绑定游标变量」，bound cursor variable）最容易被误解的一点是：它的值在 PG 16 之前是它自己的名字。

PostgreSQL 版本声明 cur CURSOR FOR ... 后，cur 的值
< 16字符串 'cur'（等于变量名本身）
≥ 16空（NULL），直到 OPEN 才被赋值

这个变化是 PG 16 的行为修正：以前把变量名当作门户名，会让「未 OPEN 就 FETCH」时意外去操作一个叫 cur 的已存在门户。现在的语义更干净——绑定游标在 OPEN 之前是不可用的。

门户的生命周期 ​

门户不是一个可以长期存在的东西，它绑定在事务上：

事件对门户的影响
OPEN创建门户，开始持有查询
FETCH / MOVE推进读取位置
CLOSE显式销毁门户
事务结束（COMMIT / ROLLBACK）所有门户隐式关闭

也就是说，除了语法上可以 CLOSE，事务边界是更根本的「回收站」。这一条对「返回游标给客户端」的用法至关重要——见下文「跨事务的游标」。

查询计划与游标的关系 ​

游标打开的那一刻，查询会被规划（plan）并将执行状态挂到门户上。它有两种执行策略：

策略触发条件行为
惰性（lazy）普通游标每次 FETCH 推进一点，按需取行
物化（materialized）声明/打开为 SCROLL一次性把结果算出来（按需物化，可能落临时文件）

synchronous_commit、work_mem 等参数会影响惰性游标的实际行为，但「一次取一点」这个基本模型不变。

语法与用法 ​

声明游标的语法原型 ​

text
-- 未绑定游标变量：只是一个字符串占位
名称 refcursor;

-- 绑定游标：声明时就绑定查询
名称 [ [ NO ] SCROLL ] CURSOR [ ( 参数名 参数类型 [, ...] ) ] FOR 查询;

-- 也可以是 IS（Oracle 兼容写法，与 FOR 等价）
名称 [ [ NO ] SCROLL ] CURSOR [ ( 参数名 参数类型 [, ...] ) ] IS 查询;

其中：

片段可选性含义
[ NO ] SCROLL可选是否允许向后读取；默认由查询决定
CURSOR必需声明这是游标而非普通变量
( 参数 )可选带参绑定游标的形参列表
FOR / IS必需二者等价，IS 是为 Oracle 迁移提供
查询必需任意 SELECT 或 VALUES

最小可运行示例：绑定游标 ​

sql
DO $$
DECLARE
    cur CURSOR FOR SELECT id, name FROM products ORDER BY id;
    r   record;
BEGIN
    OPEN cur;
    FETCH cur INTO r;
    RAISE NOTICE '第一行: % / %', r.id, r.name;
    CLOSE cur;
END;
$$;
text
NOTICE:  第一行: 1 / 机械键盘
DO

最小可运行示例：未绑定游标 ​

sql
DO $$
DECLARE
    cur refcursor;
    r   record;
BEGIN
    OPEN cur FOR SELECT id, name FROM products ORDER BY id;
    FETCH cur INTO r;
    RAISE NOTICE '第一行: % / %', r.id, r.name;
    CLOSE cur;
END;
$$;
text
NOTICE:  第一行: 1 / 机械键盘
DO

最小可运行示例：带参绑定游标 ​

sql
DO $$
DECLARE
    cur CURSOR (min_price numeric) FOR
        SELECT id, name, price FROM products WHERE price >= min_price ORDER BY id;
    r record;
BEGIN
    OPEN cur (100);
    FETCH cur INTO r;
    RAISE NOTICE '% 的价格 %', r.name, r.price;
    CLOSE cur;
END;
$$;
text
NOTICE:  显示器 的价格 1299.00
DO

注意 OPEN 时参数值要用括号包住：OPEN cur (100);。参数可以有多组，顺序与声明一致。

三种 OPEN 形式 ​

OPEN 有且仅有三种形式，用途严格区分：

text
-- 形式一：给未绑定游标变量绑定查询
OPEN 未绑定游标变量 [ [ NO ] SCROLL ] FOR 查询;

-- 形式二：动态 SQL，查询是一条字符串
OPEN 未绑定游标变量 [ [ NO ] SCROLL ] FOR EXECUTE 查询字符串 [ USING 参数 [, ...] ];

-- 形式三：打开绑定游标（参数可用位置写法或命名写法）
OPEN 绑定游标变量 [ ( [ 参数名 { := | => } ] 参数值 [, ...] ) ];
形式适用游标查询来源关键限制
OPEN ... FOR 查询未绑定静态 SQL可在 FOR 前写 [ [ NO ] SCROLL ]；查询在编译期解析
OPEN ... FOR EXECUTE未绑定字符串可在 FOR 前写 [ [ NO ] SCROLL ]；支持 USING 传参
OPEN 绑定游标 ( 参数 )绑定声明处不能写 SCROLL（滚动行为在声明时已定）

形式三的参数支持位置写法与命名写法，与函数调用一致，也可混用：

sql
OPEN curs3(42);
OPEN curs3(key := 42);
OPEN curs3(key => 42);

关键限制：绑定游标的 OPEN 不能再指定 SCROLL ​

这是最容易踩的坑之一：

sql
-- 错误！SCROLL 必须在声明处指定，不能在 OPEN 时追加
DO $$
DECLARE
    cur CURSOR FOR SELECT id FROM products;
BEGIN
    OPEN cur SCROLL;          -- 语法错误
END;
$$;
text
ERROR:  syntax error at or near "SCROLL"
LINE 6:     OPEN cur SCROLL;
                   ^

正确做法：

sql
-- 推荐：在声明处决定滚动性
DO $$
DECLARE
    cur SCROLL CURSOR FOR SELECT id FROM products ORDER BY id;
BEGIN
    OPEN cur;
    -- 现在可以 FETCH PRIOR / FETCH LAST
END;
$$;

详细说明 ​

为什么唯一的游标类型是 refcursor ​

PostgreSQL 的设计里，游标变量不是「一个有字段的结构体」，而是「一个指向服务端门户的名字」。所以它只需要一种类型：refcursor。

你可能想做的事PostgreSQL 的做法
声明多个不同类型的游标都声明为 refcursor，值不同即可
把游标当参数传形参写 refcursor
从函数返回游标RETURNS refcursor
把游标存到表里存它的名字字符串，但那只是名字，不是活游标

refcursor 的值是一个字符串，所以它和 text 之间可以自由转换：

sql
DO $$
DECLARE
    cur refcursor := 'my_portal';
    s   text;
BEGIN
    OPEN cur FOR SELECT id FROM products;
    s := cur;                       -- 隐式转换：拿到门户名
    RAISE NOTICE '门户名 = %', s;
    CLOSE cur;
END;
$$;
text
NOTICE:  门户名 = my_portal
DO

这解释了「返回游标」的实现原理：函数只是把门户名交还给调用方，调用方在同一事务内再用这个名字去 FETCH。

未绑定游标：名字由谁决定 ​

未绑定游标可以用两种方式获得名字。

方式一：自己显式赋值。

sql
DO $$
DECLARE
    cur refcursor := 'report_cursor';
BEGIN
    OPEN cur FOR SELECT id FROM products;
    -- 门户名就是 'report_cursor'
    CLOSE cur;
END;
$$;

方式二：不赋值，让系统自动生成。

sql
DO $$
DECLARE
    cur refcursor;
BEGIN
    OPEN cur FOR SELECT id FROM products;
    RAISE NOTICE '自动门户名 = %', cur;
    CLOSE cur;
END;
$$;
text
NOTICE:  自动门户名 = <unnamed portal 1>
DO

自动生成的名字形如 <unnamed portal N>，其中 N 在每个会话内递增。名字里带尖括号，所以它在 SQL 层不是合法的标识符——你只能通过变量来操作它，不能在 FETCH 里直接写 FETCH "<unnamed portal 1>"（除非加双引号并且它确实存在）。

名字重复会报错：如果一个门户名已被占用，再 OPEN 一个同名的就会失败。

sql
-- 错误！门户名重复
DO $$
DECLARE
    c1 refcursor := 'dup';
    c2 refcursor := 'dup';
BEGIN
    OPEN c1 FOR SELECT 1;
    OPEN c2 FOR SELECT 2;   -- ERROR: cursor "dup" already in use
END;
$$;
text
ERROR:  cursor "dup" already in use

对应 SQLSTATE 为 42P03（duplicate_cursor）。

绑定游标：声明即绑定 ​

绑定游标把查询写在 DECLARE 段，语义上相当于「预备好一条查询，只是还没执行」。

sql
DO $$
DECLARE
    cur CURSOR FOR SELECT id, name, price FROM products ORDER BY id;
    r   record;
BEGIN
    OPEN cur;
    LOOP
        FETCH cur INTO r;
        EXIT WHEN NOT FOUND;         -- FOUND 是内置游标状态变量
        RAISE NOTICE '% - %', r.id, r.name;
    END LOOP;
    CLOSE cur;
END;
$$;
text
NOTICE:  1 - 机械键盘
NOTICE:  2 - 显示器
NOTICE:  3 - 鼠标垫
DO

FOUND 是 PL/pgSQL 的内置布尔变量，每次 FETCH 后由系统自动设置：取到行则为 true，取不到了则为 false。它是绑定、未绑定游标与 SELECT INTO 共用的状态变量。

绑定游标的查询里可以引用外层变量——因为查询在函数编译期只能看到「当时的」外围变量？不，实际是在 OPEN 时求值的。这在下面「变量捕获」小节展开。

带参绑定游标：参数与作用域 ​

带参游标把「参数」写成声明的一部分，调用时用括号传入：

sql
DO $$
DECLARE
    cur CURSOR (lo numeric, hi numeric) FOR
        SELECT id, name, price FROM products
        WHERE price BETWEEN lo AND hi
        ORDER BY price;
    r record;
BEGIN
    OPEN cur (100, 500);
    LOOP
        FETCH cur INTO r;
        EXIT WHEN NOT FOUND;
        RAISE NOTICE '% = %', r.name, r.price;
    END LOOP;
    CLOSE cur;
END;
$$;
text
NOTICE:  键盘托架 = 199.00
NOTICE:  鼠标垫 = 299.00
DO

参数名只在游标内部可见，和函数体的其他变量不冲突。参数类型必须是明确可推断的，不允许 record 之类的伪类型。

参数与 OPEN ... FOR 的互斥：带参游标只能用形式三打开，不能再用 OPEN cur FOR ... 覆盖它的查询——因为查询已经绑死了。

SCROLL 与 NO SCROLL ​

SCROLL 允许向后移动读取位置（FETCH PRIOR、FETCH LAST、FETCH BACKWARD 等），代价是可能需要物化整个结果集。

sql
DO $$
DECLARE
    cur SCROLL CURSOR FOR SELECT id FROM products ORDER BY id;
    r record;
BEGIN
    OPEN cur;
    FETCH LAST FROM cur INTO r;      -- 先看最后一行
    RAISE NOTICE '最后一行 id = %', r.id;
    FETCH FIRST FROM cur INTO r;     -- 再回到第一行
    RAISE NOTICE '第一行 id = %', r.id;
    CLOSE cur;
END;
$$;
text
NOTICE:  最后一行 id = 3
NOTICE:  第一行 id = 1
DO
声明允许向后读是否可能物化与 FOR UPDATE 兼容
NO SCROLL否否可以
（默认，不写）由查询决定视情况可以
SCROLL是是不可以

关键限制：SCROLL 不能与 FOR UPDATE / FOR SHARE 同时使用。

sql
-- 错误！SCROLL 与 FOR UPDATE 冲突
DO $$
DECLARE
    cur SCROLL CURSOR FOR SELECT id FROM products FOR UPDATE;
BEGIN
    OPEN cur;
END;
$$;
text
ERROR:  DECLARE SCROLL CURSOR ... FOR UPDATE is not supported
DETAIL:  scrollable cursors must be READ ONLY.

如果查询里含有易变函数（volatile function），官方文档建议用 NO SCROLL，因为向后读的「重读」语义对易变函数没有明确定义——同一个位置读两次可能得到不同结果。

sql
-- 推荐：含易变函数时显式声明 NO SCROLL
DO $$
DECLARE
    cur NO SCROLL CURSOR FOR
        SELECT id, random() AS rnd FROM products ORDER BY id;
BEGIN
    OPEN cur;
END;
$$;

变量捕获：查询在 OPEN 时求值 ​

绑定游标查询里引用的外部变量，是在 OPEN 执行时被求值的，而不是在声明时。这一点和很多人的直觉相反。

sql
DO $$
DECLARE
    threshold numeric := 100;
    cur CURSOR FOR SELECT id, name FROM products WHERE price >= threshold;
    r record;
BEGIN
    threshold := 1000;         -- 在 OPEN 之前改了它
    OPEN cur;                  -- 此刻才用 threshold 的值
    LOOP
        FETCH cur INTO r;
        EXIT WHEN NOT FOUND;
        RAISE NOTICE '% (>=1000)', r.name;
    END LOOP;
    CLOSE cur;
END;
$$;
text
NOTICE:  显示器 (>=1000)
DO

所以正确的心智模型是：声明只是「记下查询文本」，OPEN 才真正把当前的变量值填进去并规划执行。这也解释了为什么同一条绑定游标可以在循环里反复 OPEN（每次都拿最新的变量值）——不过实际中通常用带参游标替代，更清晰。

返回游标：把门户交给客户端 ​

函数可以返回 refcursor，实现「把结果集流式交给调用方」：

sql
CREATE OR REPLACE FUNCTION open_products_cur() RETURNS refcursor AS $$
DECLARE
    cur refcursor;
BEGIN
    OPEN cur FOR SELECT id, name, price FROM products ORDER BY id;
    RETURN cur;
END;
$$ LANGUAGE plpgsql;

调用方必须在同一事务内取数：

sql
BEGIN;
SELECT open_products_cur();
text
 open_products_cur
-------------------
 <unnamed portal 1>
(1 row)

然后在同一事务里 FETCH：

sql
FETCH ALL FROM "<unnamed portal 1>";
text
 id |   name   |  price
----+----------+---------
  1 | 机械键盘 | 599.00
  2 | 显示器   | 1299.00
  3 | 鼠标垫   |  299.00
(3 rows)

一旦 COMMIT，门户消失，再 FETCH 就会报 34000（invalid_cursor_name）。

sql
COMMIT;
FETCH ALL FROM "<unnamed portal 1>";
text
ERROR:  cursor "<unnamed portal 1>" does not exist

要让它跨事务存活，需要 WITH HOLD——但那是 SQL 层 DECLARE 的选项，PL/pgSQL 的游标声明没有 WITH HOLD。跨事务游标要用 SQL 层 DECLARE 或在函数外声明。

SQL 层 DECLARE：与 PL/pgSQL 声明的区别 ​

游标也可以在事务里的 SQL 层用 DECLARE 创建，语法与 PL/pgSQL 不同：

text
DECLARE 名称 [ BINARY ] [ ASENSITIVE | INSENSITIVE ]
        [ [ NO ] SCROLL ] CURSOR
        [ { WITH | WITHOUT } HOLD ] FOR 查询;
选项作用说明
BINARY以二进制格式返回数据客户端需能解析
ASENSITIVE / INSENSITIVE敏感性声明PostgreSQL 中无实际效果，仅 SQL 兼容
[ NO ] SCROLL能否向后读与 PL/pgSQL 一致
{ WITH | WITHOUT } HOLD能否跨事务存活仅 SQL 层有此选项
sql
BEGIN;
DECLARE held_cursor CURSOR WITH HOLD FOR SELECT id, name FROM products ORDER BY id;
FETCH 1 FROM held_cursor;
COMMIT;
-- 事务已结束，但 WITH HOLD 游标仍然可用
FETCH ALL FROM held_cursor;
CLOSE held_cursor;

WITH HOLD 的游标在提交时会把结果物化（复制到临时文件或内存），因此不能用 FOR UPDATE / FOR SHARE：

text
ERROR:  DECLARE CURSOR WITH HOLD ... FOR UPDATE is not supported
DETAIL:  holdable cursors must be READ ONLY.

用 pg_cursors 查看当前会话的游标 ​

系统视图 pg_cursors 列出当前会话里所有打开的门户：

sql
BEGIN;
DECLARE v_cur CURSOR FOR SELECT id FROM products;
SELECT name, statement, is_holdable, is_scrollable FROM pg_cursors WHERE name = 'v_cur';
text
 name  |         statement          | is_holdable | is_scrollable
-------+----------------------------+-------------+---------------
 v_cur | SELECT id FROM products;   | f           | f
(1 row)
字段含义
name门户名
statement门户对应的查询文本
is_holdable是否 WITH HOLD
is_scrollable是否可向后读
creation_time创建时间
is_binary是否二进制格式

这是一个非常实用的排障工具：当函数里「游标打不开」时，SELECT * FROM pg_cursors; 能告诉你当前到底有哪些门户、名字有没有冲突。

打开游标的规划时机 ​

有一类坑来自「游标在循环里反复打开」。每次 OPEN 都会重新规划查询：

sql
DO $$
DECLARE
    cur refcursor;
    r   record;
    pid int;
BEGIN
    FOR pid IN SELECT id FROM products ORDER BY id LOOP
        OPEN cur FOR SELECT id, name FROM products WHERE id = pid;
        FETCH cur INTO r;
        RAISE NOTICE '处理 %', r.name;
        CLOSE cur;              -- 必须关闭，否则同名/自动名会堆积
    END LOOP;
END;
$$;
text
NOTICE:  处理 机械键盘
NOTICE:  处理 显示器
NOTICE:  处理 鼠标垫
DO

如果循环里 OPEN 却不 CLOSE，门户会一直堆积到事务结束，占用内存并可能触达上限。在循环里打开游标，必须配对关闭。

实战示例 ​

示例一：按价格区间遍历商品并汇总 ​

需求：给定价格区间，遍历区间内商品，输出名称与价格，并统计数量。

sql
DO $$
DECLARE
    cur       CURSOR (lo numeric, hi numeric) FOR
                  SELECT name, price FROM products
                  WHERE price BETWEEN lo AND hi ORDER BY price;
    r         record;
    cnt       int := 0;
BEGIN
    OPEN cur (100, 2000);
    LOOP
        FETCH cur INTO r;
        EXIT WHEN NOT FOUND;
        RAISE NOTICE '% -> %', r.name, r.price;
        cnt := cnt + 1;
    END LOOP;
    CLOSE cur;
    RAISE NOTICE '共 % 件', cnt;
END;
$$;
text
NOTICE:  键盘托架 -> 199.00
NOTICE:  鼠标垫 -> 299.00
NOTICE:  机械键盘 -> 599.00
NOTICE:  显示器 -> 1299.00
NOTICE:  共 4 件
DO

示例二：动态 SQL + 未绑定游标 ​

需求：表名与排序列由参数决定，用未绑定游标打开动态查询。

sql
CREATE OR REPLACE FUNCTION list_dynamic(p_table text, p_limit int)
RETURNS void AS $$
DECLARE
    cur refcursor;
    r   record;
    n   int := 0;
BEGIN
    OPEN cur FOR EXECUTE
        format('SELECT * FROM %I ORDER BY 1 LIMIT $1', p_table)
        USING p_limit;
    LOOP
        FETCH cur INTO r;
        EXIT WHEN NOT FOUND;
        n := n + 1;
    END LOOP;
    CLOSE cur;
    RAISE NOTICE '从 % 取到 % 行', p_table, n;
END;
$$ LANGUAGE plpgsql;

SELECT list_dynamic('products', 2);
text
NOTICE:  从 products 取到 2 行
 list_dynamic
--------------
(1 row)

注意 %I 用于标识符转义，$1 用于参数占位，二者配合可以安全拼接动态表名。

示例三：返回游标给调用方 ​

需求：封装一个「打开员工列表」的函数，供客户端分批拉取。

sql
CREATE TABLE IF NOT EXISTS employees (
    id     int PRIMARY KEY,
    name   text,
    salary numeric
);

INSERT INTO employees VALUES
    (1, '张三', 12000),
    (2, '李四', 18000),
    (3, '王五', 9000)
ON CONFLICT DO NOTHING;

CREATE OR REPLACE FUNCTION open_emp_cur(p_min numeric DEFAULT 0)
RETURNS refcursor AS $$
DECLARE
    cur refcursor := 'emp_portal';
BEGIN
    OPEN cur FOR
        SELECT id, name, salary FROM employees
        WHERE salary >= p_min ORDER BY salary DESC;
    RETURN cur;
END;
$$ LANGUAGE plpgsql;
sql
BEGIN;
SELECT open_emp_cur(10000);
FETCH 1 FROM emp_portal;
FETCH 1 FROM emp_portal;
COMMIT;
text
 open_emp_cur
--------------
 emp_portal
(1 row)

 id | name | salary
----+------+--------
  2 | 李四 |  18000
(1 row)

 id | name | salary
----+------+--------
  1 | 张三 |  12000
(1 row)

示例四：SCROLL 游标实现「来回翻页」 ​

需求：既要看第一行，也要看最后一行，还要往回退一行。

sql
DO $$
DECLARE
    cur SCROLL CURSOR FOR SELECT id, name FROM products ORDER BY id;
    r   record;
BEGIN
    OPEN cur;
    FETCH FIRST FROM cur INTO r;
    RAISE NOTICE '首行: %', r.name;
    FETCH LAST FROM cur INTO r;
    RAISE NOTICE '末行: %', r.name;
    FETCH PRIOR FROM cur INTO r;
    RAISE NOTICE '末行前一行: %', r.name;
    CLOSE cur;
END;
$$;
text
NOTICE:  首行: 机械键盘
NOTICE:  末行: 鼠标垫
NOTICE:  末行前一行: 显示器
DO

示例五：验证门户生命周期 ​

需求：观察显式 CLOSE 与事务结束两种回收路径。

sql
BEGIN;
DECLARE p1 CURSOR FOR SELECT 1;
DECLARE p2 CURSOR FOR SELECT 2;
SELECT count(*) AS before_close FROM pg_cursors WHERE name IN ('p1', 'p2');
CLOSE p1;
SELECT count(*) AS after_close_p1 FROM pg_cursors WHERE name IN ('p1', 'p2');
COMMIT;
SELECT count(*) AS after_commit FROM pg_cursors WHERE name IN ('p1', 'p2');
text
 before_close
--------------
            2
(1 row)

 after_close_p1
----------------
              1
(1 row)

 after_commit
--------------
            0
(1 row)

注意事项 ​

  • refcursor 是唯一游标类型，不要尝试定义别的类型。
  • 绑定游标在 OPEN 前不可用：PG 16 起绑定游标变量的值为 NULL；旧版本等于变量名，属于历史行为。
  • 门户名冲突会报错 42P03（duplicate_cursor）：命名未绑定游标或 SQL 层 DECLARE 时尤其要注意。
  • SCROLL 与 FOR UPDATE / FOR SHARE 互斥，且 SCROLL 可能引起物化。
  • 含易变函数的查询建议 NO SCROLL。
  • WITH HOLD 只属于 SQL 层 DECLARE，PL/pgSQL 的游标声明没有这个选项。
  • 事务结束会关闭所有门户；要跨事务必须 WITH HOLD 且该游标只读。
  • 循环里 OPEN 必须配对 CLOSE，否则门户堆积。
错误写法原因正确做法
OPEN cur SCROLL;语法错误，SCROLL 不能出现在 OPEN声明处写 SCROLL
DECLARE cur SCROLL CURSOR ... FOR UPDATE滚动游标必须只读去掉 SCROLL 或去掉 FOR UPDATE
OPEN c1...; OPEN c2...;（同名）门户名重复 42P03用不同名字
DECLARE cur CURSOR WITH HOLD ...（PL/pgSQL）PL/pgSQL 无此选项用 SQL 层 DECLARE ... WITH HOLD
循环里只 OPEN 不 CLOSE门户堆积每次 OPEN 后 CLOSE
COMMIT; FETCH ...门户已随事务关闭 34000同事务内取数，或用 WITH HOLD

最佳实践 ​

  • 优先用带参绑定游标代替「循环里反复 OPEN」：参数让语义更清晰，避免门户堆积。
  • 只读、小结果集，优先考虑 FOR ... IN 隐式游标：它自动开关、代码更短。
  • 需要向后读，明确写 SCROLL；否则写 NO SCROLL：显式声明滚动性比依赖默认更可读。
  • 含易变函数的查询一律 NO SCROLL。
  • 给未绑定游标取有意义的名字：自动名 <unnamed portal N> 在排障时难以辨认。
  • 跨事务返回结果用 WITH HOLD 或分批 LIMIT/OFFSET，不要指望普通门户存活。
场景推荐反例
固定查询 + 参数带参绑定游标循环里拼字符串 OPEN
小结果集遍历FOR r IN SELECT ...手写 OPEN/FETCH/CLOSE
大结果集 + 早退显式游标 + EXIT WHEN一次性 SELECT INTO 全部
需要向后读SCROLL CURSOR默认游标 FETCH PRIOR（可能失败）
返回给客户端RETURNS refcursor + 同事务 FETCH跨事务 FETCH（34000）
sql
-- 推荐：语义清晰的带参游标
cur CURSOR (min numeric) FOR
    SELECT * FROM products WHERE price >= min ORDER BY id;

-- 不推荐：循环内反复 OPEN 同一个未绑定游标且不取名
FOR i IN 1..1000 LOOP
    OPEN c FOR SELECT * FROM t WHERE id = i;   -- 门户堆积风险
    ...
END LOOP;

下一节

下一节我们学习：FETCH / MOVE / CLOSE，掌握游标打开之后如何精确控制读取位置、如何在结果集内前后移动，以及如何正确关闭门户。

参考链接 ​