?
本文檔使用 php中文網(wǎng)手冊(cè) 發(fā)布
39.7. 游標(biāo)
如果不想一次執(zhí)行整個(gè)命令，可以設(shè)置一個(gè)封裝該命令的游標(biāo)（cursor），然后每次讀取幾行命令結(jié)果。 這么干的一個(gè)原因是在結(jié)果包含數(shù)量非常大的行時(shí)避免內(nèi)存耗盡。 不過(guò)PL/pgSQL用戶不必?fù)?dān)心這個(gè)，因?yàn)?tt class="LITERAL">FOR循環(huán)自動(dòng)在內(nèi)部使用一個(gè)游標(biāo)以避免內(nèi)存問(wèn)題。 一個(gè)更有趣的用法是某個(gè)函數(shù)可以返回一個(gè)它創(chuàng)建的游標(biāo)的引用，這樣就允許調(diào)用者讀取各行。 從而提供了一種從函數(shù)返回一個(gè)結(jié)果集的手段。
39.7.1. 聲明游標(biāo)變量
所有在PL/pgSQL里對(duì)游標(biāo)的訪問(wèn)都是通過(guò)游標(biāo)變量實(shí)現(xiàn)的，它總是特殊的數(shù)據(jù)類(lèi)型refcursor。 創(chuàng)建游標(biāo)變量的一個(gè)方法是把它聲明為一個(gè)類(lèi)型為refcursor的變量。 另外一個(gè)方法是使用游標(biāo)聲明語(yǔ)法，像下面這樣：
name [ [ NO ] SCROLL ] CURSOR [ ( arguments ) ] FOR query;
（Oracle兼容性中FOR可以用IS替代） 如果定義了SCROLL，那么游標(biāo)可以向后滾動(dòng)；如果定義了NO SCROLL，那么向后取的動(dòng)作會(huì)被拒絕； 如果二者都沒(méi)有定義，那么是否進(jìn)行向后取的動(dòng)作會(huì)根據(jù)查詢來(lái)判斷。 如果有arguments， 那么它是一個(gè)逗號(hào)分隔name datatype列表， 這個(gè)列表定義由已給查詢中的參數(shù)值來(lái)替代的name。 實(shí)際用于代換這些名字的數(shù)值將在在游標(biāo)打開(kāi)之后聲明。
例如：
DECLARE
curs1 refcursor;
curs2 CURSOR FOR SELECT * FROM tenk1;
curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;
所有這三個(gè)變量都是refcursor類(lèi)型，但是第一個(gè)可以用于任何命令， 而第二個(gè)已經(jīng)綁定（bound）了一個(gè)聲明完整的命令，最后一個(gè)是綁定了一個(gè)帶參數(shù)的命令。 key將在游標(biāo)打開(kāi)的時(shí)候被代換成一個(gè)整數(shù)。 變量curs1可以稱(chēng)之為未綁定的，因?yàn)樗鼪](méi)有和任何查詢相綁定。
39.7.2. 打開(kāi)游標(biāo)
在你使用游標(biāo)檢索行之前，你必需先打開(kāi)它。 這是和SQL命令DECLARE CURSOR相等的操作。 PL/pgSQL有三種形式的OPEN語(yǔ)句，兩種用于未綁定的游標(biāo)變量，另外一種用于已綁定的游標(biāo)變量。
Note: 可以通過(guò)Section 39.7.4中描述的FOR語(yǔ)句，在不用打開(kāi)游標(biāo)的情況下使用已綁定的游標(biāo)
39.7.2.1. OPEN FOR query
OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR query;
該游標(biāo)變量打開(kāi)并且執(zhí)行給出的查詢。 游標(biāo)不能是已經(jīng)打開(kāi)的，并且它必需是聲明為一個(gè)未綁定的游標(biāo)(也就是聲明為一個(gè)簡(jiǎn)單的refcursor變量)。 查詢必須是一條SELECT或者其它返回行的東西(比如EXPLAIN)。 查詢是和其它在PL/pgSQL里的SQL命令平等對(duì)待的：先代換PL/pgSQL的變量名，而且執(zhí)行計(jì)劃為將來(lái)可能的復(fù)用緩存起來(lái)。 當(dāng)一個(gè)PL/pgSQL變量被替換到游標(biāo)查詢中時(shí)，被替換的值是在OPEN時(shí)它所具有的值。 后續(xù)的改變不會(huì)影響游標(biāo)的動(dòng)作，對(duì)于一個(gè)已經(jīng)綁定的游標(biāo)來(lái)說(shuō)，SCROLL和NO SCROLL這兩個(gè)選項(xiàng)具有相同的含義。
一個(gè)例子：
OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
39.7.2.2. OPEN FOR EXECUTEOPEN FOR EXECUTE
OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR EXECUTE query_string [ USING expression [, ... ] ];
打開(kāi)游標(biāo)變量并且執(zhí)行給出的查詢。 游標(biāo)不能是已打開(kāi)的，并且必須聲明為一個(gè)未綁定的游標(biāo)(也就是一個(gè)簡(jiǎn)單的refcursor變量)。 命令是用和那些用于EXECUTE命令一樣的方法聲明的字符串表達(dá)式， 這樣，就有了命令可以在兩次運(yùn)行間發(fā)生變化的靈活性。參閱Section 39.10.2） 這也意味著在命令字符串上不能進(jìn)行變量替換。跟EXECUTE一起，通過(guò)使用USING，參數(shù)值可以被插入到動(dòng)態(tài)命令中。 對(duì)于一個(gè)已經(jīng)綁定的游標(biāo)來(lái)說(shuō)，SCROLL和NO SCROLL這兩個(gè)選項(xiàng)具有相同的含義
一個(gè)例子：
OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident(tabname)
|| ' WHERE col1 = $1' USING keyvalue;
在這個(gè)例子中，表名被插入到文本查詢中，因此使用quote_ident()時(shí)要注意SQL injection。
通過(guò)USING參數(shù)col1比較，因此不需要使用引號(hào)。
39.7.2.3. 打開(kāi)一個(gè)綁定的游標(biāo)
OPEN bound_cursorvar [ ( argument_values ) ];
這種形式的OPEN用于打開(kāi)一個(gè)游標(biāo)變量，該游標(biāo)變量的命令是在聲明的時(shí)候和它綁定在一起的。 游標(biāo)不能是已經(jīng)打開(kāi)的。 當(dāng)且僅當(dāng)該游標(biāo)聲明為接受參數(shù)的時(shí)候，語(yǔ)句中才必需出現(xiàn)一個(gè)實(shí)際參數(shù)值表達(dá)式的列表。 這些值將代換到命令中。 一個(gè)綁定的游標(biāo)的命令計(jì)劃總是認(rèn)為可緩沖的，這種情況下沒(méi)有等效的EXECUTE。 需要注意的是SCROLL和NO SCROLL不能被聲明，因?yàn)橛螛?biāo)的滾動(dòng)動(dòng)作已經(jīng)被定義了。
因?yàn)樵诒唤壎ǖ挠螛?biāo)查詢上已經(jīng)執(zhí)行了變量替換，因此有兩種方式可以將變量值傳遞到有表上： 要么是OPEN使用明確的參數(shù)，要么是使用隱式的參數(shù)（該參數(shù)要指向插敘中的PL/pgSQL變量）。 然而，只有在綁定的游標(biāo)（已聲明）之前聲明的變量才能替換進(jìn)去。 任何情況下，被傳遞的值是在OPEN時(shí)決定。
例如：
OPEN curs2; OPEN curs3(42);
39.7.3. 使用游標(biāo)
一旦你已經(jīng)打開(kāi)了一個(gè)游標(biāo)，那么你就可以用這里描述的語(yǔ)句操作它。
這些操作不需要發(fā)生在和打開(kāi)該游標(biāo)開(kāi)始操作的同一個(gè)函數(shù)里。 你可以從函數(shù)里返回一個(gè)refcursor值，然后讓調(diào)用者操作該游標(biāo)。 在內(nèi)部，refcursor值只是一個(gè)包含該游標(biāo)命令的活躍查詢的信使的字符串名。 這個(gè)名字可以傳來(lái)傳去，可以賦予其它refcursor變量等等，也不用擔(dān)心擾亂信使。
所有信使在事務(wù)的結(jié)尾都會(huì)隱含地關(guān)閉。 因此一個(gè)refcursor值只能在該事務(wù)結(jié)束前用于引用一個(gè)打開(kāi)的游標(biāo)。
39.7.3.1. FETCH
FETCH [ direction { FROM | IN } ] cursor INTO target;
FETCH從游標(biāo)中檢索下一行到目標(biāo)中， 目標(biāo)可以是一個(gè)行變量、記錄變量、逗號(hào)分隔的普通變量列表，就像 SELECT INTO 里一樣，如同SELECT INTO， 如果下一行中沒(méi)有，目標(biāo)會(huì)設(shè)為NULL。 如同使用FETCH一樣，可以使用特殊變量FOUND來(lái)檢查該行是否符合。
direction字句可以是任何一個(gè)SQLFETCH命令允許的變形，除了那些可以抓取不止一行的； 形如： NEXT, PRIOR, FIRST, LAST, ABSOLUTE count, RELATIVE count, FORWARD, or BACKWARD. Omitting direction is the same as specifying NEXT. direction values that require moving backward are likely to fail unless the cursor was declared or opened with the SCROLL option.
cursor 必須是一個(gè)指向一個(gè)打開(kāi)的游標(biāo)的refcursor變量的名字。
一個(gè)例子：
FETCH curs1 INTO rowvar; FETCH curs2 INTO foo, bar, baz; FETCH LAST FROM curs3 INTO x, y; FETCH RELATIVE -2 FROM curs4 INTO x;
39.7.3.2. MOVE
MOVE [ direction { FROM | IN } ] cursor;
MOVE重新定位一個(gè)游標(biāo)，而不需要檢索任何數(shù)據(jù)。 MOVE的工作方式與FETCH及其相似， 除了MOVE只是重新定位游標(biāo)并且不返回至移動(dòng)到的行出。 在進(jìn)行SELECT INTO命令時(shí)，聲明的FOUND變量可以用來(lái)檢查下一個(gè)需要移動(dòng)到的行是否存在。
Tdirection可以是任何一個(gè)SQL FETCH命令允許的變形，如下： NEXT, PRIOR, FIRST, LAST, ABSOLUTE count, RELATIVE count, ALL, FORWARD [ count | ALL ], or BACKWARD [ count | ALL ]. Omitting direction is the same as specifying NEXT. direction values that require moving backward are likely to fail unless the cursor was declared or opened with the SCROLL option.
例如：
MOVE curs1; MOVE LAST FROM curs3; MOVE RELATIVE -2 FROM curs4; MOVE FORWARD 2 FROM curs4;
39.7.3.3. UPDATE/DELETE WHERE CURRENT OF
UPDATE table SET ... WHERE CURRENT OF cursor; DELETE FROM table WHERE CURRENT OF cursor;
當(dāng)一個(gè)游標(biāo)被定位到一個(gè)表的行上，那么通過(guò)使用該游標(biāo)來(lái)識(shí)別該行，從而進(jìn)行更新或刪除操作。 當(dāng)然，對(duì)于如何定義游標(biāo)查詢（特別是沒(méi)有分組時(shí)）是存在一定限制的；在游標(biāo)中使用FOR UPDATE是個(gè)不錯(cuò)的主意。 更多信息可參閱DECLARE。
例如：
UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;
39.7.3.4. CLOSE
CLOSE cursor;
CLOSE關(guān)閉支撐在一個(gè)打開(kāi)的游標(biāo)下面的信使。 這樣就可以在事務(wù)結(jié)束之前釋放資源，或者釋放掉該游標(biāo)變量，用于稍后再次打開(kāi)。
一個(gè)例子：
CLOSE curs1;
39.7.3.5. 返回游標(biāo)
PL/pgSQL函數(shù)可以向調(diào)用者返回游標(biāo) 這個(gè)功能用于從函數(shù)里返回多行或多列，特別是巨大的結(jié)果集。 要想這么做，該函數(shù)必須打開(kāi)游標(biāo)并且把該游標(biāo)的名字返回給調(diào)用者， 或者簡(jiǎn)單的使用指定的入口名或調(diào)用者已知的名字打開(kāi)游標(biāo)。 調(diào)用者然后從游標(biāo)里抓取行。 游標(biāo)可以由調(diào)用者關(guān)閉，或者是在事務(wù)結(jié)束的時(shí)候自動(dòng)關(guān)閉。
函數(shù)返回的游標(biāo)名可以由調(diào)用者聲明或者自動(dòng)生成。 要聲明一個(gè)信使的名字，只要在打開(kāi)游標(biāo)之前，給refcursor變量賦予一個(gè)字符串就可以了。 refcursor變量的字符串值將被OPEN當(dāng)作下層的信使的名字使用。 不過(guò)，如果refcursor變量是空，那么OPEN將自動(dòng)生成一個(gè)和現(xiàn)有信使不沖突的名字， 然后將它賦予refcursor變量。
Note: 一個(gè)綁定的游標(biāo)變量其名字初始化為對(duì)應(yīng)的字符串值， 因此信使的名字和游標(biāo)變量名同名，除非程序員在打開(kāi)游標(biāo)之前通過(guò)賦值覆蓋了這個(gè)名字。 但是一個(gè)未綁定的游標(biāo)變量初始化的時(shí)候缺省是空， 因此它會(huì)收到一個(gè)自動(dòng)生成的唯一名字，除非被覆蓋。
下面的例子顯示了一個(gè)調(diào)用者聲明游標(biāo)名字的方法：
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;
下面的例子使用了自動(dòng)生成的游標(biāo)名：
CREATE FUNCTION reffunc2() RETURNS refcursor AS '
DECLARE
ref refcursor;
BEGIN
OPEN ref FOR SELECT col FROM test;
RETURN ref;
END;
' LANGUAGE plpgsql;
-- need to be in a transaction to use cursors.
BEGIN;
SELECT reffunc2();
reffunc2
--------------------
<unnamed cursor 1>
(1 row)
FETCH ALL IN "<unnamed cursor 1>";
COMMIT;
下面的例子顯示了從一個(gè)函數(shù)里返回多個(gè)游標(biāo)的方法：
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;
-- 需要在事務(wù)里使用游標(biāo)
BEGIN;
SELECT * FROM myfunc('a', 'b');
FETCH ALL FROM a;
FETCH ALL FROM b;
COMMIT;
39.7.4. 通過(guò)游標(biāo)結(jié)果進(jìn)行循環(huán)t
有這么一個(gè)FOR語(yǔ)法的變形，它允許通過(guò)游標(biāo)返回的行進(jìn)行迭代。如下：
[ <<label>> ] FOR recordvar IN bound_cursorvar [ ( argument_values ) ] LOOP statements END LOOP [ label ];
在聲明游標(biāo)變量時(shí)，它必須已經(jīng)綁定到一些查詢語(yǔ)句上，并且不能是打開(kāi)狀態(tài)。 FOR語(yǔ)法會(huì)自動(dòng)打開(kāi)游標(biāo)，并且當(dāng)退出循環(huán)時(shí)自動(dòng)關(guān)閉游標(biāo)。 只有當(dāng)游標(biāo)被聲明要使用參數(shù)時(shí)，必須有一列實(shí)際參數(shù)值表達(dá)式。 這些值會(huì)被替換到查詢中，采用如同OPEN的方式。 recordvar變量會(huì)自動(dòng)定義為record類(lèi)型，并且只存在于循環(huán)中（循環(huán)中任何的定義變量名的動(dòng)作都會(huì)被忽略）。 每一個(gè)由游標(biāo)返回的行都會(huì)陸續(xù)的被分配到記錄變量中，然后執(zhí)行循環(huán)體。