PostgreSQL游标示例(创建游标,并在函数中遍历之)
PostgreSQL游标示例(创建游标,并在函数中遍历之)
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);
评论暂时关闭