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;
