Postgresql游标使用介绍（cursor）

游标一般适用于大结果集，大结果集在内存中放不下且数据可以一条一条处理的情况比较适合使用游标。游标的使用简单总结可以分为三步：1. 定义游标2. 打开游标3. 使用游标- 注意游标一般适用于大结果集，大结果集在内存中放不下且数据可以一条一条处理的情况比较适合使用游标。- 下面介绍了三种游标声明的方式，分别给出了三种游标的Open方式- 如果绑定SQL了，可以直接Open开始使用，每次fe

高铭杰

8786人浏览 · 2022-07-08 17:33:18

高铭杰 · 2022-07-08 17:33:18 发布

相关
《Postgresql游标使用介绍（cursor）》
《Postgresql游标编译过程&结果记录》

Postgresql中的游标

Postgresql中游标有两种：

SQL中直接调用游标使用：https://www.postgresql.org/docs/current/sql-declare.html
包装在PLPGSQL中的游标：https://www.postgresql.org/docs/current/plpgsql-cursors.html

本篇重点介绍第二种PLPGSQL中的游标。

游标一般适用于大结果集，大结果集在内存中放不下且数据可以一条一条处理的情况比较适合使用游标。

1 游标语法

游标的使用简单总结可以分为三步：

定义游标
打开游标
使用游标

其中每一步都有几种不同的语法可以使用，下面每种分别给出实例。

在这里插入图片描述

2 定义游标 & 打开游标

注意游标一般适用于大结果集，大结果集在内存中放不下且数据可以一条一条处理的情况比较适合使用游标。
下面介绍了三种游标声明的方式，分别给出了三种游标的Open方式，主要区别就是
- 有没有绑定SQL：curs1 refcursor; 和 curs2 CURSOR FOR SELECT c1 FROM tf1;
- 有没有绑定值：curs3 CURSOR (key integer) FOR SELECT * FROM tf1 WHERE c1 > key;
如果绑定SQL了，可以直接Open开始使用，每次fetch一条来处理
如果绑定值，需要再open的时候把具体值传进去，然后fetch一条来处理

数据准备

drop table tf1;
create table tf1(c1 int, c2 int,  c3 varchar(32), c4 varchar(32), c5 int);
insert into tf1 values(1,1000, 'China','Dalian', 23000);
insert into tf1 values(2,4000, 'Janpan', 'Tokio', 45000);
insert into tf1 values(3,1500, 'China', 'Xian', 25000);
insert into tf1 values(4,300, 'China', 'Changsha', 24000);
insert into tf1 values(5,400,'USA','New York', 35000);
insert into tf1 values(6,5000, 'USA', 'Bostom', 15000);

postgres=# select * from tf1;
 c1 |  c2  |   c3   |    c4    |  c5   
----+------+--------+----------+-------
  1 | 1000 | China  | Dalian   | 23000
  2 | 4000 | Janpan | Tokio    | 45000
  3 | 1500 | China  | Xian     | 25000
  4 |  300 | China  | Changsha | 24000
  5 |  400 | USA    | New York | 35000
  6 | 5000 | USA    | Bostom   | 15000

定义使用游标


CREATE  OR REPLACE FUNCTION tfun1() RETURNS int AS $$
DECLARE
    curs1 refcursor;                       
    curs2 CURSOR FOR SELECT c1 FROM tf1;
    curs3 CURSOR (key integer) FOR SELECT * FROM tf1 WHERE c1 > key;
    
    x int;
    y tf1%ROWTYPE;
BEGIN
    open curs1 FOR SELECT * FROM tf1 WHERE c1 > 3;
    fetch curs1 into y; RAISE NOTICE 'curs1 : %', y.c3;
    fetch curs1 into y; RAISE NOTICE 'curs1 : %', y.c3;

    open curs2;
    fetch curs2 into x; RAISE NOTICE 'curs2 : %', x;
    fetch curs2 into x; RAISE NOTICE 'curs2 : %', x;
    
    OPEN curs3(4);  -- OPEN curs3(key := 4);
    fetch curs3 into y; RAISE NOTICE 'curs3 : %', y.c4;
    fetch curs3 into y; RAISE NOTICE 'curs3 : %', y.c4;
    return 0;
END;
$$ LANGUAGE plpgsql;

执行结果

select tfun1();

postgres=# select tfun1();
NOTICE:  curs1 : China
NOTICE:  curs1 : USA
NOTICE:  curs2 : 1
NOTICE:  curs2 : 2
NOTICE:  curs3 : New York
NOTICE:  curs3 : Bostom
 tfun1 
-------
     0
(1 row)

3 使用游标（方法一）

3.1 fetch

总结速查：LAST语法直接转义到最后一行；RELATIVE语法相对当前行前后移动。

上面的例子提到了使用游标最简单的方式fetch

语法：FETCH [ direction { FROM | IN } ] cursor INTO target;
例子：

FETCH curs1 INTO rowvar;
FETCH curs2 INTO foo, bar, baz;
FETCH LAST FROM curs3 INTO x, y;
FETCH RELATIVE -2 FROM curs4 INTO x;

上面例子中前两个都比较好理解，后两个的含义见下面实例


select * from tf1;
 c1 |  c2  |   c3   |    c4    |  c5   
----+------+--------+----------+-------
  1 | 1000 | China  | Dalian   | 23000
  2 | 4000 | Janpan | Tokio    | 45000
  3 | 1500 | China  | Xian     | 25000
  4 |  300 | China  | Changsha | 24000
  5 |  400 | USA    | New York | 35000
  6 | 5000 | USA    | Bostom   | 15000

CREATE  OR REPLACE FUNCTION tfun2() RETURNS int AS $$
DECLARE
    curs1 refcursor;
    y tf1%ROWTYPE;
    x RECORD;
    z1 int;
    z2 int;
BEGIN
    open curs1 FOR SELECT * FROM tf1;

    fetch last from curs1 into y;          RAISE NOTICE 'fetch into ROWTYPE : %', y.c2;
    fetch RELATIVE -2 from curs1 into y;   RAISE NOTICE 'fetch into ROWTYPE : %', y.c2;
    fetch curs1 into y;                    RAISE NOTICE 'fetch into ROWTYPE : %', y.c2;

    fetch curs1 into x;                    RAISE NOTICE 'fetch into RECORD  : %', x.c2;
    
    fetch last from curs1 into z1,z2;      RAISE NOTICE 'fetch into var     : %,%', z1, z2;
    return 0;
END;
$$ LANGUAGE plpgsql;

select tfun2();
NOTICE:  fetch into ROWTYPE : 5000
NOTICE:  fetch into ROWTYPE : 300
NOTICE:  fetch into ROWTYPE : 400
NOTICE:  fetch into RECORD  : 5000
NOTICE:  fetch into var     : 6,5000

从结果来看，FETCH LAST FROM curs3 INTO x, y;是直接把游标指向最后一行得到5000。

当前游标是最后一行，执行FETCH RELATIVE -2 FROM curs4 INTO x;后，相对最后一行向前移动2行得到300。

FETCH可以将结果存入ROWTYPE、RECORD、普通变量。但FETCH不能将多行变量一次性存入数组（类似于ORACLE的BULK COLLECT），可以使用类似：select array_agg(id) INTO v_ids的语法代替（v_ids为数组）。

3.2 MOVE

MOVE语法和FETCH相同，区别是MOVE只移动游标，不获取数据。

语法：MOVE [ direction { FROM | IN } ] cursor;
例子：

MOVE curs1;
MOVE LAST FROM curs3;
MOVE RELATIVE -2 FROM curs4;
MOVE FORWARD 2 FROM curs4;

3.3 UPDATE/DELETE WHERE CURRENT OF

使用游标更新或删除当前指向的行
语法：UPDATE table SET ... WHERE CURRENT OF cursor;
语法：DELETE FROM table WHERE CURRENT OF cursor;

实例：

select * from tf1;
 c1 |  c2  |   c3   |    c4    |  c5   
----+------+--------+----------+-------
  1 | 1000 | China  | Dalian   | 23000
  2 | 4000 | Janpan | Tokio    | 45000
  3 | 1500 | China  | Xian     | 25000
  4 |  300 | China  | Changsha | 24000
  5 |  400 | USA    | New York | 35000
  6 | 5000 | USA    | Bostom   | 15000


CREATE  OR REPLACE FUNCTION tfun3() RETURNS int AS $$
DECLARE
    curs1 refcursor;
    y tf1%ROWTYPE;
BEGIN
    open curs1 FOR SELECT * FROM tf1;
    fetch last from curs1 into y; 
    RAISE NOTICE 'curs1 : %', y.c2;
    
    delete from tf1 WHERE CURRENT OF curs1;
    return 0;
END;
$$ LANGUAGE plpgsql;

select tfun3();
select * from tf1;
 c1 |  c2  |   c3   |    c4    |  c5   
----+------+--------+----------+-------
  1 | 1000 | China  | Dalian   | 23000
  2 | 4000 | Janpan | Tokio    | 45000
  3 | 1500 | China  | Xian     | 25000
  4 |  300 | China  | Changsha | 24000
  5 |  400 | USA    | New York | 35000

最后一行删除了。

3.4 CLOSE

关闭游标，释放相关资源。

语法：CLOSE cursor;

4 使用游标（方法二）返回游标

游标可以作为函数的返回值返回给外层调用者，调用者使用fetch语句可以获取游标内容。

实例：


CREATE OR REPLACE FUNCTION tf4(refcursor) RETURNS refcursor AS '
BEGIN
    OPEN $1 FOR SELECT c4 FROM tf1;
    RETURN $1;
END;
' LANGUAGE plpgsql;

BEGIN;
SELECT tf4('funccursor');

FETCH ALL IN funccursor;
    c4    
----------
 Dalian
 Tokio
 Xian
 Changsha
 New York
 
COMMIT;