40.7. 游标

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

40.7.1. 声明游标变量

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

_name_ [ [ NO ] SCROLL ] CURSOR [ ( `_arguments_` ) ] FOR _query_;

(Oracle兼容中FOR可以用IS代替)。 如果定义了SCROLL,则游标可以向后回滚;如果定义了NO SCROLL, 则向后抓取的动作被拒绝;如果二者都没有定义, 那么是否进行向后取的动作会根据查询来判断。 如果有_arguments_, 那么它是一个逗号分隔_name_``_datatype_列表, 这个列表定义由已给查询中的参数值来替代的name。 实际用于代换这些名字的数值将在游标打开之后声明。

例如:

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

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

40.7.2. 打开游标

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

Note: 可以通过Section 40.7.4中描述的FOR语句, 在不用打开游标的情况下使用已绑定的游标。

40.7.2.1. OPEN FOR _query_

OPEN _unbound_cursorvar_ [ [ NO ] SCROLL ] FOR _query_;

该游标变量打开并且执行给出的查询。游标不能是已经打开的, 并且它必需是声明为一个未绑定的游标(也就是声明为一个简单的refcursor变量)。 查询必须是一条SELECT或者其它返回行的东西(比如EXPLAIN)。 查询是和其它在PL/pgSQL里的SQL命令平等对待的:先代换PL/pgSQL的变量名, 而且执行计划为将来可能的复用缓存起来。 当一个PL/pgSQL变量被替换到游标查询中时, 被替换的值是在OPEN时它所具有的值。 后续的改变不会影响游标的动作,对于一个已经绑定的游标来说, SCROLLNO SCROLL这两个选项具有相同的含义。

一个例子:

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

40.7.2.2. OPEN FOR EXECUTE

OPEN _unbound_cursorvar_ [ [ NO ] SCROLL ] FOR EXECUTE _query_string_
                                     [ USING `_expression_` [, ... ] ];

打开游标变量并且执行给出的查询。游标不能是已打开的, 并且必须声明为一个未绑定的游标(也就是一个简单的refcursor变量)。 命令是用和那些用于EXECUTE命令一样的方法声明的字符串表达式, 这样,就有了命令可以在两次运行间发生变化的灵活性。 参阅Section 40.10.2)这也意味着在命令字符串上不能进行变量替换。 跟EXECUTE一起,通过使用USING,参数值可以被插入到动态命令中。 对于一个已经绑定的游标来说,SCROLLNO SCROLL这两个选项具有相同的含义。

一个例子:

OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident(tabname)
                                        || ' WHERE col1 = $1' USING keyvalue;

在这个例子中,表名被插入到文本查询中, 因此使用quote_ident()时要注意SQL注入。 通过USING参数对插入的col1进行比较值,因此不需要使用引号。

40.7.2.3. 打开一个绑定的游标

OPEN _bound_cursorvar_ [ ( [ `_argument_name_` := ] `_argument_value_` [, ...] ) ];

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

一个绑定的游标的命令计划总是认为可缓冲的,这种情况下没有等效的EXECUTE。 需要注意的是SCROLLNO SCROLL不能在OPEN中被声明, 因为游标的滚动动作已经被定义了。

参数值可以使用positional或者named符号传递。 在位置符号中,所有的参数以顺序指定。 在命名法中,每个参数的名称使用:=声明以 从参数表达式中分开。类似于调用函数,在Section 4.3中描述, 它也允许混合位置和命名法。

例子(以上使用游标声明的例子):

OPEN curs2;
OPEN curs3(42);
OPEN curs3(key := 42);

因为在绑定游标查询上做了变量替换,有两种方法将值传递到游标:要么 使用明确参数到OPEN,或者隐式地在查询中引用PL/pgSQL变量。 然而,只有在绑定游标之前声明的变量将取代它。在这两种情况下 可以在OPEN时决定将被传递的值。例如,另一种方式来获得相同的效果 如curs3上面的例子

DECLARE
    key integer;
    curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key;
BEGIN
    key := 42;
    OPEN curs4;

40.7.3. 使用游标

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

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

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

40.7.3.1. FETCH

FETCH [ `_direction_` { FROM | IN } ] _cursor_ INTO _target_;

FETCH从游标中检索下一行到目标中, 目标可以是一个行变量、记录变量、逗号分隔的普通变量列表, 就像SELECT INTO一样, 如果下一行中没有,目标会设为NULL。如同SELECT INTO, 可以使用特殊变量FOUND来检查是否检索出一个行。

_direction_子句可以是任何 一个SQL FETCH命令允许的变量, 除了那些可以抓取不止一行的;形如:NEXT, PRIORFIRSTLASTABSOLUTE _count_RELATIVE _count_FORWARD或者BACKWARD。 忽略_direction_作为声明的NEXT是相同的。 _direction_值需要往后移动可能会失败,除非声明的或者打开的 游标带有SCROLL选项。

_cursor_必须是一个指向一个 打开的游标的refcursor变量的名字。

一个例子:

FETCH curs1 INTO rowvar;
FETCH curs2 INTO foo, bar, baz;
FETCH LAST FROM curs3 INTO x, y;
FETCH RELATIVE -2 FROM curs4 INTO x;

40.7.3.2. MOVE

MOVE [ `_direction_` { FROM | IN } ] _cursor_;

MOVE重新定位一个游标,而不需要检索任何数据。 MOVE的工作方式与FETCH及其相似, 除了它只是重新定位游标并且不返回至移动到的行。 在进行SELECT INTO命令时, 声明的FOUND变量可以用来检查下一个需要移动到的行是否存在。

_direction_可以是任何一个SQL FETCH 命令允许的变量,如下NEXT, PRIORFIRSTLASTABSOLUTE _count_, RELATIVE _count_, ALLFORWARD [ _count_ | ALL ] 或者BACKWARD [ _count_ | ALL ]。 忽略_direction_作为声明的NEXT是相同的。 _direction_值需要往后移动可能会失败,除非声明的或者打开的 游标带有SCROLL选项。

例如:

MOVE curs1;
MOVE LAST FROM curs3;
MOVE RELATIVE -2 FROM curs4;
MOVE FORWARD 2 FROM curs4;

40.7.3.3. UPDATE/DELETE WHERE CURRENT OF

UPDATE _table_ SET ... WHERE CURRENT OF _cursor_;
DELETE FROM _table_ WHERE CURRENT OF _cursor_;

当一个游标被定位到一个表的行上,那么通过使用该游标来识别该行, 从而进行更新或删除操作。当然,对于如何定义游标查询(特别是没有分组时)是存在一定限制的; 在游标中使用FOR UPDATE是个不错的主意。更多信息可参阅DECLARE

例如:

UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;

40.7.3.4. CLOSE

CLOSE _cursor_;

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

例如:

CLOSE curs1;

40.7.3.5. 返回游标

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

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

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

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

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;

40.7.4. 通过游标结果进行循环

有这么一个FOR语法的变形,它允许通过游标返回的行进行迭代。如下:

[ <<`_label_`>> ]
FOR _recordvar_ IN _bound_cursorvar_ [ ( [ `_argument_name_` := ] `_argument_value_` [, ...] ) ] LOOP
    _statements_
END LOOP [ `_label_` ];

在声明游标变量时,它必须已经绑定到一些查询语句上,并且不能是打开状态。 FOR语法会自动打开游标,并且当退出循环时自动关闭游标。只有当游标被声明要使用参数时, 必须有一列实际参数值表达式。这些值会被替换到查询中,采用如同OPEN的方式 (参阅Section 40.7.2.3)。

_recordvar_变量会自动定义为record类型, 并且只存在于循环中(循环中任何的定义变量名的动作都会被忽略)。 每一个由游标返回的行都会陆续的被分配到记录变量中,然后执行循环体。