Returns a result set using Oracle pipelined;

1. Use

1 Create or ReplacePackage refcursor_pkg is2 3   --Author:mr.yang4   --created:5/14/2017 5:13:42 PM5   --Purpose:6Type refcur_t isRefcursor returnSys_product%RowType;7Type Refcur_t1 isRefcursor /*return Sys_product%rowtype*/8   ;9Type Refcur_t2 isRefcursor returnBase_member%RowType;Ten  OneType Outrec_typ isRecord ( AV_pidinteger, -V_pcodevarchar(4000), -V_pnamevarchar(4000)); the  -Type Outrecset is Table  ofOutrec_typ; -   functionF_trans (P refcur_t)returnOutrecset - pipelined; +   functionF_TRANS1 (P refcur_t1)returnOutrecset - pipelined; +   functionF_TRANS1 (SP REFCUR_T1, BM Refcur_t2)returnOutrecset A pipelined; at  - Endrefcursor_pkg;

2.body

1 Create or ReplacePackage Body Refcursor_pkg is2   functionF_trans (P refcur_t)returnOutrecset3pipelined as4 Out_rec Outrec_typ;5In_rec P%RowType;6   7   begin8   9 LoopTen       FetchP One          intoIn_rec; A       Exit  whenP%NotFound; -Out_rec.v_pid:=In_rec.productid; -Out_rec.v_pcode:=In_rec.productcode; theOut_rec.v_pname:=In_rec.productname; -      -       Piperow (OUT_REC); -     EndLoop; +     Closep; -     return; + Exception A      whenOthers Then atDbms_output.put_line (Sqlcode||sqlerrm); -   EndF_trans; -  -   functionF_TRANS1 (P refcur_t1)returnOutrecset -pipelined as - Out_rec Outrec_typ; inIn_rec sys_product%RowType; -   begin to Loop +       FetchP -          intoIn_rec; the       Exit  whenP%NotFound; *Out_rec.v_pid:=In_rec.productid; $Out_rec.v_pcode:=In_rec.productcode;Panax NotoginsengOut_rec.v_pname:=In_rec.productname; -      the       Piperow (OUT_REC); +     EndLoop; A     Closep; the     return; + Exception -      whenOthers Then $Dbms_output.put_line (Sqlcode||sqlerrm); $      -   Endf_trans1; -   --------------------------------------------------------- the   functionF_TRANS1 (SP REFCUR_T1, BM Refcur_t2)returnOutrecset -pipelined asWuyi    the Out_rec Outrec_typ; -In_rec sys_product%RowType; WuIN_REC_BM BM%RowType; -   begin About    $    - Loop -       FetchSP -          intoIn_rec; A       Exit  whenSp%NotFound; +       /*out_rec.v_pid: = In_rec.productid; the Out_rec.v_pcode: = In_rec.productcode; - out_rec.v_pname: = In_rec.productname; $ Pipe Row (OUT_REC); the out_rec.v_pid: = in_rec_bm.id; the Out_rec.v_pcode: = In_rec_bm.cnfullname; the out_rec.v_pname: = In_rec_bm.pinyin; the Pipe Row (OUT_REC);*/ -      in     EndLoop; the     Closesp; the    About Loop the       FetchBM the          intoIN_REC_BM; the       Exit  whenBm%NotFound; +Out_rec.v_pid:=in_rec_bm.id; -Out_rec.v_pcode:=In_rec_bm.cnfullname; theOut_rec.v_pname:=In_rec_bm.pinyin;Bayi       Piperow (OUT_REC); the        theOut_rec.v_pid:=In_rec.productid; -Out_rec.v_pcode:=In_rec.productcode; -Out_rec.v_pname:=In_rec.productname; the       Piperow (OUT_REC);  the     EndLoop; the     CloseBM; the    -     return; the Exception the      whenOthers Then theDbms_output.put_line (Sqlcode||sqlerrm);94      the   Endf_trans1; the  the Endrefcursor_pkg;

View Code

3. Results:

Returns a result set using Oracle pipelined;