PL/pgSQL 游标

在本教程中，您将了解 PL/pgSQL 游标以及如何使用它们逐行处理结果集。

摘要：在本教程中，您将了解 PL/pgSQL 游标以及如何使用它们逐行处理结果集。

PL/pgSQL 游标允许您封装查询并一次处理每一行。

通常，当您想要将一个大的结果集划分为多个部分并单独处理每个部分时，您可以使用游标。如果一次性处理，可能会出现内存溢出错误。

最重要的是，您可以开发一个函数来返回对游标的引用。这是从函数返回大结果集的有效方法。函数的调用者可以根据游标引用处理结果集。

下图说明了如何在 PostgreSQL 中使用游标：

PL/pgSQL 游标

  • 首先，声明一个游标。
  • 接下来，打开光标。
  • 然后，将结果集中的行提取到目标中。
  • 之后，检查是否还有更多的行要提取。如果是，则转至步骤 3，否则转至步骤 5。
  • 最后，关闭光标。

我们将在以下各节中更详细地检查每个步骤。

声明游标

要访问游标，您需要在块的声明部分声明一个游标变量。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 提供了打开未绑定和绑定游标的语法。

打开未绑定的游标

您可以使用以下语法打开一个未绑定的游标：

OPEN unbound_cursor_variable [ [ NO ] SCROLL ] FOR query;

因为未绑定的游标变量在我们声明时没有绑定到任何查询，所以我们必须在打开它时指定查询。请参阅以下示例：

open my_cursor for 
    select * from city 
    where country = 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 、UPDATE或DELETE语句对其进行操作。

获取下一行

fetch [ direction { from | in } ] cursor_variable 
into target_variable;

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

默认情况下，如果您没有明确指定方向，游标会获取下一行。以下内容对游标有效：

  • 下一个
  • 最后的
  • 事先的
  • 第一的
  • 绝对计数
  • 相对计数
  • 向前
  • 落后

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

请参阅以下获取游标的示例。

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 or 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 cursor_variable;

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

PL/pgSQL 游标——把它们放在一起

以下 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;

select get_film_titles(2006);

,Grosse Wonderful:2006,Day Unfaithful:2006,Reap Unfaithful:2006,Unfaithful Kill:2006,Wonderful Drop:2006

在本教程中，您学习了如何使用 PL/pgSQL 游标循环遍历一组行并单独处理每一行。