{"Entry":{"collection":"sql","key":"fetch","name":"FETCH","aliases":["fetch"],"metadata":{"aliases":["fetch"],"changed_in":["6.5","7.1","7.4","9.0","10"],"changes":[{"from":"6.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":{"added":["FETCH RELATIVE 0 FROM cursor"],"removed":[]},"to":"6.5"},{"from":"6.5","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","usage","compatibility"],"removed":[]},"status":"changed","synopsis":null,"to":"7.0"},{"from":"7.0","purpose_changed":false,"renamed":{"from_file":"sql-fetch.htm","to_file":"sql-fetch.html"},"sections":{"added":[],"changed":["usage","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["FETCH [ direction ] [ count ] { IN | FROM } cursor","FETCH [ FORWARD | BACKWARD | RELATIVE ] [ # | ALL | NEXT | PRIOR ] { IN | FROM } cursor","FETCH RELATIVE 0 FROM cursor."],"removed":["FETCH [ selector ] [ count ] { IN | FROM } cursor","FETCH [ RELATIVE ] [ { [ # | ALL | NEXT | PRIOR ] } ] FROM ] cursor"]},"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":true,"renamed":null,"sections":{"added":["parameters","outputs","notes","examples"],"changed":["description","compatibility"],"removed":["usage"]},"status":"changed","synopsis":{"added":["FETCH [ direction { FROM | IN } ] cursorname","where direction can be empty or one of:","NEXT","PRIOR","FIRST","LAST","ABSOLUTE count","RELATIVE count","count","ALL","FORWARD count","FORWARD ALL","BACKWARD count","BACKWARD ALL"],"removed":["FETCH [ direction ] [ count ] { IN | FROM } cursor","FETCH [ FORWARD | BACKWARD | RELATIVE ] [ # | ALL | NEXT | PRIOR ]","{ IN | FROM } cursor","FETCH RELATIVE 0 FROM cursor."]},"to":"7.4"},{"from":"7.4","purpose_changed":false,"renamed":null,"sections":{"added":["see_also"],"changed":["compatibility"],"removed":[]},"status":"changed","synopsis":null,"to":"8.0"},{"from":"8.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes","examples"],"removed":[]},"status":"changed","synopsis":null,"to":"8.3"},{"from":"8.3","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"8.4"},{"from":"8.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["FETCH [ direction [ FROM | IN ] ] cursor_name"],"removed":["FETCH [ direction { FROM | IN } ] cursorname","where direction can be empty or one of:"]},"to":"9.0"},{"from":"9.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"9.3"},{"from":"9.5","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"9.6"},{"from":"9.6","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":{"added":["FETCH [ direction ] [ FROM | IN ] cursor_name"],"removed":["FETCH [ direction [ FROM | IN ] ] cursor_name"]},"to":"10"},{"from":"10","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"11"},{"from":"11","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"12"},{"from":"13","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"14"},{"from":"16","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"17"}],"content_hash":"4c8d934027ba87b02458905903efe362b158fa514cfa0a1c8f736627f3d3e023","editorial":{},"first_version":"6.4","group":"cursor","imported_at":"2026-09-30T17:43:39.238501+08:00","last_version":"20","name":"FETCH","object":"","position":12004,"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":"retrieve rows from a query using a cursor","purpose_zh":"","related":["close","declare","move"],"slug":"fetch","source_rev":"a709ab85","synopsis":"FETCH [ direction ] [ FROM | IN ] cursor_name\nwhere direction can be one of:\n\nNEXT\nPRIOR\nFIRST\nLAST\nABSOLUTE count\nRELATIVE count\ncount\nALL\nFORWARD\nFORWARD count\nFORWARD ALL\nBACKWARD\nBACKWARD count\nBACKWARD ALL","verb":"FETCH"}},"Definition":{"Collection":"sql","Key":"fetch","SourceDatabase":"center","Version":"18","SourceTable":"sqlcmd","SourceKey":"fetch","SourceRevision":"a709ab85","Facts":{"anchor":"SQL-FETCH","file":"sql-fetch.html","lang":"en","name":"FETCH","purpose":"retrieve rows from a query using a cursor","purpose_zh":"","related":["close","declare","move"],"sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eFETCH\u003c/code\u003e retrieves rows using a previously-created cursor.\u003c/p\u003e\u003cp\u003eA cursor has an associated position, which is used by \u003ccode class=\"command\"\u003eFETCH\u003c/code\u003e. The cursor position can be before the first row of the query result, on any particular row of the result, or after the last row of the result. When created, a cursor is positioned before the first row. After fetching some rows, the cursor is positioned on the row most recently retrieved. If \u003ccode class=\"command\"\u003eFETCH\u003c/code\u003e runs off the end of the available rows then the cursor is left positioned after the last row, or before the first row if fetching backward. \u003ccode class=\"command\"\u003eFETCH ALL\u003c/code\u003e or \u003ccode class=\"command\"\u003eFETCH BACKWARD ALL\u003c/code\u003e will always leave the cursor positioned after the last row or before the first row.\u003c/p\u003e\u003cp\u003eThe forms \u003ccode class=\"literal\"\u003eNEXT\u003c/code\u003e, \u003ccode class=\"literal\"\u003ePRIOR\u003c/code\u003e, \u003ccode class=\"literal\"\u003eFIRST\u003c/code\u003e, \u003ccode class=\"literal\"\u003eLAST\u003c/code\u003e, \u003ccode class=\"literal\"\u003eABSOLUTE\u003c/code\u003e, \u003ccode class=\"literal\"\u003eRELATIVE\u003c/code\u003e fetch a single row after moving the cursor appropriately. If there is no such row, an empty result is returned, and the cursor is left positioned before the first row or after the last row as appropriate.\u003c/p\u003e\u003cp\u003eThe forms using \u003ccode class=\"literal\"\u003eFORWARD\u003c/code\u003e and \u003ccode class=\"literal\"\u003eBACKWARD\u003c/code\u003e retrieve the indicated number of rows moving in the forward or backward direction, leaving the cursor positioned on the last-returned row (or after/before all rows, if the \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e exceeds the number of rows available).\u003c/p\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003eRELATIVE 0\u003c/code\u003e, \u003ccode class=\"literal\"\u003eFORWARD 0\u003c/code\u003e, and \u003ccode class=\"literal\"\u003eBACKWARD 0\u003c/code\u003e all request fetching the current row without moving the cursor, that is, re-fetching the most recently fetched row. This will succeed unless the cursor is positioned before the first row or after the last row; in which case, no row is returned.\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#PLPGSQL-CURSOR-USING\" title=\"41.7.3. Using Cursors\"\u003eSection 41.7.3\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\u003edirection\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003edirection\u003c/code\u003e\u003c/em\u003e defines the fetch direction and number of rows to fetch. It can be one of the following:\u003c/p\u003e\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eNEXT\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eFetch the next row. This is the default if \u003cem class=\"replaceable\"\u003e\u003ccode\u003edirection\u003c/code\u003e\u003c/em\u003e is omitted.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003ePRIOR\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eFetch the prior row.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eFIRST\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eFetch the first row of the query (same as \u003ccode class=\"literal\"\u003eABSOLUTE 1\u003c/code\u003e).\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eLAST\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eFetch the last row of the query (same as \u003ccode class=\"literal\"\u003eABSOLUTE -1\u003c/code\u003e).\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eABSOLUTE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eFetch the \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e'th row of the query, or the \u003ccode class=\"literal\"\u003eabs(\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e)\u003c/code\u003e'th row from the end if \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e is negative. Position before first row or after last row if \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e is out of range; in particular, \u003ccode class=\"literal\"\u003eABSOLUTE 0\u003c/code\u003e positions before the first row.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eRELATIVE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eFetch the \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e'th succeeding row, or the \u003ccode class=\"literal\"\u003eabs(\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e)\u003c/code\u003e'th prior row if \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e is negative. \u003ccode class=\"literal\"\u003eRELATIVE 0\u003c/code\u003e re-fetches the current row, if any.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eFetch the next \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e rows (same as \u003ccode class=\"literal\"\u003eFORWARD \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e).\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eALL\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eFetch all remaining rows (same as \u003ccode class=\"literal\"\u003eFORWARD ALL\u003c/code\u003e).\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eFORWARD\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eFetch the next row (same as \u003ccode class=\"literal\"\u003eNEXT\u003c/code\u003e).\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eFORWARD \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eFetch the next \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e rows. \u003ccode class=\"literal\"\u003eFORWARD 0\u003c/code\u003e re-fetches the current row.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eFORWARD ALL\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eFetch all remaining rows.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eBACKWARD\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eFetch the prior row (same as \u003ccode class=\"literal\"\u003ePRIOR\u003c/code\u003e).\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eBACKWARD \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eFetch the prior \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e rows (scanning backwards). \u003ccode class=\"literal\"\u003eBACKWARD 0\u003c/code\u003e re-fetches the current row.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eBACKWARD ALL\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eFetch all prior rows (scanning backwards).\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e is a possibly-signed integer constant, determining the location or number of rows to fetch. For \u003ccode class=\"literal\"\u003eFORWARD\u003c/code\u003e and \u003ccode class=\"literal\"\u003eBACKWARD\u003c/code\u003e cases, specifying a negative \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e is equivalent to changing the sense of \u003ccode class=\"literal\"\u003eFORWARD\u003c/code\u003e and \u003ccode class=\"literal\"\u003eBACKWARD\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecursor_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eAn open cursor's name.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"Parameters"},{"html":"\u003cp\u003eOn successful completion, a \u003ccode class=\"command\"\u003eFETCH\u003c/code\u003e command returns a command tag of the form\u003c/p\u003e\u003cpre class=\"screen\"\u003eFETCH \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\n\u003c/pre\u003e\u003cp\u003eThe \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e is the number of rows fetched (possibly zero). Note that in \u003cspan class=\"application\"\u003epsql\u003c/span\u003e, the command tag will not actually be displayed, since \u003cspan class=\"application\"\u003epsql\u003c/span\u003e displays the fetched rows instead.\u003c/p\u003e","key":"outputs","title":"Outputs"},{"html":"\u003cp\u003eThe cursor should be declared with the \u003ccode class=\"literal\"\u003eSCROLL\u003c/code\u003e option if one intends to use any variants of \u003ccode class=\"command\"\u003eFETCH\u003c/code\u003e other than \u003ccode class=\"command\"\u003eFETCH NEXT\u003c/code\u003e or \u003ccode class=\"command\"\u003eFETCH FORWARD\u003c/code\u003e with a positive count. For simple queries \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e will allow backwards fetch from cursors not declared with \u003ccode class=\"literal\"\u003eSCROLL\u003c/code\u003e, but this behavior is best not relied on. If the cursor is declared with \u003ccode class=\"literal\"\u003eNO SCROLL\u003c/code\u003e, no backward fetches are allowed.\u003c/p\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003eABSOLUTE\u003c/code\u003e fetches are not any faster than navigating to the desired row with a relative move: the underlying implementation must traverse all the intermediate rows anyway. Negative absolute fetches are even worse: the query must be read to the end to find the last row, and then traversed backward from there. However, rewinding to the start of the query (as with \u003ccode class=\"literal\"\u003eFETCH ABSOLUTE 0\u003c/code\u003e) is fast.\u003c/p\u003e\u003cp\u003e\u003ca href=\"/docs/18/sql-declare.html\" title=\"DECLARE\"\u003e\u003ccode class=\"command\"\u003eDECLARE\u003c/code\u003e\u003c/a\u003e is used to define a cursor. Use \u003ca href=\"/docs/18/sql-move.html\" title=\"MOVE\"\u003e\u003ccode class=\"command\"\u003eMOVE\u003c/code\u003e\u003c/a\u003e to change cursor position without retrieving data.\u003c/p\u003e","key":"notes","title":"Notes"},{"html":"\u003cp\u003eThe following example traverses a table using a cursor:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eBEGIN WORK;\n\n-- Set up a cursor:\nDECLARE liahona SCROLL CURSOR FOR SELECT * FROM films;\n\n-- Fetch the first 5 rows in the cursor liahona:\nFETCH FORWARD 5 FROM liahona;\n\n code  |          title          | did | date_prod  |   kind   |  len\n-------+-------------------------+-----+------------+----------+-------\n BL101 | The Third Man           | 101 | 1949-12-23 | Drama    | 01:44\n BL102 | The African Queen       | 101 | 1951-08-11 | Romantic | 01:43\n JL201 | Une Femme est une Femme | 102 | 1961-03-12 | Romantic | 01:25\n P_301 | Vertigo                 | 103 | 1958-11-14 | Action   | 02:08\n P_302 | Becket                  | 103 | 1964-02-03 | Drama    | 02:28\n\n-- Fetch the previous row:\nFETCH PRIOR FROM liahona;\n\n code  |  title  | did | date_prod  |  kind  |  len\n-------+---------+-----+------------+--------+-------\n P_301 | Vertigo | 103 | 1958-11-14 | Action | 02:08\n\n-- Close the cursor and end the transaction:\nCLOSE liahona;\nCOMMIT WORK;\n\u003c/pre\u003e","key":"examples","title":"Examples"},{"html":"\u003cp\u003eThe SQL standard defines \u003ccode class=\"command\"\u003eFETCH\u003c/code\u003e for use in embedded SQL only. The variant of \u003ccode class=\"command\"\u003eFETCH\u003c/code\u003e described here returns the data as if it were a \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e result rather than placing it in host variables. Other than this point, \u003ccode class=\"command\"\u003eFETCH\u003c/code\u003e is fully upward-compatible with the SQL standard.\u003c/p\u003e\u003cp\u003eThe \u003ccode class=\"command\"\u003eFETCH\u003c/code\u003e forms involving \u003ccode class=\"literal\"\u003eFORWARD\u003c/code\u003e and \u003ccode class=\"literal\"\u003eBACKWARD\u003c/code\u003e, as well as the forms \u003ccode class=\"literal\"\u003eFETCH \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e and \u003ccode class=\"literal\"\u003eFETCH ALL\u003c/code\u003e, in which \u003ccode class=\"literal\"\u003eFORWARD\u003c/code\u003e is implicit, are \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e extensions.\u003c/p\u003e\u003cp\u003eThe SQL standard allows only \u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e preceding the cursor name; the option to use \u003ccode class=\"literal\"\u003eIN\u003c/code\u003e, or to leave them out altogether, is an 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/declare/?v=18\" title=\"DECLARE\"\u003e\u003cspan class=\"refentrytitle\"\u003eDECLARE\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":"FETCH [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003edirection\u003c/code\u003e\u003c/em\u003e ] [ FROM | IN ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecursor_name\u003c/code\u003e\u003c/em\u003e\n\n\u003cspan class=\"phrase\"\u003ewhere \u003cem class=\"replaceable\"\u003e\u003ccode\u003edirection\u003c/code\u003e\u003c/em\u003e can be one of:\u003c/span\u003e\n\n    NEXT\n    PRIOR\n    FIRST\n    LAST\n    ABSOLUTE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\n    RELATIVE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\n    \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\n    ALL\n    FORWARD\n    FORWARD \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\n    FORWARD ALL\n    BACKWARD\n    BACKWARD \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\n    BACKWARD ALL","synopsis_text":"FETCH [ direction ] [ FROM | IN ] cursor_name\nwhere direction can be one of:\n\nNEXT\nPRIOR\nFIRST\nLAST\nABSOLUTE count\nRELATIVE count\ncount\nALL\nFORWARD\nFORWARD count\nFORWARD ALL\nBACKWARD\nBACKWARD count\nBACKWARD ALL"},"ManualEvidence":{},"MeasuredEvidence":{}},"Text":{"Collection":"sql","Key":"fetch","SourceDatabase":"pgweb","Version":"18","Locale":"zh-Hans","Title":"FETCH","Summary":"使用游标从查询中检索行","BodyHTML":"\u003cpre\u003eFETCH [ direction ] [ FROM | IN ] cursor_name\n其中 direction 可以是以下之一：\n\nNEXT\nPRIOR\nFIRST\nLAST\nABSOLUTE count\nRELATIVE count\ncount\nALL\nFORWARD\nFORWARD count\nFORWARD ALL\nBACKWARD\nBACKWARD count\nBACKWARD ALL\u003c/pre\u003e\u003csection\u003e\u003ch2\u003e描述\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eFETCH\u003c/code\u003e使用先前创建的游标检索行。\u003c/p\u003e\u003cp\u003e游标带有一个关联位置，\u003ccode\u003eFETCH\u003c/code\u003e会使用该位置。游标位置可以位于查询结果的第一行之前、结果中的任意一行上，或者位于结果的最后一行之后。游标创建时位于第一行之前。提取若干行后，游标会定位在最近提取的那一行上。如果\u003ccode\u003eFETCH\u003c/code\u003e 越过了可用行的末尾，那么游标会留在最后一行之后；如果是向后提取，则会留在第一行之前。\u003ccode\u003eFETCH ALL\u003c/code\u003e或者 \u003ccode\u003eFETCH BACKWARD ALL\u003c/code\u003e总是会让游标位于最后一行之后或第一行之前。\u003c/p\u003e\u003cp\u003e\u003ccode\u003eNEXT\u003c/code\u003e、\u003ccode\u003ePRIOR\u003c/code\u003e、\u003ccode\u003eFIRST\u003c/code\u003e、\u003ccode\u003eLAST\u003c/code\u003e、\u003ccode\u003eABSOLUTE\u003c/code\u003e、\u003ccode\u003eRELATIVE\u003c/code\u003e 这些形式会在适当地移动游标后提取一行。如果不存在这样的行，则返回空结果，并根据情况将游标定位在第一行之前或最后一行之后。\u003c/p\u003e\u003cp\u003e使用\u003ccode\u003eFORWARD\u003c/code\u003e和\u003ccode\u003eBACKWARD\u003c/code\u003e的形式会在向前或向后的方向上提取指定数量的行，并将游标定位在最后返回的那一行上（如果\u003cem\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e超过可用行数，则定位在所有行之后或之前）。\u003c/p\u003e\u003cp\u003e\u003ccode\u003eRELATIVE 0\u003c/code\u003e、\u003ccode\u003eFORWARD 0\u003c/code\u003e以及 \u003ccode\u003eBACKWARD 0\u003c/code\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#PLPGSQL-CURSOR-USING\" rel=\"nofollow\"\u003e第 41.7.3 节\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\u003edirection\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003cem\u003e\u003ccode\u003edirection\u003c/code\u003e\u003c/em\u003e定义提取方向以及要提取的行数。它可以是下列之一：\u003c/p\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eNEXT\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e提取下一行。如果省略\u003cem\u003e\u003ccode\u003edirection\u003c/code\u003e\u003c/em\u003e，这将是默认值。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003ePRIOR\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e提取前一行。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eFIRST\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e提取查询的第一行（与\u003ccode\u003eABSOLUTE 1\u003c/code\u003e相同）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eLAST\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e提取查询的最后一行（与\u003ccode\u003eABSOLUTE -1\u003c/code\u003e相同）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eABSOLUTE \u003cem\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e提取查询的第\u003cem\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e行；如果\u003cem\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e为负，则提取从末尾算起的第\u003ccode\u003eabs(\u003cem\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e)\u003c/code\u003e行。如果 \u003cem\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e超出范围，则定位在第一行之前或最后一行之后。特别地，\u003ccode\u003eABSOLUTE 0\u003c/code\u003e会定位在第一行之前。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eRELATIVE \u003cem\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e提取后面的第\u003cem\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e行；如果\u003cem\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e为负，则提取前面的第\u003ccode\u003eabs(\u003cem\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e)\u003c/code\u003e行。\u003ccode\u003eRELATIVE 0\u003c/code\u003e会重新提取当前行（如果有）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e提取接下来的\u003cem\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e行（与\u003ccode\u003eFORWARD \u003cem\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e相同）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eALL\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e提取所有剩余行（与\u003ccode\u003eFORWARD ALL\u003c/code\u003e相同）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eFORWARD\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e提取下一行（与\u003ccode\u003eNEXT\u003c/code\u003e相同）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eFORWARD \u003cem\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e提取接下来的\u003cem\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e行。\u003ccode\u003eFORWARD 0\u003c/code\u003e会重新提取当前行。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eFORWARD ALL\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e提取所有剩余行。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eBACKWARD\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e提取前一行（与\u003ccode\u003ePRIOR\u003c/code\u003e相同）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eBACKWARD \u003cem\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e提取前面的\u003cem\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e行（反向扫描）。\u003ccode\u003eBACKWARD 0\u003c/code\u003e会重新提取当前行。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eBACKWARD ALL\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e提取之前的所有行（反向扫描）。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003cem\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e是一个可带符号的整数常量，用于确定要提取的位置或行数。对于 \u003ccode\u003eFORWARD\u003c/code\u003e和\u003ccode\u003eBACKWARD\u003c/code\u003e这两种情况，指定负的\u003cem\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e等效于颠倒\u003ccode\u003eFORWARD\u003c/code\u003e和\u003ccode\u003eBACKWARD\u003c/code\u003e的方向。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ecursor_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e一个已打开游标的名称。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e输出\u003c/h2\u003e\u003cp\u003e成功完成时，\u003ccode\u003eFETCH\u003c/code\u003e命令会返回如下形式的命令标签：\u003c/p\u003e\u003cpre\u003eFETCH \u003cem\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\n\u003c/pre\u003e\u003cp\u003e其中\u003cem\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e是提取到的行数（可能为零）。注意，在\u003cspan\u003epsql\u003c/span\u003e中，命令标签实际上不会显示，因为\u003cspan\u003epsql\u003c/span\u003e会改为显示提取到的行。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e注解\u003c/h2\u003e\u003cp\u003e如果打算使用除\u003ccode\u003eFETCH NEXT\u003c/code\u003e或带正数计数的 \u003ccode\u003eFETCH FORWARD\u003c/code\u003e之外的任何\u003ccode\u003eFETCH\u003c/code\u003e 变体，则应当在声明游标时使用\u003ccode\u003eSCROLL\u003c/code\u003e选项。对于简单查询，\u003cspan\u003ePostgreSQL\u003c/span\u003e允许从未使用\u003ccode\u003eSCROLL\u003c/code\u003e 声明的游标向后提取，但最好不要依赖这种行为。如果游标声明为 \u003ccode\u003eNO SCROLL\u003c/code\u003e，则不允许向后提取。\u003c/p\u003e\u003cp\u003e\u003ccode\u003eABSOLUTE\u003c/code\u003e提取并不比通过相对移动到达目标行更快：无论如何，底层实现都必须遍历所有中间行。负值的绝对提取甚至更糟：必须先把查询读到末尾以找到最后一行，然后再从那里反向遍历。不过，回卷到查询起始处（如\u003ccode\u003eFETCH ABSOLUTE 0\u003c/code\u003e）是很快的。\u003c/p\u003e\u003cp\u003e\u003ca href=\"/docs/18/sql-declare.html\" title=\"DECLARE\" rel=\"nofollow\"\u003e\u003ccode\u003eDECLARE\u003c/code\u003e\u003c/a\u003e用于定义游标。使用\u003ca href=\"/docs/18/sql-move.html\" title=\"MOVE\" rel=\"nofollow\"\u003e\u003ccode\u003eMOVE\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\u003eBEGIN WORK;\n\n-- 建立一个游标：\nDECLARE liahona SCROLL CURSOR FOR SELECT * FROM films;\n\n-- 从游标 liahona 中提取前 5 行：\nFETCH FORWARD 5 FROM liahona;\n\n code  |          title          | did | date_prod  |   kind   |  len\n-------+-------------------------+-----+------------+----------+-------\n BL101 | The Third Man           | 101 | 1949-12-23 | Drama    | 01:44\n BL102 | The African Queen       | 101 | 1951-08-11 | Romantic | 01:43\n JL201 | Une Femme est une Femme | 102 | 1961-03-12 | Romantic | 01:25\n P_301 | Vertigo                 | 103 | 1958-11-14 | Action   | 02:08\n P_302 | Becket                  | 103 | 1964-02-03 | Drama    | 02:28\n\n-- 提取前一行：\nFETCH PRIOR FROM liahona;\n\n code  |  title  | did | date_prod  |  kind  |  len\n-------+---------+-----+------------+--------+-------\n P_301 | Vertigo | 103 | 1958-11-14 | Action | 02:08\n\n-- 关闭游标并结束事务：\nCLOSE liahona;\nCOMMIT WORK;\n\u003c/pre\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e兼容性\u003c/h2\u003e\u003cp\u003eSQL 标准只为嵌入式 SQL 定义了\u003ccode\u003eFETCH\u003c/code\u003e。这里描述的\u003ccode\u003eFETCH\u003c/code\u003e变体会像 \u003ccode\u003eSELECT\u003c/code\u003e结果那样返回数据，而不是把数据放入主变量中。除此之外，\u003ccode\u003eFETCH\u003c/code\u003e与 SQL 标准完全向上兼容。\u003c/p\u003e\u003cp\u003e涉及\u003ccode\u003eFORWARD\u003c/code\u003e和\u003ccode\u003eBACKWARD\u003c/code\u003e的 \u003ccode\u003eFETCH\u003c/code\u003e形式，以及形式\u003ccode\u003eFETCH \u003cem\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e和\u003ccode\u003eFETCH ALL\u003c/code\u003e（其中隐含了\u003ccode\u003eFORWARD\u003c/code\u003e）都是 \u003cspan\u003ePostgreSQL\u003c/span\u003e扩展。\u003c/p\u003e\u003cp\u003eSQL 标准只允许在游标名之前使用\u003ccode\u003eFROM\u003c/code\u003e；允许使用 \u003ccode\u003eIN\u003c/code\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/declare/?v=18\" title=\"DECLARE\" rel=\"nofollow\"\u003e\u003cspan\u003eDECLARE\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":"252c34d615da769571f3088919e0a5040546eb87a7f8eb1a06ec51ba0181c2d2","Payload":{"purpose_zh":"使用游标从查询中检索行","sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eFETCH\u003c/code\u003e使用先前创建的游标检索行。\u003c/p\u003e\u003cp\u003e游标带有一个关联位置，\u003ccode class=\"command\"\u003eFETCH\u003c/code\u003e会使用该位置。游标位置可以位于查询结果的第一行之前、结果中的任意一行上，或者位于结果的最后一行之后。游标创建时位于第一行之前。提取若干行后，游标会定位在最近提取的那一行上。如果\u003ccode class=\"command\"\u003eFETCH\u003c/code\u003e 越过了可用行的末尾，那么游标会留在最后一行之后；如果是向后提取，则会留在第一行之前。\u003ccode class=\"command\"\u003eFETCH ALL\u003c/code\u003e或者 \u003ccode class=\"command\"\u003eFETCH BACKWARD ALL\u003c/code\u003e总是会让游标位于最后一行之后或第一行之前。\u003c/p\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003eNEXT\u003c/code\u003e、\u003ccode class=\"literal\"\u003ePRIOR\u003c/code\u003e、\u003ccode class=\"literal\"\u003eFIRST\u003c/code\u003e、\u003ccode class=\"literal\"\u003eLAST\u003c/code\u003e、\u003ccode class=\"literal\"\u003eABSOLUTE\u003c/code\u003e、\u003ccode class=\"literal\"\u003eRELATIVE\u003c/code\u003e 这些形式会在适当地移动游标后提取一行。如果不存在这样的行，则返回空结果，并根据情况将游标定位在第一行之前或最后一行之后。\u003c/p\u003e\u003cp\u003e使用\u003ccode class=\"literal\"\u003eFORWARD\u003c/code\u003e和\u003ccode class=\"literal\"\u003eBACKWARD\u003c/code\u003e的形式会在向前或向后的方向上提取指定数量的行，并将游标定位在最后返回的那一行上（如果\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e超过可用行数，则定位在所有行之后或之前）。\u003c/p\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003eRELATIVE 0\u003c/code\u003e、\u003ccode class=\"literal\"\u003eFORWARD 0\u003c/code\u003e以及 \u003ccode class=\"literal\"\u003eBACKWARD 0\u003c/code\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#PLPGSQL-CURSOR-USING\" title=\"41.7.3. 使用游标\"\u003e第 41.7.3 节\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\u003edirection\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003edirection\u003c/code\u003e\u003c/em\u003e定义提取方向以及要提取的行数。它可以是下列之一：\u003c/p\u003e\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eNEXT\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e提取下一行。如果省略\u003cem class=\"replaceable\"\u003e\u003ccode\u003edirection\u003c/code\u003e\u003c/em\u003e，这将是默认值。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003ePRIOR\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\"\u003eFIRST\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e提取查询的第一行（与\u003ccode class=\"literal\"\u003eABSOLUTE 1\u003c/code\u003e相同）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eLAST\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e提取查询的最后一行（与\u003ccode class=\"literal\"\u003eABSOLUTE -1\u003c/code\u003e相同）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eABSOLUTE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e提取查询的第\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e行；如果\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e为负，则提取从末尾算起的第\u003ccode class=\"literal\"\u003eabs(\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e)\u003c/code\u003e行。如果 \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e超出范围，则定位在第一行之前或最后一行之后。特别地，\u003ccode class=\"literal\"\u003eABSOLUTE 0\u003c/code\u003e会定位在第一行之前。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eRELATIVE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e提取后面的第\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e行；如果\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e为负，则提取前面的第\u003ccode class=\"literal\"\u003eabs(\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e)\u003c/code\u003e行。\u003ccode class=\"literal\"\u003eRELATIVE 0\u003c/code\u003e会重新提取当前行（如果有）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e提取接下来的\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e行（与\u003ccode class=\"literal\"\u003eFORWARD \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e相同）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eALL\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e提取所有剩余行（与\u003ccode class=\"literal\"\u003eFORWARD ALL\u003c/code\u003e相同）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eFORWARD\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e提取下一行（与\u003ccode class=\"literal\"\u003eNEXT\u003c/code\u003e相同）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eFORWARD \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e提取接下来的\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e行。\u003ccode class=\"literal\"\u003eFORWARD 0\u003c/code\u003e会重新提取当前行。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eFORWARD ALL\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\"\u003eBACKWARD\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e提取前一行（与\u003ccode class=\"literal\"\u003ePRIOR\u003c/code\u003e相同）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eBACKWARD \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e提取前面的\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e行（反向扫描）。\u003ccode class=\"literal\"\u003eBACKWARD 0\u003c/code\u003e会重新提取当前行。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eBACKWARD ALL\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e提取之前的所有行（反向扫描）。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e是一个可带符号的整数常量，用于确定要提取的位置或行数。对于 \u003ccode class=\"literal\"\u003eFORWARD\u003c/code\u003e和\u003ccode class=\"literal\"\u003eBACKWARD\u003c/code\u003e这两种情况，指定负的\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e等效于颠倒\u003ccode class=\"literal\"\u003eFORWARD\u003c/code\u003e和\u003ccode class=\"literal\"\u003eBACKWARD\u003c/code\u003e的方向。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecursor_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e一个已打开游标的名称。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"参数"},{"html":"\u003cp\u003e成功完成时，\u003ccode class=\"command\"\u003eFETCH\u003c/code\u003e命令会返回如下形式的命令标签：\u003c/p\u003e\u003cpre class=\"screen\"\u003eFETCH \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\n\u003c/pre\u003e\u003cp\u003e其中\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e是提取到的行数（可能为零）。注意，在\u003cspan class=\"application\"\u003epsql\u003c/span\u003e中，命令标签实际上不会显示，因为\u003cspan class=\"application\"\u003epsql\u003c/span\u003e会改为显示提取到的行。\u003c/p\u003e","key":"outputs","title":"输出"},{"html":"\u003cp\u003e如果打算使用除\u003ccode class=\"command\"\u003eFETCH NEXT\u003c/code\u003e或带正数计数的 \u003ccode class=\"command\"\u003eFETCH FORWARD\u003c/code\u003e之外的任何\u003ccode class=\"command\"\u003eFETCH\u003c/code\u003e 变体，则应当在声明游标时使用\u003ccode class=\"literal\"\u003eSCROLL\u003c/code\u003e选项。对于简单查询，\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e允许从未使用\u003ccode class=\"literal\"\u003eSCROLL\u003c/code\u003e 声明的游标向后提取，但最好不要依赖这种行为。如果游标声明为 \u003ccode class=\"literal\"\u003eNO SCROLL\u003c/code\u003e，则不允许向后提取。\u003c/p\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003eABSOLUTE\u003c/code\u003e提取并不比通过相对移动到达目标行更快：无论如何，底层实现都必须遍历所有中间行。负值的绝对提取甚至更糟：必须先把查询读到末尾以找到最后一行，然后再从那里反向遍历。不过，回卷到查询起始处（如\u003ccode class=\"literal\"\u003eFETCH ABSOLUTE 0\u003c/code\u003e）是很快的。\u003c/p\u003e\u003cp\u003e\u003ca href=\"/docs/18/sql-declare.html\" title=\"DECLARE\"\u003e\u003ccode class=\"command\"\u003eDECLARE\u003c/code\u003e\u003c/a\u003e用于定义游标。使用\u003ca href=\"/docs/18/sql-move.html\" title=\"MOVE\"\u003e\u003ccode class=\"command\"\u003eMOVE\u003c/code\u003e\u003c/a\u003e可以在不检索数据的情况下改变游标位置。\u003c/p\u003e","key":"notes","title":"注解"},{"html":"\u003cp\u003e下面的示例展示了如何使用游标遍历一个表：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eBEGIN WORK;\n\n-- 建立一个游标：\nDECLARE liahona SCROLL CURSOR FOR SELECT * FROM films;\n\n-- 从游标 liahona 中提取前 5 行：\nFETCH FORWARD 5 FROM liahona;\n\n code  |          title          | did | date_prod  |   kind   |  len\n-------+-------------------------+-----+------------+----------+-------\n BL101 | The Third Man           | 101 | 1949-12-23 | Drama    | 01:44\n BL102 | The African Queen       | 101 | 1951-08-11 | Romantic | 01:43\n JL201 | Une Femme est une Femme | 102 | 1961-03-12 | Romantic | 01:25\n P_301 | Vertigo                 | 103 | 1958-11-14 | Action   | 02:08\n P_302 | Becket                  | 103 | 1964-02-03 | Drama    | 02:28\n\n-- 提取前一行：\nFETCH PRIOR FROM liahona;\n\n code  |  title  | did | date_prod  |  kind  |  len\n-------+---------+-----+------------+--------+-------\n P_301 | Vertigo | 103 | 1958-11-14 | Action | 02:08\n\n-- 关闭游标并结束事务：\nCLOSE liahona;\nCOMMIT WORK;\n\u003c/pre\u003e","key":"examples","title":"示例"},{"html":"\u003cp\u003eSQL 标准只为嵌入式 SQL 定义了\u003ccode class=\"command\"\u003eFETCH\u003c/code\u003e。这里描述的\u003ccode class=\"command\"\u003eFETCH\u003c/code\u003e变体会像 \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e结果那样返回数据，而不是把数据放入主变量中。除此之外，\u003ccode class=\"command\"\u003eFETCH\u003c/code\u003e与 SQL 标准完全向上兼容。\u003c/p\u003e\u003cp\u003e涉及\u003ccode class=\"literal\"\u003eFORWARD\u003c/code\u003e和\u003ccode class=\"literal\"\u003eBACKWARD\u003c/code\u003e的 \u003ccode class=\"command\"\u003eFETCH\u003c/code\u003e形式，以及形式\u003ccode class=\"literal\"\u003eFETCH \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e和\u003ccode class=\"literal\"\u003eFETCH ALL\u003c/code\u003e（其中隐含了\u003ccode class=\"literal\"\u003eFORWARD\u003c/code\u003e）都是 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e扩展。\u003c/p\u003e\u003cp\u003eSQL 标准只允许在游标名之前使用\u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e；允许使用 \u003ccode class=\"literal\"\u003eIN\u003c/code\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/declare/?v=18\" title=\"DECLARE\"\u003e\u003cspan class=\"refentrytitle\"\u003eDECLARE\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":"FETCH [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003edirection\u003c/code\u003e\u003c/em\u003e ] [ FROM | IN ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecursor_name\u003c/code\u003e\u003c/em\u003e\n\n\u003cspan class=\"phrase\"\u003e其中 \u003cem class=\"replaceable\"\u003e\u003ccode\u003edirection\u003c/code\u003e\u003c/em\u003e 可以是以下之一：\u003c/span\u003e\n\n    NEXT\n    PRIOR\n    FIRST\n    LAST\n    ABSOLUTE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\n    RELATIVE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\n    \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\n    ALL\n    FORWARD\n    FORWARD \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\n    FORWARD ALL\n    BACKWARD\n    BACKWARD \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\n    BACKWARD ALL","synopsis_text":"FETCH [ direction ] [ FROM | IN ] cursor_name\n其中 direction 可以是以下之一：\n\nNEXT\nPRIOR\nFIRST\nLAST\nABSOLUTE count\nRELATIVE count\ncount\nALL\nFORWARD\nFORWARD count\nFORWARD ALL\nBACKWARD\nBACKWARD count\nBACKWARD ALL"}},"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}