如何用 refcursor 返回结果集

可以通过返回 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;