| PostgreSQL 8.1 中文文档 | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 36. PL/pgSQL - SQL 过程语言 | Fast Forward | Next |
如果不想一次执行整个命令，我们可以设置一个封装该命令的 游标，然后每次读取几行命令结果。 这么干的一个原因是在结果包含数量非常大的行时避免内存耗尽。 （当然，PL/pgSQL 用户通常不必担心这个， 因为 FOR 循环自动在内部使用一个游标以避免内存问题。） 一个更有趣的用法是某个函数可以返回一个它创建的游标的引用， 这样就允许调用者读取各行。这就提供了一种从函数返回一个结果集的手段。
所有在 PL/pgSQL 里对游标的访问都是通过游标变量实现的， 它总是特殊的数据类型 refcursor。 创建一个游标变量的一个方法是把它声明为一个类型为 refcursor 的变量。 另外一个方法是使用游标声明语法，通常是下面这样：
name CURSOR [ ( arguments ) ] FOR query;（出于兼容Oracle的考虑，FOR 可以替换为 IS。） 如果有 arguments，那么它是一个逗号分隔的 name datatype 配对的列表，它们定义那些将会用参数值替换掉的所给出命令中的名字。 实际用于代换这些名字的数值将在后面声明，在游标打开之后。
几个例子∶
DECLARE
curs1 refcursor;
curs2 CURSOR FOR SELECT * FROM tenk1;
curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;所有这三个变量都是类型为 refcursor， 但是第一个可以用于任何命令，而第二个已经绑定 了一个声明完整的命令，最后一个是绑定了一个带参数的命令。 （key 将在游标打开的时候被代换成一个整数。） 变量 curs1 可以称之为未绑定的， 因为它没有和任何查询相绑定。
在你使用游标检索行之前，你必需宪打开它。 （这是和 SQL 命令 DECLARE CURSOR 相等的操作。） PL/pgSQL 有三种形式的OPEN语句， 两种用于未绑定的游标变量，另外一种用于绑定的游标变量。
OPEN unbound_cursor FOR 查询;
该游标变量打开，并且执行给出的查询。游标不能是已经打开的， 并且它必需是声明为一个未绑定的游标（也就事说，声明为一个简单的 refcursor 变量）。 查询必须是一条 SELECT，或者其它返回行的东西（比如 EXPLAIN）。 查询是和其它在 PL/pgSQL 里的 SQL 命令平等对待的： 先代换 PL/pgSQL 的变量名，而且执行计划为将来可能的复用缓存起来。
一个例子：
OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
OPEN unbound_cursor FOR EXECUTE query-string;
打开游标变量并且执行给出的查询。游标不能是已打开的，并且必须声明为一个未绑定的游标（也就是说， 是一个简单的 refcursor 变量）。命令是用和那些用于 EXECUTE 命令一样的方法声明的字串表达式， 这样，我们就有了命令可以在两次运行间发生变化的灵活性。
一个例子：
OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);
OPEN bound_cursor [ ( argument_values ) ];这种形式的OPEN用于打开一个游标变量，该游标变量的命令是在声明的时候和它绑定在一起的。 游标不能是已经打开的。 当且仅当该游标声明为接受参数的时候，语句中才必需出现一个实际参数值表达式的列表。 这些值将代换到命令中。一个绑定的游标的命令计划总是认为可缓冲的 -- 这种情况下没有等效的EXECUTE。
例子：
OPEN curs2; OPEN curs3(42);
一旦你已经打开了一个游标，那么你就可以用这里描述的语句操作它。
这些操作不需要发生在和打开该游标开始操作的同一个函数里。 你可以从函数里返回一个 refcursor 数值，然后让调用者操作该游标。 （在内部，refcursor 值只是一个包含该游标命令的活跃查询的信使的字串名。 这个名字可以传来传去，可以赋予其它 refcursor 变量等等，也不用担心扰乱信使。）
所有信使在事务的结尾都会隐含地关闭。因此一个 refcursor 值只能在该事务结束前用于引用一个打开的游标。
FETCH cursor INTO target;
FETCH从游标中检索下一行到目标中，目标可以是一个行变量，一个记录变量， 或者是一个逗号分隔的普通变量的列表，就象SELECT INTO里一样。 和SELECT INTO一样，你可以使用特殊变量FOUND检查是否检索出一个行。
一个例子：
FETCH curs1 INTO rowvar; FETCH curs2 INTO foo, bar, baz;
CLOSE cursor;
CLOSE关闭支撑在一个打开的游标下面的信使。 这样我们就可以在事务结束之前施放资源，或者释放掉该游标变量，用于稍后再次打开。
一个例子：
CLOSE curs1;
PL/pgSQL 函数可以向调用者返回游标。 这个功能用于从函数里返回多行或多列。要想这么做的时候， 该函数打开游标并且把该游标的名字返回给调用者。 调用者然后从游标里FETCH行。 游标可以由调用者关闭，或者是在事务结束的时候自动关闭。
函数返回的游标名可以由调用者声明或者自动生成。 要声明一个信使的名字，只要再打开游标之前，给 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;