商城网站优化方案,品牌宣传策略有哪些,wordpress用户注册打文章,北京市网站建设企业概述
PostgreSQL游标可以封装查询并对其中每一行记录进行单独处理。当我们想对大量结果集进行分批处理时可以使用游标#xff0c;因为一次性处理可能造成内存溢出。
另外我们可以定义函数返回游标类型变量#xff0c;这是函数返回大数据集的有效方式#xff0c;函数调用者…概述
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子句。
首先指定游标变量名称。接着使用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。可以有下面值
NEXTLASTPRIORFIRSTABSOLUTE countRELATIVE countFORWARDBACKWARD
注意使用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语句释放资源或释放游标变量使其可以被再次打开。
完整示例
下面的示例用于查询fooid为指定值的fooname首先创建表foo create table foo ( fooid int, fooname text ) 插入数据然后创建实例 create or replace function get_fooname1(id integer) returns text as $$ declare title text default fooname ; f_name record; cur_fname cursor(id integer) for select fooname,fooid from foo where fooidid; begin open cur_fname(id); loop fetch cur_fname into f_name; exit when not found; title : title || : ||f_name.fooname; end loop; close cur_fname; return title; end; $$ language plpgsql; 然后使用select语句可以查询 postgres# select * from get_fooname1(1); get_fooname1 -------------- fooname :hi (1 row)
