Postgresql游标使用介绍（cursor）

2022-07-14 13:53:38 浏览数 (1)

Postgresql中的游标

Postgresql中游标有两种：

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

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

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

1 游标语法

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

  1. 定义游标
  2. 打开游标
  3. 使用游标

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

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一条来处理

数据准备

代码语言：javascript复制
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

定义使用游标

代码语言：javascript复制
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;

执行结果

代码语言：javascript复制
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; 例子：

代码语言：javascript复制
FETCH curs1 INTO rowvar;
FETCH curs2 INTO foo, bar, baz;
FETCH LAST FROM curs3 INTO x, y;
FETCH RELATIVE -2 FROM curs4 INTO x;

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

代码语言：javascript复制
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; 例子：

代码语言：javascript复制
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;

实例：

代码语言：javascript复制
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语句可以获取游标内容。

实例：

代码语言：javascript复制
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;
postgresql sql css

0 人点赞