Postgresql游标使用介绍（cursor）

mingjie

发布于 2022-07-14 13:53:38

2.1K0

发布于 2022-07-14 13:53:38

文章被收录于专栏：Postgresql源码分析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;
BEGIN
    open curs1 FOR SELECT * FROM tf1;
    fetch last from curs1 into y; RAISE NOTICE 'curs1 : %', y.c2;
    fetch RELATIVE -2 from curs1 into y; RAISE NOTICE 'curs1 : %', y.c2;
    fetch curs1 into y; RAISE NOTICE 'curs1 : %', y.c2;
    
    return 0;
END;
$$ LANGUAGE plpgsql;

select tfun2();
NOTICE:  curs1 : 5000
NOTICE:  curs1 : 300
NOTICE:  curs1 : 400

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

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

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;

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2022-07-08，如有侵权请联系 cloudcommunity@tencent.com 删除

postgresql