NAME

DECLARE - 定義一個(gè)游標(biāo)

SYNOPSIS

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

DESCRIPTION 描述

DECLARE 允許用戶(hù)創(chuàng)建游標(biāo)， 用于在一個(gè)大的查詢(xún)里面檢索少數(shù)幾行數(shù)據(jù)。 使用 FETCH [fetch(7)]，游標(biāo)可以既可以返回文本也可以返回二進(jìn)制格式。

 通常游標(biāo)返回文本格式，和 SELECT 生成的是一樣的。 因?yàn)閿?shù)據(jù)在系統(tǒng)內(nèi)部是用二進(jìn)制格式存儲(chǔ)的， 系統(tǒng)必須對(duì)數(shù)據(jù)做一定轉(zhuǎn)換以生成文本格式。 一旦數(shù)據(jù)是以文本形式返回，那么客戶(hù)端應(yīng)用需要把它們轉(zhuǎn)換成二進(jìn)制進(jìn)行操作。 另外，文本格式一般都比對(duì)應(yīng)的二進(jìn)制格式占的存儲(chǔ)空間大。 二進(jìn)制游標(biāo)給你返回內(nèi)部二進(jìn)制形態(tài)的數(shù)據(jù)。當(dāng)然，如果你想以文本方式顯示數(shù)據(jù)，那么以文本方式檢索會(huì)為你節(jié)約很多客戶(hù)端的工作。

 比如，如果查詢(xún)從一個(gè)整數(shù)列返回一個(gè)一， 在缺省的游標(biāo)里你將獲得一個(gè)字符串 1，而如果是一個(gè)二進(jìn)制游標(biāo)， 你將得到一個(gè) 4-字節(jié)的包含該數(shù)值內(nèi)部形式的數(shù)值（大端序）。

 游標(biāo)應(yīng)該小心使用二進(jìn)制游標(biāo)。一些用戶(hù)應(yīng)用如 psql 是不識(shí)別二進(jìn)制游標(biāo)的， 而且期望返回的數(shù)據(jù)是文本格式。

Note: 注意: 如果客戶(hù)端應(yīng)用使用"擴(kuò)展查詢(xún)"協(xié)議發(fā)出 FETCH 命令， 那么 Bind 協(xié)議聲明數(shù)據(jù)是用文本還是用二進(jìn)制格式檢索。 這個(gè)選擇覆蓋游標(biāo)的定義。因此，在使用擴(kuò)展查詢(xún)協(xié)議的時(shí)候，二進(jìn)制游標(biāo)的概念已經(jīng)過(guò)時(shí)了 － 任何游標(biāo)都可以當(dāng)作文本或者二進(jìn)制的格式發(fā)出。

PARAMETERS 參數(shù)

name

 將在隨后FETCH操作中使用的游標(biāo)名。

BINARY

 令游標(biāo)以二進(jìn)制而不是文本格式獲取數(shù)據(jù)。

INSENSITIVE

 表明從游標(biāo)檢索出來(lái)的數(shù)據(jù)不應(yīng)該被其他進(jìn)程或游標(biāo)的更新動(dòng)作影響。 在 PostgreSQL 里，所有游標(biāo)都是不敏感的，這個(gè)關(guān)鍵字沒(méi)有什么作用，提供它只是為了和 SQL 標(biāo)準(zhǔn)兼容。

SCROLL

NO SCROLL

SCROLL 聲明該游標(biāo)可以用于以非順序的方式檢索數(shù)據(jù)行（也就是向后檢索）。 根據(jù)查詢(xún)的執(zhí)行計(jì)劃的不同，聲明 SCROLL 可能會(huì)對(duì)查詢(xún)的執(zhí)行時(shí)間附加一定的影響。 NO SCROLL 聲明該游標(biāo)不能用于以非順序的方式檢索數(shù)據(jù)行（也就是向后檢索）。

WITH HOLD

WITHOUT HOLD

WITH HOLD 聲明該游標(biāo)可以在創(chuàng)建它的事務(wù)成功提交后繼續(xù)使用。 WITHOUT HOLD 聲明該游標(biāo)不能在創(chuàng)建它的的事務(wù)提交后使用。如果既沒(méi)有聲明 WITHOUT HOLD，也沒(méi)有聲明 WITH HOLD， 那么缺省是 WITH HOLD。

query

 一個(gè)SELECT查詢(xún)，它提供由游標(biāo)返回的行。 請(qǐng)參考 SELECT 語(yǔ)句獲取有關(guān)有效查詢(xún)的詳細(xì)信息。

FOR READ ONLY

FOR UPDATE

FOR READ ONLY 表明游標(biāo)將用于只讀模式。 FOR UPDATE 表明游標(biāo)將被用于更新表。 因?yàn)槟壳?PostgreSQL 不支持游標(biāo)更新， 所以聲明 FOR UPDATE 將產(chǎn)生一個(gè)錯(cuò)誤信息。而聲明 FOR READ ONLY 沒(méi)有作用。

column

 將被更新的列。因?yàn)橛螛?biāo)更新目前不被 PostgreSQL 支持， 所以 FOR UPDATE 子句將產(chǎn)生一個(gè)錯(cuò)誤信息。

BINARY，INSENSITIVE，SCROLL 關(guān)鍵字可以以任何順序出現(xiàn)。

NOTES 注意

 如果沒(méi)有聲明 WITH HOLD，那么這個(gè)命令創(chuàng)建的游標(biāo)只能在當(dāng)前事務(wù)中使用。 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)] 定義一個(gè)事務(wù)塊。

 如果聲明了 WITH HOLD，并且創(chuàng)建該游標(biāo)的事務(wù)成功提交， 那么游標(biāo)還可以在同一會(huì)話(huà)隨后的事務(wù)里訪問(wèn)。（但如果創(chuàng)建它的事務(wù)回滾，那么游標(biāo)被刪除。） 帶著 WITH HOLD 創(chuàng)建的游標(biāo)是用一個(gè)明確的 CLOSE 命令，或者是會(huì)話(huà)終止來(lái)關(guān)閉的。 在目前的實(shí)現(xiàn)里，由一個(gè)游標(biāo)代表的行是被拷貝到一個(gè)臨時(shí)文件或者內(nèi)存區(qū)里的，這樣他們就仍然可以在隨后的事務(wù)中被訪問(wèn)。

 在定義一個(gè)要用來(lái)向后抓取的游標(biāo)的時(shí)候，我們應(yīng)該聲明 SCROLL 選項(xiàng)。 這個(gè)是 SQL 標(biāo)準(zhǔn)要求的。不過(guò)，為了和早期的版本兼容， PostgreSQL 在沒(méi)有 SCROLL 的時(shí)候也允許向后抓取， 只要游標(biāo)的查詢(xún)計(jì)劃簡(jiǎn)單得不需要額外的開(kāi)銷(xiāo)就可以支持它。 不過(guò)，我們建議應(yīng)用開(kāi)發(fā)人員不要依賴(lài)于使用沒(méi)有帶著 SCROLL  定義的游標(biāo)的后向查找功能。如果聲明了 NO SCROLL，那么不管怎樣都會(huì)禁止向后抓取的功能。

 在 SQL 標(biāo)準(zhǔn)中游標(biāo)只能在嵌入 SQL （ESQL） 的應(yīng)用中使用。 PostgreSQL 服務(wù)器沒(méi)有一個(gè)明確的 OPEN  語(yǔ)句；一個(gè)游標(biāo)被認(rèn)為在定義時(shí)就已經(jīng)打開(kāi)了。 不過(guò)，PostgreSQL嵌入的 SQL 預(yù)編譯器， ecpg， 支持 SQL92 習(xí)慣，包括那些和DECLARE和OPEN相關(guān)的語(yǔ)句。  

EXAMPLES 例子

 定義一個(gè)游標(biāo)：

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.