DECLARE 中文man页面-51CTO.COM

NAME

DECLARE - 定义一个游标

SYNOPSIS

DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
    CURSOR [ { WITH | WITHOUT } HOLD ] FOR query
    [ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ]

DESCRIPTION 描述

DECLARE 允许用户创建游标，用于在一个大的查询里面检索少数几行数据。使用 FETCH [fetch(7)]，游标可以既可以返回文本也可以返回二进制格式。

通常游标返回文本格式，和 SELECT 生成的是一样的。因为数据在系统内部是用二进制格式存储的，系统必须对数据做一定转换以生成文本格式。一旦数据是以文本形式返回，那么客户端应用需要把它们转换成二进制进行操作。另外，文本格式一般都比对应的二进制格式占的存储空间大。二进制游标给你返回内部二进制形态的数据。当然，如果你想以文本方式显示数据，那么以文本方式检索会为你节约很多客户端的工作。

比如，如果查询从一个整数列返回一个一，在缺省的游标里你将获得一个字符串 1，而如果是一个二进制游标，你将得到一个 4-字节的包含该数值内部形式的数值（大端序）。

游标应该小心使用二进制游标。一些用户应用如 psql 是不识别二进制游标的，而且期望返回的数据是文本格式。

: Note: 注意: 如果客户端应用使用"扩展查询"协议发出 FETCH 命令，那么 Bind 协议声明数据是用文本还是用二进制格式检索。这个选择覆盖游标的定义。因此，在使用扩展查询协议的时候，二进制游标的概念已经过时了－任何游标都可以当作文本或者二进制的格式发出。

PARAMETERS 参数

name: 将在随后FETCH操作中使用的游标名。
BINARY: 令游标以二进制而不是文本格式获取数据。
INSENSITIVE: 表明从游标检索出来的数据不应该被其他进程或游标的更新动作影响。在 PostgreSQL 里，所有游标都是不敏感的，这个关键字没有什么作用，提供它只是为了和 SQL 标准兼容。
SCROLL
NO SCROLL: SCROLL 声明该游标可以用于以非顺序的方式检索数据行（也就是向后检索）。根据查询的执行计划的不同，声明 SCROLL 可能会对查询的执行时间附加一定的影响。 NO SCROLL 声明该游标不能用于以非顺序的方式检索数据行（也就是向后检索）。
WITH HOLD
WITHOUT HOLD: WITH HOLD 声明该游标可以在创建它的事务成功提交后继续使用。 WITHOUT HOLD 声明该游标不能在创建它的的事务提交后使用。如果既没有声明 WITHOUT HOLD，也没有声明 WITH HOLD，那么缺省是 WITH HOLD。
query: 一个SELECT查询，它提供由游标返回的行。请参考 SELECT 语句获取有关有效查询的详细信息。
FOR READ ONLY
FOR UPDATE: FOR READ ONLY 表明游标将用于只读模式。 FOR UPDATE 表明游标将被用于更新表。因为目前 PostgreSQL 不支持游标更新，所以声明 FOR UPDATE 将产生一个错误信息。而声明 FOR READ ONLY 没有作用。
column: 将被更新的列。因为游标更新目前不被 PostgreSQL 支持，所以 FOR UPDATE 子句将产生一个错误信息。

BINARY，INSENSITIVE，SCROLL 关键字可以以任何顺序出现。

NOTES 注意

如果没有声明 WITH HOLD，那么这个命令创建的游标只能在当前事务中使用。 Thus, DECLARE without WITH HOLD is useless outside a transaction block: the cursor would survive only to the completion of the statement. Therefore PostgreSQL reports an error if this command is used outside a transaction block. 使用 BEGIN [begin(7)], COMMIT [commit(7)] 和 ROLLBACK [rollback(7)] 定义一个事务块。

如果声明了 WITH HOLD，并且创建该游标的事务成功提交，那么游标还可以在同一会话随后的事务里访问。（但如果创建它的事务回滚，那么游标被删除。）带着 WITH HOLD 创建的游标是用一个明确的 CLOSE 命令，或者是会话终止来关闭的。在目前的实现里，由一个游标代表的行是被拷贝到一个临时文件或者内存区里的，这样他们就仍然可以在随后的事务中被访问。

在定义一个要用来向后抓取的游标的时候，我们应该声明 SCROLL 选项。这个是 SQL 标准要求的。不过，为了和早期的版本兼容， PostgreSQL 在没有 SCROLL 的时候也允许向后抓取，只要游标的查询计划简单得不需要额外的开销就可以支持它。不过，我们建议应用开发人员不要依赖于使用没有带着 SCROLL 定义的游标的后向查找功能。如果声明了 NO SCROLL，那么不管怎样都会禁止向后抓取的功能。

在 SQL 标准中游标只能在嵌入 SQL （ESQL）的应用中使用。 PostgreSQL 服务器没有一个明确的 OPEN 语句；一个游标被认为在定义时就已经打开了。不过，PostgreSQL嵌入的 SQL 预编译器， ecpg，支持 SQL92 习惯，包括那些和DECLARE和OPEN相关的语句。

EXAMPLES 例子

定义一个游标：

DECLARE liahona CURSOR FOR SELECT * FROM films;

#p#

NAME

DECLARE - define a cursor

SYNOPSIS

DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
    CURSOR [ { WITH | WITHOUT } HOLD ] FOR query
    [ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ]

DESCRIPTION

DECLARE allows a user to create cursors, which can be used to retrieve a small number of rows at a time out of a larger query. Cursors can return data either in text or in binary format using FETCH [fetch(7)].

Normal cursors return data in text format, the same as a SELECT would produce. Since data is stored natively in binary format, the system must do a conversion to produce the text format. Once the information comes back in text form, the client application may need to convert it to a binary format to manipulate it. In addition, data in the text format is often larger in size than in the binary format. Binary cursors return the data in a binary representation that may be more easily manipulated. Nevertheless, if you intend to display the data as text anyway, retrieving it in text form will save you some effort on the client side.

As an example, if a query returns a value of one from an integer column, you would get a string of 1 with a default cursor whereas with a binary cursor you would get a 4-byte field containing the internal representation of the value (in big-endian byte order).

Binary cursors should be used carefully. Many applications, including psql, are not prepared to handle binary cursors and expect data to come back in the text format.

: Note: When the client application uses the ``extended query'' protocol to issue a FETCH command, the Bind protocol message specifies whether data is to be retrieved in text or binary format. This choice overrides the way that the cursor is defined. The concept of a binary cursor as such is thus obsolete when using extended query protocol --- any cursor can be treated as either text or binary.

PARAMETERS

name: The name of the cursor to be created.
BINARY: Causes the cursor to return data in binary rather than in text format.
INSENSITIVE: Indicates that data retrieved from the cursor should be unaffected by updates to the tables underlying the cursor while the cursor exists. In PostgreSQL, all cursors are insensitive; this key word currently has no effect and is present for compatibility with the SQL standard.
SCROLL
NO SCROLL: SCROLL specifies that the cursor may be used to retrieve rows in a nonsequential fashion (e.g., backward). Depending upon the complexity of the query's execution plan, specifying SCROLL may impose a performance penalty on the query's execution time. NO SCROLL specifies that the cursor cannot be used to retrieve rows in a nonsequential fashion.
WITH HOLD
WITHOUT HOLD: WITH HOLD specifies that the cursor may continue to be used after the transaction that created it successfully commits. WITHOUT HOLD specifies that the cursor cannot be used outside of the transaction that created it. If neither WITHOUT HOLD nor WITH HOLD is specified, WITHOUT HOLD is the default.
query: A SELECT command that will provide the rows to be returned by the cursor. Refer to SELECT [select(7)] for further information about valid queries.
FOR READ ONLY
FOR UPDATE: FOR READ ONLY indicates that the cursor will be used in a read-only mode. FOR UPDATE indicates that the cursor will be used to update tables. Since cursor updates are not currently supported in PostgreSQL, specifying FOR UPDATE will cause an error message and specifying FOR READ ONLY has no effect.
column: Column(s) to be updated by the cursor. Since cursor updates are not currently supported in PostgreSQL, the FOR UPDATE clause provokes an error message.

The key words BINARY, INSENSITIVE, and SCROLL may appear in any order.

NOTES

Unless WITH HOLD is specified, the cursor created by this command can only be used within the current transaction. Thus, DECLARE without WITH HOLD is useless outside a transaction block: the cursor would survive only to the completion of the statement. Therefore PostgreSQL reports an error if this command is used outside a transaction block. Use BEGIN [begin(7)], COMMIT [commit(7)] and ROLLBACK [rollback(7)] to define a transaction block.

If WITH HOLD is specified and the transaction that created the cursor successfully commits, the cursor can continue to be accessed by subsequent transactions in the same session. (But if the creating transaction is aborted, the cursor is removed.) A cursor created with WITH HOLD is closed when an explicit CLOSE command is issued on it, or the session ends. In the current implementation, the rows represented by a held cursor are copied into a temporary file or memory area so that they remain available for subsequent transactions.

The SCROLL option should be specified when defining a cursor that will be used to fetch backwards. This is required by the SQL standard. However, for compatibility with earlier versions, PostgreSQL will allow backward fetches without SCROLL, if the cursor's query plan is simple enough that no extra overhead is needed to support it. However, application developers are advised not to rely on using backward fetches from a cursor that has not been created with SCROLL. If NO SCROLL is specified, then backward fetches are disallowed in any case.

The SQL standard only makes provisions for cursors in embedded SQL. The PostgreSQL server does not implement an OPEN statement for cursors; a cursor is considered to be open when it is declared. However, ECPG, the embedded SQL preprocessor for PostgreSQL, supports the standard SQL cursor conventions, including those involving DECLARE and OPEN statements.

EXAMPLES

To declare a cursor:

DECLARE liahona CURSOR FOR SELECT * FROM films;

See FETCH [fetch(7)] for more examples of cursor usage.