本章我们将讨论 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 目标变量;
FETCH
语句从游标中获取下一行,并赋值给目标变量
。它可以是一条记录、行变量或者以逗号分隔的变量列表。如果找不到更多多行,则将目标变量
设置为NULL
方向
可能的取值: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 } ] 游标变量;
举例:
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 $$;