PL/pgSQL 入门教程（四）：使用游标（cursor）-阿里云开发者社区

一、什么是游标？为什么要用游标？

1.1 游标的本质

你可以把游标理解为 PostgreSQL 中的“数据指针”——它不像普通 SELECT 语句那样一次性把所有查询结果加载到内存，而是像翻书一样，每次只读取一行或几行数据，还能自由控制读取的方向（向前、向后）和位置。

1.2 游标能解决什么问题？

避免内存溢出：查询几十万、几百万行数据时，一次性加载会占满内存，游标逐行读取可规避这个问题；
逐行处理数据：需要对每行数据做个性化处理（比如调用函数、复杂计算、条件更新）时，游标能精准操作每一行；
函数返回大结果集：PL/pgSQL 函数无法直接返回超大结果集，游标可以作为“桥梁”，让调用方按需读取数据；
精准更新/删除指定行：通过游标定位到某一行后，可直接更新或删除这一行。

小提醒：PL/pgSQL 的 FOR 循环会自动用游标，日常简单遍历不用手动写游标，但复杂场景（比如动态查询、返回结果集）必须手动操作。

二、游标基础：变量与分类

2.1 游标变量的类型

所有游标都基于 refcursor 类型的变量，这是 PostgreSQL 专门为游标设计的特殊类型，你可以把它理解为“游标身份证”。

2.2 游标的两种分类

类型	特点	适用场景
未绑定游标	声明时不指定查询，打开时再定义	动态查询（比如表名、条件可变）
绑定游标	声明时就绑定固定查询（可带参数）	固定逻辑的重复查询

三、游标核心操作：声明 → 打开 → 使用 → 关闭

3.1 第一步：声明游标变量

声明有两种方式，对应两种游标类型：

方式1：声明未绑定游标（最灵活）

直接声明 refcursor 类型变量，后续可绑定任意查询：

DECLARE
    cur_unbound refcursor; -- 未绑定游标，无固定查询

方式2：声明绑定游标（固定查询）

声明时直接绑定查询，支持带参数，语法：

name [ [ NO ] SCROLL ] CURSOR [ (参数名 类型) ] FOR 查询语句;

SCROLL：允许游标向后读取（比如读上一行、最后一行）；
NO SCROLL：禁止向后读取，仅能向前；
参数：查询中可使用的变量，打开时传值。

示例：

DECLARE
    -- 绑定固定查询（无参数）：查询用户表所有数据
    cur_bind1 CURSOR FOR SELECT id, name FROM t_user;

    -- 绑定参数化查询：按用户ID范围查询
    cur_bind2 CURSOR (min_id int, max_id int) FOR 
        SELECT id, name FROM t_user WHERE id BETWEEN min_id AND max_id;

3.2 第二步：打开游标

游标必须“打开”才能用，相当于给游标“加载查询逻辑”。PL/pgSQL 有3种打开方式，对应不同场景：

方式1：打开未绑定游标（固定查询）

语法：

OPEN 未绑定游标名 [ SCROLL | NO SCROLL ] FOR 查询语句;

示例：

-- 给未绑定游标绑定“查询订单表”的逻辑
OPEN cur_unbound FOR SELECT order_id, amount FROM t_order WHERE status = 'paid';

方式2：打开未绑定游标（动态查询）

如果查询的表名、字段名是变量（比如用户传入），用 EXECUTE 动态拼接查询：
语法：

OPEN 未绑定游标名 FOR EXECUTE 动态查询字符串 [ USING 参数1, 参数2... ];

示例：

DECLARE
    tab_name text := 't_order'; -- 动态表名
    status_val text := 'paid';  -- 查询条件参数
BEGIN
    -- 拼接查询语句（%I 避免表名/字段名注入），USING 传参数（避免SQL注入）
    OPEN cur_unbound FOR EXECUTE 
        format('SELECT order_id, amount FROM %I WHERE status = $1', tab_name)
        USING status_val;
END;

方式3：打开绑定游标

绑定游标声明时已有查询，打开时只需传参数（如果有）：
语法：

OPEN 绑定游标名 [ (参数值1, 参数值2...) ];
-- 或命名参数（更清晰）
OPEN 绑定游标名 (参数名 := 参数值);

示例：

-- 打开无参数的绑定游标
OPEN cur_bind1;

-- 打开带参数的绑定游标（两种传参方式）
OPEN cur_bind2(1, 100); -- 位置传参：min_id=1，max_id=100
-- OPEN cur_bind2(min_id := 1, max_id := 100); -- 命名传参（推荐）

3.3 第三步：使用游标（核心操作）

打开游标后，可通过 FETCH、MOVE、UPDATE/DELETE 操作数据：

1. FETCH：读取游标数据

最常用的操作，把游标指向的行读取到变量中。
语法：

FETCH [ 方向 ] FROM 游标名 INTO 变量1 [, 变量2...];

方向可选：NEXT（下一行，默认）、PRIOR（上一行）、FIRST（第一行）、LAST（最后一行）、RELATIVE n（相对当前位置移动n行）；
变量：可以是行变量、记录变量，或多个普通变量（与查询字段一一对应）。

示例：

DECLARE
    cur_unbound refcursor;
    v_order_id int;
    v_amount numeric;
BEGIN
    -- 打开游标
    OPEN cur_unbound FOR SELECT order_id, amount FROM t_order WHERE status = 'paid';

    -- 读取第一行
    FETCH NEXT FROM cur_unbound INTO v_order_id, v_amount;
    RAISE NOTICE '第一行订单：ID=%，金额=%', v_order_id, v_amount;

    -- 读取上一行（需游标声明/打开时加SCROLL）
    FETCH PRIOR FROM cur_unbound INTO v_order_id, v_amount;
    RAISE NOTICE '回到上一行：ID=%，金额=%', v_order_id, v_amount;
END;

2. MOVE：移动游标位置（不读取数据）

只想调整游标位置，不想读取数据时用 MOVE，语法和 FETCH 几乎一样：

MOVE [ 方向 ] FROM 游标名;

示例：

-- 把游标移动到最后一行（不读取数据）
MOVE LAST FROM cur_unbound;
-- 从最后一行向前移动2行
MOVE RELATIVE -2 FROM cur_unbound;

3. UPDATE/DELETE：修改游标当前行

游标定位到某一行后，可直接修改/删除这一行，精准度极高：
语法：

-- 更新当前行
UPDATE 表名 SET 字段 = 值 WHERE CURRENT OF 游标名;
-- 删除当前行
DELETE FROM 表名 WHERE CURRENT OF 游标名;

示例：

DECLARE
    cur_unbound refcursor;
    v_order_id int;
    v_amount numeric;
BEGIN
    -- 打开游标（加FOR UPDATE锁定行，避免并发修改）
    OPEN cur_unbound FOR SELECT order_id, amount FROM t_order WHERE id = 100 FOR UPDATE;

    -- 读取目标行
    FETCH NEXT FROM cur_unbound INTO v_order_id, v_amount;

    -- 更新当前行的金额
    UPDATE t_order SET amount = v_amount * 0.9 WHERE CURRENT OF cur_unbound;
END;

3.4 第四步：关闭游标

用完游标后必须关闭，释放服务器资源（门户/portal）：
语法：

CLOSE 游标名;

示例：

CLOSE cur_unbound;

四、简化操作：FOR循环自动遍历游标

如果只是简单遍历游标结果，不用手动写 OPEN/FETCH/CLOSE，PL/pgSQL 的 FOR 循环会自动处理，这是日常最常用的方式。

语法：

[ <<循环标签>> ]
FOR 记录变量 IN 绑定游标名 [ (参数值) ] LOOP
    -- 循环体：处理每一行数据
END LOOP [ 循环标签 ];

记录变量：自动创建 record 类型变量，无需提前声明；
游标会自动打开，循环结束后自动关闭。

示例：遍历用户表数据

CREATE OR REPLACE FUNCTION fn_loop_cursor()
RETURNS void AS $$
DECLARE
    -- 声明绑定游标：查询ID<10的用户
    cur_user CURSOR (max_id int) FOR SELECT id, name FROM t_user WHERE id < max_id;
    v_user record; -- 接收每行数据的记录变量
BEGIN
    -- FOR循环自动打开游标，遍历完自动关闭
    FOR v_user IN cur_user(10) LOOP
        RAISE NOTICE '用户ID：%，姓名：%', v_user.id, v_user.name;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT fn_loop_cursor();

五、高级用法：函数返回游标（返回大结果集）

PL/pgSQL 函数无法直接返回几十万行数据，但可以返回游标，让调用方按需读取。注意：游标依赖事务，必须在事务内调用！

场景1：返回单个游标（自动生成游标名）

-- 1. 创建测试表
CREATE TABLE t_product (id int, name text, price numeric);
INSERT INTO t_product VALUES (1, '手机', 2999), (2, '电脑', 5999);

-- 2. 创建返回游标的函数
CREATE OR REPLACE FUNCTION fn_return_cursor()
RETURNS refcursor AS $$
DECLARE
    cur_prod refcursor; -- 未绑定游标
BEGIN
    -- 打开游标（自动生成游标名）
    OPEN cur_prod FOR SELECT id, name, price FROM t_product;
    RETURN cur_prod; -- 返回游标名
END;
$$ LANGUAGE plpgsql;

-- 3. 调用函数（必须在事务内）
BEGIN;
    -- 获取游标名（比如返回 <unnamed cursor 1>）
    SELECT fn_return_cursor();
    -- 读取游标数据
    FETCH ALL IN "<unnamed cursor 1>";
COMMIT; -- 事务结束，游标自动关闭

场景2：返回多个游标（同时返回多张表数据）

CREATE OR REPLACE FUNCTION fn_return_multi_cursor(cur1 refcursor, cur2 refcursor)
RETURNS SETOF refcursor AS $$
BEGIN
    -- 第一个游标：返回用户表数据
    OPEN cur1 FOR SELECT id, name FROM t_user;
    RETURN NEXT cur1;
    
    -- 第二个游标：返回订单表数据
    OPEN cur2 FOR SELECT order_id, amount FROM t_order;
    RETURN NEXT cur2;
END;
$$ LANGUAGE plpgsql;

-- 调用（事务内）
BEGIN;
    -- 指定两个游标名：cur_user、cur_order
    SELECT * FROM fn_return_multi_cursor('cur_user', 'cur_order');

    -- 读取第一个游标
    FETCH ALL FROM cur_user;
    -- 读取第二个游标
    FETCH ALL FROM cur_order;
COMMIT;

六、实战案例：游标解决实际问题

注意：以下案例只是举例说明游标的用法，请勿直接用于生产环境！！！

案例1：逐行处理订单，计算优惠金额

需求：对所有“未支付”的订单，按金额计算优惠（>1000减100，否则减50），更新到表中。
```plpgsql
CREATE OR REPLACE FUNCTION fn_calc_discount()
RETURNS void AS $$ DECLARE cur_order refcursor; v_order record; v_discount numeric; BEGIN -- 打开游标：查询未支付订单 OPEN cur_order FOR SELECT id, amount FROM t_order WHERE status = 'unpaid'; -- 循环读取每一行 LOOP -- 读取下一行，无数据则退出循环 FETCH NEXT FROM cur_order INTO v_order; EXIT WHEN NOT FOUND; -- 计算优惠 IF v_order.amount > 1000 THEN v_discount := 100; ELSE v_discount := 50; END IF; -- 更新当前订单的优惠金额 UPDATE t_order SET discount = v_discount, final_amount = amount - v_discount WHERE CURRENT OF cur_order; END LOOP; -- 关闭游标 CLOSE cur_order; RAISE NOTICE '优惠计算完成'; END; $$ LANGUAGE plpgsql;

-- 调用函数
SELECT fn_calc_discount();


### 案例2：动态游标查询不同表的数据
需求：根据传入的表名，查询该表的前10条数据（动态表名用游标实现）。
```plpgsql
CREATE OR REPLACE FUNCTION fn_dynamic_cursor(tab_name text, OUT result refcursor)
RETURNS refcursor AS $$
BEGIN
    -- 打开动态游标：拼接表名，返回前10行
    OPEN result FOR EXECUTE 
        format('SELECT * FROM %I LIMIT 10', tab_name);
END;
$$ LANGUAGE plpgsql;

-- 调用（查询t_user表）
BEGIN;
    SELECT fn_dynamic_cursor('t_user');
    FETCH ALL IN "<unnamed cursor 1>";
COMMIT;

案例3：批量更新大数据集

需求：更新100万行用户数据，一次性更新会锁表，用游标分批更新（每次更1000行）。

CREATE OR REPLACE FUNCTION fn_batch_update()
RETURNS void AS $$
DECLARE
    cur_user CURSOR FOR SELECT id FROM t_user WHERE update_flag = 'N';
    v_id int;
    v_count int := 0;
BEGIN
    OPEN cur_user;
    LOOP
        -- 读取一行
        FETCH NEXT FROM cur_user INTO v_id;
        EXIT WHEN NOT FOUND;
        
        -- 批量更新：每1000行提交一次
        UPDATE t_user SET update_flag = 'Y' WHERE id = v_id;
        v_count := v_count + 1;
        IF v_count % 1000 = 0 THEN
            COMMIT;
            RAISE NOTICE '已更新%行', v_count;
        END IF;
    END LOOP;
    
    -- 提交剩余数据
    COMMIT;
    CLOSE cur_user;
    RAISE NOTICE '批量更新完成，总计更新%行', v_count;
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT fn_batch_update();

PL/pgSQL 入门教程（四）：使用游标（cursor）

一、什么是游标？为什么要用游标？

1.1 游标的本质

1.2 游标能解决什么问题？

二、游标基础：变量与分类

2.1 游标变量的类型

2.2 游标的两种分类

三、游标核心操作：声明 → 打开 → 使用 → 关闭

3.1 第一步：声明游标变量

方式1：声明未绑定游标（最灵活）

方式2：声明绑定游标（固定查询）

3.2 第二步：打开游标

方式1：打开未绑定游标（固定查询）

方式2：打开未绑定游标（动态查询）

方式3：打开绑定游标

3.3 第三步：使用游标（核心操作）

1. FETCH：读取游标数据

2. MOVE：移动游标位置（不读取数据）

3. UPDATE/DELETE：修改游标当前行

3.4 第四步：关闭游标

四、简化操作：FOR循环自动遍历游标

语法：

示例：遍历用户表数据

五、高级用法：函数返回游标（返回大结果集）

场景1：返回单个游标（自动生成游标名）

场景2：返回多个游标（同时返回多张表数据）

六、实战案例：游标解决实际问题

案例1：逐行处理订单，计算优惠金额

案例3：批量更新大数据集

热门文章

最新文章

相关电子书

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

PL/pgSQL 入门教程（四）：使用游标（cursor）

一、什么是游标？为什么要用游标？

1.1 游标的本质

1.2 游标能解决什么问题？

二、游标基础：变量与分类

2.1 游标变量的类型

2.2 游标的两种分类

三、游标核心操作：声明 → 打开 → 使用 → 关闭

3.1 第一步：声明游标变量

方式1：声明未绑定游标（最灵活）

方式2：声明绑定游标（固定查询）

3.2 第二步：打开游标

方式1：打开未绑定游标（固定查询）

方式2：打开未绑定游标（动态查询）

方式3：打开绑定游标

3.3 第三步：使用游标（核心操作）

1. FETCH：读取游标数据

2. MOVE：移动游标位置（不读取数据）

3. UPDATE/DELETE：修改游标当前行

3.4 第四步：关闭游标

四、简化操作：FOR循环自动遍历游标

语法：

示例：遍历用户表数据

五、高级用法：函数返回游标（返回大结果集）

场景1：返回单个游标（自动生成游标名）

场景2：返回多个游标（同时返回多张表数据）

六、实战案例：游标解决实际问题

案例1：逐行处理订单，计算优惠金额

案例3：批量更新大数据集

热门文章

最新文章

相关电子书