Postgresql遊标使用介紹（cursor）

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;

複制