以下内容大部分翻译自官方文档。
https://www.postgresql.org/docs/10/static/plpgsql-cursors.html
=================================================
可以设置一个cursor来封装查询，然后一次读取几行查询结果，而不是同时执行整个查询。这样做的一个原因是，当结果包含大量行时，避免内存溢出。（然而，PL/pgSQL用户通常不需要担心这一点，因为FOR循环在内部自动使用光标以避免内存问题。）更有趣的用法是返回对函数创建的光标的引用，允许调用者读取行。这提供了一种从函数返回大行集的有效方法。
1. Declaring Cursor Variables
在PL/PGSQL中，所有对游标的访问都是通过游标变量进行的，这些变量始终是特殊数据类型的refcursor。创建游标变量的一种方法是将其声明为refcursor类型的变量。另一种方法是使用游标声明语法，这通常是：
| name [ [ NO ] SCROLL ] CURSOR [ ( arguments ) ] FOR query; |
如果指定了SCROLL，则光标将能够向后滚动；如果没有指定SCROLL，则将拒绝向后获取；如果没有出现任何规范，则取决于是否允许向后获取。参数（如果指定）是一个逗号分隔的对名称数据类型列表，该列表定义要由给定查询中的参数值替换的名称。替换这些名称的实际值将在光标打开后指定。
Examples:
| DECLARE curs1 refcursor; curs2 CURSOR FOR SELECT * FROM tenk1; curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key; |
这三个变量都具有数据类型refcursor，
第一个变量可用于任何查询，
第二个变量具有已经绑定到它的完全指定的查询，
第三个变量具有绑定到它的参数化查询。（当光标被打开时，键将被整数参数值替换。）
变量curs1被称为未绑定，因为它不绑定到任何特定的查询。
2. Opening Cursors
在游标可以用来检索行之前，必须打开它。（这是与SQL命令DECLARE CURSOR等效的操作。）PL/pgSQL有三种形式的OPEN语句，其中两种使用未绑定的光标变量，而另一种使用绑定的光标变量。
2.1. OPEN FOR query
| OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR query; |
打开游标变量，并指定要执行的查询。光标不能已经打开，并且它必须被声明为未绑定的光标变量（即，作为简单的refcursor变量）。查询必须是选择，或者返回行的其他内容（如解释）。查询的处理方式与PL/pgSQL中的其他SQL命令相同：替换PL/pgSQL变量名，缓存查询计划以供可能的重用。当PL/pgSQL变量被替换到光标查询中时，被替换的值是OPEN时所具有的值；变量的后续更改不会影响光标的行为。滚动和无滚动选项具有与绑定游标相同的含义。
Example:
| OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey; |
2.2. OPEN FOR EXECUTE
| OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR EXECUTE query_string [ USING expression [, ... ] ]; |
打开游标变量，并指定要执行的查询。光标不能已经打开，并且它必须被声明为未绑定的光标变量（即，作为简单的refcursor变量）。该查询被指定为字符串表达式，与执行命令中的方式相同。与往常一样，这提供了灵活性，因此查询计划可以随着每次运行而变化，并且它还意味着对命令字符串不进行变量替换。与执行一样，参数值可以通过FATATE（）插入到动态命令中并使用。滚动和无滚动选项具有与绑定游标相同的含义。
Example:
| OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1',tabname) USING keyvalue; |
2.3. Opening a Bound Cursor
| OPEN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ]; |
这种打开形式用于打开一个游标变量，该变量在声明时绑定到它。光标不能打开。当且仅当游标被声明为参数时，必须出现实际的参数值表达式的列表。这些值将在查询中被替换。
绑定游标的查询计划始终被认为是可缓存的，在这种情况下没有等效的执行。注意，在游标中不能指定滚动和滚动，因为已经确定了游标的滚动行为。
可以使用位置或命名符号传递参数值。在位置表示法中，所有参数按顺序指定。在命名符号中，每个参数的名称都是使用：=将其从参数表达式中分离出来的。类似于在第4.3节中描述的调用函数，它也允许混合位置和命名符号。
| OPEN curs2; OPEN curs3(42); OPEN curs3(key := 42); |
因为变量替换是在绑定游标的查询上完成的，所以实际上有两种方法可以将值传递到游标中：要么使用显式参数OPEN，要么在查询中引用PL/pgSQL变量。但是，只有声明绑定游标之前声明的变量将被替换为变量。在任一种情况下，要在打开时确定要传递的值。获得与上面的CURS3示例相同的效果的另一种方法是
| DECLARE key integer; curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key; BEGIN key := 42; OPEN curs4; |
3. Using Cursors
这些操作不必在打开光标开始的相同函数中发生。您可以从函数中返回一个refcursor值，并让调用方在光标上操作。
所有入口在事务结束时隐式关闭。因此，refcursor值仅用于引用一个打开的游标直到事务结束。
3.1. FETCH
FETCH将光标中的下一行检索到目标中，目标可以是行变量、记录变量或逗号分隔的简单变量列表，就像SELECT INTO一样。如果没有下一行，则将目标设置为null（s）。与选择项一样，可以检查找到的特殊变量以查看是否获得行。
direction子句可以是SQL FETCH命令中允许的任何变体，除了可以获取多行的变体；即，它可以是Next、PRIOR、FIRST、LAST、ABSOLUTE count、RELATIVE count、FORWARD或BACKWARD。省略方向与指定下一个方向相同。在使用计数的表单中，计数可以是任何整数值表达式（不同于SQL FETCH命令，它只允许整数常数）。需要向后移动的方向值可能会失败，除非光标用滚动选项声明或打开。
游标必须是引用打开光标入口的ReFuxSort变量的名称。
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; |
3.2. MOVE
在没有检索任何数据的情况下移动游标重新定位。移动完全类似于FETCH命令，除了它只重新定位光标，不返回移动到的行。与SELECT一样，可以查找找到的特殊变量，看看是否有下一行要移动。
Examples:
| MOVE curs1; MOVE LAST FROM curs3; MOVE RELATIVE -2 FROM curs4; MOVE FORWARD 2 FROM curs4; |
3.3. UPDATE/DELETE WHERE CURRENT OF
当游标定位在表行上时，可以使用游标来更新或删除该行以标识行。对于光标的查询可以是什么（特别是没有分组）存在限制，最好在光标中使用FOR UPDATE。
Example:
| UPDATE foo SET dataval = myval WHERE CURRENT OF curs1; |
3.4. CLOSE
关闭打开的光标。这可以用于在事务结束之前释放资源，或者释放游标变量以再次打开。
3.5. Returning Cursors
PL/PGSQL函数可以将游标返回给调用方。这对于返回多行或多行非常有用，特别是对于非常大的结果集。为此，函数打开光标并将光标名返回给调用者（或者简单地使用由调用者指定或以其他方式已知的入口名打开光标）。然后调用方可以从游标中获取行。游标可以由调用方关闭，或者在事务关闭时自动关闭。
用于游标的门户名称可以由程序员指定或自动生成。若要指定门户名称，只需在打开它之前将字符串分配给RIFCURSOR变量。ReFuxSort变量的字符串值将作为基础门户的名称以OPEN使用。但是，如果refcursor变量为空，OPEN会自动生成一个不与任何现有门户冲突的名称，并将其分配给refcursor变量。
Examples:
| mytest=# CREATE OR REPLACE FUNCTION reffunc(refcursor) RETURNS refcursor AS ' mytest'# BEGIN mytest'# OPEN $1 FOR SELECT col FROM test; mytest'# RETURN $1; mytest'# END; mytest'# ' LANGUAGE plpgsql; CREATE FUNCTION mytest=# BEGIN; BEGIN mytest=# SELECT reffunc('funccursor'); reffunc ------------ funccursor (1 row)
mytest=# FETCH ALL IN funccursor; col ----- 123 123 1 (3 rows)
mytest=# COMMIT; COMMIT |
| mytest=# CREATE OR REPLACE FUNCTION reffunc2() RETURNS refcursor AS ' mytest'# DECLARE mytest'# ref refcursor; mytest'# BEGIN mytest'# OPEN ref FOR SELECT col FROM test; mytest'# RETURN ref; mytest'# END; mytest'# ' LANGUAGE plpgsql; CREATE FUNCTION mytest=# BEGIN; BEGIN mytest=# SELECT reffunc2(); reffunc2 -------------------- <unnamed portal 2> (1 row)
mytest=# FETCH ALL IN "<unnamed portal 2>"; col ----- 123 123 1 (3 rows)
mytest=# COMMIT; COMMIT |
| mytest=# create table table_1 (col1 int, col2 varchar(10)); CREATE TABLE mytest=# create table table_2 (col1 int, col2 varchar(10)); CREATE TABLE mytest=# insert into table_1 values (1,'a'); INSERT 0 1 mytest=# insert into table_1 values (2,'b'); INSERT 0 1 mytest=# insert into table_2 values (11,'aa'); INSERT 0 1 mytest=# insert into table_2 values (22,'bb'); INSERT 0 1 mytest=# CREATE OR REPLACE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$ mytest$# BEGIN mytest$# OPEN $1 FOR SELECT * FROM table_1; mytest$# RETURN NEXT $1; mytest$# OPEN $2 FOR SELECT * FROM table_2; mytest$# RETURN NEXT $2; mytest$# END; mytest$# $$ LANGUAGE plpgsql; CREATE FUNCTION mytest=# BEGIN; BEGIN mytest=# SELECT * FROM myfunc('a', 'b'); myfunc -------- a b (2 rows)
mytest=# mytest=# FETCH ALL FROM a; col1 | col2 ------+------ 1 | a 2 | b (2 rows)
mytest=# FETCH ALL FROM b; col1 | col2 ------+------ 11 | aa 22 | bb (2 rows)
mytest=# COMMIT; COMMIT
|
4. Looping Through a Cursor's Result
FOR语句的一个变体允许迭代遍历游标返回的行。语法是：
| [ <<label>> ] FOR recordvar IN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ] LOOP statements END LOOP [ label ]; |
在声明时，游标变量必须绑定到某个查询，并且它不能打开。for语句自动打开游标，当循环退出时，它再次关闭游标。当且仅当游标被声明为参数时，必须出现实际的参数值表达式的列表。这些值将在查询中被替换，就像在打开时一样
变量recordvar自动定义为类型记录，并且只存在于循环中（在循环中忽略变量名的任何现有定义）。由光标返回的每一行被依次分配给该记录变量，并执行循环体。
8760
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
