{"Entry":{"collection":"sql","key":"declare","name":"DECLARE","aliases":["declare"],"metadata":{"aliases":["declare"],"changed_in":["7.0","7.4","8.3","14"],"changes":[{"from":"6.5","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":{"added":["DECLARE cursorname [ BINARY ] [ INSENSITIVE ] [ SCROLL ]"],"removed":["DECLARE cursor [ BINARY ] [ INSENSITIVE ] [ SCROLL ]"]},"to":"7.0"},{"from":"7.0","purpose_changed":false,"renamed":{"from_file":"sql-declare.htm","to_file":"sql-declare.html"},"sections":{"added":[],"changed":["description","compatibility"],"removed":[]},"status":"changed","synopsis":null,"to":"7.1"},{"from":"7.1","purpose_changed":true,"renamed":null,"sections":{"added":[],"changed":["description","compatibility"],"removed":[]},"status":"changed","synopsis":null,"to":"7.2"},{"from":"7.3","purpose_changed":false,"renamed":null,"sections":{"added":["parameters","notes","examples"],"changed":["description","compatibility"],"removed":["usage"]},"status":"changed","synopsis":{"added":["DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]","CURSOR [ { WITH | WITHOUT } HOLD ] FOR query","[ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ]"],"removed":["DECLARE cursorname [ BINARY ] [ INSENSITIVE ] [ SCROLL ]"]},"to":"7.4"},{"from":"7.4","purpose_changed":false,"renamed":null,"sections":{"added":["see_also"],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"8.0"},{"from":"8.0","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"8.1"},{"from":"8.1","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"8.2"},{"from":"8.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","notes","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":[],"removed":["[ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ]"]},"to":"8.3"},{"from":"8.3","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"8.4"},{"from":"8.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"9.0"},{"from":"9.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"9.3"},{"from":"9.5","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"9.6"},{"from":"9.6","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"10"},{"from":"10","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"11"},{"from":"11","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"12"},{"from":"12","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"13"},{"from":"13","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","notes","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["DECLARE name [ BINARY ] [ ASENSITIVE | INSENSITIVE ] [ [ NO ] SCROLL ]"],"removed":[]},"to":"14"},{"from":"15","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"16"},{"from":"16","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"17"}],"content_hash":"9866ab278eadba4826eadabae2053ca3b161b5bed77fc7c91944dd0440017f02","editorial":{},"first_version":"6.4","group":"cursor","imported_at":"2026-09-30T17:43:39.177545+08:00","last_version":"20","name":"DECLARE","object":"","position":12002,"present_in":["6.4","6.5","7.0","7.1","7.2","7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6","10","11","12","13","14","15","16","17","18","19","20"],"purpose":"define a cursor","purpose_zh":"","related":["close","fetch","move"],"slug":"declare","source_rev":"a709ab85","synopsis":"DECLARE name [ BINARY ] [ ASENSITIVE | INSENSITIVE ] [ [ NO ] SCROLL ]\nCURSOR [ { WITH | WITHOUT } HOLD ] FOR query","verb":"DECLARE"}},"Definition":{"Collection":"sql","Key":"declare","SourceDatabase":"center","Version":"18","SourceTable":"sqlcmd","SourceKey":"declare","SourceRevision":"a709ab85","Facts":{"anchor":"SQL-DECLARE","file":"sql-declare.html","lang":"en","name":"DECLARE","purpose":"define a cursor","purpose_zh":"","related":["close","fetch","move"],"sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eDECLARE\u003c/code\u003e 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. After the cursor is created, rows are fetched from it using \u003ca href=\"/docs/18/sql-fetch.html\" title=\"FETCH\"\u003e\u003ccode class=\"command\"\u003eFETCH\u003c/code\u003e\u003c/a\u003e.\u003c/p\u003e\u003cdiv class=\"note\"\u003e\u003ch3\u003eNote\u003c/h3\u003e\u003cp\u003eThis page describes usage of cursors at the SQL command level. If you are trying to use cursors inside a \u003cspan class=\"application\"\u003ePL/pgSQL\u003c/span\u003e function, the rules are different — see \u003ca href=\"/docs/18/plpgsql-cursors.html\" title=\"41.7. Cursors\"\u003eSection 41.7\u003c/a\u003e.\u003c/p\u003e\u003c/div\u003e","key":"description","title":"Description"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name of the cursor to be created. This must be different from any other active cursor name in the session.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eBINARY\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eCauses the cursor to return data in binary rather than in text format.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eASENSITIVE\u003c/code\u003e\u003cbr\u003e\u003c/span\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eINSENSITIVE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eCursor sensitivity determines whether changes to the data underlying the cursor, done in the same transaction, after the cursor has been declared, are visible in the cursor. \u003ccode class=\"literal\"\u003eINSENSITIVE\u003c/code\u003e means they are not visible, \u003ccode class=\"literal\"\u003eASENSITIVE\u003c/code\u003e means the behavior is implementation-dependent. A third behavior, \u003ccode class=\"literal\"\u003eSENSITIVE\u003c/code\u003e, meaning that such changes are visible in the cursor, is not available in \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e. In \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e, all cursors are insensitive; so these key words have no effect and are only accepted for compatibility with the SQL standard.\u003c/p\u003e\u003cp\u003eSpecifying \u003ccode class=\"literal\"\u003eINSENSITIVE\u003c/code\u003e together with \u003ccode class=\"literal\"\u003eFOR UPDATE\u003c/code\u003e or \u003ccode class=\"literal\"\u003eFOR SHARE\u003c/code\u003e is an error.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eSCROLL\u003c/code\u003e\u003cbr\u003e\u003c/span\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eNO SCROLL\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003eSCROLL\u003c/code\u003e specifies that the cursor can be used to retrieve rows in a nonsequential fashion (e.g., backward). Depending upon the complexity of the query's execution plan, specifying \u003ccode class=\"literal\"\u003eSCROLL\u003c/code\u003e might impose a performance penalty on the query's execution time. \u003ccode class=\"literal\"\u003eNO SCROLL\u003c/code\u003e specifies that the cursor cannot be used to retrieve rows in a nonsequential fashion. The default is to allow scrolling in some cases; this is not the same as specifying \u003ccode class=\"literal\"\u003eSCROLL\u003c/code\u003e. See \u003ca href=\"/docs/18/sql-declare.html#SQL-DECLARE-NOTES\" title=\"Notes\"\u003eNotes\u003c/a\u003e below for details.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eWITH HOLD\u003c/code\u003e\u003cbr\u003e\u003c/span\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eWITHOUT HOLD\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003eWITH HOLD\u003c/code\u003e specifies that the cursor can continue to be used after the transaction that created it successfully commits. \u003ccode class=\"literal\"\u003eWITHOUT HOLD\u003c/code\u003e specifies that the cursor cannot be used outside of the transaction that created it. If neither \u003ccode class=\"literal\"\u003eWITHOUT HOLD\u003c/code\u003e nor \u003ccode class=\"literal\"\u003eWITH HOLD\u003c/code\u003e is specified, \u003ccode class=\"literal\"\u003eWITHOUT HOLD\u003c/code\u003e is the default.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eA \u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\"\u003e\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e\u003c/a\u003e or \u003ca href=\"/docs/18/sql-values.html\" title=\"VALUES\"\u003e\u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e\u003c/a\u003e command which will provide the rows to be returned by the cursor.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cp\u003eThe key words \u003ccode class=\"literal\"\u003eASENSITIVE\u003c/code\u003e, \u003ccode class=\"literal\"\u003eBINARY\u003c/code\u003e, \u003ccode class=\"literal\"\u003eINSENSITIVE\u003c/code\u003e, and \u003ccode class=\"literal\"\u003eSCROLL\u003c/code\u003e can appear in any order.\u003c/p\u003e","key":"parameters","title":"Parameters"},{"html":"\u003cp\u003eNormal cursors return data in text format, the same as a \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e would produce. The \u003ccode class=\"literal\"\u003eBINARY\u003c/code\u003e option specifies that the cursor should return data in binary format. This reduces conversion effort for both the server and client, at the cost of more programmer effort to deal with platform-dependent binary data formats. As an example, if a query returns a value of one from an integer column, you would get a string of \u003ccode class=\"literal\"\u003e1\u003c/code\u003e 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).\u003c/p\u003e\u003cp\u003eBinary cursors should be used carefully. Many applications, including \u003cspan class=\"application\"\u003epsql\u003c/span\u003e, are not prepared to handle binary cursors and expect data to come back in the text format.\u003c/p\u003e\u003cdiv class=\"note\"\u003e\u003ch3\u003eNote\u003c/h3\u003e\u003cp\u003eWhen the client application uses the \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003eextended query\u003c/span\u003e”\u003c/span\u003e protocol to issue a \u003ccode class=\"command\"\u003eFETCH\u003c/code\u003e 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.\u003c/p\u003e\u003c/div\u003e\u003cp\u003eUnless \u003ccode class=\"literal\"\u003eWITH HOLD\u003c/code\u003e is specified, the cursor created by this command can only be used within the current transaction. Thus, \u003ccode class=\"command\"\u003eDECLARE\u003c/code\u003e without \u003ccode class=\"literal\"\u003eWITH HOLD\u003c/code\u003e is useless outside a transaction block: the cursor would survive only to the completion of the statement. Therefore \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e reports an error if such a command is used outside a transaction block. Use \u003ca href=\"/docs/18/sql-begin.html\" title=\"BEGIN\"\u003e\u003ccode class=\"command\"\u003eBEGIN\u003c/code\u003e\u003c/a\u003e and \u003ca href=\"/docs/18/sql-commit.html\" title=\"COMMIT\"\u003e\u003ccode class=\"command\"\u003eCOMMIT\u003c/code\u003e\u003c/a\u003e (or \u003ca href=\"/docs/18/sql-rollback.html\" title=\"ROLLBACK\"\u003e\u003ccode class=\"command\"\u003eROLLBACK\u003c/code\u003e\u003c/a\u003e) to define a transaction block.\u003c/p\u003e\u003cp\u003eIf \u003ccode class=\"literal\"\u003eWITH HOLD\u003c/code\u003e 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 \u003ccode class=\"literal\"\u003eWITH HOLD\u003c/code\u003e is closed when an explicit \u003ccode class=\"command\"\u003eCLOSE\u003c/code\u003e 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.\u003c/p\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003eWITH HOLD\u003c/code\u003e may not be specified when the query includes \u003ccode class=\"literal\"\u003eFOR UPDATE\u003c/code\u003e or \u003ccode class=\"literal\"\u003eFOR SHARE\u003c/code\u003e.\u003c/p\u003e\u003cp\u003eThe \u003ccode class=\"literal\"\u003eSCROLL\u003c/code\u003e 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, \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e will allow backward fetches without \u003ccode class=\"literal\"\u003eSCROLL\u003c/code\u003e, 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 \u003ccode class=\"literal\"\u003eSCROLL\u003c/code\u003e. If \u003ccode class=\"literal\"\u003eNO SCROLL\u003c/code\u003e is specified, then backward fetches are disallowed in any case.\u003c/p\u003e\u003cp\u003eBackward fetches are also disallowed when the query includes \u003ccode class=\"literal\"\u003eFOR UPDATE\u003c/code\u003e or \u003ccode class=\"literal\"\u003eFOR SHARE\u003c/code\u003e; therefore \u003ccode class=\"literal\"\u003eSCROLL\u003c/code\u003e may not be specified in this case.\u003c/p\u003e\u003cdiv class=\"caution\"\u003e\u003ch3\u003eCaution\u003c/h3\u003e\u003cp\u003eScrollable cursors may give unexpected results if they invoke any volatile functions (see \u003ca href=\"/docs/18/xfunc-volatility.html\" title=\"36.7. Function Volatility Categories\"\u003eSection 36.7\u003c/a\u003e). When a previously fetched row is re-fetched, the functions might be re-executed, perhaps leading to results different from the first time. It's best to specify \u003ccode class=\"literal\"\u003eNO SCROLL\u003c/code\u003e for a query involving volatile functions. If that is not practical, one workaround is to declare the cursor \u003ccode class=\"literal\"\u003eSCROLL WITH HOLD\u003c/code\u003e and commit the transaction before reading any rows from it. This will force the entire output of the cursor to be materialized in temporary storage, so that volatile functions are executed exactly once for each row.\u003c/p\u003e\u003c/div\u003e\u003cp\u003eIf the cursor's query includes \u003ccode class=\"literal\"\u003eFOR UPDATE\u003c/code\u003e or \u003ccode class=\"literal\"\u003eFOR SHARE\u003c/code\u003e, then returned rows are locked at the time they are first fetched, in the same way as for a regular \u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\"\u003e\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e\u003c/a\u003e command with these options. In addition, the returned rows will be the most up-to-date versions.\u003c/p\u003e\u003cdiv class=\"caution\"\u003e\u003ch3\u003eCaution\u003c/h3\u003e\u003cp\u003eIt is generally recommended to use \u003ccode class=\"literal\"\u003eFOR UPDATE\u003c/code\u003e if the cursor is intended to be used with \u003ccode class=\"command\"\u003eUPDATE ... WHERE CURRENT OF\u003c/code\u003e or \u003ccode class=\"command\"\u003eDELETE ... WHERE CURRENT OF\u003c/code\u003e. Using \u003ccode class=\"literal\"\u003eFOR UPDATE\u003c/code\u003e prevents other sessions from changing the rows between the time they are fetched and the time they are updated. Without \u003ccode class=\"literal\"\u003eFOR UPDATE\u003c/code\u003e, a subsequent \u003ccode class=\"literal\"\u003eWHERE CURRENT OF\u003c/code\u003e command will have no effect if the row was changed since the cursor was created.\u003c/p\u003e\u003cp\u003eAnother reason to use \u003ccode class=\"literal\"\u003eFOR UPDATE\u003c/code\u003e is that without it, a subsequent \u003ccode class=\"literal\"\u003eWHERE CURRENT OF\u003c/code\u003e might fail if the cursor query does not meet the SQL standard's rules for being \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003esimply updatable\u003c/span\u003e”\u003c/span\u003e (in particular, the cursor must reference just one table and not use grouping or \u003ccode class=\"literal\"\u003eORDER BY\u003c/code\u003e). Cursors that are not simply updatable might work, or might not, depending on plan choice details; so in the worst case, an application might work in testing and then fail in production. If \u003ccode class=\"literal\"\u003eFOR UPDATE\u003c/code\u003e is specified, the cursor is guaranteed to be updatable.\u003c/p\u003e\u003cp\u003eThe main reason not to use \u003ccode class=\"literal\"\u003eFOR UPDATE\u003c/code\u003e with \u003ccode class=\"literal\"\u003eWHERE CURRENT OF\u003c/code\u003e is if you need the cursor to be scrollable, or to be isolated from concurrent updates (that is, continue to show the old data). If this is a requirement, pay close heed to the caveats shown above.\u003c/p\u003e\u003c/div\u003e\u003cp\u003eThe SQL standard only makes provisions for cursors in embedded \u003cacronym\u003eSQL\u003c/acronym\u003e. The \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e server does not implement an \u003ccode class=\"command\"\u003eOPEN\u003c/code\u003e statement for cursors; a cursor is considered to be open when it is declared. However, \u003cspan class=\"application\"\u003eECPG\u003c/span\u003e, the embedded SQL preprocessor for \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e, supports the standard SQL cursor conventions, including those involving \u003ccode class=\"command\"\u003eDECLARE\u003c/code\u003e and \u003ccode class=\"command\"\u003eOPEN\u003c/code\u003e statements.\u003c/p\u003e\u003cp\u003eThe server data structure underlying an open cursor is called a \u003cem class=\"firstterm\"\u003eportal\u003c/em\u003e. Portal names are exposed in the client protocol: a client can fetch rows directly from an open portal, if it knows the portal name. When creating a cursor with \u003ccode class=\"command\"\u003eDECLARE\u003c/code\u003e, the portal name is the same as the cursor name.\u003c/p\u003e\u003cp\u003eYou can see all available cursors by querying the \u003ca href=\"/docs/18/view-pg-cursors.html\" title=\"53.7. pg_cursors\"\u003e\u003ccode class=\"structname\"\u003epg_cursors\u003c/code\u003e\u003c/a\u003e system view.\u003c/p\u003e","key":"notes","title":"Notes"},{"html":"\u003cp\u003eTo declare a cursor:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eDECLARE liahona CURSOR FOR SELECT * FROM films;\n\u003c/pre\u003e\u003cp\u003eSee \u003ca href=\"/docs/18/sql-fetch.html\" title=\"FETCH\"\u003e\u003cspan class=\"refentrytitle\"\u003eFETCH\u003c/span\u003e\u003c/a\u003e for more examples of cursor usage.\u003c/p\u003e","key":"examples","title":"Examples"},{"html":"\u003cp\u003eThe SQL standard allows cursors only in embedded \u003cacronym\u003eSQL\u003c/acronym\u003e and in modules. \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e permits cursors to be used interactively.\u003c/p\u003e\u003cp\u003eAccording to the SQL standard, changes made to insensitive cursors by \u003ccode class=\"literal\"\u003eUPDATE ... WHERE CURRENT OF\u003c/code\u003e and \u003ccode class=\"literal\"\u003eDELETE ... WHERE CURRENT OF\u003c/code\u003e statements are visible in that same cursor. \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e treats these statements like all other data changing statements in that they are not visible in insensitive cursors.\u003c/p\u003e\u003cp\u003eBinary cursors are a \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e extension.\u003c/p\u003e","key":"compatibility","title":"Compatibility"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/close/?v=18\" title=\"CLOSE\"\u003e\u003cspan class=\"refentrytitle\"\u003eCLOSE\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/fetch/?v=18\" title=\"FETCH\"\u003e\u003cspan class=\"refentrytitle\"\u003eFETCH\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/move/?v=18\" title=\"MOVE\"\u003e\u003cspan class=\"refentrytitle\"\u003eMOVE\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"See Also"}],"sections_same_as":"","slug":"18","synopsis_html":"DECLARE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e [ BINARY ] [ ASENSITIVE | INSENSITIVE ] [ [ NO ] SCROLL ]\n    CURSOR [ { WITH | WITHOUT } HOLD ] FOR \u003cem class=\"replaceable\"\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e","synopsis_text":"DECLARE name [ BINARY ] [ ASENSITIVE | INSENSITIVE ] [ [ NO ] SCROLL ]\nCURSOR [ { WITH | WITHOUT } HOLD ] FOR query"},"ManualEvidence":{},"MeasuredEvidence":{}},"Text":{"Collection":"sql","Key":"declare","SourceDatabase":"pgweb","Version":"18","Locale":"zh-Hans","Title":"DECLARE","Summary":"定义一个游标","BodyHTML":"\u003cpre\u003eDECLARE name [ BINARY ] [ ASENSITIVE | INSENSITIVE ] [ [ NO ] SCROLL ]\nCURSOR [ { WITH | WITHOUT } HOLD ] FOR query\u003c/pre\u003e\u003csection\u003e\u003ch2\u003e描述\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eDECLARE\u003c/code\u003e允许用户创建游标，游标可用于从较大的查询中一次取出少量行。游标创建后，可使用\u003ca href=\"/docs/18/sql-fetch.html\" title=\"FETCH\" rel=\"nofollow\"\u003e\u003ccode\u003eFETCH\u003c/code\u003e\u003c/a\u003e从中取出行。\u003c/p\u003e\u003cdiv\u003e\u003ch3\u003e注意\u003c/h3\u003e\u003cp\u003e本页面描述的是 SQL 命令层面的游标用法。如果想要在 \u003cspan\u003ePL/pgSQL\u003c/span\u003e函数中使用游标，规则会有所不同 — 见\u003ca href=\"/docs/18/plpgsql-cursors.html\" rel=\"nofollow\"\u003e第 41.7 节\u003c/a\u003e。\u003c/p\u003e\u003c/div\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e参数\u003c/h2\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要创建的游标名称。它必须与该会话中任何其他活动游标的名称不同。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eBINARY\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e使游标以二进制格式而不是文本格式返回数据。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eASENSITIVE\u003c/code\u003e\u003cbr\u003e\u003c/span\u003e\u003cspan\u003e\u003ccode\u003eINSENSITIVE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e游标的敏感性决定了在声明游标之后、于同一事务中对其底层数据所做的更改是否会在游标中可见。\u003ccode\u003eINSENSITIVE\u003c/code\u003e表示这些更改不可见，\u003ccode\u003eASENSITIVE\u003c/code\u003e表示这种行为取决于具体实现。第三种行为是\u003ccode\u003eSENSITIVE\u003c/code\u003e，表示这类更改在游标中可见，而\u003cspan\u003ePostgreSQL\u003c/span\u003e不支持这种行为。在\u003cspan\u003ePostgreSQL\u003c/span\u003e中，所有游标都是不敏感的，因此这些关键字不起作用，只是为兼容 SQL 标准而接受。\u003c/p\u003e\u003cp\u003e将\u003ccode\u003eINSENSITIVE\u003c/code\u003e与\u003ccode\u003eFOR UPDATE\u003c/code\u003e或\u003ccode\u003eFOR SHARE\u003c/code\u003e一起指定会报错。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eSCROLL\u003c/code\u003e\u003cbr\u003e\u003c/span\u003e\u003cspan\u003e\u003ccode\u003eNO SCROLL\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003ccode\u003eSCROLL\u003c/code\u003e指定游标可用于以非顺序方式（例如向后）取出行。根据查询执行计划的复杂程度，指定\u003ccode\u003eSCROLL\u003c/code\u003e可能会给查询执行带来性能开销。\u003ccode\u003eNO SCROLL\u003c/code\u003e指定游标不能以非顺序方式取出行。默认情况下只在某些情形下允许滚动，这与显式指定 \u003ccode\u003eSCROLL\u003c/code\u003e并不相同。详见下文\u003ca href=\"/docs/18/sql-declare.html#SQL-DECLARE-NOTES\" title=\"注解\" rel=\"nofollow\"\u003e注解\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eWITH HOLD\u003c/code\u003e\u003cbr\u003e\u003c/span\u003e\u003cspan\u003e\u003ccode\u003eWITHOUT HOLD\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003ccode\u003eWITH HOLD\u003c/code\u003e指定在创建该游标的事务成功提交后，仍可继续使用该游标。\u003ccode\u003eWITHOUT HOLD\u003c/code\u003e指定该游标不能在创建它的事务之外使用。如果既未指定\u003ccode\u003eWITHOUT HOLD\u003c/code\u003e也未指定\u003ccode\u003eWITH HOLD\u003c/code\u003e，默认值是\u003ccode\u003eWITHOUT HOLD\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e用于为该游标提供其要返回的行的\u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\" rel=\"nofollow\"\u003e\u003ccode\u003eSELECT\u003c/code\u003e\u003c/a\u003e或\u003ca href=\"/docs/18/sql-values.html\" title=\"VALUES\" rel=\"nofollow\"\u003e\u003ccode\u003eVALUES\u003c/code\u003e\u003c/a\u003e命令。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cp\u003e关键字\u003ccode\u003eASENSITIVE\u003c/code\u003e、\u003ccode\u003eBINARY\u003c/code\u003e、\u003ccode\u003eINSENSITIVE\u003c/code\u003e和\u003ccode\u003eSCROLL\u003c/code\u003e可以按任意顺序出现。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e注解\u003c/h2\u003e\u003cp\u003e普通游标以文本格式返回数据，就像\u003ccode\u003eSELECT\u003c/code\u003e产生的结果一样。\u003ccode\u003eBINARY\u003c/code\u003e选项指定游标应以二进制格式返回数据。这减少了服务器和客户端两端的转换工作量，但代价是程序员需要付出更多精力来处理与平台相关的二进制数据格式。举例来说，如果某个查询从一个整型列返回值 1，那么默认游标会返回字符串 \u003ccode\u003e1\u003c/code\u003e，而二进制游标则会返回一个 4 字节字段，其中包含该值的内部表示（采用大端字节序）。\u003c/p\u003e\u003cp\u003e应谨慎使用二进制游标。许多应用程序（包括 \u003cspan\u003epsql\u003c/span\u003e）都没有准备好处理二进制游标，而是期望返回的数据为文本格式。\u003c/p\u003e\u003cdiv\u003e\u003ch3\u003e注意\u003c/h3\u003e\u003cp\u003e当客户端应用使用\u003cspan\u003e“\u003cspan\u003e扩展查询\u003c/span\u003e”\u003c/span\u003e协议发出\u003ccode\u003eFETCH\u003c/code\u003e命令时，Bind 协议消息会指定数据应以文本格式还是二进制格式提取。这一选择会覆盖定义游标时所指定的方式。因此，在使用扩展查询协议时，二进制游标这一概念实际上已经过时了 — 任何游标都可以按文本或二进制方式处理。\u003c/p\u003e\u003c/div\u003e\u003cp\u003e除非指定了\u003ccode\u003eWITH HOLD\u003c/code\u003e，否则该命令创建的游标只能在当前事务中使用。因此，\u003ccode\u003eDECLARE\u003c/code\u003e若不带\u003ccode\u003eWITH HOLD\u003c/code\u003e，在事务块之外就毫无用处：游标只能存活到该语句执行完成。所以，如果在事务块之外使用这种命令，\u003cspan\u003ePostgreSQL\u003c/span\u003e会报错。可使用\u003ca href=\"/docs/18/sql-begin.html\" title=\"BEGIN\" rel=\"nofollow\"\u003e\u003ccode\u003eBEGIN\u003c/code\u003e\u003c/a\u003e和\u003ca href=\"/docs/18/sql-commit.html\" title=\"COMMIT\" rel=\"nofollow\"\u003e\u003ccode\u003eCOMMIT\u003c/code\u003e\u003c/a\u003e（或\u003ca href=\"/docs/18/sql-rollback.html\" title=\"ROLLBACK\" rel=\"nofollow\"\u003e\u003ccode\u003eROLLBACK\u003c/code\u003e\u003c/a\u003e）来定义事务块。\u003c/p\u003e\u003cp\u003e如果指定了\u003ccode\u003eWITH HOLD\u003c/code\u003e，且创建游标的事务成功提交，那么在同一会话中的后续事务里仍可继续访问该游标。（但如果创建事务被中止，游标会被移除。）使用\u003ccode\u003eWITH HOLD\u003c/code\u003e创建的游标会在对其发出显式\u003ccode\u003eCLOSE\u003c/code\u003e命令时关闭，或在会话结束时关闭。在当前实现中，这种游标所表示的行会被复制到临时文件或内存区域中，以便它们在后续事务中仍然可用。\u003c/p\u003e\u003cp\u003e当查询包括\u003ccode\u003eFOR UPDATE\u003c/code\u003e或\u003ccode\u003eFOR SHARE\u003c/code\u003e时，不能指定\u003ccode\u003eWITH HOLD\u003c/code\u003e。\u003c/p\u003e\u003cp\u003e在定义将用于向后取出行的游标时，应指定\u003ccode\u003eSCROLL\u003c/code\u003e选项。这是 SQL 标准所要求的。不过，为了兼容早期版本，如果游标的查询计划足够简单，以致支持向后取出不需要额外开销，\u003cspan\u003ePostgreSQL\u003c/span\u003e也允许在未指定\u003ccode\u003eSCROLL\u003c/code\u003e的情况下向后取出行。不过，建议应用开发者不要依赖于从未用\u003ccode\u003eSCROLL\u003c/code\u003e创建的游标中向后取出行。如果指定了\u003ccode\u003eNO SCROLL\u003c/code\u003e，那么无论如何都不允许向后取出行。\u003c/p\u003e\u003cp\u003e当查询包含\u003ccode\u003eFOR UPDATE\u003c/code\u003e或\u003ccode\u003eFOR SHARE\u003c/code\u003e时，同样不允许向后取出行。因此在这种情况下不能指定\u003ccode\u003eSCROLL\u003c/code\u003e。\u003c/p\u003e\u003cdiv\u003e\u003ch3\u003e小心\u003c/h3\u003e\u003cp\u003e如果可滚动游标调用了任何易变函数（见\u003ca href=\"/docs/18/xfunc-volatility.html\" rel=\"nofollow\"\u003e第 36.7 节\u003c/a\u003e），则可能得到意外结果。当重新取出先前已经取出过的行时，这些函数可能会再次执行，从而导致与第一次不同的结果。对于涉及易变函数的查询，最好指定\u003ccode\u003eNO SCROLL\u003c/code\u003e。如果这不现实，一个变通办法是将游标声明为\u003ccode\u003eSCROLL WITH HOLD\u003c/code\u003e，并在读取其中任何行之前提交事务。这样会强制将游标的整个输出物化到临时存储中，从而使每一行上的易变函数都只执行一次。\u003c/p\u003e\u003c/div\u003e\u003cp\u003e如果游标的查询包含\u003ccode\u003eFOR UPDATE\u003c/code\u003e或\u003ccode\u003eFOR SHARE\u003c/code\u003e，那么返回的行会像带有这些选项的常规\u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\" rel=\"nofollow\"\u003e\u003ccode\u003eSELECT\u003c/code\u003e\u003c/a\u003e命令那样，在首次取出时被锁定。此外，返回的将是这些行的最新版本。\u003c/p\u003e\u003cdiv\u003e\u003ch3\u003e小心\u003c/h3\u003e\u003cp\u003e通常都建议使用\u003ccode\u003eFOR UPDATE\u003c/code\u003e，如果游标打算与 \u003ccode\u003eUPDATE ... WHERE CURRENT OF\u003c/code\u003e或 \u003ccode\u003eDELETE ... WHERE CURRENT OF\u003c/code\u003e一起使用。使用\u003ccode\u003eFOR UPDATE\u003c/code\u003e可以防止其他会话在这些行被取出之后、被更新之前更改它们。如果不使用\u003ccode\u003eFOR UPDATE\u003c/code\u003e，而某一行在游标创建后已经被更改，那么后续的\u003ccode\u003eWHERE CURRENT OF\u003c/code\u003e命令将不会有任何效果。\u003c/p\u003e\u003cp\u003e使用\u003ccode\u003eFOR UPDATE\u003c/code\u003e的另一个原因是：如果没有它，而后续的\u003ccode\u003eWHERE CURRENT OF\u003c/code\u003e 所针对的游标查询不符合 SQL 标准关于\u003cspan\u003e“\u003cspan\u003e简单可更新\u003c/span\u003e”\u003c/span\u003e的规则，则该命令可能失败。（特别是，游标必须只引用一个表，且不能使用分组或\u003ccode\u003eORDER BY\u003c/code\u003e）。对于并非简单可更新的游标，是否可用取决于计划选择的细节，可能能工作，也可能不能。因此在最坏情况下，应用可能在测试中可用，却会在生产中失败。如果指定了\u003ccode\u003eFOR UPDATE\u003c/code\u003e，则可保证该游标可更新。\u003c/p\u003e\u003cp\u003e不将\u003ccode\u003eFOR UPDATE\u003c/code\u003e与\u003ccode\u003eWHERE CURRENT OF\u003c/code\u003e一起使用的主要原因，是你需要游标可滚动，或者需要它与并发更新隔离（也就是说，继续显示旧数据）。如果这是需求，请务必仔细注意上面的警告。\u003c/p\u003e\u003c/div\u003e\u003cp\u003eSQL 标准只为嵌入式\u003cacronym\u003eSQL\u003c/acronym\u003e中的游标作出规定。\u003cspan\u003ePostgreSQL\u003c/span\u003e服务器没有为游标实现\u003ccode\u003eOPEN\u003c/code\u003e语句；游标在声明时即被视为打开。不过，\u003cspan\u003eECPG\u003c/span\u003e是\u003cspan\u003ePostgreSQL\u003c/span\u003e的嵌入式 SQL 预处理器，它支持标准 SQL 的游标约定，包括涉及\u003ccode\u003eDECLARE\u003c/code\u003e和\u003ccode\u003eOPEN\u003c/code\u003e语句的那些约定。\u003c/p\u003e\u003cp\u003e打开的游标在服务器端的底层数据结构称为\u003cem\u003eportal\u003c/em\u003e。客户端协议会暴露 portal 名称：只要知道该名称，客户端就可以直接从一个打开的 portal 中取出行。使用\u003ccode\u003eDECLARE\u003c/code\u003e创建游标时，portal 名称与游标名称相同。\u003c/p\u003e\u003cp\u003e可以通过查询\u003ca href=\"/docs/18/view-pg-cursors.html\" rel=\"nofollow\"\u003e\u003ccode\u003epg_cursors\u003c/code\u003e\u003c/a\u003e 系统视图查看所有可用游标。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e示例\u003c/h2\u003e\u003cp\u003e声明一个游标：\u003c/p\u003e\u003cpre\u003eDECLARE liahona CURSOR FOR SELECT * FROM films;\n\u003c/pre\u003e\u003cp\u003e更多游标用法示例见\u003ca href=\"/docs/18/sql-fetch.html\" title=\"FETCH\" rel=\"nofollow\"\u003e\u003cspan\u003eFETCH\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e兼容性\u003c/h2\u003e\u003cp\u003eSQL 标准只允许在嵌入式\u003cacronym\u003eSQL\u003c/acronym\u003e和模块中使用游标。\u003cspan\u003ePostgreSQL\u003c/span\u003e允许以交互方式使用游标。\u003c/p\u003e\u003cp\u003e根据 SQL 标准，通过\u003ccode\u003eUPDATE ... WHERE CURRENT OF\u003c/code\u003e和\u003ccode\u003eDELETE ... WHERE CURRENT OF\u003c/code\u003e语句对不敏感游标所做的更改，在同一个游标中是可见的。\u003cspan\u003ePostgreSQL\u003c/span\u003e将这些语句与所有其他更改数据的语句同等对待，因此这些更改在不敏感游标中不可见。\u003c/p\u003e\u003cp\u003e二进制游标是\u003cspan\u003ePostgreSQL\u003c/span\u003e的一种扩展。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e另见\u003c/h2\u003e\u003cspan\u003e\u003ca href=\"/wiki/sql/close/?v=18\" title=\"CLOSE\" rel=\"nofollow\"\u003e\u003cspan\u003eCLOSE\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/fetch/?v=18\" title=\"FETCH\" rel=\"nofollow\"\u003e\u003cspan\u003eFETCH\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/move/?v=18\" title=\"MOVE\" rel=\"nofollow\"\u003e\u003cspan\u003eMOVE\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e\u003c/section\u003e","SourceRevision":"1b5ca64c","ContentHash":"0a0be0a421134ef63599f673da036b8780d6150d1dcaada0710df6fe91714d0c","Payload":{"purpose_zh":"定义一个游标","sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eDECLARE\u003c/code\u003e允许用户创建游标，游标可用于从较大的查询中一次取出少量行。游标创建后，可使用\u003ca href=\"/docs/18/sql-fetch.html\" title=\"FETCH\"\u003e\u003ccode class=\"command\"\u003eFETCH\u003c/code\u003e\u003c/a\u003e从中取出行。\u003c/p\u003e\u003cdiv class=\"note\"\u003e\u003ch3\u003e注意\u003c/h3\u003e\u003cp\u003e本页面描述的是 SQL 命令层面的游标用法。如果想要在 \u003cspan class=\"application\"\u003ePL/pgSQL\u003c/span\u003e函数中使用游标，规则会有所不同 — 见\u003ca href=\"/docs/18/plpgsql-cursors.html\" title=\"41.7. 游标\"\u003e第 41.7 节\u003c/a\u003e。\u003c/p\u003e\u003c/div\u003e","key":"description","title":"描述"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要创建的游标名称。它必须与该会话中任何其他活动游标的名称不同。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eBINARY\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e使游标以二进制格式而不是文本格式返回数据。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eASENSITIVE\u003c/code\u003e\u003cbr\u003e\u003c/span\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eINSENSITIVE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e游标的敏感性决定了在声明游标之后、于同一事务中对其底层数据所做的更改是否会在游标中可见。\u003ccode class=\"literal\"\u003eINSENSITIVE\u003c/code\u003e表示这些更改不可见，\u003ccode class=\"literal\"\u003eASENSITIVE\u003c/code\u003e表示这种行为取决于具体实现。第三种行为是\u003ccode class=\"literal\"\u003eSENSITIVE\u003c/code\u003e，表示这类更改在游标中可见，而\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e不支持这种行为。在\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e中，所有游标都是不敏感的，因此这些关键字不起作用，只是为兼容 SQL 标准而接受。\u003c/p\u003e\u003cp\u003e将\u003ccode class=\"literal\"\u003eINSENSITIVE\u003c/code\u003e与\u003ccode class=\"literal\"\u003eFOR UPDATE\u003c/code\u003e或\u003ccode class=\"literal\"\u003eFOR SHARE\u003c/code\u003e一起指定会报错。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eSCROLL\u003c/code\u003e\u003cbr\u003e\u003c/span\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eNO SCROLL\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003eSCROLL\u003c/code\u003e指定游标可用于以非顺序方式（例如向后）取出行。根据查询执行计划的复杂程度，指定\u003ccode class=\"literal\"\u003eSCROLL\u003c/code\u003e可能会给查询执行带来性能开销。\u003ccode class=\"literal\"\u003eNO SCROLL\u003c/code\u003e指定游标不能以非顺序方式取出行。默认情况下只在某些情形下允许滚动，这与显式指定 \u003ccode class=\"literal\"\u003eSCROLL\u003c/code\u003e并不相同。详见下文\u003ca href=\"/docs/18/sql-declare.html#SQL-DECLARE-NOTES\" title=\"注解\"\u003e注解\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eWITH HOLD\u003c/code\u003e\u003cbr\u003e\u003c/span\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eWITHOUT HOLD\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003eWITH HOLD\u003c/code\u003e指定在创建该游标的事务成功提交后，仍可继续使用该游标。\u003ccode class=\"literal\"\u003eWITHOUT HOLD\u003c/code\u003e指定该游标不能在创建它的事务之外使用。如果既未指定\u003ccode class=\"literal\"\u003eWITHOUT HOLD\u003c/code\u003e也未指定\u003ccode class=\"literal\"\u003eWITH HOLD\u003c/code\u003e，默认值是\u003ccode class=\"literal\"\u003eWITHOUT HOLD\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e用于为该游标提供其要返回的行的\u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\"\u003e\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e\u003c/a\u003e或\u003ca href=\"/docs/18/sql-values.html\" title=\"VALUES\"\u003e\u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e\u003c/a\u003e命令。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cp\u003e关键字\u003ccode class=\"literal\"\u003eASENSITIVE\u003c/code\u003e、\u003ccode class=\"literal\"\u003eBINARY\u003c/code\u003e、\u003ccode class=\"literal\"\u003eINSENSITIVE\u003c/code\u003e和\u003ccode class=\"literal\"\u003eSCROLL\u003c/code\u003e可以按任意顺序出现。\u003c/p\u003e","key":"parameters","title":"参数"},{"html":"\u003cp\u003e普通游标以文本格式返回数据，就像\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e产生的结果一样。\u003ccode class=\"literal\"\u003eBINARY\u003c/code\u003e选项指定游标应以二进制格式返回数据。这减少了服务器和客户端两端的转换工作量，但代价是程序员需要付出更多精力来处理与平台相关的二进制数据格式。举例来说，如果某个查询从一个整型列返回值 1，那么默认游标会返回字符串 \u003ccode class=\"literal\"\u003e1\u003c/code\u003e，而二进制游标则会返回一个 4 字节字段，其中包含该值的内部表示（采用大端字节序）。\u003c/p\u003e\u003cp\u003e应谨慎使用二进制游标。许多应用程序（包括 \u003cspan class=\"application\"\u003epsql\u003c/span\u003e）都没有准备好处理二进制游标，而是期望返回的数据为文本格式。\u003c/p\u003e\u003cdiv class=\"note\"\u003e\u003ch3\u003e注意\u003c/h3\u003e\u003cp\u003e当客户端应用使用\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e扩展查询\u003c/span\u003e”\u003c/span\u003e协议发出\u003ccode class=\"command\"\u003eFETCH\u003c/code\u003e命令时，Bind 协议消息会指定数据应以文本格式还是二进制格式提取。这一选择会覆盖定义游标时所指定的方式。因此，在使用扩展查询协议时，二进制游标这一概念实际上已经过时了 — 任何游标都可以按文本或二进制方式处理。\u003c/p\u003e\u003c/div\u003e\u003cp\u003e除非指定了\u003ccode class=\"literal\"\u003eWITH HOLD\u003c/code\u003e，否则该命令创建的游标只能在当前事务中使用。因此，\u003ccode class=\"command\"\u003eDECLARE\u003c/code\u003e若不带\u003ccode class=\"literal\"\u003eWITH HOLD\u003c/code\u003e，在事务块之外就毫无用处：游标只能存活到该语句执行完成。所以，如果在事务块之外使用这种命令，\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e会报错。可使用\u003ca href=\"/docs/18/sql-begin.html\" title=\"BEGIN\"\u003e\u003ccode class=\"command\"\u003eBEGIN\u003c/code\u003e\u003c/a\u003e和\u003ca href=\"/docs/18/sql-commit.html\" title=\"COMMIT\"\u003e\u003ccode class=\"command\"\u003eCOMMIT\u003c/code\u003e\u003c/a\u003e（或\u003ca href=\"/docs/18/sql-rollback.html\" title=\"ROLLBACK\"\u003e\u003ccode class=\"command\"\u003eROLLBACK\u003c/code\u003e\u003c/a\u003e）来定义事务块。\u003c/p\u003e\u003cp\u003e如果指定了\u003ccode class=\"literal\"\u003eWITH HOLD\u003c/code\u003e，且创建游标的事务成功提交，那么在同一会话中的后续事务里仍可继续访问该游标。（但如果创建事务被中止，游标会被移除。）使用\u003ccode class=\"literal\"\u003eWITH HOLD\u003c/code\u003e创建的游标会在对其发出显式\u003ccode class=\"command\"\u003eCLOSE\u003c/code\u003e命令时关闭，或在会话结束时关闭。在当前实现中，这种游标所表示的行会被复制到临时文件或内存区域中，以便它们在后续事务中仍然可用。\u003c/p\u003e\u003cp\u003e当查询包括\u003ccode class=\"literal\"\u003eFOR UPDATE\u003c/code\u003e或\u003ccode class=\"literal\"\u003eFOR SHARE\u003c/code\u003e时，不能指定\u003ccode class=\"literal\"\u003eWITH HOLD\u003c/code\u003e。\u003c/p\u003e\u003cp\u003e在定义将用于向后取出行的游标时，应指定\u003ccode class=\"literal\"\u003eSCROLL\u003c/code\u003e选项。这是 SQL 标准所要求的。不过，为了兼容早期版本，如果游标的查询计划足够简单，以致支持向后取出不需要额外开销，\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e也允许在未指定\u003ccode class=\"literal\"\u003eSCROLL\u003c/code\u003e的情况下向后取出行。不过，建议应用开发者不要依赖于从未用\u003ccode class=\"literal\"\u003eSCROLL\u003c/code\u003e创建的游标中向后取出行。如果指定了\u003ccode class=\"literal\"\u003eNO SCROLL\u003c/code\u003e，那么无论如何都不允许向后取出行。\u003c/p\u003e\u003cp\u003e当查询包含\u003ccode class=\"literal\"\u003eFOR UPDATE\u003c/code\u003e或\u003ccode class=\"literal\"\u003eFOR SHARE\u003c/code\u003e时，同样不允许向后取出行。因此在这种情况下不能指定\u003ccode class=\"literal\"\u003eSCROLL\u003c/code\u003e。\u003c/p\u003e\u003cdiv class=\"caution\"\u003e\u003ch3\u003e小心\u003c/h3\u003e\u003cp\u003e如果可滚动游标调用了任何易变函数（见\u003ca href=\"/docs/18/xfunc-volatility.html\" title=\"36.7. 函数易变性分类\"\u003e第 36.7 节\u003c/a\u003e），则可能得到意外结果。当重新取出先前已经取出过的行时，这些函数可能会再次执行，从而导致与第一次不同的结果。对于涉及易变函数的查询，最好指定\u003ccode class=\"literal\"\u003eNO SCROLL\u003c/code\u003e。如果这不现实，一个变通办法是将游标声明为\u003ccode class=\"literal\"\u003eSCROLL WITH HOLD\u003c/code\u003e，并在读取其中任何行之前提交事务。这样会强制将游标的整个输出物化到临时存储中，从而使每一行上的易变函数都只执行一次。\u003c/p\u003e\u003c/div\u003e\u003cp\u003e如果游标的查询包含\u003ccode class=\"literal\"\u003eFOR UPDATE\u003c/code\u003e或\u003ccode class=\"literal\"\u003eFOR SHARE\u003c/code\u003e，那么返回的行会像带有这些选项的常规\u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\"\u003e\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e\u003c/a\u003e命令那样，在首次取出时被锁定。此外，返回的将是这些行的最新版本。\u003c/p\u003e\u003cdiv class=\"caution\"\u003e\u003ch3\u003e小心\u003c/h3\u003e\u003cp\u003e通常都建议使用\u003ccode class=\"literal\"\u003eFOR UPDATE\u003c/code\u003e，如果游标打算与 \u003ccode class=\"command\"\u003eUPDATE ... WHERE CURRENT OF\u003c/code\u003e或 \u003ccode class=\"command\"\u003eDELETE ... WHERE CURRENT OF\u003c/code\u003e一起使用。使用\u003ccode class=\"literal\"\u003eFOR UPDATE\u003c/code\u003e可以防止其他会话在这些行被取出之后、被更新之前更改它们。如果不使用\u003ccode class=\"literal\"\u003eFOR UPDATE\u003c/code\u003e，而某一行在游标创建后已经被更改，那么后续的\u003ccode class=\"literal\"\u003eWHERE CURRENT OF\u003c/code\u003e命令将不会有任何效果。\u003c/p\u003e\u003cp\u003e使用\u003ccode class=\"literal\"\u003eFOR UPDATE\u003c/code\u003e的另一个原因是：如果没有它，而后续的\u003ccode class=\"literal\"\u003eWHERE CURRENT OF\u003c/code\u003e 所针对的游标查询不符合 SQL 标准关于\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e简单可更新\u003c/span\u003e”\u003c/span\u003e的规则，则该命令可能失败。（特别是，游标必须只引用一个表，且不能使用分组或\u003ccode class=\"literal\"\u003eORDER BY\u003c/code\u003e）。对于并非简单可更新的游标，是否可用取决于计划选择的细节，可能能工作，也可能不能。因此在最坏情况下，应用可能在测试中可用，却会在生产中失败。如果指定了\u003ccode class=\"literal\"\u003eFOR UPDATE\u003c/code\u003e，则可保证该游标可更新。\u003c/p\u003e\u003cp\u003e不将\u003ccode class=\"literal\"\u003eFOR UPDATE\u003c/code\u003e与\u003ccode class=\"literal\"\u003eWHERE CURRENT OF\u003c/code\u003e一起使用的主要原因，是你需要游标可滚动，或者需要它与并发更新隔离（也就是说，继续显示旧数据）。如果这是需求，请务必仔细注意上面的警告。\u003c/p\u003e\u003c/div\u003e\u003cp\u003eSQL 标准只为嵌入式\u003cacronym\u003eSQL\u003c/acronym\u003e中的游标作出规定。\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e服务器没有为游标实现\u003ccode class=\"command\"\u003eOPEN\u003c/code\u003e语句；游标在声明时即被视为打开。不过，\u003cspan class=\"application\"\u003eECPG\u003c/span\u003e是\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e的嵌入式 SQL 预处理器，它支持标准 SQL 的游标约定，包括涉及\u003ccode class=\"command\"\u003eDECLARE\u003c/code\u003e和\u003ccode class=\"command\"\u003eOPEN\u003c/code\u003e语句的那些约定。\u003c/p\u003e\u003cp\u003e打开的游标在服务器端的底层数据结构称为\u003cem class=\"firstterm\"\u003eportal\u003c/em\u003e。客户端协议会暴露 portal 名称：只要知道该名称，客户端就可以直接从一个打开的 portal 中取出行。使用\u003ccode class=\"command\"\u003eDECLARE\u003c/code\u003e创建游标时，portal 名称与游标名称相同。\u003c/p\u003e\u003cp\u003e可以通过查询\u003ca href=\"/docs/18/view-pg-cursors.html\" title=\"53.7. pg_cursors\"\u003e\u003ccode class=\"structname\"\u003epg_cursors\u003c/code\u003e\u003c/a\u003e 系统视图查看所有可用游标。\u003c/p\u003e","key":"notes","title":"注解"},{"html":"\u003cp\u003e声明一个游标：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eDECLARE liahona CURSOR FOR SELECT * FROM films;\n\u003c/pre\u003e\u003cp\u003e更多游标用法示例见\u003ca href=\"/docs/18/sql-fetch.html\" title=\"FETCH\"\u003e\u003cspan class=\"refentrytitle\"\u003eFETCH\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e","key":"examples","title":"示例"},{"html":"\u003cp\u003eSQL 标准只允许在嵌入式\u003cacronym\u003eSQL\u003c/acronym\u003e和模块中使用游标。\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e允许以交互方式使用游标。\u003c/p\u003e\u003cp\u003e根据 SQL 标准，通过\u003ccode class=\"literal\"\u003eUPDATE ... WHERE CURRENT OF\u003c/code\u003e和\u003ccode class=\"literal\"\u003eDELETE ... WHERE CURRENT OF\u003c/code\u003e语句对不敏感游标所做的更改，在同一个游标中是可见的。\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e将这些语句与所有其他更改数据的语句同等对待，因此这些更改在不敏感游标中不可见。\u003c/p\u003e\u003cp\u003e二进制游标是\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e的一种扩展。\u003c/p\u003e","key":"compatibility","title":"兼容性"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/close/?v=18\" title=\"CLOSE\"\u003e\u003cspan class=\"refentrytitle\"\u003eCLOSE\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/fetch/?v=18\" title=\"FETCH\"\u003e\u003cspan class=\"refentrytitle\"\u003eFETCH\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/move/?v=18\" title=\"MOVE\"\u003e\u003cspan class=\"refentrytitle\"\u003eMOVE\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"另见"}],"sections_same_as":"","synopsis_html":"DECLARE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e [ BINARY ] [ ASENSITIVE | INSENSITIVE ] [ [ NO ] SCROLL ]\n    CURSOR [ { WITH | WITHOUT } HOLD ] FOR \u003cem class=\"replaceable\"\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e","synopsis_text":"DECLARE name [ BINARY ] [ ASENSITIVE | INSENSITIVE ] [ [ NO ] SCROLL ]\nCURSOR [ { WITH | WITHOUT } HOLD ] FOR query"}},"RequestedLocale":"zh-Hans","Fallback":false,"Versions":["10","11","12","13","14","15","16","17","18","19","20","6.4","6.5","7.0","7.1","7.2","7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6"],"Locales":["en","zh-Hans"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}