1、下面的代码会创建一个top100cur()函数,该函数返回一个匿名游标
- --drop function top100cur();
- create function top100cur() returns refcursor as $$
- declare
- abc refcursor;
- begin
- open abc for select * from person limit 100;
- return abc;
- end
- $$language plpgsql;
- SELECT top100cur();--返回匿名游标
- --fetch all from abc;--匿名游标,所以此行错误
- --drop function top100cur();
- create function top100cur() returns refcursor as $$
- declare
- abc cursor for select * from person limit 100;
- begin
- open abc;
- return abc;
- end
- $$language plpgsql;
- SELECT top100cur();--返回名字为abc的游标
- fetch all from abc;--此行正确,执行时,记得把此行与select行以前拖黑,然后F5
- --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 top100cur(refcursor);
- create function top100cur(refcursor) returns refcursor as $$
- declare
- $1 cursor for select * from person limit 100;
- begin
- open $1;--不open的话,返回的名字为"$1"游标不能使用!
- return $1;
- end
- $$language plpgsql;
- SELECT top100cur('abc');
- fetch all from "$1";
- --drop function top100cur(refcursor);
- create function top100cur(refcursor) returns refcursor as $$
- declare
- $1 refcursor;
- begin
- open $1 for select * from person limit 100;
- return $1;
- end
- $$language plpgsql;
- SELECT top100cur('abc');
- --fetch all from "$1";--匿名游标,所以此行错误
- --drop function top100cur(refcursor);
- create function top100cur(refcursor) returns refcursor as $$
- declare
- abcdef refcursor;
- begin
- open $1 for select * from person limit 100;
- return $1;
- end
- $$language plpgsql;
- SELECT top100cur('abc');
- fetch all from "abc";
7、直接open一个没有被declare,也没有被传入的游标变量
- --drop function top100cur(refcursor);
- create function top100cur(refcursor) returns refcursor as $$
- declare
- abcdef refcursor;
- begin
- --open defg for select * from person limit 100;--错误: "defg" 不是一个已知变量
- --return defg;-----------LINE 5: open defg for select * from person limit 100;
- end
- $$language plpgsql;
总结:
1、declare的变量,会导致函数参数被隐藏(如,declare $1后,则第一个匿名参数就被隐藏了);
2、declare只是声明一个游标,不会open游标,而没有open的游标是不能用的哦~
3、declare之后再open游标时,如果这个游标是个未绑定的(declare时没有cursor for XXX),那么open后得到的是一个匿名游标;
4、open操作的游标变量,要么是declare的,要么是当做参数传入的字符串;除此之外,报错!