Oracle returns result set

New project start want to use stored procedures to return the result set, the company often write, this only a few years to forget the almost, cost the eldest brother to do well. Leave a print for future review ...

Oracle Returns a result set that needs to be used to the package, and the result set is returned as a cursor type (cursor). And the returned result set cannot take advantage of the EXEC store in PL/SQL

Procedure name () See result set (fuzzy remember is can but not implement, through function implementation is see a cursor result set, click is can see result set, perhaps is mistaken,

Expect to have a friend answer to know).

case code to implement the paging stored procedure .

Fn_page through functions to achieve paging, PL/SQL execution can see the result set, but the function is not flexible;

Sp_page stored procedure implementation.

Definition of Baotou

Fn_page

1 CREATE OR REPLACEPackage Pkg_test2  as3Type Refcursortype isREFCURSOR;4 5 functionFn_page6(p_pagesizeint,--number of records per page7P_pagenoint,--Current page number, starting from 18P_sqlselectvarchar2) 9  returnRefcursortype;Ten  One PROCEDURESp_page A(p_pagesizeint,--number of records per page -P_pagenoint,--Current page number, starting from 1 -P_sqlselectvarchar2,--query statements, with sort parts theP_outrecordcount outint,--returns the total number of records -P_outcursor out Refcursortype);--procedure in the package named get (only interface not content) -  -  + ENDPkg_test;

Inclusion

1 Create or ReplacePackage Body Pkg_test2  as procedureSp_page (p_pagesizeint,--number of records per page3P_pagenoint,--Current page number, starting from 14P_sqlselectvarchar2,--query statements, with sort parts5P_outrecordcount outint,--returns the total number of records6 p_outcursor out Refcursortype)7  is8V_sqlvarchar2( the);9V_countint;TenV_heirownumint; OneV_lowrownumint; A  - begin - ----Fetch the total number of records theV_sql:= 'Select COUNT (*) from (' ||P_sqlselect|| ')'; - ExecuteImmediate V_sql intoV_count; -P_outrecordcount:=V_count; - ----Perform paged query +V_heirownum:=P_pageno*p_pagesize; -V_lowrownum:=V_heirownum-P_pagesize+ 1; +  AV_sql:= 'SELECT * at From ( - SELECT a.*, rownum RN - From ('||P_sqlselect||') A - WHERE rownum <='||To_char (V_heirownum)|| ' - ) B - WHERE RN >=' ||To_char (v_lowrownum); in --note the use of rownum aliases, the first direct use of rownum, the second must use the alias RN -  to OPENP_outcursor forV_sql; +  - EndSp_page; the  * ------------------------------------------------------------------------------- $ functionFn_pagePanax Notoginseng(p_pagesizeint,--number of records per page -P_pagenoint,--Current page number, starting from 1 theP_sqlselectvarchar2)  + returnRefcursortype A  isAllinfo Refcursortype; theV_sqlvarchar2( the); + --V_count int; -V_heirownumint; $V_lowrownumint; $ begin -V_heirownum:=P_pageno*p_pagesize; -V_lowrownum:=V_heirownum-P_pagesize+ 1; the  -V_sql:= 'SELECT *Wuyi From ( the SELECT a.*, rownum RN - From ('||P_sqlselect||') A Wu WHERE rownum <='||To_char (V_heirownum)|| ' - ) B About WHERE RN >=' ||To_char (v_lowrownum); $ --note the use of rownum aliases, the first direct use of rownum, the second must use the alias RN -  - OPENAllinfo forV_sql; -  --Open Allinfo for select Id,name from test where rownum<40; A   returnAllinfo; + EndFn_page; the EndPkg_test;

Calls can be invoked using call, Exec\execute, begin ... end; The way.

Oracle returns result set