40.7. 游标 · PostgreSQL 中文文档 9.3

# 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](#calibre_link-1580)中描述的`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](#calibre_link-1573)）这也意味着在命令字符串上不能进行变量替换。跟`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](#calibre_link-733)中描述，它也允许混合位置和命名法。例子（以上使用游标声明的例子）: ``` 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](#calibre_link-74)命令允许的变量，除了那些可以抓取不止一行的；形如：`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](#calibre_link-74) 命令允许的变量，如下`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](#calibre_link-72)。例如： ``` 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](#calibre_link-1582))。 `_recordvar_`变量会自动定义为`record`类型，并且只存在于循环中（循环中任何的定义变量名的动作都会被忽略）。每一个由游标返回的行都会陆续的被分配到记录变量中，然后执行循环体。