| PostgreSQL 8.0.0 中文文件（轉譯自 PostgreSQL 中國 製作的簡體中文版本） | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 35. PL/pgSQL - SQL 過程語言 | Fast Forward | Next |
如果不想一次執行整個命令，我們可以設置一個封裝該命令的 游標，然後每次讀取幾行命令結果。 這麼幹的一個原因是在結果包含數量非常大的行時避免內存耗盡。 （當然，PL/pgSQL 用戶通常不必擔心這個， 因為 FOR 循環自動在內部使用一個游標以避免內存問題。） 一個更有趣的用法是某個函數可以返回一個它建立的游標的引用， 這樣就允許調用者讀取各行。這就提供了一種從函數返回一個結果集的手段。
所有在 PL/pgSQL 裡對游標的訪問都是透過游標變量實現的， 它總是特殊的資料類型 refcursor。 建立一個游標變量的一個方法是把它聲明為一個類型為 refcursor 的變量。 另外一個方法是使用游標聲明語法，通常是下面這樣：
name CURSOR [ ( arguments ) ] FOR query ;（出於兼容Oracle的考慮，FOR 可以替換為 IS。） 如果有 arguments，那麼它是一個逗號分隔的 name datatype 配對的列資料表，它們定義那些將會用參數值替換掉的所給出命令中的名字。 實際用於代換這些名字的數值將在後面聲明，在游標打開之後。
幾個例子︰
DECLARE
curs1 refcursor;
curs2 CURSOR FOR SELECT * FROM tenk1;
curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;所有這三個變量都是類型為 refcursor， 但是第一個可以用於任何命令，而第二個已經綁定 了一個聲明完整的命令，最後一個是綁定了一個帶參數的命令。 （key 將在游標打開的時候被代換成一個整數。） 變量 curs1 可以稱之為未綁定的， 因為它沒有和任何查詢相綁定。
在您使用游標檢索行之前，您必需憲打開它。 （這是和 SQL 命令 DECLARE CURSOR 相等的操作。） PL/pgSQL 有三種形式的OPEN語句， 兩種用於未綁定的游標變量，另外一種用於綁定的游標變量。
OPEN unbound_cursor FOR SELECT ...;
該游標變量打開，並且執行給出的查詢。游標不能是已經打開的， 並且它必需是聲明為一個未綁定的游標（也就事說，聲明為一個簡單的 refcursor 變量）。 SELECT 命令是和其它在 PL/pgSQL 裡的 SELECT 命令平等對待的： 先代換 PL/pgSQL 的變量名，而且執行計劃為將來可能的復用緩存起來。
一個例子：
OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
OPEN unbound_cursor FOR EXECUTE query-string;
打開游標變量並且執行給出的查詢。游標不能是已打開的，並且必須聲明為一個未綁定的游標（也就是說， 是一個簡單的 refcursor 變量）。命令是用和那些用於 EXECUTE 命令一樣的方法聲明的字串資料表達式， 這樣，我們就有了命令可以在兩次執行間發生變化的靈活性。
一個例子：
OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);
OPEN bound_cursor [ ( argument_values ) ];這種形式的OPEN用於打開一個游標變量，該游標變量的命令是在聲明的時候和它綁定在一起的。 游標不能是已經打開的。 當且僅當該游標聲明為接受參數的時候，語句中才必需出現一個實際參數值資料表達式的列資料表。 這些值將代換到命令中。一個綁定的游標的命令計劃總是認為可緩衝的 -- 這種情況下沒有等效的EXECUTE。
例子：
OPEN curs2; OPEN curs3(42);
一旦您已經打開了一個游標，那麼您就可以用這裡描述的語句操作它。
這些操作不需要發生在和打開該游標開始操作的同一個函數里。 您可以從函數里返回一個 refcursor 數值，然後讓調用者操作該游標。 （在內部，refcursor 值只是一個包含該游標命令的活躍查詢的信使的字串名。 這個名字可以傳來傳去，可以賦予其它 refcursor 變量等等，也不用擔心擾亂信使。）
所有信使在交易的結尾都會隱含地關閉。因此一個 refcursor 值只能在該交易結束前用於引用一個打開的游標。
FETCH cursor INTO target;
FETCH從游標中檢索下一行到目標中，目標可以是一個行變量，一個記錄變量， 或者是一個逗號分隔的普通變量的列資料表，就像SELECT INTO裡一樣。 和SELECT INTO一樣，您可以使用特殊變量FOUND檢查是否檢索出一個行。
一個例子：
FETCH curs1 INTO rowvar; FETCH curs2 INTO foo, bar, baz;
CLOSE cursor;
CLOSE關閉支撐在一個打開的游標下面的信使。 這樣我們就可以在交易結束之前施放資源，或者釋放掉該游標變量，用於稍後再次打開。
一個例子：
CLOSE curs1;
PL/pgSQL 函數可以向調用者返回游標。 這個功能用於從函數里返回多行或多列。要想這麼做的時候， 該函數打開游標並且把該游標的名字返回給調用者。 調用者然後從游標裡FETCH行。 游標可以由調用者關閉，或者是在交易結束的時候自動關閉。
函數返回的游標名可以由調用者聲明或者自動生成。 要聲明一個信使的名字，只要再打開游標之前，給 refcursor 變量賦予一個字串就可以了。 refcursor 變量的字串值將被 OPEN 當作下層的信使的名字使用。 不過，如果 refcursor 變量是空，那麼 OPEN 將自動生成一個和現有信使不衝突的名字， 然後將它賦予 refcursor 變量。
注意: 一個綁定的游標變量其名字初始化為對應的字串值，因此信使的名字和游標變量名同名， 除非程序員再打開游標之前透過賦值覆蓋了這個名字。但是一個未綁定的游標變量初始化的時候預設是空， 因此它會收到一個自動生成的唯一的名字，除非被覆蓋。
下面的例子顯示了一個調用者聲明游標名字的方法：
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;
RETURN;
END;
$$ LANGUAGE plpgsql;
-- 需要在交易裡使用游標。
BEGIN;
SELECT * FROM myfunc('a', 'b');
FETCH ALL FROM a;
FETCH ALL FROM b;
COMMIT;