游标

PostgreSQL 9.4.4 中文手册
上一页 | 上一级 | 章 40. PL/pgSQL - SQL过程语言 | 下一页

# 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`语句， 两种用于未绑定的游标变量， 另外一种用于已绑定的游标变量。

> 注意: 可以通过第 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`命令一样的方法声明的字符串表达式， 这样，就有了命令可以在两次运行间发生变化的灵活性。 参阅第 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符号传递。 在位置符号中，所有的参数以顺序指定。 在命名法中，每个参数的名称使用`:=`声明以 从参数表达式中分开。类似于调用函数，在第 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`变量。

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

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

    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`的方式 (参阅第 40.7.2.3 节)。

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

上一页 | 起始页 | 下一页
控制结构 | 上一级 | 错误和消息