可以通过返回 Refcursor 类型的函数,或者out 类型的函数或 procedure 返回结果集。
一、返回refcursor 类型的函数
create or replace function f_get_set(refcursor,refcursor)
returns setof refcursor
as $$
begin
open $1 for select * from t1;
return next $1;
open $2 for select * from t1;
return next $2;
end;
$$ language plsql;
begin;
select * from get_set('a'::refcursor,'b'::refcursor);
fetch all from a;
end
二、通过OUT类型的函数参数
create or replace function func_get_set_out(out cur_a refcursor,out cur_b refcursor)
as $$
begin
open cur_a for select * from t1;
open cur_b for select * from t1;
end;
$$ language plpgsql;
declare
v_ref_cur1 refcursor;
v_ref_cur2 refcursor;
v_t1 t1%rowtype;
begin
select * from get_set_out() into v_ref_cur1,v_ref_cur2;
loop
fetch v_ref_cur1 into v_t1;
exit when v_ref_cur1%NOTFOUND;
raise notice '%',v_t1.oid;
end loop;
loop
fetch v_ref_cur2 into v_t1;
exit when v_ref_cur2%NOTFOUND;
raise notice '%',v_t1.oid;
end loop;
end;
三、通过OUT类型的过程参数
create or replace procedure proc_get_set_out(inout cur_a refcursor,inout cur_b refcursor)
as $$
begin
open cur_a for select * from t1;
open cur_b for select * from t1;
end;
$$ language plpgsql;
declare
v_ref_cur1 refcursor;
v_ref_cur2 refcursor;
v_t1 t1%rowtype;
begin
call proc_get_set_out(v_ref_cur1,v_ref_cur2);
loop
fetch v_ref_cur1 into v_t1;
exit when v_ref_cur1%NOTFOUND;
raise notice '%',v_t1.oid;
end loop;
loop
fetch v_ref_cur2 into v_t1;
exit when v_ref_cur2%NOTFOUND;
raise notice '%',v_t1.oid;
end loop;
end;