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时它所具有的值。后续的改变不会影响游标的动作，对于一个已经绑定的游标来说， SCROLL和NO 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，参数值可以被插入到动态命令中。对于一个已经绑定的游标来说，SCROLL和NO 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。需要注意的是SCROLL和NO 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, PRIOR，FIRST，LAST， ABSOLUTE _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, PRIOR， FIRST， LAST， ABSOLUTE _count_, RELATIVE _count_, ALL， FORWARD [ _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类型，并且只存在于循环中（循环中任何的定义变量名的动作都会被忽略）。每一个由游标返回的行都会陆续的被分配到记录变量中，然后执行循环体。

40.7. 游标

40.7.1. 声明游标变量

40.7.2. 打开游标

40.7.2.1. OPEN FOR _query_

40.7.2.2. OPEN FOR EXECUTE

40.7.2.3. 打开一个绑定的游标

40.7.3. 使用游标

40.7.3.1. FETCH

40.7.3.2. MOVE

40.7.3.3. UPDATE/DELETE WHERE CURRENT OF

40.7.3.4. CLOSE

40.7.3.5. 返回游标

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

40.7.2.1. `OPEN FOR` `_query_`

40.7.2.2. `OPEN FOR EXECUTE`

40.7.3.1. `FETCH`

40.7.3.2. `MOVE`

40.7.3.3. `UPDATE/DELETE WHERE CURRENT OF`

40.7.3.4. `CLOSE`