37.8. 遊標

如果不想一次執行整個命令,我們可以設置一個封裝該命令的 遊標,然後每次讀取幾行命令結果。 這麼幹的一個原因是在結果包含數量非常大的行時避免內存耗盡。 (當然,PL/pgSQL 用戶通常不必擔心這個, 因為 FOR 循環自動在內部使用一個遊標以避免內存問題。) 一個更有趣的用法是某個函數可以返回一個它創建的遊標的引用, 這樣就允許調用者讀取各行。這就提供了一種從函數返回一個結果集的手段。

37.8.1. 聲明遊標變量

所有在 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 可以稱之為未綁定的, 因為它沒有和任何查詢相綁定。

37.8.2. 打開遊標

在你使用遊標檢索行之前,你必需憲打開它。 (這是和 SQL 命令 DECLARE CURSOR 相等的操作。) PL/pgSQL 有三種形式的OPEN語句, 兩種用于未綁定的遊標變量,另外一種用于綁定的遊標變量。

37.8.2.1. OPEN FOR SELECT

OPEN unbound-cursor FOR SELECT ...;

該遊標變量打開,並且執行給出的查詢。遊標不能是已經打開的, 並且它必需是聲明為一個未綁定的遊標(也就事說,聲明為一個簡單的 refcursor 變量)。 SELECT 命令是和其它在 PL/pgSQL 裡的 SELECT 命令平等對待的: 先代換 PL/pgSQL 的變量名,而且執行計劃為將來可能的復用緩存起來。

一個例子:

OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;

37.8.2.2. OPEN FOR EXECUTE

OPEN unbound-cursor FOR EXECUTE query-string;

打開遊標變量並且執行給出的查詢。遊標不能是已打開的,並且必須聲明為一個未綁定的遊標(也就是說, 是一個簡單的 refcursor 變量)。命令是用和那些用于 EXECUTE 命令一樣的方法聲明的字串表達式, 這樣,我們就有了命令可以在兩次運行間發生變化的靈活性。

一個例子:

OPEN curs1 FOR EXECUTE ''SELECT * FROM '' || quote_ident($1);

37.8.2.3. 打開一個綁定的遊標

OPEN bound-cursor [ ( argument_values ) ];

這種形式的OPEN用于打開一個遊標變量,該遊標變量的命令是在聲明的時候和它綁定在一起的。 遊標不能是已經打開的。 當且僅當該遊標聲明為接受參數的時候,語句中才必需出現一個實際參數值表達式的列表。 這些值將代換到命令中。一個綁定的遊標的命令計劃總是認為可緩衝的 -- 這種情況下沒有等效的EXECUTE

例子:

OPEN curs2;
OPEN curs3(42);

37.8.3. 使用遊標

一旦你已經打開了一個遊標,那麼你就可以用這裡描述的語句操作它。

這些操作不需要發生在和打開該遊標開始操作的同一個函數裡。 你可以從函數裡返回一個 refcursor 數值,然後讓調用者操作該遊標。 (在內部,refcursor 值只是一個包含該遊標命令的活躍查詢的信使的字串名。 這個名字可以傳來傳去,可以賦予其它 refcursor 變量等等,也不用擔心擾亂信使。)

所有信使在事務的結尾都會隱含地關閉。因此一個 refcursor 值只能在該事務結束前用于引用一個打開的遊標。

37.8.3.1. FETCH

FETCH cursor INTO target;

FETCH從遊標中檢索下一行到目標中,目標可以是一個行變量,一個記錄變量, 或者是一個逗號分隔的普通變量的列表,就象SELECT INTO裡一樣。 和SELECT INTO一樣,你可以使用特殊變量FOUND檢查是否檢索出一個行。

一個例子:

FETCH curs1 INTO rowvar;
FETCH curs2 INTO foo, bar, baz;

37.8.3.2. CLOSE

CLOSE cursor;

CLOSE關閉支撐在一個打開的遊標下面的信使。 這樣我們就可以在事務結束之前施放資源,或者釋放掉該遊標變量,用于稍後再次打開。

一個例子:

CLOSE curs1;

37.8.3.3. 返回遊標

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;