使用 oracle pipelined 返回一個結果集；

標籤：rpo   splay   font   image   open   put   opened   ret   使用   

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.結果：

 

 

使用 oracle pipelined 返回一個結果集；