40.7. 游標 · PostgreSQL 中文文檔 9.3

# 40.7\. 游標如果不想一次執行整個命令，可以設置一個封裝該命令的游標（_cursor_），然后每次讀取幾行命令結果。這么干的一個原因是在結果包含數量非常大的行時避免內存耗盡。不過PL/pgSQL用戶不必擔心這個，因為`FOR` 循環自動在內部使用一個游標以避免內存問題。一個更有趣的用法是某個函數可以返回一個它創建的游標的引用，這樣就允許調用者讀取各行。從而提供了一種從函數返回一個結果集的手段。 ## 40.7.1\. 聲明游標變量所有在PL/pgSQL里對游標的訪問都是通過游標變量實現的，它總是特殊的數據類型`refcursor`。創建游標變量的一個方法是把它聲明為一個類型為`refcursor`的變量。另外一個方法是使用游標聲明語法，像下面這樣： ``` _name_ [ [ NO ] SCROLL ] CURSOR [ ( `_arguments_` ) ] FOR _query_; ``` (Oracle兼容中`FOR`可以用`IS`代替)。如果定義了`SCROLL`，則游標可以向后回滾；如果定義了`NO SCROLL`，則向后抓取的動作被拒絕；如果二者都沒有定義，那么是否進行向后取的動作會根據查詢來判斷。如果有`_arguments_`，那么它是一個逗號分隔`_name_``_datatype_`列表，這個列表定義由已給查詢中的參數值來替代的name。實際用于代換這些名字的數值將在游標打開之后聲明。例如： ``` DECLARE curs1 refcursor; curs2 CURSOR FOR SELECT * FROM tenk1; curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key; ``` 所有這三個變量都是`refcursor`類型，但是第一個可以用于任何命令，而第二個已經綁定（_bound_）了一個聲明完整的命令，最后一個是綁定了一個帶參數的命令。 `key`將在游標打開的時候被代換成一個整數。變量`curs1`可以稱之為_未綁定_的，因為它沒有和任何查詢相綁定。 ## 40.7.2\. 打開游標在你使用游標檢索行之前，你必需先_打開_它。這是和SQL命令`DECLARE CURSOR`相等的操作。 PL/pgSQL有三種形式的`OPEN`語句，兩種用于未綁定的游標變量，另外一種用于已綁定的游標變量。 > **Note:** 可以通過[Section 40.7.4](#calibre_link-1580)中描述的`FOR`語句，在不用打開游標的情況下使用已綁定的游標。 ### 40.7.2.1\. `OPEN FOR` `_query_` ``` OPEN _unbound_cursorvar_ [ [ NO ] SCROLL ] FOR _query_; ``` 該游標變量打開并且執行給出的查詢。游標不能是已經打開的，并且它必需是聲明為一個未綁定的游標(也就是聲明為一個簡單的`refcursor`變量)。查詢必須是一條`SELECT`或者其它返回行的東西(比如`EXPLAIN`)。查詢是和其它在PL/pgSQL里的SQL命令平等對待的：先代換PL/pgSQL的變量名，而且執行計劃為將來可能的復用緩存起來。當一個PL/pgSQL變量被替換到游標查詢中時，被替換的值是在`OPEN`時它所具有的值。后續的改變不會影響游標的動作，對于一個已經綁定的游標來說， `SCROLL`和`NO SCROLL`這兩個選項具有相同的含義。一個例子： ``` OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey; ``` ### 40.7.2.2\. `OPEN FOR EXECUTE` ``` OPEN _unbound_cursorvar_ [ [ NO ] SCROLL ] FOR EXECUTE _query_string_ [ USING `_expression_` [, ... ] ]; ``` 打開游標變量并且執行給出的查詢。游標不能是已打開的，并且必須聲明為一個未綁定的游標(也就是一個簡單的`refcursor`變量)。命令是用和那些用于`EXECUTE`命令一樣的方法聲明的字符串表達式，這樣，就有了命令可以在兩次運行間發生變化的靈活性。參閱[Section 40.10.2](#calibre_link-1573)）這也意味著在命令字符串上不能進行變量替換。跟`EXECUTE`一起，通過使用`USING`，參數值可以被插入到動態命令中。對于一個已經綁定的游標來說，`SCROLL`和`NO SCROLL`這兩個選項具有相同的含義。一個例子： ``` OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident(tabname) || ' WHERE col1 = $1' USING keyvalue; ``` 在這個例子中，表名被插入到文本查詢中，因此使用`quote_ident()`時要注意SQL注入。通過`USING`參數對插入的`col1`進行比較值，因此不需要使用引號。 ### 40.7.2.3\. 打開一個綁定的游標 ``` OPEN _bound_cursorvar_ [ ( [ `_argument_name_` := ] `_argument_value_` [, ...] ) ]; ``` 這種形式的`OPEN`用于打開一個游標變量，該游標變量的命令是在聲明的時候和它綁定在一起的。游標不能是已經打開的。當且僅當該游標聲明為接受參數的時候，語句中才必需出現一個實際參數值表達式的列表。這些值將代換到命令中。一個綁定的游標的命令計劃總是認為可緩沖的，這種情況下沒有等效的`EXECUTE`。需要注意的是`SCROLL`和`NO SCROLL`不能在`OPEN`中被聲明，因為游標的滾動動作已經被定義了。參數值可以使用_positional_或者_named_符號傳遞。在位置符號中，所有的參數以順序指定。在命名法中，每個參數的名稱使用`:=`聲明以從參數表達式中分開。類似于調用函數，在[Section 4.3](#calibre_link-733)中描述，它也允許混合位置和命名法。例子（以上使用游標聲明的例子）: ``` OPEN curs2; OPEN curs3(42); OPEN curs3(key := 42); ``` 因為在綁定游標查詢上做了變量替換，有兩種方法將值傳遞到游標：要么使用明確參數到`OPEN`，或者隱式地在查詢中引用PL/pgSQL變量。然而，只有在綁定游標之前聲明的變量將取代它。在這兩種情況下可以在`OPEN`時決定將被傳遞的值。例如，另一種方式來獲得相同的效果如`curs3`上面的例子 ``` DECLARE key integer; curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key; BEGIN key := 42; OPEN curs4; ``` ## 40.7.3\. 使用游標一旦你已經打開了一個游標，那么你就可以用這里描述的語句操作它。這些操作不需要發生在和打開該游標開始操作的同一個函數里。你可以從函數里返回一個`refcursor`值，然后讓調用者操作該游標。在內部，`refcursor`值只是一個包含該游標命令的活躍查詢的信使的字符串名。這個名字可以傳來傳去，可以賦予其它`refcursor`變量等等，也不用擔心擾亂信使。所有信使在事務的結尾都會隱含地關閉。因此一個`refcursor`值只能在該事務結束前用于引用一個打開的游標。 ### 40.7.3.1\. `FETCH` ``` FETCH [ `_direction_` { FROM | IN } ] _cursor_ INTO _target_; ``` `FETCH`從游標中檢索下一行到目標中，目標可以是一個行變量、記錄變量、逗號分隔的普通變量列表，就像`SELECT INTO`一樣，如果下一行中沒有，目標會設為NULL。如同`SELECT INTO`，可以使用特殊變量`FOUND`來檢查是否檢索出一個行。 `_direction_`子句可以是任何一個SQL [FETCH](#calibre_link-74)命令允許的變量，除了那些可以抓取不止一行的；形如：`NEXT`, `PRIOR`，`FIRST`，`LAST`， `ABSOLUTE` `_count_`， `RELATIVE` `_count_`， `FORWARD`或者`BACKWARD`。忽略`_direction_`作為聲明的`NEXT`是相同的。 `_direction_`值需要往后移動可能會失敗，除非聲明的或者打開的游標帶有`SCROLL`選項。 `_cursor_`必須是一個指向一個打開的游標的`refcursor`變量的名字。一個例子： ``` FETCH curs1 INTO rowvar; FETCH curs2 INTO foo, bar, baz; FETCH LAST FROM curs3 INTO x, y; FETCH RELATIVE -2 FROM curs4 INTO x; ``` ### 40.7.3.2\. `MOVE` ``` MOVE [ `_direction_` { FROM | IN } ] _cursor_; ``` `MOVE`重新定位一個游標，而不需要檢索任何數據。 `MOVE`的工作方式與`FETCH`及其相似，除了它只是重新定位游標并且不返回至移動到的行。在進行`SELECT INTO`命令時，聲明的`FOUND`變量可以用來檢查下一個需要移動到的行是否存在。 `_direction_`可以是任何一個SQL [FETCH](#calibre_link-74) 命令允許的變量，如下`NEXT`, `PRIOR`， `FIRST`， `LAST`， `ABSOLUTE` `_count_`, `RELATIVE` `_count_`, `ALL`， `FORWARD` [ `_count_` | `ALL` ] 或者`BACKWARD` [ `_count_` | `ALL` ]。忽略`_direction_`作為聲明的`NEXT`是相同的。 `_direction_`值需要往后移動可能會失敗，除非聲明的或者打開的游標帶有`SCROLL`選項。例如： ``` MOVE curs1; MOVE LAST FROM curs3; MOVE RELATIVE -2 FROM curs4; MOVE FORWARD 2 FROM curs4; ``` ### 40.7.3.3\. `UPDATE/DELETE WHERE CURRENT OF` ``` UPDATE _table_ SET ... WHERE CURRENT OF _cursor_; DELETE FROM _table_ WHERE CURRENT OF _cursor_; ``` 當一個游標被定位到一個表的行上，那么通過使用該游標來識別該行，從而進行更新或刪除操作。當然，對于如何定義游標查詢（特別是沒有分組時）是存在一定限制的；在游標中使用`FOR UPDATE`是個不錯的主意。更多信息可參閱[DECLARE](#calibre_link-72)。例如： ``` UPDATE foo SET dataval = myval WHERE CURRENT OF curs1; ``` ### 40.7.3.4\. `CLOSE` ``` CLOSE _cursor_; ``` `CLOSE`關閉支撐在一個打開的游標下面的信使。這樣就可以在事務結束之前釋放資源，或者釋放掉該游標變量，用于稍后再次打開。例如： ``` CLOSE curs1; ``` ### 40.7.3.5\. 返回游標 PL/pgSQL函數可以向調用者返回游標這個功能用于從函數里返回多行或多列，特別是巨大的結果集。要想這么做，該函數必須打開游標并且把該游標的名字返回給調用者，或者簡單的使用指定的入口名或調用者已知的名字打開游標。調用者然后從游標里抓取行。游標可以由調用者關閉，或者是在事務結束的時候自動關閉。函數返回的游標名可以由調用者聲明或者自動生成。要聲明一個信使的名字，只要在打開游標之前，給`refcursor`變量賦予一個字符串就可以了。 `refcursor`變量的字符串值將被`OPEN`當作下層的信使的名字使用。不過，如果`refcursor`變量是空，那么`OPEN`將自動生成一個和現有信使不沖突的名字，然后將它賦予`refcursor`變量。 > **Note:** 一個綁定的游標變量其名字初始化為對應的字符串值，因此信使的名字和游標變量名同名，除非程序員在打開游標之前通過賦值覆蓋了這個名字。但是一個未綁定的游標變量初始化的時候缺省是空，因此它會收到一個自動生成的唯一名字，除非被覆蓋。下面的例子顯示了一個調用者聲明游標名字的方法： ``` CREATE TABLE test (col text); INSERT INTO test VALUES ('123'); CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS ' BEGIN OPEN $1 FOR SELECT col FROM test; RETURN $1; END; ' LANGUAGE plpgsql; BEGIN; SELECT reffunc('funccursor'); FETCH ALL IN funccursor; COMMIT; ``` 下面的例子使用了自動生成的游標名： ``` CREATE FUNCTION reffunc2() RETURNS refcursor AS ' DECLARE ref refcursor; BEGIN OPEN ref FOR SELECT col FROM test; RETURN ref; END; ' LANGUAGE plpgsql; -- 需要在一個事務中使用游標。 BEGIN; SELECT reffunc2(); reffunc2 -------------------- <unnamed cursor 1> (1 row) FETCH ALL IN "<unnamed cursor 1>"; COMMIT; ``` 下面的例子顯示了從一個函數里返回多個游標的方法： ``` CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$ BEGIN OPEN $1 FOR SELECT * FROM table_1; RETURN NEXT $1; OPEN $2 FOR SELECT * FROM table_2; RETURN NEXT $2; END; $$ LANGUAGE plpgsql; -- 需要在事務里使用游標。 BEGIN; SELECT * FROM myfunc('a', 'b'); FETCH ALL FROM a; FETCH ALL FROM b; COMMIT; ``` ## 40.7.4\. 通過游標結果進行循環有這么一個`FOR`語法的變形，它允許通過游標返回的行進行迭代。如下： ``` [ <<`_label_`>> ] FOR _recordvar_ IN _bound_cursorvar_ [ ( [ `_argument_name_` := ] `_argument_value_` [, ...] ) ] LOOP _statements_ END LOOP [ `_label_` ]; ``` 在聲明游標變量時，它必須已經綁定到一些查詢語句上，并且_不能_是打開狀態。 `FOR`語法會自動打開游標，并且當退出循環時自動關閉游標。只有當游標被聲明要使用參數時，必須有一列實際參數值表達式。這些值會被替換到查詢中，采用如同`OPEN`的方式 (參閱[Section 40.7.2.3](#calibre_link-1582))。 `_recordvar_`變量會自動定義為`record`類型，并且只存在于循環中（循環中任何的定義變量名的動作都會被忽略）。每一個由游標返回的行都會陸續的被分配到記錄變量中，然后執行循環體。