游标

本章我们将讨论 PL/pgSQL 的游标以及如何使用它们逐行处理结果集。

游标用于将查询进行封装，并逐行进行处理。通常在将大型结果集分解为多个部分，并进行单独处理时，可以使用游标，以避免出现内存溢出。

声明游标

要使用游标，必须先对其进行声明。

PostgreSQL 提供了 REFCURSOR 的特殊类型用来声明游标。语法如下：

declare 游标名 refcursor;

游标名 [ [no] scroll ] cursor [(参数列表)] for 查询;

• SCROLL：游标可以向后滚动；NO SCROLL：游标无法向后滚动
• 参数列表：定义查询参数（参数名 数据类型），多个参数以逗号分隔。当游标打开时，这些参数将被具体值替换。
• 查询：可以是任何有效的 SELECT 语句

以下示例演示了如何声明游标：

declare
    cur_films  cursor for 
		select * 
		from film;
    cur_films2 cursor (year integer) for 
		select * 
		from film 
		where release_year = year;

打开游标

游标必须先打开才能用于查询，PostgreSQL 提供了打开绑定和未绑定游标的方法。

OPEN 未绑定游标变量 [ [ NO ] SCROLL ] FOR query;

由于未绑定游标变量在声明时没有和任何查询进行绑定，因此在打开它的时候需要指定查询，如下例：

open my_cursor for 
	select * from city 
	where country = p_country;

PostgreSQL 还允许打开游标时，将其绑定到动态查询上，语法如下：

open 未绑定游标变量[ [ no ] scroll ] 
for execute 查询字符串 [using 表达式 [, ... ] ];

比如：

query := 'select * from city order by $1';

open cur_city for execute query using sort_field;

打开绑定游标

因为绑定游标在声明时已经和查询做了绑定，所以打开它的时候，只需要将必要的参数传递给查询即可，语法如下：

open 游标变量[ (参数1:=值1,参数2:=值2,...)];

比如打开上面声明的 cur_films 和 cur_films2游标：

open cur_films;
open cur_films2(year:=2005);

使用游标

获取下一行

语法：

fetch [ 方向 { from | in } ] 游标变量 
into 目标变量;

• NEXT：下一行（默认值）
• LAST：最后一行
• PRIOR：上一行
• FIRST：第一行
• ABSOLUTE 数值：绝对计数
• RELATIVE 数值：相对计数
• FORWARD：向前
• BACKWARD：后退
• FORWARD 和 BACKWARD仅适用于 SCROLL 声明的游标

示例：

fetch cur_films into row_film;
fetch last from row_film into title, release_year;

移动游标

如果只是想移动游标而不需要检查数据，可以使用 MOVE 语句。语法如下：

move [ 方向 { from | in } ] 游标变量;

方向 的取值和 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 表名 
set 字段 = 值, ... 
where current of 游标变量;

delete from 表名 
where current of 游标变量;

举例：

update film 
set release_year = p_year 
where current of cur_films;

关闭游标

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

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 cur_films(p_year);
	
   loop
    -- 从游标中获取下一行
      fetch cur_films into rec_film;
    -- 如果没有更多行，退出循环
      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 $$;