PostgreSQL 教程: PL/pgSQL 游标

八月 19, 2023

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

目录

PL/pgSQL 游标简介

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

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

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

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

PL/pgSQL Cursor

  • 首先，声明一个游标。

  • 接下来，打开游标。

  • 然后，从结果集中提取行到目标变量中。

  • 之后，检查是否还有更多行需要获取。如果是，则执行步骤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。

默认情况下，如果您未显式指定方向，则游标将获取下一行。以下选项对游标有效：

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

请注意，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语句删除游标标识的行，或使用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 游标来迭代访问一组行并单独处理每一行。

了解更多

PostgreSQL PL/pgSQL 教程

搜索

分类

标签