1.使用

 1 create or replace package refcursor_pkg is 2  3 -- Author : mr.yang 4 -- Created : 5/14/2017 5:13:42 PM 5 -- Purpose :  6 type refcur_t is ref cursor return sys_product%rowtype; 7 type refcur_t1 is ref cursor /*return sys_product%rowtype*/ 8  ; 9 type refcur_t2 is ref cursor return base_member%rowtype;10 11 type outrec_typ is record(12 v_pid integer,13 v_pcode varchar(4000),14 v_pname varchar(4000));15 16 type outrecset is table of outrec_typ;17 function f_trans(p refcur_t) return outrecset18  pipelined;19 function f_trans1(p refcur_t1) return outrecset20  pipelined;21 function f_trans1(sp refcur_t1, bm refcur_t2) return outrecset22  pipelined;23 24 end refcursor_pkg;

2.body

 1 create or replace package body refcursor_pkg is 2 function f_trans(p refcur_t) return outrecset 3 pipelined as 4  out_rec outrec_typ; 5 in_rec p%rowtype; 6  7 begin 8  9  loop10 fetch p11 into in_rec;12 exit when p%notfound;13 out_rec.v_pid := in_rec.productid;14 out_rec.v_pcode := in_rec.productcode;15 out_rec.v_pname := in_rec.productname;16 17 pipe row(out_rec);18 end loop;19 close p;20 return;21  Exception22 when others then23 dbms_output.put_line(sqlcode || sqlerrm);24 end f_trans;25 26 function f_trans1(p refcur_t1) return outrecset27 pipelined as28  out_rec outrec_typ;29 in_rec sys_product%rowtype;30 begin31  loop32 fetch p33 into in_rec;34 exit when p%notfound;35 out_rec.v_pid := in_rec.productid;36 out_rec.v_pcode := in_rec.productcode;37 out_rec.v_pname := in_rec.productname;38 39 pipe row(out_rec);40 end loop;41 close p;42 return;43  Exception44 when others then45 dbms_output.put_line(sqlcode || sqlerrm);46 47 end f_trans1;48 ---------------------------------------------------------49 function f_trans1(sp refcur_t1, bm refcur_t2) return outrecset50 pipelined as51 52  out_rec outrec_typ;53 in_rec sys_product%rowtype;54 in_rec_bm bm%rowtype;55 begin56 57 58  loop59 fetch sp60 into in_rec;61 exit when sp%notfound;62 /* out_rec.v_pid := in_rec.productid;63  out_rec.v_pcode := in_rec.productcode;64  out_rec.v_pname := in_rec.productname;65  pipe row(out_rec);66  out_rec.v_pid := in_rec_bm.id;67  out_rec.v_pcode := in_rec_bm.cnfullname;68  out_rec.v_pname := in_rec_bm.pinyin;69  pipe row(out_rec);*/70 71 end loop;72 close sp;73 74  loop75 fetch bm76 into in_rec_bm;77 exit when bm%notfound;78 out_rec.v_pid := in_rec_bm.id;79 out_rec.v_pcode := in_rec_bm.cnfullname;80 out_rec.v_pname := in_rec_bm.pinyin;81 pipe row(out_rec);82 83 out_rec.v_pid := in_rec.productid;84 out_rec.v_pcode := in_rec.productcode;85 out_rec.v_pname := in_rec.productname;86 pipe row(out_rec); 87 end loop;88 close bm;89 90 return;91  Exception92 when others then93 dbms_output.put_line(sqlcode || sqlerrm);94 95 end f_trans1;96 97 end refcursor_pkg;

View Code

3.结果：