Postgresql 游标使用

前端之家收集整理的这篇文章主要介绍了Postgresql 游标使用前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

游标

37.8. 游标

如果不想一次执行整个命令,可以设置一个封装该命令的游标,然后每次读取几行命令结果。这么干的一个原因是在结果包含数量非常大的行时避免内存耗尽。不过 PL/pgsql 用户不必担心这个,因为 FOR 循环自动在内部使用一个游标以避免内存问题。一个更有趣的用法是某个函数可以返回一个它创建的游标的引用,这样就允许调用者读取各行。从而提供了一种从函数返回一个结果集的手段。

37.8.1. 声明游标变量

所有在 PL/pgsql 里对游标的访问都是通过游标变量实现的,它总是特殊的数据类型 refcursor 。创建游标变量的一个方法是把它声明为一个类型为 refcursor 的变量。另外一个方法是使用游标声明语法,像下面这样:

name CURSOR [( arguments )] FOR query;

出于兼容 Oracle 的考虑,FOR 可以被替换为 IS 。如果有 arguments ,那么它是一个逗号分隔的 name datatype 对的列表,它们定义那些将会用参数值替换掉的所给出命令中的名字。实际用于代换这些名字的数值将在在游标打开之后声明。

几个例子:

DECLARE
    curs1 refcursor;
    curs2 CURSOR FOR SELECT * FROM tenk1;
    curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;

所有这三个变量都是 refcursor 类型,但是第一个可以用于任何命令,而第二个已经绑定了一个声明完整的命令,最后一个是绑定了一个带参数的命令。key 将在游标打开的时候被代换成一个整数。变量 curs1 可以称之为未绑定的,因为它没有和任何查询相绑定。

37.8.2. 打开游标

在你使用游标检索行之前,你必需先打开它。这是和 sql 命令 DECLARE CURSOR相等的操作。PL/pgsql 有三种形式的 OPEN 语句,两种用于未绑定的游标变量,另外一种用于已绑定的游标变量。

37.8.2.1. OPEN FOR query

OPEN unbound_cursor FOR query;

该游标变量打开并且执行给出的查询。游标不能是已经打开的,并且它必需是声明为一个未绑定的游标(也就是声明为一个简单的 refcursor 变量)。查询必须是一条 SELECT 或者其它返回行的东西(比如 EXPLAIN)。查询是和其它在 PL/pgsql 里的 sql 命令平等对待的:先代换 PL/pgsql 的变量名,而且执行计划为将来可能的复用缓存起来。

一个例子:

OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;

37.8.2.2. OPEN FOR EXECUTE

OPEN unbound_cursor FOR EXECUTE query_string;

打开游标变量并且执行给出的查询。游标不能是已打开的,并且必须声明为一个未绑定的游标(也就是一个简单的 refcursor 变量)。命令是用和那些用于 EXECUTE 命令一样的方法声明的字符串表达式,这样,就有了命令可以在两次运行间发生变化的灵活性。

一个例子:

OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);

37.8.2.3. 打开一个绑定的游标

OPEN bound_cursor [( argument_values )];

这种形式的 OPEN 用于打开一个游标变量,该游标变量的命令是在声明的时候和它绑定在一起的。游标不能是已经打开的。当且仅当该游标声明为接受参数的时候,语句中才必需出现一个实际参数值表达式的列表。这些值将代换到命令中。一个绑定的游标的命令计划总是认为可缓冲的,这种情况下没有等效的 EXECUTE

例子:

OPEN curs2;
OPEN curs3(42);

37.8.3. 使用游标

一旦你已经打开了一个游标,那么你就可以用这里描述的语句操作它。

这些操作不需要发生在和打开该游标开始操作的同一个函数里。你可以从函数里返回一个 refcursor 值,然后让调用者操作该游标。在内部,refcursor 值只是一个包含该游标命令的活跃查询的信使的字符串名。这个名字可以传来传去,可以赋予其它 refcursor 变量等等,也不用担心扰乱信使。

所有信使在事务的结尾都会隐含地关闭。因此一个 refcursor 值只能在该事务结束前用于引用一个打开的游标。

37.8.3.1. FETCH

FETCH cursor INTO target;

FETCH 从游标中检索下一行到目标中,目标可以是一个行变量、记录变量、逗号分隔的普通变量列表,就像 SELECT INTO 里一样。和 SELECT INTO 一样,可以使用特殊变量 FOUND 检查是否检索出一个行。

一个例子:

FETCH curs1 INTO rowvar;
FETCH curs2 INTO foo,bar,baz;

37.8.3.2. CLOSE

CLOSE cursor;

CLOSE 关闭支撑在一个打开的游标下面的信使。这样就可以在事务结束之前释放资源,或者释放掉该游标变量,用于稍后再次打开。

一个例子:

CLOSE curs1;

37.8.3.3. 返回游标

PL/pgsql 函数可以向调用者返回游标。这个功能用于从函数里返回多行或多列,特别是巨大的结果集。要想这么做,该函数必须打开游标并且把该游标的名字返回给调用者,或者简单的使用指定的入口名或调用者已知的名字打开游标。调用者然后从游标里抓取行。游标可以由调用关闭,或者是在事务结束的时候自动关闭

函数返回的游标名可以由调用者声明或者自动生成。要声明一个信使的名字,只要在打开游标之前,给 refcursor 变量赋予一个字符串就可以了。refcursor 变量的字符串值将被 OPEN 当作下层的信使的名字使用。不过,如果 refcursor 变量是空,那么 OPEN自动生成一个和现有信使不冲突的名字,然后将它赋予 refcursor 变量。

【注意】 一个绑定的游标变量其名字初始化为对应的字符串值,因此信使的名字和游标变量名同名,除非程序员在打开游标之前通过赋值覆盖了这个名字。但是一个未绑定的游标变量初始化的时候缺省是空,因此它会收到一个自动生成的唯一名字,除非被覆盖。

下面的例子显示了一个调用者声明游标名字的方法

CREATE TABLE test (col text);
INSERT INTO test VALUES ('123');

CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
    OPEN $1 FOR SELECT col FROM test;
    RETURN $1;
END;
' LANGUAGE plpgsql;

BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;

下面的例子使用了自动生成的游标名:

CREATE FUNCTION reffunc2() RETURNS refcursor AS '
DECLARE
    ref refcursor;
BEGIN
    OPEN ref FOR SELECT col FROM test;
    RETURN ref;
END;
' LANGUAGE plpgsql;

BEGIN;
SELECT reffunc2();

      reffunc2      
--------------------
 <unnamed cursor 1>
(1 row)

FETCH ALL IN "<unnamed cursor 1>";
COMMIT;

下面的例子显示了从一个函数里返回多个游标的方法

CREATE FUNCTION myfunc(refcursor,refcursor) RETURNS SETOF refcursor AS $$
BEGIN
    OPEN $1 FOR SELECT * FROM table_1;
    RETURN NEXT $1;
    OPEN $2 FOR SELECT * FROM table_2;
    RETURN NEXT $2;
END;
$$ LANGUAGE plpgsql;

-- 需要在事务里使用游标。
BEGIN;

SELECT * FROM myfunc('a','b');

FETCH ALL FROM a;
FETCH ALL FROM b;
COMMIT;
原文链接:https://www.f2er.com/postgresql/197284.html

猜你在找的Postgre SQL相关文章