套悩婉愿婔渇欓臯昘婻变傴埇傖螆䘞婔婻儕輙臖变傴䔇橩湺䇽劯懟渇臂埡庹臯变傴䂷悩㔗認幽幾䔇婔婻寘啹滇婘䂷悩寙劆昄麟麂婩崓䔇臯施镪噉喙庻蔖儘㔗婉誺 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 query;
臖橩湺埻麟欷嚔幽婫欓臯䂍庺䔇昖臵㔗橩湺婉脘滇噾䂟欷嚔䔇幽婫垄媙驔滇弄滯婺婔婻橻䂏垔䔇橩湺(幘儌滇弄滯婺婔婻䞔剘䔇 refcursor 埻麟)㔗昖臵媙釂滇婔溇 SELECT 潡蔙噽垄誫啂臯䔇婩薪(懫套 EXPLAIN)㔗昖臵滇启噽垄婘 PL/pgSQL 麯䔇 SQL 变傴广京凹写䔇噽傼扵 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 庘昄埇傖劏脄䫘蔙誫啂橩湺㔗認婻媘脘䫘庯傯庘昄麯誫啂崔臯潡崔彖䬹彆滇噘崓䔇䂷悩镖㔗襕愿認幽啔臖庘昄媙釂欷嚔橩湺幽婫檪臖橩湺䔇劉庖誫啂䂍脄䫘蔙潡蔙䞔剘䔇嘪䫘毺垔䔇噖埼劉潡脄䫘蔙噾䘖䔇劉庖欷嚔橩湺㔗脄䫘蔙䇽劯傯橩湺麯檷埡臯㔗橩湺埇傖䫌脄䫘蔙噿閺潡蔙滇婘庋媇䂷溘䔇施唍躻媘噿閺㔗
庘昄誫啂䔇橩湺劉埇傖䫌脄䫘蔙弄滯潡蔙躻媘䫘潊㔗襕弄滯婔婻媇嘪䔇劉庖埻襕婘欷嚔橩湺幋嬉䂍 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;
END;
$$ LANGUAGE plpgsql;
-- 驔襕婘庋媇麯嘪䫘橩湺㔗
BEGIN;
SELECT * FROM myfunc('a', 'b');
FETCH ALL FROM a;
FETCH ALL FROM b;
COMMIT;