42.7. Cursors

• 42 .7.1. 声明游标变量

• 42 .7.2. 开启游标

• 42 .7.3. 使用游标

• 42 .7.4. 遍历游标的结果

无需一次执行整个查询，而是可以设置封装查询的“游标”，然后一次读取几行查询结果。这样做的原因之一是当结果包含大量行时避免内存溢出。 (但是，PL/pgSQL 用户通常不必担心，因为FOR循环会在内部自动使用游标以避免内存问题.)一种更有趣的用法是返回对函数创建的游标的引用，从而允许调用方读取行。这提供了一种从函数返回大行集的有效方法。

42 .7.1. 声明游标变量

PL/pgSQL 中对游标的所有访问都通过游标变量进行，游标变量始终为特殊数据类型refcursor。创建游标变量的一种方法就是将其声明为refcursor类型的变量。另一种方法是使用游标声明语法，通常是：

name [ [ NO ] SCROLL ] CURSOR [ ( arguments ) ] FOR query;

(为了与 Oracle 兼容，可以用IS代替FOR.)如果指定了SCROLL，则光标将能够向后滚动；如果指定了NO SCROLL，则将拒绝向后取回；如果两个规范都没有出现，则是否允许向后提取取决于查询。 * arguments *(如果指定)是对name datatype对的逗号分隔列表，这些对定义了将由给定查询中的参数值替换的名称。稍后打开游标时，将指定替换这些名称的实际值。

Some examples:

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

所有这三个变量的数据类型均为refcursor，但是第一个可以与任何查询一起使用，而第二个具有已完全绑定到它的完全指定的查询，而最后一个具有绑定到它的参数化查询。 (打开游标时，key将被整数参数值代替.)由于未将变量curs1绑定到任何特定查询，因此它被称为“未绑定”。

42 .7.2. 开启游标

在使用游标检索行之前，必须将其“打开”。 (这是等效于 SQL 命令DECLARE CURSOR的操作.)PL/pgSQL 具有OPEN语句的三种形式，其中两种使用未绑定的游标变量，而第三种使用绑定的游标变量。

42 .7.2.1. OPEN FOR 查询

OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR query;

将打开游标变量，并执行指定的查询。游标无法打开，它必须已经声明为未绑定的游标变量(即，简单的refcursor变量)。该查询必须是SELECT或返回行的其他内容(例如EXPLAIN)。该查询的处理方式与 PL/pgSQL 中的其他 SQL 命令相同：替换 PL/pgSQL 变量名称，并缓存查询计划以备将来使用。将 PL/pgSQL 变量替换为游标查询时，替换的值是OPEN时它具有的值；随后对该变量的更改将不会影响游标的行为。 SCROLL和NO SCROLL选项的含义与绑定游标的含义相同。

An example:

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

42 .7.2.2. 开放执行

OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR EXECUTE query_string
                                     [ USING expression [, ... ] ];

将打开游标变量，并执行指定的查询。游标不能已经打开，它必须已声明为未绑定的游标变量(即，简单的refcursor变量)。该查询以与EXECUTE命令相同的方式指定为字符串表达式。像往常一样，这提供了灵 Active，因此查询计划可以从一次运行到下一次运行(请参见Section 42.10.2)有所不同，这还意味着不对命令字符串进行变量替换。与EXECUTE一样，可以通过format()和USING将参数值插入动态命令中。 SCROLL和NO SCROLL选项的含义与绑定游标的含义相同。

An example:

OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1',tabname) USING keyvalue;

在此示例中，表名称通过format()插入查询中。 col1的比较值是通过USING参数插入的，因此不需要引用。

42 .7.2.3. 打开绑定游标

OPEN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ];

这种形式的OPEN用于打开一个游标变量，该变量在声明时与其查询绑定。游标无法打开。当且仅当游标被声明为采用参数时，才必须出现实际参数值表达式的列表。这些值将在查询中替换。

绑定游标的查询计划始终被认为是可缓存的。在这种情况下，没有EXECUTE的等效项。请注意，不能在OPEN中指定SCROLL和NO SCROLL，因为已经确定了光标的滚动行为。

可以使用* positional 或 named *表示法传递参数值。在位置表示法中，所有参数均按 Sequences 指定。在命名符号中，使用:=指定每个参数的名称，以将其与参数表达式分开。与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;

42 .7.3. 使用游标

打开游标后，可以使用此处描述的语句对其进行操作。

这些操作不需要在打开游标开始的同一函数中进行。您可以从函数中返回refcursor值，并让调用者对光标进行操作。 (内部refcursor值只是包含活动光标查询的所谓门户的字符串名称.可以在不干扰门户的情况下传递该名称，将其分配给其他refcursor变量，依此类推.)

所有门户在事务结束时都隐式关闭。因此，refcursor值仅可用于引用打开的游标，直到事务结束为止。

42.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相同。在使用 count 的表单中， count *可以是任何整数值的表达式(与 SQL FETCH命令不同，后者仅允许整数常量)。 * direction *需要向后移动的值很可能会失败，除非使用SCROLL选项声明或打开了光标。

• cursor *必须是引用打开的游标门户的refcursor变量的名称。

Examples:

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

42.7.3.2. MOVE

MOVE [ direction { FROM | IN } ] cursor;

MOVE重新定位光标而未获取任何数据。 MOVE的工作方式与FETCH命令完全相同，不同之处在于它仅重新定位光标，而不返回移动到的行。与SELECT INTO一样，可以检查特殊变量FOUND以查看是否有下一行要移至。

Examples:

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

42 .7.3.3. 更新/删除当前位置

UPDATE table SET ... WHERE CURRENT OF cursor;
DELETE FROM table WHERE CURRENT OF cursor;

当光标位于表行上时，可以使用光标标识该行来更新或删除该行。游标的查询可能受到限制(特别是没有分组)，最好在游标中使用FOR UPDATE。有关更多信息，请参见DECLARE参考页。

An example:

UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;

42.7.3.4. CLOSE

CLOSE cursor;

CLOSE关闭打开的游标下面的门户。这可用于在事务结束之前释放资源，或释放光标变量以再次打开。

An example:

CLOSE curs1;

42 .7.3.5. 返回游标

PL/pgSQL 函数可以将游标返回给调用者。这对于返回多行或多列很有用，尤其是对于非常大的结果集。为此，该函数打开游标，然后将游标名称返回给调用方(或仅使用调用方指定的门户名称或其他已知的门户名称来打开游标)。然后，调用者可以从游标中获取行。游标可以由调用者关闭，或者在事务关闭时自动关闭。

游标使用的门户名称可以由程序员指定或自动生成。要指定门户名称，只需在打开之前为refcursor变量分配一个字符串。 OPEN会将refcursor变量的字符串值用作基础门户的名称。但是，如果refcursor变量为 null，则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;

-- need to be in a transaction to use cursors.
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;

-- need to be in a transaction to use cursors.
BEGIN;

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

FETCH ALL FROM a;
FETCH ALL FROM b;
COMMIT;

42 .7.4. 遍历游标的结果

FOR语句有一个变体，它允许迭代游标返回的行。语法为：

[ <<label>> ]
FOR recordvar IN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ] LOOP
    statements
END LOOP [ label ];

游标变量在声明时必须已绑定到某个查询，并且它不能已打开。 FOR语句自动打开游标，并在退出循环时再次关闭游标。当且仅当游标被声明为采用参数时，才必须出现实际参数值表达式的列表。这些值将以与OPEN相同的方式在查询中替换(请参见Section 42.7.2.3)。

变量* recordvar *被自动定义为类型record，并且仅在循环内部存在(变量名称的任何现有定义在循环内都会被忽略)。游标返回的每一行都被依次分配给该记录变量，并执行循环体。