PostgreSQL遊标示例(建立遊标,并在函數中周遊之)
--drop function top100cur(refcursor);
create function top100cur(refcursor) returns refcursor as $$
begin
open $1 for select * from person limit 100;
return $1;
end
$$language plpgsql;
----------測試遊标---------
-- SELECT top100cur('abc');
-- fetch all from abc;
-- drop function from2cur(refcursor,int,int);
--這是一個傳回遊标中在一定範圍内記錄的函數--
create function from2cur(refcursor,int,int)returns setof text as $$
declare--聲明一些下标變量
pnam text;
pno text;
index int;
lower int;
upper int;
begin
index:=1;
lower:=$2;
upper:=$3;
fetch $1 into pnam,pno;--必須先fetch一條,否則found為false
while found loop
--隻在[lower,upper]區間的記錄才傳回--
if lower<=index and upper>=index then
return next pnam||pno;
end if;
fetch $1 into pnam,pno;
index:=index+1;
--超過upper後,函數傳回--
if index>upper then
return;
end if;
end loop;
end
$$language plpgsql;
select top100cur('abc');--建立一個名字為abc的遊标
-- fetch all in abc;--測試遊标
select * from from2cur('abc',2,5);
本文出自:億恩科技【www.enkj.com】