postgresql 使用游标笔记

　　游标介绍：游标是一种从表中检索数据并进行操作的灵活手段，游标主要用在服务器上，处理由客户端发送给服务端的sql语句，或是批处理、存储过程、触发器中的数据处理请求。

       游标的优点在于它允许应用程序对查询语句select 返回的行结果集中每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作；它还提供对基于游标位置而对表中数据进行删除或更新的能力。缺点是处理大数据量时，效率低下，占用内存大。一般来说，能使用其他方式处理数据时，最好不要使用游标，除非是当你使用while循环,子查询，临时表，表变量,自建函数或其他方式都无法处理某种操作的时候，再考虑使用游标。

        游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。游标的一个常见用途就是保存查询结果，以便以后使用。游标的结果集是由SELECT语句产生，如果处理过程需要重复使用一个记录集，那么创建一次游标而重复使用若干次，比重复查询数据库要快的多。

        PostgreSQL游标可以封装查询并对其中每一行记录进行单独处理。当我们想对大量结果集进行分批处理时可以使用游标，因为一次性处理可能造成内存溢出。另外我们可以定义函数返回游标类型变量，这是函数返回大数据集的有效方式，函数调用者根据返回游标对结果进行处理。

　　游标使用顺序：声明游标   >   打开游标   >  使用游标   >   关闭游标 。 

　　先展示一个游标的示例，以下get_film_titles(integer)函数接受代表电影发行年份的参数。在函数内部，我们查询所有发行年份等于传递给该函数的发行年份的电影。我们使用光标在各行之间循环，并连接标题和标题包含ful 单词的电影发行年份。

CREATE OR REPLACE FUNCTION get_film_titles(p_year INTEGER)
   RETURNS text AS $$
-- 声明游标
DECLARE 
 titles TEXT DEFAULT '';
 rec_film   RECORD;
 cur_films CURSOR(p_year INTEGER) FOR SELECT * FROM film WHERE release_year = p_year;
BEGIN
   -- 打开游标
   OPEN cur_films(p_year);
 
   LOOP
    -- 获取记录放入film
      FETCH cur_films INTO rec_film;
    -- exit when no more row to fetch
      EXIT WHEN NOT FOUND;
 
    -- 构建输出
      IF rec_film.title LIKE '%ful%' THEN 
         titles := titles || ',' || rec_film.title || ':' || rec_film.release_year;
      END IF;
   END LOOP;
  
   -- 关闭游标
   CLOSE cur_films;
 
   RETURN titles;
END; $$
 
LANGUAGE plpgsql;

SELECT get_film_titles(2006);

--返回结果 ,Grosse Wonderful:2006,Day Unfaithful:2006,Reap Unfaithful:2006,Unfaithful Kill:2006,Wonderful Drop:2006

一、声明游标

　　PostgreSQL声明游标有两种方法，一种是使用特殊类型REFCURSOR声明游标变量，另一种是声明和查询绑定使用。

-- 第一种方式
DECLARE  my_cursor REFCURSOR;

-- 第二种方式
cursor_name [ [NO] SCROLL ] CURSOR [( name datatype, name data type, ...)] FOR query;

　　首先，为光标指定一个变量名。接着指定是否可以使用向后滚动光标SCROLL，如果使用NO SCROLL，则光标无法向后滚动。然后，在CURSOR关键字后面加上一个逗号分隔的参数列表（name datatype），这些参数定义了查询的参数。打开游标时，这些参数将被值替换。最后，可以在FOR关键字之后指定查询，使用任何有效的SELECT语句。

　　示例：

DECLARE
    cur_films  CURSOR FOR SELECT * FROM film;
    cur_films2 CURSOR (year integer) FOR SELECT * FROM film WHERE release_year = year;

-- 该cur_films 包含film表所有行。
-- 本cur_films2 包含film表特定发行年份的记录。

二、打开游标

　　 PostgreSQL提供了用于打开未绑定和绑定的游标的语法。

　　1.打开未绑定的游标

OPEN unbound_cursor_variable [ [ NO ] SCROLL ] FOR query;

-- 由于声明时未绑定的游标变量未绑定到任何查询，因此在打开它时必须指定查询。请参见以下示例：
OPEN my_cursor FOR SELECT * FROM city WHERE counter = p_country;

-- PostgreSQL允许我们打开游标并将其绑定到动态查询。语法如下：
OPEN unbound_cursor_variable[ [ NO ] SCROLL ]
FOR EXECUTE query_string [USING expression [, ... ] ];

-- 在下面的示例中，我们构建一个动态查询，该动态查询根据一个sort_field参数对行进行排序，并打开执行该动态查询的游标。
query := 'SELECT * FROM city ORDER BY $1';
OPEN cur_city FOR EXECUTE query USING sort_field;

　　2.打开绑定的游标

　因为绑定游标在声明时已经绑定到查询，所以当我们打开它时，只需要在必要时将参数传递给查询。

OPEN cursor_variable[ (name:=value,name:=value,...)];

-- 在下面的示例中，我们打开了绑定游标，cur_films并cur_films2在上面声明了该游标：
OPEN cur_films;
OPEN cur_films2(year:=2005);

三、使用游标

　　使用FETCH，MOVE，UPDATE或DELETE语句操作游标。

 　　1.获取下一行

FETCH [ direction { FROM | IN } ] cursor_variable INTO target_variable;

该FETCH语句从游标中获取下一行，并为其分配一个target_variable，它可以是记录，行变量或逗号分隔的变量列表。如果找不到更多行，则将target_variable其设置为NULL（s）。

　　如果不显示指定方向，方向缺省为NEXT。可以有下面值：

• NEXT
• LAST
• PRIOR
• FIRST
• ABSOLUTE count
• RELATIVE count
• FORWARD
• BACKWARD

　　请注意，FORWARD和BACKWARD方向仅适用于用SCROLL option 声明的游标。

　　示例：

FETCH cur_films INTO row_film;
FETCH LAST FROM row_film INTO title, release_year;

　　2.移动光标

MOVE [ direction { FROM | IN } ] cursor_variable;

　　如果只想移动游标而不检索任何行，则使用该MOVE语句。方向接受与FETCH语句相同的值。

MOVE cur_films2;
MOVE LAST FROM cur_films;
MOVE RELATIVE -1 FROM cur_films;
MOVE FORWARD 3 FROM cur_films;

　　3.删除和更新行

　　使用DELETE WHERE CURRENT OF或UPDATE WHERE CURRENT OF语句删除或更新游标标识的行。

UPDATE table_name
SET column = value, ...
WHERE CURRENT OF cursor_variable;
 
DELETE FROM table_name
WHERE CURRENT OF cursor_variable;

示例：
UPDATE film SET release_year = p_year
WHERE CURRENT OF cur_films;

四、关闭游标

 使用CLOSE关闭打开的游标，CLOSE语句释放资源或释放游标变量，以允许使用该OPEN语句再次打开它。

CLOSE cursor_variable;

五、其他

-- 临时表返回结果例子
BEGIN;
DO $$
    DECLARE
        temp_geometry st_geometry;  
        geometry_record RECORD;
        cur_geometry CURSOR FOR SELECT shape as shape FROM mainbasin;
    BEGIN
        OPEN cur_geometry;
        FETCH cur_geometry INTO temp_geometry;
        LOOP
            FETCH cur_geometry INTO geometry_record;
            EXIT WHEN NOT FOUND;
            temp_geometry := st_union(temp_geometry,geometry_record.shape);
        END LOOP;
        CLOSE cur_geometry;

        DROP TABLE IF EXISTS temp_table;
        CREATE TEMP TABLE temp_table AS 
        SELECT st_envelope(temp_geometry) shape;
    END; 
$$;
COMMIT;
SELECT st_astext(shape) FROM temp_table;

参考：https://www.postgresqltutorial.com/plpgsql-cursor/