介绍PostgreSQL游标(Cursor)
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了介绍PostgreSQL游标(Cursor)，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4880字，纯文字阅读大概需要7分钟。
内容图文
介绍PostgreSQL游标(Cursor)
本文我们通过一些示例进行详细说明如何使用PostgreSQL游标。
概述
PostgreSQL游标可以封装查询并对其中每一行记录进行单独处理。当我们想对大量结果集进行分批处理时可以使用游标，因为一次性处理可能造成内存溢出。
另外我们可以定义函数返回游标类型变量，这是函数返回大数据集的有效方式，函数调用者根据返回游标对结果进行处理。
下图展示了如何使用PostgreSQL游标：
- 第一步声明游标.
- 接着打开游标.
- 然后从结果中取出行至目标变量中.
- 之后，检查是否有更多的行可以继续取。如何有返回第三步，否则至第五步.
- 最后,关闭游标.
下面章节我们会详细说明每一步。
声明游标
为了访问游标，需要在声明块中声明游标变量。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表中带有特定发行年份记录的游标变量。
绑定游标变量被初始化为字符串值表示其名称(官方文档成为portal name)，后续一致不变。但非绑定游标变量初始缺省为null值，所以后期会接受一个自动生成的唯一名称。
当递归函数中定义游标时，需定义为非绑定游标，否则会产生错误：cursor already in use。
## 打开游标
游标在使用之前必须要打开，PostgreSQL提供特定语法用于打开绑定游标和非绑定游标。
打开非绑定游标
打开非绑定游标语法：
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;
打开绑定游标
因为绑定游标声明时已经绑定了查询，所以打开时，仅需要传入必要参数即可：
OPEN cursor_variable[ (name:=value,name:=value,...)];
下面示例中，打开上节声明的绑定游标cur_films和cur_films2：
OPEN cur_films;
OPEN cur_films2(year:=2005);
使用游标
打开游标之后，可以使用FETCH, MOVE操纵游标，并更新或删除记录。
取下一行记录语法：
FETCH [ direction { FROM | IN } ] cursor_variable INTO target_variable;
fetch语句从游标中获得下一行记录并赋值给目标变量target_variable，可以是record类型或row变量或逗号分隔的变量列表。如果没有发现可取行，目标变量target_variable为null。
如果不显示指定方向，方向缺省为NEXT。可以有下面值：
- NEXT
- LAST
- PRIOR
- FIRST
- ABSOLUTE count
- RELATIVE count
- FORWARD
- BACKWARD
注意，使用SCROLL声明游标可以FORWARD 和 BACKWARD 。请看示例：
FETCH cur_films INTO row_film;
FETCH LAST FROM row_film INTO title, release_year;
移动游标
语法如下：
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;
删除或更新行
一旦游标位置确定，则可以删除或更新行，提供使用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关键字：
CLOSEC cursor_variable;
close语句释放资源或释放游标变量使其可以被再次打开。
完整示例
下面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 title, release_year
FROM film
WHERE release_year = p_year;
BEGIN
-- Open the cursor
OPEN cur_films(p_year);
LOOP
-- fetch row into the film
FETCH cur_films INTO rec_film;
-- exit when no more row to fetch
EXIT WHEN NOT FOUND;
-- build the output
IF rec_film.title LIKE '%ful%' THEN
titles := titles || ',' || rec_film.title || ':' || rec_film.release_year;
END IF;
END LOOP;
-- Close the cursor
CLOSE cur_films;
RETURN titles;
END; $$
LANGUAGE plpgsql;
上述代码先open游标，然后使用loop方式依次判断。我们也可以使用for…in方式循环，避免手工打开或关闭游标。
调用代码为：
SELECT get_film_titles(2006);
如果不关心返回值，则使用perform关键字执行。
总结
本文我们学习了PL/pgSQL知识，并通过示例展示如何循环每条记录并实现相应业务处理。
内容总结
以上是互联网集市为您收集整理的介绍PostgreSQL游标(Cursor)全部内容，希望文章能够帮你解决介绍PostgreSQL游标(Cursor)所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。