PostgreSQL: Documentation: 9.2: Cursors
This page in other versions:
&|& Development versions:
&|& Unsupported versions:
Rather than executing a whole query at once, it is possible to
set up a cursor that encapsulates the
query, and then read the query result a few rows at a time. One
reason for doing this is to avoid memory overrun when the result
contains a large number of rows. (However, PL/pgSQL users do not normally need to worry
about that, since FOR loops
automatically use a cursor internally to avoid memory problems.)
A more interesting usage is to return a reference to a cursor
that a function has created, allowing the caller to read the
rows. This provides an efficient way to return large row sets
from functions.
All access to cursors in PL/pgSQL goes through cursor variables,
which are always of the special data type refcursor. One way to create a cursor variable is
just to declare it as a variable of type refcursor. Another way is to use the cursor
declaration syntax, which in general is:
name [ [ NO ] SCROLL ] CURSOR [ ( arguments ) ] FOR query;
(FOR can be replaced by IS for Oracle
compatibility.) If SCROLL is
specified, the cursor will be capable o if
NO SCROLL is specified, backward
fetc if neither specification appears, it
is query-dependent whether backward fetches will be allowed.
arguments, if specified, is a
comma-separated list of pairs name datatype that define names to be
replaced by parameter values in the given query. The actual
values to substitute for these names will be specified later,
when the cursor is opened.
Some examples:
curs2 CURSOR FOR SELECT * FROM tenk1;
curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 =
All three of these variables have the data type refcursor, but the first can be used with any
query, while the second has a fully specified query already
bound to it, and the last has a
parameterized query bound to it. (key
will be replaced by an integer parameter value when the cursor
is opened.) The variable curs1 is said
to be unbound since it is not bound to
any particular query.
Before a cursor can be used to retrieve rows, it must be
opened. (This is the equivalent action
to the SQL command DECLARE CURSOR.)
PL/pgSQL has three forms of
the OPEN statement, two of which use
unbound cursor variables while the third uses a bound cursor
Note: Bound cursor variables can also be used
without explicitly opening the cursor, via the FOR statement described in .
OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR query;
The cursor variable is opened and given the specified
query to execute. The cursor cannot be open already, and it
must have been declared as an unbound cursor variable (that
is, as a simple refcursor variable).
The query must be a SELECT, or
something else that returns rows (such as EXPLAIN). The query is treated in the same way
as other SQL commands in PL/pgSQL: PL/pgSQL variable names are substituted,
and the query plan is cached for possible reuse. When a
PL/pgSQL variable is
substituted into the cursor query, the value that is
substituted is the one it has at the time of the OPEN; subsequent changes to the variable will
not affect the cursor's behavior. The SCROLL and NO SCROLL
options have the same meanings as for a bound cursor.
An example:
OPEN curs1 FOR SELECT * FROM foo WHERE key =
OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR EXECUTE query_string
[ USING expression [, ... ] ];
The cursor variable is opened and given the specified
query to execute. The cursor cannot be open already, and it
must have been declared as an unbound cursor variable (that
is, as a simple refcursor variable).
The query is specified as a string expression, in the same
way as in the EXECUTE command. As
usual, this gives flexibility so the query plan can vary from
one run to the next (see ), and it also means that variable substitution is
not done on the command string. As with EXECUTE, parameter values can be inserted into
the dynamic command via USING. The
SCROLL and NO
SCROLL options have the same meanings as for a bound
An example:
OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident(tabname)
|| ' WHERE col1 = $1' USING
In this example, the table name is inserted into the query
textually, so use of quote_ident() is recommended to guard
against SQL injection. The comparison value for col1 is inserted via a USING parameter, so it needs no quoting.
OPEN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ];
This form of OPEN is used to open
a cursor variable whose query was bound to it when it was
declared. The cursor cannot be open already. A list of actual
argument value expressions must appear if and only if the
cursor was declared to take arguments. These values will be
substituted in the query.
The query plan for a bound cursor is always considered
there is no equivalent of EXECUTE in this case. Notice that SCROLL and NO SCROLL
cannot be specified in OPEN, as the
cursor's scrolling behavior was already determined.
Argument values can be passed using either positional or named
notation. In positional notation, all arguments are specified
in order. In named notation, each argument's name is
specified using := to separate it
from the argument expression. Similar to calling functions,
described in , it is also allowed to mix positional and named
Examples (these use the cursor declaration examples
OPEN curs2;
OPEN curs3(42);
OPEN curs3(key := 42);
Because variable substitution is done on a bound cursor's
query, there are really two ways to pass values into the
cursor: either with an explicit argument to OPEN, or implicitly by referencing a
PL/pgSQL variable in the
query. However, only variables declared before the bound
cursor was declared will be substituted into it. In either
case the value to be passed is determined at the time of the
OPEN. For example, another way to
get the same effect as the curs3
example above is
curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 =
key := 42;
OPEN curs4;
Once a cursor has been opened, it can be manipulated with
the statements described here.
These manipulations need not occur in the same function that
opened the cursor to begin with. You can return a refcursor value out of a function and let the
caller operate on the cursor. (Internally, a refcursor value is simply the string name of a
so-called portal containing the active query for the cursor.
This name can be passed around, assigned to other refcursor variables, and so on, without disturbing
the portal.)
All portals are implicitly closed at transaction end.
Therefore a refcursor value is usable to
reference an open cursor only until the end of the
transaction.
FETCH [ direction { FROM | IN } ] cursor INTO target;
FETCH retrieves the next row from
the cursor into a target, which might be a row variable, a
record variable, or a comma-separated list of simple
variables, just like SELECT INTO. If
there is no next row, the target is set to NULL(s). As with
SELECT INTO, the special variable
FOUND can be checked to see whether
a row was obtained or not.
The direction clause can
be any of the variants allowed in the SQL
command except the ones that can
fet namely, it can be NEXT, PRIOR,
FIRST, LAST, ABSOLUTE
count, RELATIVE count, FORWARD, or BACKWARD.
Omitting direction is the
same as specifying NEXT. direction values that require moving
backward are likely to fail unless the cursor was declared or
opened with the SCROLL option.
cursor must be the name of
a refcursor variable that references an
open cursor portal.
FETCH curs1 INTO
FETCH curs2 INTO foo, bar,
FETCH LAST FROM curs3 INTO x,
FETCH RELATIVE -2 FROM curs4 INTO
MOVE [ direction { FROM | IN } ] cursor;
MOVE repositions a cursor without
retrieving any data. MOVE works
exactly like the FETCH command,
except it only repositions the cursor and does not return the
row moved to. As with SELECT INTO,
the special variable FOUND can be
checked to see whether there was a next row to move to.
The direction clause can
be any of the variants allowed in the SQL
command, namely NEXT, PRIOR,
FIRST, LAST, ABSOLUTE
count, RELATIVE count, ALL,
FORWARD [ count | ALL ], or BACKWARD [ count | ALL ]. Omitting direction is the same as specifying
NEXT. direction values that require moving
backward are likely to fail unless the cursor was declared or
opened with the SCROLL option.
MOVE curs1;
MOVE LAST FROM curs3;
MOVE RELATIVE -2 FROM curs4;
MOVE FORWARD 2 FROM curs4;
UPDATE table SET ... WHERE CURRENT OF cursor;
DELETE FROM table WHERE CURRENT OF cursor;
When a cursor is positioned on a table row, that row can
be updated or deleted using the cursor to identify the row.
There are restrictions on what the cursor's query can be (in
particular, no grouping) and it's best to use FOR UPDATE in the cursor. For more information
An example:
UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;
CLOSE cursor;
CLOSE closes the portal
underlying an open cursor. This can be used to release
resources earlier than end of transaction, or to free up the
cursor variable to be opened again.
An example:
CLOSE curs1;
PL/pgSQL functions can
return cursors to the caller. This is useful to return
multiple rows or columns, especially with very large result
sets. To do this, the function opens the cursor and returns
the cursor name to the caller (or simply opens the cursor
using a portal name specified by or otherwise known to the
caller). The caller can then fetch rows from the cursor. The
cursor can be closed by the caller, or it will be closed
automatically when the transaction closes.
The portal name used for a cursor can be specified by the
programmer or automatically generated. To specify a portal
name, simply assign a string to the refcursor variable before opening it. The string
value of the refcursor variable will be
used by OPEN as the name of the
underlying portal. However, if the refcursor variable is null, OPEN automatically generates a name that does
not conflict with any existing portal, and assigns it to the
refcursor variable.
Note: A bound cursor variable is initialized to
the string value representing its name, so that the
portal name is the same as the cursor variable name,
unless the programmer overrides it by assignment before
opening the cursor. But an unbound cursor variable
defaults to the null value initially, so it will receive
an automatically-generated unique name, unless
overridden.
The following example shows one way a cursor name can be
supplied by the caller:
CREATE TABLE test (col text);
INSERT INTO test VALUES ('123');
CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
OPEN $1 FOR SELECT col FROM
RETURN $1;
' LANGUAGE
SELECT reffunc('funccursor');
FETCH ALL IN
The following example uses automatic cursor name
generation:
CREATE FUNCTION reffunc2() RETURNS refcursor AS '
OPEN ref FOR SELECT col FROM
' LANGUAGE
-- need to be in a transaction to use cursors.
SELECT reffunc2();
--------------------
&unnamed cursor 1&
FETCH ALL IN "&unnamed cursor 1&";
The following example shows one way to return multiple
cursors from a single function:
CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$
OPEN $1 FOR SELECT * FROM table_1;
RETURN NEXT $1;
OPEN $2 FOR SELECT * FROM table_2;
RETURN NEXT $2;
$$ LANGUAGE
-- need to be in a transaction to use cursors.
SELECT * FROM myfunc('a', 'b');
FETCH ALL FROM
FETCH ALL FROM
There is a variant of the FOR
statement that allows iterating through the rows returned by a
cursor. The syntax is:
[ &&label&& ]
FOR recordvar IN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ] LOOP
statements
END LOOP [ label ];
The cursor variable must have been bound to some query when
it was declared, and it cannot be open already. The
FOR statement automatically opens the
cursor, and it closes the cursor again when the loop exits. A
list of actual argument value expressions must appear if and
only if the cursor was declared to take arguments. These values
will be substituted in the query, in just the same way as
during an OPEN (see ).
The variable recordvar is
automatically defined as type record and
exists only inside the loop (any existing definition of the
variable name is ignored within the loop). Each row returned by
the cursor is successively assigned to this record variable and
the loop body is executed.只有认证用户才能操作
为了享受完美体验，赶快去认证吧
视频 & 视频详情
麦块有你更精彩交个朋友吧愿him与你同在土豪，不解释
&&&&&&阔绰的打赏1颗钻石确定
手撸大树史蒂夫
你需要先登录才能回帖&
剩余2000个字
热门推荐直播视频
1自制模组生存 第一次直播 我的mc名字52mc
热度：145802-02
2【北】服务器小游戏直播来一发♂！
热度：225701-29
3人渣的全程高能直播录————1.8.7的极限生存（又作死玩极限）
热度：283401-29
4【直播】屌丝的生存（击败末影龙算完结）
热度：399701-24
5我是一只吸血鬼
热度：1243301-24
6《盗天遮日》
热度：86001-24
1麦姐周周“波”第二期：圣诞节快乐~
热度：653812-25
2我的世界Minecraft《国外战墙Mineplex双人搞基贤逼拿到10颗钻石》
热度：878712-17
3我的世界★Minecraft【1.9PVE神秘军团作战】雷电炎爆,治疗图腾！
热度：196812-08
4【豪大屌解说】：☆最后战线☆The End Battle Line 第二集
热度：84412-10
5Minecraft超大型RPG雷亚王国#6 气质“打手枪”
热度：74412-11
6视频: 《幻》----生存+RPG，我的世界1.7.2纯净服务器介绍视频
热度：85912-10
热门下载服务器游戏地图
热度：52801-28
2YourCraft
热度：14799809-26
热度：1722910-15
热度：621811-03
热度：433511-25
热度：2096105-21
1我的世界1.7.2纯净版
热度：882134
2我的世界1.7.2超级无敌整合包
热度：608011
3我的世界1.7.10纯净版
热度：423522
4我的世界1.7.10千钰之巅服务器客户端
热度：404763
5我的世界1.7.2希望领域服务器客户端
热度：324229
6我的世界1.6.4herobrine太空光影天堂懒人包
热度：304949
热度：155361
热度：116005
3NPC大型RPG空岛2
热度：108343
4丧尸之战完美版2403制作
热度：82372
5恶魔岛rpgv1.1
热度：80384
6天空幻境-大型空岛RPG之战
热度：75617
客户端下载
胆怯为诗奉一生の焦氏
滑稽 の羊癫疯∝╬══→A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Server Error in '/' Application.
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException (0x): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)]
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +428
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +65
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +117
System.Data.SqlClient.SqlConnection.Open() +122
FCMS.DataProvider.SqlConnectionPool.Create() +38
FCMS.DataProvider.ObjectPool.GetObjectFromPool() +670
FCMS.DataProvider.SqlConnectionPool.BorrowConnection() +33
[Exception: 对像池取出连接对象失败]
FCMS.DataProvider.SqlConnectionPool.BorrowConnection() +107
FCMS.DataProvider.SqlDBManager.RunSql_Query(String sql) +44
FCMS.DataProvider.DBCenter.Run_SQL_Query(String Sql) +19
FengcheWeb.PageControllers.contentPageController.DoAction(String action, HttpRequest Request) +696
FengcheWeb.PageControllers.PageControllerFactory.ProcessMain(HttpRequest Request, HtmlTextWriter writer, Type source) +340
PUWords._Default.Render(HtmlTextWriter writer) +34
System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) +27
System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter) +99
System.Web.UI.Control.RenderControl(HtmlTextWriter writer) +25
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1266
Version Information:&Microsoft .NET Framework Version:2.0.; ASP.NET Version:2.0.